WHats wrong with this query?

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I want to create a simple query, to ask the user for a start date and
end date, and it will display all records in my table tblGroups within
that date range,

I keep getting an error on this code that there are characters after
the end of the sql statement

PARAMETERS [Enter Start Date: mm/dd/yyyy] DateTime, [Enter End Date mm/
dd/yyyy] DateTime;
SELECT * FROM tblGroups;
WHERE date BETWEEN
[Enter Start Date: mm/dd/yyyy] AND [Enter End Date: mm/dd/yyyy];

Any ideas what I am doing wrong or if I am doing this correctly?

I then want to run a report based on this query.
 
Well I rewrote the query a bit and now it sort of works, it at least
runs.

However it prompts me for End Date 2 times and no matter what values I
put in the date range, for example 03/01/2009 and 03/10/2009 - I
still get resultsa for all things in the table, even things with a
date of 03/20/2009 etc..??

PARAMETERS [Enter Start Date: mm/dd/yyyy] DateTime, [Enter End Date mm/
dd/yyyy] DateTime;
SELECT *
FROM tblGroups
WHERE date Between
[Enter Start Date: mm/dd/yyyy] And
[Enter End Date: mm/dd/yyyy];


Remove the semi-colon (;) after tblGroups

Carl Rapson


I want to create a simple query, to ask the user for a start date and
end date, and it will display all records in my table tblGroups within
that date range,
Click to expand...
I keep getting an error on this code that there are characters after
the end of the sql statement
Click to expand...
PARAMETERS [Enter Start Date: mm/dd/yyyy] DateTime, [Enter End Date mm/
dd/yyyy] DateTime;
SELECT * FROM tblGroups;
WHERE date BETWEEN
[Enter Start Date: mm/dd/yyyy] AND [Enter End Date: mm/dd/yyyy];
Click to expand...
Any ideas what I am doing wrong or if I am doing this correctly?
Click to expand...
I then want to run a report based on this query.
Click to expand...
Click to expand...
 
I had a problem with the table so now I'm getting all date that I
should be BUT it is asking me for End Date twice.
If I enter nothing in the second end date prompt, it brings back
nothing. If I enter nothing in the 1st end date prompt but enter
something in the second I get my results ??

PARAMETERS [Enter Start Date: mm/dd/yyyy] DateTime, [Enter End Date
mm/
dd/yyyy] DateTime;
SELECT *
FROM tblGroups
WHERE date Between
[Enter Start Date: mm/dd/yyyy] And
[Enter End Date: mm/dd/yyyy];


Well I rewrote the query a bit and now it sort of works, it at least
runs.

However it prompts me for End Date 2 times and no matter what values I
put in the date range, for example 03/01/2009 and 03/10/2009   - I
still get resultsa for all things in the table, even things with a
date of 03/20/2009 etc..??

PARAMETERS [Enter Start Date: mm/dd/yyyy] DateTime, [Enter End Date mm/
dd/yyyy] DateTime;
SELECT *
FROM tblGroups
WHERE date Between
[Enter Start Date: mm/dd/yyyy] And
[Enter End Date: mm/dd/yyyy];

Remove the semi-colon (;) after tblGroups
Click to expand...
Carl Rapson
Click to expand...
news:413bc316-c4e5-40af-bfd0-80f8faa01ede@l13g2000vba.googlegroups.com....
Click to expand...
I want to create a simple query, to ask the user for a start date and
end date, and it will display all records in my table tblGroups within
that date range,
I keep getting an error on this code that there are characters after
the end of the sql statement
PARAMETERS [Enter Start Date: mm/dd/yyyy] DateTime, [Enter End Date mm/
dd/yyyy] DateTime;
SELECT * FROM tblGroups;
WHERE date BETWEEN
[Enter Start Date: mm/dd/yyyy] AND [Enter End Date: mm/dd/yyyy];
Any ideas what I am doing wrong or if I am doing this correctly?
I then want to run a report based on this query.
Click to expand...
Click to expand...
Click to expand...
 
Paul said:
Well I rewrote the query a bit and now it sort of works, it at least runs.

However it prompts me for End Date 2 times and no matter what values I put
in the date range, for example 03/01/2009 and 03/10/2009 - I still get
resultsa for all things in the table, even things with a date of
03/20/2009 etc..??

PARAMETERS [Enter Start Date: mm/dd/yyyy] DateTime, [Enter End Date mm/
dd/yyyy] DateTime;
SELECT *
FROM tblGroups
WHERE date Between
[Enter Start Date: mm/dd/yyyy] And
[Enter End Date: mm/dd/yyyy];
Click to expand...


If you look closely, you'll see that your name for the end date parameter
isn't exactly the same in your PARAMETERS declaration as when you use it as
a criterion. In the criterion you have a colon, in the declaration you you
don't.

Also, it appears you have a field named "date". Date is a reserved word,
and you run the risk of having it be misinterpreted. It would be best to
rename that field. If you can't do that, I suggest you qualify any
reference to it like this: "tblGroups.[date]".

Try this modified SQL:

PARAMETERS
[Enter Start Date: mm/dd/yyyy] DateTime,
[Enter End Date: mm/dd/yyyy] DateTime;
SELECT *
FROM tblGroups
WHERE tblGroups.[date] Between
[Enter Start Date: mm/dd/yyyy] And
[Enter End Date: mm/dd/yyyy];
 
Back
Top