Data type mismatch error

[cpberg1]

I'm attempting to create a query to show an expiration date of an authorization for my employees. The authorization expiration is based upon their age at the time they were given the authorization, 5 years if under 40 at the time of authorization, 2 years if 40 or older. It is also valid until the end of the calendar month which it expires.

I've created the query from a table:employees with fields; employees!lastname, employees!firstname, employees!DateofBirth, employees!AuthorizationDate.

I used a DateDiff function to create AgeAtAuthorization by comparing DateofBirth and Authorization Date.

Next I used an IIF to return the AuthorizationExpirationDate based on the AgeAtAuthorization and AuthorizationDate. I also used a SerialDate calculation to return the end of the calendar month value for the AuthorizationExpirationDate.

Query works great and I get the correct ExpirationDates for all the employees. Now I'd like to limit the results by query or what shows on my ExpirationReport. When I try to limit the AuthorizationExpirationDate column on the query to >Now()+30 it prompts for an AgeAtAuthorization parameter.

If I try to write a second query with AuthorizationExpirationDate column critera >Now()+30 I get the data type mismatch in criteria expression.

I suspect my problem is the Serial Date calculation in the middle of the query is incompatible with the Now() Criteria at the end.

I'm really new to access, your patient and concise help is appreciated!

 

[vbaInet]

1. Remove the AuthorizationExpirationDate calculated field from your query and save it. i.e. Copy the calculation into Notepad or something.
2. Create a new query based on the query in step 1 and pull in all the fields from that query.
3. Include the AuthorizationExpirationDate field in this query and enter >Date()+30 under the criteria.

 

[cpberg1]

Your solution seems to have worked. However it also seems like I was running into null fields returning #Error when making calculations in date fields. Found some workaround by putting far off unrealistic dates in the null field. Any suggestions for something more elegant? I'm able to run the query by excluding null by criteria but then I cannot >Date()+30. If I put the date 1/1/2100 (or similar) in my null DateOfBirth fields than your solution works great, however I have incorrect data in the table.

My limited Access knowledge is glowing brightly! I'm 6 hours into this project, I need to take a class (or lurk around old threads here more).

 

[vbaInet]

Perhaps:

>Date()+30 Or Is Null

Copy and paste that into one line.

 

[cpberg1]

Would not give proper results but got me thinking about how I was excluding null. Read up on nz and Iff relating to use of null. I ended up using an iff to substitute an incorrect date for null DOB in my query. Report then warns user that a null value is on of the two fields was discovered and prompts for data entry. I think it is a solution that I'm pretty happy with. Continuing to learn!

 

[vbaInet]

I wasn't sure what your other question was because if you applied the criteria under the Date field it will not include Null. Unless one of your other calculations was returning Null somewhere along the line. I will need to see what you're doing to be able to give any constructive advice.