Solved WHERE with multiple options (1 Viewer)

[wmix]

Hi Everyone, I'm struggling with creating a string that's being used to create a query.

Couple of quick notes...
strList - data comes from earlier in the code.
SrchDate.Value exists and has been verified before the below code happens.
sDate1 and sDate2 have been previously defined in the code as Date variables. MAYBE this is the problem? They are dates and not a string?

I'm able to get the IF statement to work. But the ELSE statement is causing me issues.

I THINK I need the following information:
qProducts_MachineCheck.FillWeek Between # sDate1 # And # sDate2 #
However, no matter what I combination of statements I use I end up with errors. I'm so stumped, and would greatly appreciate guidance on this, thank you!

Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Dim strSQL As String
Dim strSelect As String
Dim strWhere As String

strSelect = "Select * FROM qProducts_Machine "

IF Me.SrchDate.Value = "all" Then

strWhere = "WHERE qProducts_MachineCheck.MachineID IN (" & strList & ");"

ELSE

strWhere = "WHERE qProducts_MachineCheck.MachineID IN (" & strList & ") ????? Between # sDate1 # And # sDate2 # ????? Not sure how to get this here

End If

strSQL = strSelect & strWhere

 

[Micron]

Was this one of those attempts? They might have been helpful.
"WHERE qProducts_MachineCheck.MachineID IN (" & strList & ") AND qProducts_MachineCheck.FillWeek Between #" & sDate1 & "# And # sDate2 & "#"
If in one case you want everything, then why do you need an IN clause?

 

[plog]

strSelect = "Select * FROM qProducts_Machine "

IF Me.SrchDate.Value = "all" Then

strWhere = "WHERE qProducts_MachineCheck.MachineID IN (" & strList & ");"

Your WHERE is referencing a field from a datasource your query doesn't know about. qProducts_MachineCheck isn't in the FROM or a JOIN.

 

[CJ_London]

also make sure your sDate values are formatted as a string in the format mm/dd/yyyy

 

[wmix]

Was this one of those attempts? They might have been helpful.
"WHERE qProducts_MachineCheck.MachineID IN (" & strList & ") AND qProducts_MachineCheck.FillWeek Between #" & sDate1 & "# And # sDate2 & "#"
If in one case you want everything, then why do you need an IN clause?

Micron, thanks for the reply. I do need the IN because it's possible to have multiple machines I'm searching for. I will try your code and see if it works, thanks.

 

[wmix]

strSelect = "Select * FROM qProducts_Machine "

IF Me.SrchDate.Value = "all" Then

strWhere = "WHERE qProducts_MachineCheck.MachineID IN (" & strList & ");"

Your WHERE is referencing a field from a datasource your query doesn't know about. qProducts_MachineCheck isn't in the FROM or a JOIN.

Thank you Plog for your reply. That was a typo on my part, the Select is FROM qProducts_MachineCheck.

 

[Micron]

I do need the IN because it's possible to have multiple machines I'm searching for.

I still don't get it. Usually if you want "all" then you just don't apply any criteria, which gives you everything.

 

[Gasman]

Do yourself a favour and debug.print strWhere or strSQL when it has been constructed and before you try to use it.
Then comment that out once you get the syntax correct.?

 

[wmix]

also make sure your sDate values are formatted as a string in the format mm/dd/yyyy

CJ_London, thank you for your reply. I think this might be the issue. Here's the code I'm using to get the dates. I'm not great at VBA so this may not be correct, but it does give me the dates I need.

I used a MSGBox to review the WHERE code for my string and it "appears" correct but it's not working in the created query, it gives me all the dates. Here's the current Where string I'm using for the ELSE statement:

strWhere = "WHERE qProducts_MachineCheck.MachineID IN (" & strList & ") AND qProducts_MachineCheck.FillWeek Between #" & sDate1 & "# AND #" & sDate2 & "#;"

Private Sub FrameDateSelection_Click()

Dim dt as Date
Dim iyear As Integer
Dim xyear As Integer

dt = Date
iyear = Year(dt)
xyear = iyear - 1

Dim vCYfirstdate As Date
Dim vCYlastdate As Date
Dim vLYfirstdate As Date
Dim vLYlastdate As Date

vCYfirstdate = DateSerial(Year(Date),1,1)
vCYlastdate = DateSerial(Year(Date),12,31)
vLYfirstdate = DateSerial(xyear),1,1)
vLYlastdate = DateSerial (xyear),12,31)

Dim sDate1 As String
Dim sDate2 As String

Select Case FrameDateSelection

Case 1
Me.SrchDate.Value = "lastyear"
Me.sDate1.Value = Format(vLYfirstdate,"mm/dd/yyyy")
Me.sDate2.Value = Format(vLYlastdate, "mm/dd/yyyy")

Case 2
Me.SearchDate.Value = "currentyear"
Me.sDate1.Value = Format(vCYfirstdate, "mm/dd/yyyy")
Me.sDate2.Value = Format(vCYlastdate, "mm/dd/yyyy")

Case 3
Me.SearchDate.Value = "lastandCurrentyear"
Me.sDate1.Value = Format(vLYfirstdate,"mm/dd/yyyy")
Me.sDate2.Value = Format(vCYlastdate,"mm/dd/yyyy")

Case 4
Me.SearchDate.Value = "all"

End Case
End Sub

 

[wmix]

I still don't get it. Usually if you want "all" then you just don't apply any criteria, which gives you everything.

Micron, they either have to select one machine or several machines for the query, we have over 700 machines so we don't want the report for all of them. We are trying to narrow down the data to only what is currently needed.

 

[Micron]

Sorry, I guess I don't understand your application of the word "all" that I saw in your code. I took it to mean that it was a combo choice and the implication is that it was for returning records for all machines, in which case, one would not need an IN clause. Don't worry about it or my confusion.

 

[wmix]

So I deleted everything and retyped it, must have been missing something, not sure. This now works with the dates changed to strings. Thanks everyone for your help!

strWhere = "WHERE qProducts_MachineCheck.MachineID IN (" & strList & ") AND qProducts_MachineCheck.FillWeek Between #" & sDate1 & "# AND #" & sDate2 & "#;"

 

[Micron]

Crap! I forgot to concatenate the other date variable in my first post!
"# And # sDate2