Need Help Adjusting Query Expression

[jtm013]

Hello,



I have a database that tracks tools. The tools have due dates for when they need to be calibrated. A recent policy shift has made it so all tools are now due by month and not the specific date. (Example: A tool may have previously been listed as being due for calibration on 5-19-15, but now as long as it gets done in May it is alright.)

The due date field is calculated based on a field which records the last calibration date and the specified interval (one month, 6 months, etc.)

Thanks to help from this forum I was able to figure out how to get all the due dates switched to show the last day of the month in which they are due, the problem I am now having is with several of my queries.

For example I have a query which produces a list of all tools due during the current month.
This query worked fine until I switched the due dates to show the last day of the month and now I get a data type mismatch when the query runs. Below is the setup for my query, it worked perfectly prior to my messing with the toolnextcaldate field.

Field:	toolDescription	toolID	toolLocat	toolActive	toolIssued	Year([toolNextCalDate])*12+DatePart("m",[toolNextCalDate])
Table:	tblDataPrime	tblDataPrime	tblDataPrime	tblDataPrime	tblDataPrime
Sort		Ascending
Show:	checked	checked	checked	checked	checked	not checked
Criteria:			<>"Scrap" AND <>"O-Cal"	<>0	<>0	Year(Date())*12+DatePart("m",Date())

If it helps the field toolNextCalDate is in tblDataPrime and was a calculated field that previously (when everything was working) was set up as: [toollastCalDate]+[toolCalInterval], now it is set up as: DateSerial(Year([toollastcaldate]+[toolcalInterval]),Month([toollastcaldate]+[toolCalInterval])+1,0).

[June7]

You say the toolcalInterval unit is month? Then can't just add to date with simple arithmetic. Access will default to day unit. Use DateAdd() function to specify other units.

[jtm013]

You say the toolcalInterval unit is month? Then can't just add to date with simple arithmetic. Access will default to day unit. Use DateAdd() function to specify other units.

Right sorry for leaving that out- the field toolCalInterval is recorded in days, and if memory serves (not in front of the db right now) there are 10 interval options, monthly, bi-monthly, 3 months, 4 months, 6 months, 9 months, annually, bi-annually, etc.

[June7]

Using day can complicate because not every month and year have same number of days. The calc will probably be close enough but since the requirement is to calibrate sometime in the month, not by specific day, I recommend month unit interval.

Otherwise, I don't see anything wrong with the expressions. Although I don't understand multiplying year by 12 nor why then adding the month. If you are trying to concatenate, use & character instead of +.

If you want to provide db for analysis, follow instructions at bottom of my post.

[jtm013]

Using day can complicate because not every month and year have same number of days. The calc will probably be close enough but since the requirement is to calibrate sometime in the month, not by specific day, I recommend month unit interval.

Otherwise, I don't see anything wrong with the expressions. Although I don't understand multiplying year by 12 nor why then adding the month. If you are trying to concatenate, use & character instead of +.

If you want to provide db for analysis, follow instructions at bottom of my post.

June 7, to be honest I don't particularly understand the query expressions. It was a user on this forum that helped me get the query to do what I needed it to do. All I know is that it made the query show those tools due this month.
----
In reference to the complication of using day, the new calculation of DateSerial(Year([toollastcaldate]+[toolcalInterval]),Month([toollastcaldate]+[toolCalInterval])+1,0) has so far produced an accurate end of the month due date, regardless of leap-year, month length, etc. in my tests. All I know is that after I made the switch to that calculation for field toolNextCalDate, this query and others which are very similar started throwing type mismatch errors.
---
I have attached the DB for review.
The fields in question are in table: tblDataPrime
The DB is set to open to frmDashLead (the normal dashboard) you will get a bunch of "data type mismatch" errors, this is the problem which arose from my switching the calculation in the field ToolNextCalDate.Tool Database - Copy.zip

[June7]

I am no more enlightened about the issue. Still willing to look at db.