We help IT Professionals succeed at work.

For Microsoft Access related with calibration due date

dcantel1
dcantel1 asked
on
Below is an example of what I am trying to do:

Field “Calib Due Date” formula:

Calib Due Date: Format(DateAdd("m",[CalibInterval (Months)],[Calibration Date])-1,"m/yyyy")

= 2/2011 Example

Field “IsOverdue” formula:

IsOverdue: IIf([Calib Due Date]<Date(),True,False)


= “Yes”or “No” Example

If I want to filter only for the “Yes” it will not function or in some field there are some errors, for example:

Example
Field: “Calb Due Date” = 12/2011 (month and Year)

Field: “IsOverdue” = Yes   this need to be “No” because the current date is 12/04/2011

How can correctly filter the Field: “IsOverdue” to have “No” correctly by using the example above????



Comment
Watch Question

Author

Commented:
I need help with several formulas using dates and due date - Microsoft Access

Thank you very much,

Dennis Cantellops, QSM
US FDA San Juan District

Commented:
try:

Calib Due Date: Format(DateAdd("m",[CalibInterval (Months)],[Calibration Date])-1,"m/d/yyyy")
Top Expert 2011
Commented:
Remove the Format() from the formula.  This way the data stay the correct data type.

Calib Due Date: DateAdd("m",[CalibInterval (Months)],[Calibration Date])-1


Now IsOverdue: IIf([Calib Due Date]<Date(),True,False)  should work since [Calib Due Date] is still an actual date.

When you need to display the Due data in the m/yyyy format that is when you would use:  

 Format([Calib Due Date], "m/yyyy")

Author

Commented:
TheHiTechCoach: gave a very nice solution

Thank very much,

Dennis Cantellops, US FDA