Problem with between date using Access 02

G

Guest

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.

SQL:
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
 
K

Ken Snell [MVP]

You must have used the Expression Builder to create the query statement.

In each part of the WHERE clause, you have this snippet:
Or "«Expr»" Like [Forms]!

Change it to this for each occurrence:
Or [Forms]!

(And, of course, keep the stuff that comes after [Forms]! in each line.)

--

Ken Snell
<MS ACCESS MVP>



mickeylin said:
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.

SQL:
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
Click to expand...
 
G

Guest

Now if you search for Commercial 'Angie',
Beginning Date '10/1/2004'
End Date '10/30/2004'

It returns no records. It works when use any of the combo boxes but not the
txt boxes.

Thanks in advance

Michelle

Ken Snell said:
You must have used the Expression Builder to create the query statement.

In each part of the WHERE clause, you have this snippet:
Or "«Expr»" Like [Forms]!

Change it to this for each occurrence:
Or [Forms]!

(And, of course, keep the stuff that comes after [Forms]! in each line.)

--

Ken Snell
<MS ACCESS MVP>



mickeylin said:
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.

SQL:
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
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

It may be that ACCESS is not recognizing your textboxes as date values. Try
this SQL (am using CDate function to explicitly cast the dates from the
textboxes as date values):

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 [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 [Forms]![frmRFQ
Receipt to Tender Sent]![CboBusiness Development Staff] Is Null)) AND

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

(([Tender Detail].[Date Due]=Between CDate([Forms]![frmRFQ Receipt to Tender
Sent]![txtbegdate]) And CDate([Forms]![frmRFQ Receipt to Tender
Sent]![txtenddate])
Or [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 [Forms]![frmRFQ Receipt to Tender
Sent]![CboStatus] Is Null))



--

Ken Snell
<MS ACCESS MVP>


mickeylin said:
Now if you search for Commercial 'Angie',
Beginning Date '10/1/2004'
End Date '10/30/2004'

It returns no records. It works when use any of the combo boxes but not the
txt boxes.

Thanks in advance

Michelle

Ken Snell said:
You must have used the Expression Builder to create the query statement.

In each part of the WHERE clause, you have this snippet:
Or "«Expr»" Like [Forms]!

Change it to this for each occurrence:
Or [Forms]!

(And, of course, keep the stuff that comes after [Forms]! in each line.)

--

Ken Snell
<MS ACCESS MVP>



mickeylin said:
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
Click to expand...
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.

SQL:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I've made it work.

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.

CODE:

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 ([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;

I've split it up to make it clearer so when you’re writing it in SQL, so
don't leave line spaces.

Hope it may be of use to someone.

Thanks for your help.
Michelle
 
K

Ken Snell [MVP]

That looks better!

Yes, when doing the more complicated SQL statements such as this, I too find
it's easier to work in the SQL window instead of the query DBE window.

Good luck!

--

Ken Snell
<MS ACCESS MVP>

mickeylin said:
I've made it work.

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.

CODE:

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 ([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;

I've split it up to make it clearer so when you're writing it in SQL, so
don't leave line spaces.

Hope it may be of use to someone.

Thanks for your help.
Michelle
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime error 13 Type mismatch - something to do with date?? 4
IIF ([date_combo_box] Is Null,“xâ€, Between date 1 And date 2) 1
Problems with Date() on forms - Help Please 5
Xtab criteria problem? 5
Need help with a multiple field query PLEASE 2
Help with the All Option in a Combo Box on a Form (access 2000) 8
Date Criteria problem 2
Problem with Desktop After Switching Accounts 4

Top