Problem with Date Restrictor on Form

  • Thread starter Thread starter Chuck W
  • Start date Start date
C

Chuck W

Hi,

I created a form that has a text box for start date and a
text box for end date. I also have text boxes for states
and then a button that runs a report. I am trying to
create something that would allow a sales rep to enter in
a start date, and end date and then up to ten states into
the various text boxes. It is going against a table that
has data back to 2000. The problem is that the date
restrictors are not working. It sums up sales by
customer for the entire table rather than the start and
end dates I specify. I am not getting any error messages
either. Below is my SQL. Can anyone help?

Thanks,

Chuck

SELECT AllDB.CustomerName, AllDB.state, Sum
(AllDB.Subtotal) AS SumOfSubtotal,
Customer.SalesRepRef_FullName
FROM AllDB INNER JOIN Customer ON AllDB.CustomerName =
Customer.FullName
WHERE (((AllDB.TxnDate) Between [Forms]![SelectState]!
[txtStartDate] And [Forms]![SelectState]![txtEndDate]))
GROUP BY AllDB.CustomerName, AllDB.state,
Customer.SalesRepRef_FullName
HAVING (((AllDB.state)=[Forms]![SelectState]![txtState]
Or (AllDB.state)=[Forms]![SelectState]![txtState2] Or
(AllDB.state)=[Forms]![SelectState]![txtState3] Or
(AllDB.state)=[Forms]![SelectState]![txtState4] Or
(AllDB.state)=[Forms]![SelectState]![txtState5] Or
(AllDB.state)=[Forms]![SelectState]![txtState6] Or
(AllDB.state)=[Forms]![SelectState]![txtState7] Or
(AllDB.state)=[Forms]![SelectState]![txtState8] Or
(AllDB.state)=[Forms]![SelectState]![txtState9] Or
(AllDB.state)=[Forms]![SelectState]![txtState10]));
 
WHERE (((AllDB.TxnDate) Between [Forms]![SelectState]!
[txtStartDate] And [Forms]![SelectState]![txtEndDate]))
Click to expand...

Try enclosing the dates in date delimiters "#".

WHERE (((AllDB.TxnDate) Between "#" & [Forms]![SelectState]!
[txtStartDate] & "#" And "#" & [Forms]![SelectState]![txtEndDate] & "#"))

If this give you an error that the expression is to complex, you may have to
use >= and <= instead of Between.

WHERE AllDB.TxnDate >= "#" & [Forms]![SelectState]![txtStartDate] & "#" And
AllDB.TxnDate <= "#" & [Forms]![SelectState]![txtEndDate] & "#"

--
Wayne Morgan
Microsoft Access MVP


Chuck W said:
Hi,

I created a form that has a text box for start date and a
text box for end date. I also have text boxes for states
and then a button that runs a report. I am trying to
create something that would allow a sales rep to enter in
a start date, and end date and then up to ten states into
the various text boxes. It is going against a table that
has data back to 2000. The problem is that the date
restrictors are not working. It sums up sales by
customer for the entire table rather than the start and
end dates I specify. I am not getting any error messages
either. Below is my SQL. Can anyone help?

Thanks,

Chuck

SELECT AllDB.CustomerName, AllDB.state, Sum
(AllDB.Subtotal) AS SumOfSubtotal,
Customer.SalesRepRef_FullName
FROM AllDB INNER JOIN Customer ON AllDB.CustomerName =
Customer.FullName
WHERE (((AllDB.TxnDate) Between [Forms]![SelectState]!
[txtStartDate] And [Forms]![SelectState]![txtEndDate]))
GROUP BY AllDB.CustomerName, AllDB.state,
Customer.SalesRepRef_FullName
HAVING (((AllDB.state)=[Forms]![SelectState]![txtState]
Or (AllDB.state)=[Forms]![SelectState]![txtState2] Or
(AllDB.state)=[Forms]![SelectState]![txtState3] Or
(AllDB.state)=[Forms]![SelectState]![txtState4] Or
(AllDB.state)=[Forms]![SelectState]![txtState5] Or
(AllDB.state)=[Forms]![SelectState]![txtState6] Or
(AllDB.state)=[Forms]![SelectState]![txtState7] Or
(AllDB.state)=[Forms]![SelectState]![txtState8] Or
(AllDB.state)=[Forms]![SelectState]![txtState9] Or
(AllDB.state)=[Forms]![SelectState]![txtState10]));
Click to expand...
 
Wayne,

Thanks for your help. I am getting a message saying that
the query it too complex regardless of whether or not I
am using a between or a => <= and adding the "#" and the
&. I tried taking out the state info (the Having
expression) but am still getting this.

Basically I have a table that lists the customer name,
date, amount spent and the state they reside in. I am
trying to create a form where a sale rep can plug in a
beginning and ending date and up to ten states and then
get a report sorted by state that list the total of what
each customer spent in a given time period. Do you think
it has something to do with my group by statement? Any
ideas on how to simplify this so that it runs.
-----Original Message-----
WHERE (((AllDB.TxnDate) Between [Forms]![SelectState]!
[txtStartDate] And [Forms]![SelectState]![txtEndDate]))
Click to expand...

Try enclosing the dates in date delimiters "#".

WHERE (((AllDB.TxnDate) Between "#" & [Forms]! [SelectState]!
[txtStartDate] & "#" And "#" & [Forms]![SelectState]! [txtEndDate] & "#"))

If this give you an error that the expression is to complex, you may have to
use >= and <= instead of Between.

WHERE AllDB.TxnDate >= "#" & [Forms]![SelectState]! [txtStartDate] & "#" And
AllDB.TxnDate <= "#" & [Forms]![SelectState]! [txtEndDate] & "#"

--
Wayne Morgan
Microsoft Access MVP


Hi,

I created a form that has a text box for start date and a
text box for end date. I also have text boxes for states
and then a button that runs a report. I am trying to
create something that would allow a sales rep to enter in
a start date, and end date and then up to ten states into
the various text boxes. It is going against a table that
has data back to 2000. The problem is that the date
restrictors are not working. It sums up sales by
customer for the entire table rather than the start and
end dates I specify. I am not getting any error messages
either. Below is my SQL. Can anyone help?

Thanks,

Chuck

SELECT AllDB.CustomerName, AllDB.state, Sum
(AllDB.Subtotal) AS SumOfSubtotal,
Customer.SalesRepRef_FullName
FROM AllDB INNER JOIN Customer ON AllDB.CustomerName =
Customer.FullName
WHERE (((AllDB.TxnDate) Between [Forms]![SelectState]!
[txtStartDate] And [Forms]![SelectState]![txtEndDate]))
GROUP BY AllDB.CustomerName, AllDB.state,
Customer.SalesRepRef_FullName
HAVING (((AllDB.state)=[Forms]![SelectState]![txtState]
Or (AllDB.state)=[Forms]![SelectState]![txtState2] Or
(AllDB.state)=[Forms]![SelectState]![txtState3] Or
(AllDB.state)=[Forms]![SelectState]![txtState4] Or
(AllDB.state)=[Forms]![SelectState]![txtState5] Or
(AllDB.state)=[Forms]![SelectState]![txtState6] Or
(AllDB.state)=[Forms]![SelectState]![txtState7] Or
(AllDB.state)=[Forms]![SelectState]![txtState8] Or
(AllDB.state)=[Forms]![SelectState]![txtState9] Or
(AllDB.state)=[Forms]![SelectState]![txtState10]));
Click to expand...


.
Click to expand...
 
Try declaring your parameters. ALSO I would move your having criteria into the
where clause since that will be faster.

Parameters [Forms]![SelectState]![txtStartDate], DateTime,
[Forms]![SelectState]![txtEndDate], DateTime;
SELECT AllDB.CustomerName, AllDB.state, Sum
(AllDB.Subtotal) AS SumOfSubtotal,
Customer.SalesRepRef_FullName
FROM AllDB INNER JOIN Customer
ON AllDB.CustomerName = Customer.FullName
WHERE AllDB.TxnDate Between
[Forms]![SelectState]![txtStartDate] And [Forms]![SelectState]![txtEndDate]
AND ((AllDB.state)=[Forms]![SelectState]![txtState] Or
(AllDB.state)=[Forms]![SelectState]![txtState2] Or
(AllDB.state)=[Forms]![SelectState]![txtState3] Or
(AllDB.state)=[Forms]![SelectState]![txtState4] Or
(AllDB.state)=[Forms]![SelectState]![txtState5] Or
(AllDB.state)=[Forms]![SelectState]![txtState6] Or
(AllDB.state)=[Forms]![SelectState]![txtState7] Or
(AllDB.state)=[Forms]![SelectState]![txtState8] Or
(AllDB.state)=[Forms]![SelectState]![txtState9] Or
(AllDB.state)=[Forms]![SelectState]![txtState10] )
GROUP BY AllDB.CustomerName, AllDB.state,
Customer.SalesRepRef_FullName

If you are using the query grid to build your query, then you can declare your
parameters this way

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Repeat as needed.


Chuck said:
Hi,

I created a form that has a text box for start date and a
text box for end date. I also have text boxes for states
and then a button that runs a report. I am trying to
create something that would allow a sales rep to enter in
a start date, and end date and then up to ten states into
the various text boxes. It is going against a table that
has data back to 2000. The problem is that the date
restrictors are not working. It sums up sales by
customer for the entire table rather than the start and
end dates I specify. I am not getting any error messages
either. Below is my SQL. Can anyone help?

Thanks,

Chuck

SELECT AllDB.CustomerName, AllDB.state, Sum
(AllDB.Subtotal) AS SumOfSubtotal,
Customer.SalesRepRef_FullName
FROM AllDB INNER JOIN Customer ON AllDB.CustomerName =
Customer.FullName
WHERE (((AllDB.TxnDate) Between [Forms]![SelectState]!
[txtStartDate] And [Forms]![SelectState]![txtEndDate]))
GROUP BY AllDB.CustomerName, AllDB.state,
Customer.SalesRepRef_FullName
HAVING (((AllDB.state)=[Forms]![SelectState]![txtState]
Or (AllDB.state)=[Forms]![SelectState]![txtState2] Or
(AllDB.state)=[Forms]![SelectState]![txtState3] Or
(AllDB.state)=[Forms]![SelectState]![txtState4] Or
(AllDB.state)=[Forms]![SelectState]![txtState5] Or
(AllDB.state)=[Forms]![SelectState]![txtState6] Or
(AllDB.state)=[Forms]![SelectState]![txtState7] Or
(AllDB.state)=[Forms]![SelectState]![txtState8] Or
(AllDB.state)=[Forms]![SelectState]![txtState9] Or
(AllDB.state)=[Forms]![SelectState]![txtState10]));
Click to expand...
 
Back
Top