If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with Date Restrictor on Form
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])); |
#2
|
|||
|
|||
Problem with Date Restrictor on Form
WHERE (((AllDB.TxnDate) Between [Forms]![SelectState]!
[txtStartDate] And [Forms]![SelectState]![txtEndDate])) 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" wrote in 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])); |
#3
|
|||
|
|||
Problem with Date Restrictor on Form
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])) 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" wrote in 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])); . |
#4
|
|||
|
|||
Problem with Date Restrictor on Form
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 W wrote: 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])); |
Thread Tools | |
Display Modes | |
|
|