Dates and VBA Querying..AHHHH

[WackyWaterGuy]

I was beginning to think I had a lot of this figured out....and as soon as you think so......BOOOM, you hit a wall! hahhaha

Ok, so I have been going around in circles here, hopefully someone may be able to point out where I have gone wrong. I have 2 text boxes on my form. One is for start date, and one for End date (basically, the user enters the dates they want to query between in these boxes.) I am having no issues reading these dates into two Variants in my code - StartDate and EndDate.

Below I will show my code, perhaps you can tell my why I keep getting errors.

    StartDate = Forms![ReportQuery]![Start Date]
    EndDate = Forms![ReportQuery]![End Date]
    SearchDates = "'[" & StartDate & "] And [" & EndDate & "]'"

'[COLOR=green]Then further down, I tack this on to the rest of 
my query ("MySearch" contains the other information to
 query..which works fine without the date!)[/COLOR] 

If MySearch = MySearch Then
    MySearch = MySearch  & " And [Date] Between " & SearchDates
    MySearchGVL = MySearch
    End If

Now, I have tried using SearchDates without the [...], but that just seems to return a blank query..so something isnt right. I have tried putting ' (single ticks) around the whole thing..but then it give an error saying that there is a "Between operator with no And". YES THERE IS!! ahhahha. Everything else I have tried seems to keep giving mismatched type errors or something.

I am just at a loss. Any help would really be appreciated!

Thank you!

WWG

 

[dcx693]

Here's the problem:
SearchDates = "'[" & StartDate & "] And [" & EndDate & "]'"

Dates need to be handled differenty than strings. And besides which, your syntax would still not be totally right if the two fields were strings.

Try something like this:

SearchDates = StartDate & " And " & EndDate

If that doesn't work, try:

SearchDates = "#" & StartDate & "# And #" & EndDate & "#"

You might also consider reading StartDate and EndDate into date type fields instead of variants, though it might not make much difference.

 

[WackyWaterGuy]

Hey There...thanks for the suggestions...
The first option doesnt work at all...it just returns a blank query for some reason. The second reason, however, WORKS PERFECTLY!! WOOO!

Thank you sooo much. I realized that my syntax was likely very incorrect, but the first thing I tried was your first option...and when that didnt work, I just started trying anything. I didnt even know that the # represented a date/time deliminater symbol. Realizing this now, I probably could have figured it out...eventually. Thanks for pointing it out to me......see, you learn something everyday!

Thanks again!

WWG

 

[dcx693]

No problem. I think that if you assigned those date fields to date variables in you code (instead of variants) you might not need the # symbols.