Date Fields

[znpnh]

Hi,

I created a query with one expression field that updates a user inputted date field on a form. The expression adds a certian amount of time to the field (usually six months) so I know when the next inspection should take place. Everything works great except when I put a parameter in the expression field. It will not return the property dates. If I simply remove the expression, and input the date manually, it works just fine. Am I not allowed to use date paramerter with an expression? It returns every date within the correct month, but will give me future years as well.
The expression is - NextInspectionDate: DateAdd("m",12/[InspectionFrequency],[LastInspectionDate])
The parameter is - Between [Forms]![Preventative Maintenance Dates]![Sta

 

[namliam]

is any of your fields actually a text field? sounds like it is acting as though your dates are texts.

Make sure you have actual date controls on your form and/or make sure to convert the fields EXPLICITLY to dates useing a function like DateSerial or CDate.
Implicit conversions can cause havoc like this

 

[znpnh]

I took a look at both fields and they are both date fields. My form is based on the query, but the NextInspectionDate is auto filled from the expression in the query. Works just fine with a regular field, but not an expression. I'm totally stumped.

 

[namliam]

Still only reason for the symptoms you describe is that its a text comparison, there must be something with text going on... Dateadd should return a proper date format thus the only suspect is your controls on your form.

Failing that you can try and substitute the text into the query as "fixed" values.

 

[znpnh]

I kept trying and even deleted the fields and started over. I saw a related post to this one, and used the expression in an on update in VBA, and it worked perfectly!