two where statements

[elfranzen]

'construct a where clause as required
If Nz(Me.Manufacturer.Text, "") <> "" Then
Where1 = "WHERE MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
End If

Me.list.RowSource = _
"SELECT ID, Description, Par, MaxCoins, PayLines " & _
"FROM MachineTypeQuery " & _
Where1, Where2 & _ <- how do i get something like this to work
"ORDER BY Description;"


Exit Sub
Where2 comes from another control and has to be filled in before they get to this control. also "where1" and "where2" are set as public stings

 

[pbaldy]

You can't have 2 actual WHERE clauses, but you could concatenate 2 strings together, providing they were correctly structured. For instance these 2 would work:

"WHERE A = B"
" AND C = D"

 

[elfranzen]

'construct a where clause as required
If Nz(Me.Manufacturer.Text, "") <> "" Then
Where1 = "WHERE MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
Where3 = Where1 + Where2 <- tried to combine the two
End If

Me.list.RowSource = _
"SELECT ID, Description, Par, MaxCoins, PayLines " & _
"FROM MachineTypeQuery " & _
Where3 & _ Used the combined string
"ORDER BY Description;"

Ok no errors but didn't work what is the correct way of combining these two do they need to be in order by how they are in order inthe query?

 

[pbaldy]

Where3 = Where1 & Where2

They don't necessarily have to be in a particular order, other than the syntax of the resulting statement must be valid. Add

Debug.Print Where3

after it's built and you can examine the final clause in the immediate window (and cut/paste it here if you don't see the problem).

 

[elfranzen]

Ok changed it to Where3 = Where1 & Where2

Still doesn't work

also "Debug.Print Where3" I put this right after the code and nothing happened where does this need to go and what should happen?

 

[elfranzen]

how about this

WHERE MFRcode LIKE '*7*' WHERE DenomFix LIKE '0.01*'

 

[pbaldy]

Like I said, you can't have 2 WHERE clauses (the word WHERE twice). For the second one, you'll need to change the word WHERE to the word AND.