By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

puzzling queries

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.