Problem with Date restrictor on form

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

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]));
 
The noraml advice when handling dates is to ensure that
they are enclosed in # marks and that they are formatted in
mm/dd/yyyy format.

Hope This Helps
Gerald Stanley MCSD
 
Gerald,

Thanks for your help. Would I make a change to my query
in some way? I have formatted my text boxes to be short
dates but that doesn't seem to change anything.

Thanks,

Chuck
-----Original Message-----
The noraml advice when handling dates is to ensure that
they are enclosed in # marks and that they are formatted in
mm/dd/yyyy format.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
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...
 
Chuck

Yes - the # marks and the format statement would have to
appear in the query's SQL.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald,

Thanks for your help. Would I make a change to my query
in some way? I have formatted my text boxes to be short
dates but that doesn't seem to change anything.

Thanks,

Chuck
-----Original Message-----
The noraml advice when handling dates is to ensure that
they are enclosed in # marks and that they are formatted in
mm/dd/yyyy format.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
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...
.
Click to expand...
 
Back
Top