SELECT records between two dates - syntax problem

  • Thread starter Thread starter Leonard Priestley
  • Start date Start date
L

Leonard Priestley

I am designing a form which should enable the user to select records
according to a specified date, or date window.

So far I have managed to limit the recordset to dates before a date
specified in a textbox called txtFromDate. I used the code:

Me.RecordSource = "SELECT tblDiscussion.Action, tblDiscussion.ActionDate _
FROM tblDiscussion _
WHERE (((tblDiscussion.Action) Is Not Null) AND ((tblDiscussion.ActionDate)
Is Not Null)) AND tblDiscussion.ActionDate < #" & Format(txtFromDate,
"YYYY/MM/DD") & "#" & " _
ORDER BY tblDiscussion.ActionDate"

The code I used looks the way it does because I live in New Zealand, where
dates are in the dd/mm/yyyy format, and the code "tblTable.Field = #" &
txtFromDate & "#" only seems to work reliably with US style dates.

My problem is that I would now like to extend the process, to specify both
an earlier and later date, using textboxes txtFromDate and txtToDate.

At this point, I have got bogged down in the syntax. I have spent hours
attempting to get it right, but I am no expert and the learning curve is
steep. What I have so far is:

Me.RecordSource = "SELECT tblDiscussion.Action, tblDiscussion.ActionDate _
FROM tblDiscussion _
WHERE (((tblDiscussion.Action) Is Not Null) AND ((tblDiscussion.ActionDate)
Is Not Null)) AND (tblDiscussion.ActionDate < #" & Format(txtFromDate,
"YYYY/MM/DD") & "#") & "AND (tblDiscussion.ActionDate > #" &
Format(txtToDate, "YYYY/MM/DD") & "#" & "ORDER BY tblDiscussion.ActionDate"

Any help gratefully received.
Failing that Aspirin would be acceptable

Leonard Priestley
 
A few things:

(1) As you suspect, dates in SQL statements must *always* be in US format,
regardless of your locale or PC settings.

(2) Be aware that Null values *fail all tests in all cases*. So if the
action date was null, *both* of these next tests would fail:
ActionDate >= #2003/1/1#
ActionDate <= #3002/1/1#
So you do not actually need the (tblDiscussion.ActionDate) Is Not Null. But
there is nothing wrong with keeping it there for clarity, if you want to do
that.

(3) Here's a simple form of your statement. Note the use of preliminary
steps to make it simpler. Or you could write a function to return US format
date strings (including the #...#s) whenever you need them.

dim s1 as string, s2 as string
s1 = "#" & format$ (Me![txtFromDate], "YYYY/MM/DD") & "#"
s2 = "#" & format$ (Me![txtToDate], "YYYY/MM/DD") & "#"
me.recordsource = _
"SELECT Action, ActionDate" & _
" FROM tblDiscussion" & _
" WHERE ActionDate BETWEEN " & s1 & " AND " & s2 & _
" ORDER BY ActionDate"

HTH,
TC
 
oops, if you want to ensure that the ACTION is not null (as well as the
action DATE not null):

" FROM tblDiscussion" & _
" WHERE ActionDate BETWEEN " & s1 & " AND " & s2 & _" ORDER BY ActionDate"

TC


TC said:
A few things:

(1) As you suspect, dates in SQL statements must *always* be in US format,
regardless of your locale or PC settings.

(2) Be aware that Null values *fail all tests in all cases*. So if the
action date was null, *both* of these next tests would fail:
ActionDate >= #2003/1/1#
ActionDate <= #3002/1/1#
So you do not actually need the (tblDiscussion.ActionDate) Is Not Null. But
there is nothing wrong with keeping it there for clarity, if you want to do
that.

(3) Here's a simple form of your statement. Note the use of preliminary
steps to make it simpler. Or you could write a function to return US format
date strings (including the #...#s) whenever you need them.

dim s1 as string, s2 as string
s1 = "#" & format$ (Me![txtFromDate], "YYYY/MM/DD") & "#"
s2 = "#" & format$ (Me![txtToDate], "YYYY/MM/DD") & "#"
me.recordsource = _
"SELECT Action, ActionDate" & _
" FROM tblDiscussion" & _
" WHERE ActionDate BETWEEN " & s1 & " AND " & s2 & _
" ORDER BY ActionDate"

HTH,
TC


Leonard Priestley said:
I am designing a form which should enable the user to select records
according to a specified date, or date window.

So far I have managed to limit the recordset to dates before a date
specified in a textbox called txtFromDate. I used the code:

Me.RecordSource = "SELECT tblDiscussion.Action, tblDiscussion.ActionDate _
FROM tblDiscussion _
WHERE (((tblDiscussion.Action) Is Not Null) AND ((tblDiscussion.ActionDate)
Is Not Null)) AND tblDiscussion.ActionDate < #" & Format(txtFromDate,
"YYYY/MM/DD") & "#" & " _
ORDER BY tblDiscussion.ActionDate"

The code I used looks the way it does because I live in New Zealand, where
dates are in the dd/mm/yyyy format, and the code "tblTable.Field = #" &
txtFromDate & "#" only seems to work reliably with US style dates.

My problem is that I would now like to extend the process, to specify both
an earlier and later date, using textboxes txtFromDate and txtToDate.

At this point, I have got bogged down in the syntax. I have spent hours
attempting to get it right, but I am no expert and the learning curve is
steep. What I have so far is:

Me.RecordSource = "SELECT tblDiscussion.Action, tblDiscussion.ActionDate _
FROM tblDiscussion _
WHERE (((tblDiscussion.Action) Is Not Null) AND ((tblDiscussion.ActionDate)
Is Not Null)) AND (tblDiscussion.ActionDate < #" & Format(txtFromDate,
"YYYY/MM/DD") & "#") & "AND (tblDiscussion.ActionDate > #" &
Format(txtToDate, "YYYY/MM/DD") & "#" & "ORDER BY tblDiscussion.ActionDate"

Any help gratefully received.
Failing that Aspirin would be acceptable

Leonard Priestley
Click to expand...
Click to expand...
 
Me.RecordSource = "SELECT tblDiscussion.Action, tblDiscussion.ActionDate _
FROM tblDiscussion _
WHERE (((tblDiscussion.Action) Is Not Null) AND ((tblDiscussion.ActionDate)
Is Not Null)) AND (tblDiscussion.ActionDate < #" & Format(txtFromDate,
"YYYY/MM/DD") & "#") & "AND (tblDiscussion.ActionDate > #" &
Format(txtToDate, "YYYY/MM/DD") & "#" & "ORDER BY tblDiscussion.ActionDate"
Click to expand...

It helps a lot to build up the SQL string in a variable and look at it
using the debugging facilities in the VBA editor. I think all that's
happening here is that you are missing some blanks and parentheses -
this will (for arbitrary dates) give you

SELECT tblDiscussion.Action, tblDiscussion.ActionDate FROM
tblDiscussion WHERE (((tblDiscussion.Action) Is Not Null) AND
((tblDiscussion.ActionDate) Is Not Null)) AND
(tblDiscussion.ActionDate < #2003/10/13#AND (tblDiscussion.ActionDate
#2003/10/18#ORDER BY tblDiscussion.ActionDate
Click to expand...

Note that the formatted dates and the SQL operators are jammed
together and the string dosen't have enough right parens. Just put
them inside the text string:

Me.RecordSource = "SELECT tblDiscussion.Action,
tblDiscussion.ActionDate _
FROM tblDiscussion _
WHERE (((tblDiscussion.Action) Is Not Null) AND
((tblDiscussion.ActionDate)
Is Not Null)) AND (tblDiscussion.ActionDate < #" & Format(txtFromDate,
"YYYY/MM/DD") & "#) AND (tblDiscussion.ActionDate > #" &
Format(txtToDate, "YYYY/MM/DD") & "#) ORDER BY
tblDiscussion.ActionDate;"
 
It looks to me like the greater than and less than clauses
are reversed? I'm a long way from NZ but my users think of
from-to dates as going from the earlier date to the later
date.
 
Back
Top