How to write strWhere code for start and end date

  • Thread starter Thread starter buscher75
  • Start date Start date
B

buscher75

I have a form, frmWeight, I use to enter in a date range, txtStart and
txtEnd. On click of the run report button, I want to run two queries, weight
and weight2, and open a report, rptWeight.

The way the code is currently written does not work. It seems as though a
strWhere condition would apply here (from what I have read) but I do not know
how to write the code. I have tried a few examples in the past but have had
no success.

Any help would be appreciated. Here is the code I have.

Private Sub CommandWeight_Click()
On Error GoTo Err_CommandWeight_Click


Dim strReport As String 'Name of report to open.

strReport = "rptWeight"


DoCmd.OpenReport strReport, acViewPreview

Exit_CommandWeight_Click:
Exit Sub

Err_CommandWeight_Click:
MsgBox Err.Description
Resume Exit_CommandWeight_Click

End Sub
 
strWhere = "some_date BETWEEN #" & Format(txtStart,"mm/dd./yyyy") & "# AND
#" & Format(txtEnd,"mm/dd./yyyy") & "#"

DoCmd.OpenReport strReport, acViewPreview, WhereCondition:=strWhere

This assumes that the name of the field to which you wish to apply the date
range is "some_date", amend accordingly.

Beware of date fields containing a time value as well as a date. For
example, if txtEnd is 23 July 2008, then a value in start_date of 23 July
2008, 12:00 will fall *outside* the range.
 
I tried your code and I got a syntax error:

Syntax error in date in query expression '(currentDate BETWEEN #06/23./2008#
AND #06/24./2008#)'

I removed the decmal places in the date, then re-entered my dates in the
form. I got a popup box asking for the txtStart value, then I got a blank
report. have I missed something?
 
My mistake, I got a popup box asking for the currentDate value not the
txtStart value. I changed the code to below which ran the report quicker,
but it is still a blank report

strWhere = "
.[station_GT].[currentDate] BETWEEN #" &
Format(txtStart, "mm/dd/yyyy") & "# AND #" & Format(txtEnd, "mm/dd/yyyy") &
"#"
 
Hi,

I've no idea how the decimal point got into the dates (just a typo I guess)
but as you figured out it shouldn't have been there.

I'm afraid I've no idea why your report is blank without knowing anything
about your data and your report's recordsource.

buscher75 said:
My mistake, I got a popup box asking for the currentDate value not the
txtStart value. I changed the code to below which ran the report quicker,
but it is still a blank report

strWhere = "
.[station_GT].[currentDate] BETWEEN #" &
Format(txtStart, "mm/dd/yyyy") & "# AND #" & Format(txtEnd, "mm/dd/yyyy")
&
"#"



buscher75 said:
I tried your code and I got a syntax error:

Syntax error in date in query expression '(currentDate BETWEEN
#06/23./2008#
AND #06/24./2008#)'

I removed the decmal places in the date, then re-entered my dates in the
form. I got a popup box asking for the txtStart value, then I got a
blank
report. have I missed something?
Click to expand...
Click to expand...
 
Back
Top