Parameter Query (97 VR1)

On the database (my first) I'm working on , I have a query that gives me each customer and the type of battery he uses and the date they purchased the battery. I have created a calulated field to tell me when the next battery is due. I used a function to do this:
<pre> Function FirstOfMonth(InputDate As Date, intCycle As Integer)
Dim D As Integer, M As Integer, Y As Integer

This works just fine, but the trouble happens when I want to run the query for a specified "Battery Due Date" If I type that request in the criteria line I get Error 3071 Expression is typed incorectly or is too complex to be evaluated. I've tried many different ways, but I know dates are tricky to work with. I would greatly appreciate any help I can get.
Thanks!
PFI

Re: Parameter Query (97 VR1)

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.

Re: Parameter Query (97 VR1)

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.

Re: Parameter Query (97 VR1)

I tried your suggestions, but still get the same error.
I have been working around this problem by storing the calculated date, and then there is no problem using the due date as a parameter. I no storing calculated data is a no no but so far that's all I've got.
Any more suggestions would be greatly appreciated.
Thank You

Re: Parameter Query (97 VR1)

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

Re: Parameter Query (97 VR1)

You might be on to something. I created a new database and used my function to determine the first of the month, and I had no trouble using that field as a parameter. When I cut and pasted some dates from the real data it gave me an error. The data I'm using was data I imported from an old QA4 database. When I pasted some of the dates into my practice database I noticed them comming in as times and when I set the Date/Time to "short date" the time disapeared, but maybe it needs to be stripped from the date. I didn't have time to check that out.