puzzling queries

[John Wharmby]

Hi there,

I have been stumped by a where statement! (Thought it would never happen!)

I have a database that stores parts of postcodes for a survey to see where
our customers have traveled from, and I want to query it to produce count
totals in a report. I have already built the report and it works good
querying the data as I want it, producing totals per code. However, i don't
want the whole lot all the time. When I use a WHERE in the report query it
works fine, giving me the data that I want for a particular day. But when I
use a form to try and define the dates i want and call the report using the
below code, is just asks "Enter Parameter Value" for "postcodes.STAMP"
(Postcodes being the table, STAMP being the field with the date and times
in) I have tried ! instead of . enclosing the names in [ and ] s, and i
have even trimmed down the code, the report and the query in the report to
the bare minumum. The report works fine until I want to use a WHERE in the
OpenReport then it won't do it. Anyone know a solution.

' This is what I want to use
'sTr1 = "((postcodes.STAMP) BETWEEN #" & Format(Me![startdate], "dd mmmm
yyyy") & " 00:01# and #" & Format(Me![startdate], "dd mmmm yyyy") & "
23:59#)"
'This is what I am using at the moment, just to test it out
sTr1 = "(postcodes.STAMP) = #23 August 2003#"

DoCmd.OpenReport "coderpt", acViewPreview, , sTr1

Many thanks for any help!

John
---
We believe that this message is Virus free.
If you suspect that it is not, please call John Wharmby on
023 9287 5222. Thank you.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 28/08/2003

[HJ]

I tested your code and it works fne. Maybe there is something on the report
that is causing this? Sorting and grouping? Filter?

You may also find http://www.mvps.org/access/datetime/date0005.htm useful
for using dates in WHERE statements.

HJ

"John Wharmby" <jo*******@ntlworld.com> wrote in message
news:bi*******************@news.demon.co.uk...

Hi there,

I have been stumped by a where statement! (Thought it would never happen!)

I have a database that stores parts of postcodes for a survey to see where
our customers have traveled from, and I want to query it to produce count
totals in a report. I have already built the report and it works good
querying the data as I want it, producing totals per code. However, i don't want the whole lot all the time. When I use a WHERE in the report query it works fine, giving me the data that I want for a particular day. But when I use a form to try and define the dates i want and call the report using the below code, is just asks "Enter Parameter Value" for "postcodes.STAMP"
(Postcodes being the table, STAMP being the field with the date and times
in) I have tried ! instead of . enclosing the names in [ and ] s, and i
have even trimmed down the code, the report and the query in the report to
the bare minumum. The report works fine until I want to use a WHERE in the OpenReport then it won't do it. Anyone know a solution.

' This is what I want to use
'sTr1 = "((postcodes.STAMP) BETWEEN #" & Format(Me![startdate], "dd mmmm
yyyy") & " 00:01# and #" & Format(Me![startdate], "dd mmmm yyyy") & "
23:59#)"
'This is what I am using at the moment, just to test it out
sTr1 = "(postcodes.STAMP) = #23 August 2003#"

DoCmd.OpenReport "coderpt", acViewPreview, , sTr1

Many thanks for any help!

John
---
We believe that this message is Virus free.
If you suspect that it is not, please call John Wharmby on
023 9287 5222. Thank you.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 28/08/2003