SELECT date BETWEEN var and var 1

[snayjay]

Ok...3rd post in 2 days...my errors seem to never stop coming. Can anyone see what I've done wrong. I know it's this line of code because if I comment it out...all other parts of the code work fine. Only when I set this line to run will the program screw up. The error I'm getting is:

Run Time Error '2001':

You cancelled the previous operation

Here's the code.

ws = ws & " AND ((MEMBERtbl.BIRTHDATE) BETWEEN '" & [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![BIRTHDATEx] & "' AND '" & [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![BIRTHDATEy] & "')"

What I've done is I'm creating an SQL string based on whether or not the user has chosen a field to be added to the query by checking a check box. This code is just the part for adding the criteria for BIRTHDATE to the WHERE Statement.

"ws" is the WHERE STATEMENT

I'd give you the whole code, but it's pretty long and it all works. So I know the code that is messed up has to be here. That's why the error baffles me...because it doesn't sound like it has to do with any of my code formating for this line. But trust me it's this line of code that is stopping the program.

Any help would be greatly appreciated.

~Snay

 

[cheerio]

Access SQL generally uses the # character to delimit dates rather than single quotes.

the middle bit of the code should be more like:

...BETWEEN #" & [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![BIRTHDATEx] & "# ...

and similarly for the second date.

You may find it helpful to put a debugging message in your code - either debug.print ws or MsgBox ws to see exactly what string you have built.

 

[snayjay]

Cheerio,

Thanks for that advice. I tried the # and it allowed the code to complete...but that was weird. Because I have 9 other lines of code ahead of this one using ' instead of #...and they work fine. Must be because it's a date. Anyway, thanks for the help. I wonder though if my code syntax right? Because it's not showing me any records. But if I do just a direct query on a particular date; 'Like' instead of 'Between'... it works fine.

Any thoughts?

 

[snayjay]

scratch....it's working now....don't know what I did wrong the first time. Appreciate it much!

~Snay