Multiple condition query

[sathyaganapathi]

Hi,
Could you please help to combine below two quries under one?

If Me.ComboBox1.Value <> "Lab_Pending" And Me.ComboBox1.Value <> "ALL" Then
    qry = "SELECT * FROM TBL_PlabInput WHERE " & Me.ComboBox1.Value & " LIKE '%" & Me.TextSearch.Value & "%'"
End If

If Me.ComboBox1.Value = "ALL" And sDate <> "" And Me.ComboBox1.Value = "ALL" And eDate <> "" Then ' this is working
    qry = "SELECT * FROM TBL_PlabInput where Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"
End If

Both are working fine when user separately.
when try to combine them together nothing worrks.
What I want is, first query to work when date range is given.
Please help.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Query with multiple condition to get data from access
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[sathyaganapathi]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Query with multiple condition to get data from access
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hi Fluff,
Thanks for reminding the rule and the link.
Here is the Link to other forum.
Please help with the solution to the above request.
Thanks again.

 

[sathyaganapathi]

Hi all,

Request somebody to please help on this.

I want below code to work to fetch data from access database when user want to get the data for a selected search term between two given dates.
I am not sure where the code is wrong..
Please help.

If Me.ComboBox1.Value <> "Lab_Pending" And Me.ComboBox1.Value <> "ALL" Then
    qry = "SELECT * FROM TBL_PlabInput WHERE " & Me.ComboBox1.Value & " LIKE '%" & Me.TextSearch.Value & "%' & Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"
End If

 

[Micron]

Would help if you posted what the result is - nothing returned, wrong results, error message??
I suspect it is because you should be writing along the lines of "...WHERE SomeFieldName LIKE" not "...WHERE Me.SomeControlName LIKE"

You could always use the query builder and then copy from the sql view and modify that.

 

[sathyaganapathi]

Would help if you posted what the result is - nothing returned, wrong results, error message??
I suspect it is because you should be writing along the lines of "...WHERE SomeFieldName LIKE" not "...WHERE Me.SomeControlName LIKE"

You could always use the query builder and then copy from the sql view and modify that.

Dear Micron,
As I mentioned in my earlier post, below two codes when run seperately works fine.

code 1 - works fine.

If Me.ComboBox1.Value <> "Lab_Pending" And Me.ComboBox1.Value <> "ALL" Then
    qry = "SELECT * FROM TBL_PlabInput WHERE " & Me.ComboBox1.Value & " LIKE '%" & Me.TextSearch.Value & "%'"
End If

Code 2 - works fine

If Me.ComboBox1.Value = "ALL" And sDate <> "" And Me.ComboBox1.Value = "ALL" And eDate <> "" Then
    qry = "SELECT * FROM TBL_PlabInput where Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"
End If

Or like below also works fine.

If Me.ComboBox1.Value <> "Lab_Pending" And Me.ComboBox1.Value <> "ALL" Then
    qry = "SELECT * FROM TBL_PlabInput WHERE " & Me.ComboBox1.Value & " LIKE '%" & Me.TextSearch.Value & "%'"
Elseif Me.ComboBox1.Value = "ALL" And sDate <> "" And Me.ComboBox1.Value = "ALL" And eDate <> "" Then ' this is working
    qry = "SELECT * FROM TBL_PlabInput where Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"
End If

but, I want both the above queries in one query so that user can get the data searhed in code 1 for a date range selected. where exactly I need to correct the code is the question for me now.
Could you please help to rewrite it correctly?
How to use the "query builder"?

 

[Micron]

Still not sure I get it - you have different conditions for each query. . You could post what you tried when you put them together? Or is it just

"SELECT * FROM TBL_PlabInput WHERE " & Me.ComboBox1 & " LIKE '%" & Me.TextSearch & "%' AND Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#""

 

[sathyaganapathi]

Still not sure I get it - you have different conditions for each query. . You could post what you tried when you put them together? Or is it just

"SELECT * FROM TBL_PlabInput WHERE " & Me.ComboBox1 & " LIKE '%" & Me.TextSearch & "%' AND Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#""

Dear Micron,
I tried with the code you suggested.
It is just what I wanted !!! I did small modification to the condition and the struggle is over. Thanks a lot for your help.

 

[Micron]

I took a wild guess at what you wanted. Glad I was able to help!

 

[sathyaganapathi]

Dear Micron,
100% perfect guess
btw, what is the difference between Me.Combobox1.Value and Me.Combobox?
why the earlier code was not worknig? It would be helpful if you can give some comments to understand and work on future query requirements.
Thanks in advance.