473,245 Members | 1,577 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,245 software developers and data experts.

puzzling queries

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
1 2110
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
5
by: WertmanTheMad | last post by:
Ive been playing with this for a few days and thought I might thow it out for seggestions. I have Several Queries that need counts returned The Queries are Mutually Exclusive meaning whatever...
1
by: Roger Green | last post by:
I have inherited a complex database that has many dozens of queries that derive data from a people table. I now need to be able to run these queries (from within a significant number of forms)...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
3
by: NeilAnderson | last post by:
I'm a fairly new user of access & I've never had any training, so I'm wondering if I'm doing the right thing here, or if it matter at all. I'm building a database for room booking purposes and I'm...
5
by: Jerry Hull | last post by:
I'm working with a database developed by an untrained person over several years - and on a network that has recently been upgraded with a new server installed and MS office upgraded from 2K (I...
0
by: MHenry | last post by:
Hi, I know virtually nothing about creating Macros in Access. I would appreciate some help in creating a Macro or Macros that automatically run(s) 14 Queries (three Make Table Queries, and 11...
44
by: Greg Strong | last post by:
Hello All, Is it better to create a query in DAO where a report has 4 sub-reports each of whose record source is a query created at runtime and everything is in 1 MDB file? From what I've...
4
by: Mikael Janers | last post by:
Hello! I have a question that is not realy only C# but I didn't find any better group... What is the most common/best way to handle sql queries in C# 1) Should I use stored procedures ? I...
5
by: marshmallowww | last post by:
I have an Access 2000 mde application which uses ADO and pass through queries to communicate with SQL Server 7, 2000 or 2005. Some of my customers, especially those with SQL Server 2005, have had...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.