between doesnt work

[tubar]

i have a field that adds a certain amount of months to a previous date inputed. when the query is open the user inputs a date range to pull up records. im using between mm/yy and it works when i dont compute the date but when i do compute it gives an error...is there another way to do this....i tried doing the date computing in one querry and the between in another but still same problem....any solution

 

[namliam]

what is your date computation???

If you are using a real date (DateAdd), then a between MM/YY will not work properly as dates require the third element...

 

[tubar]

It is a (dateadd)…so you are saying this can be done if I include the (third element) and what is that?

in the querry i have a field
next relamp: DateAdd("m",[lasted],[relamp date])

the criteria is
Between [Enter Start Date MM/YY] And [Enter End Date MM/YY]

i added the third element if the third element is (dd)

and both times i get
THE EXPRESION IS TOO COMPLEX OR TYPED INCORRECTLY

 

[namliam]

in the querry i have a field
next relamp: DateAdd("m",[lasted],[relamp date])

the criteria is
Between [Enter Start Date MM/YY] And [Enter End Date MM/YY]

i added the third element if the third element is (dd)

Yes here is the problem, yes your third element is DD, dates have Days, Months and years... Without it, its not a date...

Your parameters entered as "MM/YY" are strings, and here in lays the problem. Access Needs to think for you in how to translate these strings into dates in order to do do the between.

Now what is (lets say) 12/08 supposed to mean to access?
You say MM/YY, but Access dont know that... Plus keep in mind that access works with Mostly US dates...

So this can be...
12 aug
8 dec
Dec 08
aug 12 (iso)

What will access do? Mostlikely the WRONG THING.

You need to tell it what to do... Try researching something called a "Search form" here you can enter parameters and validate them before running any query.

Or alternatively if your married to this way of parameters, you can use CDate or DateValue or DateSerail (AND use the Day component) to force access the right way.

Let me know if you need more help