Start Date/End Date require multiple entries.

  • Thread starter Thread starter Fleone
  • Start date Start date
F

Fleone

I have a query that asks for a start date and end date as parameters.
Each time I run this query I have to enter the start date and the end date 4
times before it will run. It is not within a form, just a simple query.
Here is the SQL statement:
SELECT tbl_sales.[Email Address], tbl_email.[serial number],
tbl_sales.[Trans Date]
FROM tbl_sales INNER JOIN tbl_email ON tbl_sales.[Email Address] =
tbl_email.[client email]
WHERE (((tbl_email.[serial number]) Like "123#U*") AND ((tbl_sales.[Trans
Date]) Between [StartDate] And [EndDate]));

This is Access 2007 and I would REALLY love to understand why this happens.
I have gone so far as to create a new query and it works "normally" (asking
for start date and end date just once) one time and if I run it again,
boom...I have to enter it 4 times.

Thanks for any advice or solutions!
 
1. Is the tbl_email.[client email] field the primary key for that table? If
not you may have a Cartesian product messing with things.

2. What happens when you remove the following? I’m a little worried about
the # in there:
((tbl_email.[serial number]) Like "123#U*") AND

3. Lastly the SQL doesn’t show that the parameters aren’t defined. Assuming
those fields are actually date/time data types and not text fields with
something that looks like dates, the SQL should look something like:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT tbl_sales.[Email Address],
tbl_email.[serial number],
tbl_sales.[Trans Date]
FROM tbl_sales INNER JOIN tbl_email
ON tbl_sales.[Email Address] = tbl_email.[client email]
WHERE (((tbl_email.[serial number]) Like "123#U*")
AND ((tbl_sales.[Trans Date])
Between [StartDate] And [EndDate]));
 
Back
Top