Problem with between date SQL using Access 02

[mickey_lin_uk]

Hi can anyone hlep?

I have written a query In Access 2002 that runs off a form.

The form has four combo boxes & two text boxes.

Business Development (Combo)
Commercial (combo)
Customer (Combo)
Status (combo)
Beginning Date (txt)
End Date (Txt)

So the user could be really specific & use all of the criteria to narrow down the records shown by selecting data in all options or just one/two combo boxes/text boxes.

Before i added the txt boxes the query worked fine. user selected Customer name from list & Status from the list & ALL records matching those 2 criteria would be shown.

However as soon as i added the text boxes it would ONLY bring up the records matching the dates & ignore any other criteria from the combo boxes.

E.g user selected Commercial 'Angie',
Beginning Date '10/1/2004'
End Date '10/30/2004'

Here is a simplified version of my SQL.I have included nulls because i want the user to have the option not to select all of the categories.

SELECT [Tender Detail].[Tender Number], [Tender Detail].Commercial, [Tender Detail].[Business Development Staff], [Tender Detail].Customer, [Tender Detail].ProductDescription, [Tender Detail].[Date Received], [Tender Detail].[Date Due], [Tender Detail].[Tender Sent], [Tender detail.Date Due]-[Tender detail.Date Received] AS [Weeks 4], [Tender Detail.Tender Sent]-[Tender Detail.Date Received] AS [Weeks 5], [Tender Detail.Weeks 5]-[Tender Detail.Weeks 4] AS [Weeks 6], [Tender Detail].[Order Status], [Tender Detail].[Quote Value]

FROM [Tender Detail]

WHERE (([Tender Detail].Commercial =[Forms]![frmRFQ Receipt to Tender Sent]![CboCommercial] Or "«Expr»" Like [Forms]![frmRFQ Receipt to Tender Sent]![CboCommercial] Is Null)) AND

(([Tender Detail].[Business Development Staff])=[Forms]![frmRFQ Receipt to Tender Sent]![CboBusiness Development Staff] Or "«Expr»" Like [Forms]![frmRFQ Receipt to Tender Sent]![CboBusiness Development Staff] Is Null)) AND

(([Tender Detail].Customer)=[Forms]![frmRFQ Receipt to Tender Sent]![CboCustomer] Or "«Expr»" Like [Forms]![frmRFQ Receipt to Tender Sent]![CboCustomer] Is Null)) AND

(([Tender Detail].[Date Due]=Between [Forms]![frmRFQ Receipt to Tender Sent]![txtbegdate] And [Forms]![frmRFQ Receipt to Tender Sent]![txtenddate] Or "«Expr»" Like  [Forms]![frmRFQ Receipt to Tender Sent]![txtbegdate] And [Forms]![frmRFQ Receipt to Tender Sent]![txtenddate] Is Null)) AND

(([Tender Detail].[Order Status])=[Forms]![frmRFQ Receipt to Tender Sent]![CboStatus] Or "«Expr»" Like [Forms]![frmRFQ Receipt to Tender Sent]![CboStatus] Is Null))

]

Not sure why the query is ignoring the other criteria? Any ideas?

Thanks in Advance

Michelle

 

[reclusivemonkey]

Just reading your SQL made my head hurt! One thing that may make your life easier is not to leave spaces in any of the names of your Access Objects (forms, tables, queries, reports, etc.). I noticed several <<Expr>>'s in your SQL as well; this is a place holder the funtion/formula builder leaves when it is creating the strings, so if you have any of these in an SQL query its not going to work. Did you write the query in SQL or use the query builder?

 

[Pat Hartman]

1. Get rid of all instances of "«Expr»" Like .
2. When you use AND and OR in the same condition, you will almost certainly need to use parentheses to make the expression work as you expect.

Remember your math -
A + B * C actually evaluates to
A + (B * C)
NOT
(A + B) * C

Boolean logic is similar.

A AND B OR C evaluates to
(A AND B) OR C
Not
A AND (B OR C)

AND takes presidence over OR, so the AND part of the expression is evaluated and then the result is OR'd with the next expression.

And finally,
[Forms]![frmRFQ Receipt to Tender Sent]![txtbegdate] And [Forms]![frmRFQ Receipt to Tender Sent]![txtenddate] Is Null Does NOT check both fields for null. In fact it doesn't check either field for null since the two fields are AND'd and THAT result is checked for null (which it never will be). You need it to be:
[Forms]![frmRFQ Receipt to Tender Sent]![txtbegdate] Is Null OR [Forms]![frmRFQ Receipt to Tender Sent]![txtenddate] Is Null Notice that I changed the condition to OR. This is necessary because the criteria won't work if either field is null. You could have a situation where start date is valid and end date is null. The AND only handles the case where BOTH dates are null.

 

[mickey_lin_uk]

Thanks for the replies. I used the query builder. Do i need to get rid of all the "«Expr»"

Pat,
now the query brings up All records matching Commercial- 'Angie'. The dates are ignored.

Thanks for the help. I'm fairly new to all of this!

Michelle

 

[Pat Hartman]

"«Expr»" is a place holder that the builder inserts. You need to replace it with an expression. So the answer is yes, get rid of them. You'll need to post your query as it looks once you get rid of all the "«Expr»"'s.

 

[Jon K]

SELECT [Tender Number], Commercial, [Business Development Staff], Customer,
ProductDescription, [Date Received], [Date Due], [Tender Sent],
[Date Due]-[Date Received] AS [Weeks 4],
[Tender Sent]-[Date Received] AS [Weeks 5],
[Weeks 5]-[Weeks 4] AS [Weeks 6],
[Order Status], [Quote Value]
FROM [Tender Detail]

WHERE (Commercial=[Forms]![frmRFQ Receipt to Tender Sent]![CboCommercial] Or [Forms]![frmRFQ Receipt to Tender Sent]![CboCommercial] Is Null)=True AND

([Business Development Staff]=[Forms]![frmRFQ Receipt to Tender Sent]![CboBusiness Development Staff] Or [Forms]![frmRFQ Receipt to Tender Sent]![CboBusiness Development Staff] Is Null)=True AND

(Customer=[Forms]![frmRFQ Receipt to Tender Sent]![CboCustomer] Or [Forms]![frmRFQ Receipt to Tender Sent]![CboCustomer] Is Null)=True AND

([Date Due] Between [Forms]![frmRFQ Receipt to Tender Sent]![txtbegdate] And [Forms]![frmRFQ Receipt to Tender Sent]![txtenddate] Or [Forms]![frmRFQ Receipt to Tender Sent]![txtbegdate] Is Null OR [Forms]![frmRFQ Receipt to Tender Sent]![txtenddate] Is Null)=True AND

([Order Status]=[Forms]![frmRFQ Receipt to Tender Sent]![CboStatus] Or [Forms]![frmRFQ Receipt to Tender Sent]![CboStatus] Is Null)=True


Note
The =Trues are used to ensure that Access will not re-arrange the Where Clause of the statement even if you subsequently edit the query in Design View and save the query there.

They will also keep the criteria for each field neatly in a separate column in query Design View.
.

 

[Pat Hartman]

Now that the criteria is restructured, I think you need to go back to using AND in the Is Null part of the date compare.

([Date Due] Between [Forms]![frmRFQ Receipt to Tender Sent]![txtbegdate] And [Forms]![frmRFQ Receipt to Tender Sent]![txtenddate] Or ([Forms]![frmRFQ Receipt to Tender Sent]![txtbegdate] Is Null AND [Forms]![frmRFQ Receipt to Tender Sent]![txtenddate] Is Null))=True

Notice the additional set of parentheses.

 

[Jon K]

There is a slight difference between using OR and AND there.

Using OR, all the records will be returned if either txtbegdate or txtenddate is left blank.

Using AND, no records will be returned if one of them is left blank while the other contains a date.


The poster may add code on the form to ensure that both text boxes contain a date or both of them are left blank. Then the query can be simplified to check for Null value in only one of the text boxes.
.

 

[mickey_lin_uk]

Thanks for all you help! It works!!

I made a new query & used SQL to write the code NOT the design view. When I used the design view it replicated the same line loads of times, so it was very hard to see anything in the SQL view. Thats why I thought it was best to start again.

The SQL on post # 6 & 7 worked.

I can't tell you how pleased I am to finally get it working!!

Thanks Again

Michelle