How to put mutiple conditions in simple query

[rickyfong]

I have created a simple query!

And I would like to query the query sheet using conditions, with the following 2 type of practical query.

a) date range -> between starting date and ending date under invoicedate field
c) only working status -> status = "Y" under status field

My problem is when I put both in the same condition row, nothing appeared. While I put only a) , query result accordingly appeared and if I put only c) , correct query result also appear.

Then , please help how can I put both conditions under fields, as far as I also need to query independently in the same query sheet at a) and c) conditions. Thanks!! If SQL is required, please provide some reference code for me to study!! THanks again!!

 

[stopher]

If you put both conditions in the same row then the query should return only records where both conditions are true. Are you sure you have records where both conditions are true?

Chris

 

[rickyfong]

My problem is that after I enter the first date range condition, it won't query any record. If I take out the second conditon, it work as expectation. On the other way around, the second condiion also can work independently. THis is quite strange. However, as I mentioned I would like to have 3 different types of queries, of which are one for each and one for two of them together. Is that I have to created 3 different query sheets, one caters for each conditions??

I have tired some so-called SQL VB code, but I don't know how to link the result to that query sheet!!

Dim db As Database
Dim STRSQL As String, STRSQL1 As String
DoCmd.SetWarnings False
Set db = CurrentDb

STRSQL = "SELECT TransStatus.¶©µ¥±àºÅ, TransStatus.¿Í»§¼ò³Æ, TransStatus.ÐÔÙ|, TransStatus.bpi, TransStatus.ÒªÇó, TransStatus.µ¥Î»Ãû³Æ, TransStatus.¶©µ¥ÈÕÆÚ, TransStatus.éL_M, TransStatus.¸ß_M, TransStatus.¼þ”µ, TransStatus.ÊýÁ¿, TransStatus.¿ÉÒÔ, TransStatus.ÍâÅÐ, TransStatus.ÅçÀL, TransStatus.ÖÆ×÷, TransStatus.ÍâÅÐÊÕØ›, TransStatus.×ÔÈ¡, TransStatus.ËÍØ›, TransStatus.ÍËØ›, TransStatus.ÍË؛ԭÒò, TransStatus.Ô½Y, TransStatus.³ö†Î, TransStatus.ÊÕåX, TransStatus.È¡Ïû, TransStatus.È¡ÏûÈÕÆÚ, TransStatus.ÅçÀLÈÕÆÚ, TransStatus.ÖÆ×÷ÈÕÆÚ, TransStatus.ÍâÅÐÊÕØ›ÈÕÆÚ, TransStatus.ËÍØ›ÈÕÆÚ, TransStatus.ÊÕåXÈÕÆÚ, TransStatus.ÍËØ›ÈÕÆÚ, TransStatus.OpenResp, TransStatus.PrintResp, TransStatus.leader, TransStatus.ÏîÄ¿Ãû³Æ" & _
"FROM TransStatus WHERE (((TransStatus.ÅçÀL)=[forms]![¶©µ¥¸ú×Ù].[TEXT28]))"

db.Execute STRSQL


Sorry that some are chinese field names. But the sequence of the VB is something like the VB shown as before. I have created some text boxes in a form to hold condition input values. and a query sub form with the name child5 at the bottom to show query result. As before, when I put those conditions under the query design view, it worked in one by one case. When I changed to SQL VB code and taken out those conditons in the query design view. After execution, it simple return all the record to me without according to any input condition!! Please help in either way. It seems qutie simple case for experience people!! Right?? Thanks!!

 

[stopher]

The last bit needs to be something like:

"FROM TransStatus WHERE TransStatus.ÅçÀL='" & [forms]![¶©µ¥¸ú×Ù].[TEXT28] & "'"

Note how the form reference is treated as a variable and concatenated with the SQL string. Note also how we have to embed the single quote to show that the form reference is treated as a string and not a number.

hth
Chris

 

[rickyfong]

After changing the syntax, the results are the same as before. Just want to know is that I have to do something after the

db.Execute STRSQL

statement, in order to link the condition with the query inside the subform. Please note that I had removed all conditions from that query!!

Thanks!!

 

[rickyfong]

Any body!? Please Help!! Thanks a lot!!

 

[jdraw]

As was mentioned in post #2, you have to make sure you have records that meet both conditions. In other words, there may not be any data with the 2 conditions.
When you place conditions on the same line in the query wizard, you are really saying
...WHERE Condition1 AND Condition2 are true.


Here's a link with an explanation that may be useful.

http://www.databasedev.co.uk/sql-multiple-conditions.html