Parameter Query (97 VR1)

Topic

New Reply

Sorry, but you’ll have to post the SQL for the query before anyone can figure out why you get an error.

Reply | Quote

Replies

Try this instead. I aliased to two tables to simplify reading the code and to eliminate the need for all those brackets around the table names. Plus alias seem to make the query more efficient. The In operator is also a lot faster than the individual conditions joined by Or.

I’ve seen the “too complex” error before when you use a calculated expression in a where clause without referencing the specific table the field belongs to.

SELECT C.Status, B.[1 Cycle], B.[Batt Date], C.[Cust #], B.[Batt Prepay],
B.BattType, B.NumberofBatteries, [Batt Prepay]-[NumberofBatteries] AS Remaining,
FirstOfMonth([Batt Date],[1 Cycle]) AS BatteryDueDate
FROM [QryCustomerName&Address] AS C
INNER JOIN QryBatteryPlan1 AS P
ON C.[Cust #] = P.[Cust #]
WHERE C.Status In (“Current” , “REFERRAL”, “VET”, “Vet Staff”, “REF.”, “REF..”, “PITA”)
AND B.[Batt Prepay]-B.[NumberofBatteries]>=1
AND FirstOfMonth(B.[Batt Date],[1 Cycle])=[Enter Battery Due Date:];

Reply | Quote

Aliasing of tables in SQL is just like aliasing a field or an expression. When you have “[Batt Prepay]-[NumberofBatteries] AS Remaining” in your SQL, you’re creating an alias, “Remaining” for the expression “[Batt Prepay]-[NumberofBatteries] “. You alias tables the same way. The alias gives you a handy label to use when referring to the table or expression.

You can do this easily in the query grid itself by opening the properties sheet for one of the tables and changing the alias from the actual table or query name to whatever you want to call it. I usually use 1-letter aliases to keep the SQL more readable. When you do it this way, the query engine will change the SQL to use the new alias instead of the actual table name except in the FROM clause where it *must* know the actual name of the table or query that’s being used.

Try creating a new query using the SQL I posted and see whether that will run. If it works, then just replace the old query with that one.

Reply | Quote

Post the SQL as it appears when you add the criteria. Otherwise, there’s no way to tell what’s wrong.

Reply | Quote

I think you may be still having reference problems. You should always fully reference the fields in the Where conditions. Try this:

SELECT B.Status, A.[1 Cycle], A.[Batt Date], B.[Cust #], A.[Batt Prepay], A.BattType, A.NumberofBatteries, A.[Batt Prepay]-A.[NumberofBatteries] AS Remaining, FirstOfMonth(A.[Batt Date],[1 Cycle]) AS BatteryDueDate
FROM [QryCustomerName&Address] AS B INNER JOIN QryBatteryPlan1 AS A ON B.[Cust #] = A.[Cust #]
WHERE (((B.Status) In (“Current”,”REFERRAL”,”VET”,”Vet Staff”,”REF.”,”REF..”,”PITA”)) AND ((FirstOfMonth(A.[Batt Date],[1 Cycle]))=[Enter Battery Due Date:]) AND ((A.[Batt Prepay]-A.[NumberofBatteries])>=1));

Reply | Quote

I just recently got a variety of error messages including the “Expression is typed incorectly or is too complex ” message involving a date and a parameter (in Access 97). After tracking down a couple of KB that might possibly relate to the problem and tearing my hair out, I finally discovered the problem — BAD DATA. I had tested my query with my test data, but as soon as I imported the _real_ data, the holes in the data appeared. You might look for null data in the field that you are creating the calculated field in your query.
Pat

Reply | Quote