Mar 26 2009, 03:50 PM
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.
Mar 26 2009, 03:58 PM
Welcome to Utter Access!
Try changing your expression to something like:
Expr1: DateAdd("yyyy", 1, [TestDate])
Hope that helps...
Mar 26 2009, 04:27 PM
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.
Edited by: frogpond on Thu Mar 26 17:28:34 EDT 2009.
Mar 26 2009, 04:39 PM
That picture doesn't show the same code I gave you. Is GO1471C a date/time field?
Mar 27 2009, 05:41 AM
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????
Mar 27 2009, 01:39 PM
Would you mind posting a small copy of your db with test data?
Mar 27 2009, 08:11 PM
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.
Mar 28 2009, 06:30 PM
Thanks again, Van.
You brought up some information I am not aware of or even considered.
Mar 29 2009, 01:03 PM
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.
Mar 30 2009, 01:35 PM
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!
Mar 30 2009, 04:23 PM
You're welcome... Glad we could help...
Mar 31 2009, 03:26 PM
Hi Milli. You're welcome. Van and I are happy to help. Good luck with your project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here