VBA Select query with date range format issue

[Rich_in_NZ]

(XP, Access '03 in 2000 format, MySQL link table)

Hi All,

I am trying to set the recordsource of a subform in VBA but I'm having
issues with a date range due to the format. I am wanting to display all the
records from the server table, between two user-selected dates. I'm from New
Zealand so the dates are in the Britsh "dd/mm/yyyy" format. The dates on the
server table however are formatted "dd/mm/yyyy hh:nn:ss". In the past, my
date range filters have worked fine, as all the dates were formatted the
same, but now they won't work. When I try to align the formatting, I get the
incorrect date range of data.
When I filter by:
Start = 03/08/2009, End = 05/08/2009
I end up with data between the 8th of March and the 8th of May!
Any ideas as to where I'm going wrong?
Below are 3 unsuccessful attempts at this part of the Where clause:
1 - "AND (Format(qrySamplesReceived.ReceivedDate,
""dd/mm/yyyy"")>=Format(Me.cboDate.Value, ""dd/mm/yyyy"") And
(Format(qrySamplesReceived.ReceivedDate, ""dd/mm/yyyy""))
<=Format(Me.cboDateRange.Value, ""dd/mm/yyyy""))"
2 - "AND (Format(qrySamplesReceived.ReceivedDate, ""dd/mm/yyyy"") BETWEEN
Format(Me.cboDate.Value, ""dd/mm/yyyy"") AND Format(Me.cboDateRange.Value,
""dd/mm/yyyy""))
3 - "AND (qrySamplesReceived.ReceivedDate BETWEEN #" & Me.cboDate.Value & "#
AND #" & Me.cboDateRange.Value & "#)
Thanks in advance for your help,
Rich

 

[RaajDave via AccessMonster.com]

Try following FORMAT clause:

FORMAT(qrySamplesReceived.ReceivedDate, "mm/dd/yyyy")

 

[Paolo]

Hi Rich_in_NZ

try to use this format for all your dates
Format(qrySamplesReceived.ReceivedDate,"\#mm\/dd\/yyyy\#")

HTH Paolo

 

[Rich_in_NZ]

Thanks Paolo,

I had previously tried formatting the dates in this way, but must have had
it slightly incorrect as the code executed without any runtime errors, it
just didn't produce any data. Thankfully it executed perfectly first time
this morning.

Thanks again,
Rich.