I have a query with an expression column (testdate+365) to calculate an expiration date a year later. I'd like to use a parameter with the expression column, ex. between [Start date] and [End date] so I can find dates expiring within a range I choose. When I run the query and enter dates, it returns incorrect dates, not at all within the range I input.

It works fine when the parameter is used on the original date column but doesn't seem to work with the expression column.

Any advice would be appreciated.

Milli

Hi Milli,

Welcome to Utter Access!

Try changing your expression to something like:

Expr1: DateAdd("yyyy", 1, [TestDate])

Hope that helps...

It's still giving me incorrect dates. I've attached a jpg of my screen. The fields are GO1471C and the expression to the right. It sounds simple to be but I've got to be doing something wrong.

Have to go now but will check back later.

Thanks!
Milli

Edited by: frogpond on Thu Mar 26 17:28:34 EDT 2009.

That picture doesn't show the same code I gave you. Is GO1471C a date/time field?

Yes, it's a date/time field. The attachment is my original query. I did try the example you gave me but still got incorrect dates. Wondering if it's just because it's used with the expression????

Would you mind posting a small copy of your db with test data?

Either way (your expression or theDBguy's expression - which is more correct), I think you need to declare both [Start date] and [End date] as Parameters of DateTime data type instead of being just implicit Parameters.

In the DesignView of the Query, use the Menu Query / Parameters... In the left column of the dialog, type the Parameter exactly as it appears in the DesignView, i.e. [Start date] or [End date], and in the right column, select DateTime data type.

You expression returns a Double due to the numeric addition. TheDBguy's DateAdd returns a Variant (of DateTime sub-type). Either way, I think if the Parameters are implicit, i.e. of undeclared data type, Access is unlikely to know that the condition is to be performed with operations of DateTime data type and therefore Access evaluates the condition incorrectly.

Thanks again, Van.

You brought up some information I am not aware of or even considered.

Cheers.

No problemo, theDBguy.

I got stuck with this sort of problems myself a few times as like most people, I tend to use implicit Parameters.

Cheers

Thank you both for your input.

Van, I did as you suggested and it works! I tried that last week and must have typed something incorrectly.

DBguy, thank you very much for your quick replys. I did learn something new with the "dateadd".

This is a great forum and I can usually find the answers I'm looking for by reading the posts but sometimes I get stuck.

Thanks again and keep up the good work!

Milli

You're welcome... Glad we could help...

Hi Milli. You're welcome. Van and I are happy to help. Good luck with your project.