Where Clause Trouble

[elfranzen]

OK I have code to make a where clause this is working fine. Now I have to add another piece of code to filter it even more, this is where I am having trouble

Code that works fine

'construct a where clause as required for the list box
   'if the Manufactur box is blank then all games will show up on the list
   If Mfg = "0" Then Mfg = "*"
   If Denom <> "" Then where = "DenomFix LIKE '" & Denom & "*' AND "
   If Mfg <> "" Then where = where & "MFRCode LIKE '" & Mfg & "' AND "
   If Search <> "" Then where = where & "Description LIKE '*" & Search & "*' AND "
   'this will filter the list with only the approved games
   Dim Aprvd As String
   Aprvd = "Approved"
   If Me.AprrovedCheck = True Then where = where & "Approved LIKE'" & Aprvd & "'  AND "
   
   'remove the trailing "AND ", if it exists
   If Right(where, 4) = "AND " Then where = Left(where, Len(where) - 4)
   'and if there is any text in the where clause, add the word 'WHERE '
   If where <> "" Then where = "WHERE " & where
   'apply the SQL to the rowsource of the List box to Pick a game
   
   Me.List8.RowSource = _
      "SELECT Approved, ReelStops As [Corp ID], DenomFix as Denom, Description As Theme, Par, MaxCoins As [Max Coin], PayLines As [Pay Lines]" & _
      "FROM MachineTypeQuery " & _
      where & _
      "ORDER BY Description;"

Now what I need to add code to make a where clause that says something like DVIA is greater then 0 (DVIA >0) but I want to do this by its self and add it back into the where clause

Example

Private Sub ApplyPropertyWhere()
If PropertyGlobal = "DVIA" Then PropertyWhereGlobal = "DVIA > 0"

I would then put a line of code in the where clause like this

'This will filter the list with only the property they are from
    If Me.PropertyCheck = True Then where = where & PropertyWhereGlobal

Please Help me fix the "DVIA >0" Thanks

 

[namliam]

You obviously inherited this code someplace/somehow?

Look at your working code closely, you will notice something behind every "where clause" you need to do that here too

 

[elfranzen]

Yes I got help with some of this code awhile ago and some of it is mine. but I have never made where clause using the greater then symbol that is all I need help with how to make a clause with the Greater then symbol.

Can someone please help

 

[namliam]

You have it exactly right... except for you are missing the last "and" bit...

 

[elfranzen]

Yes I got that now I ended up looking up the where clause on google and reading the background on how this works and noticed I was missing the "AND"

Private Sub ApplyPropertyWhere()
If PropertyGlobal = "BT" Then PropertyWhereGlobal = "BTID > 0 AND "
If PropertyGlobal = "DV" Then PropertyWhereGlobal = "DVID > 0 AND "
End Sub

 

[namliam]

Note that you are not using PropertyWhereGlobal anywhere and that PropertyWhereGlobal can only be a check for BTID or DVID not both....

Also it is usually smart when building queries like this in code, to make sure you hare spaces where you might need them. This means putting a space in front and in back of the "part of the clause"
Like so: " BTID > 0 AND "

 

[elfranzen]

Right it will be either their home property or all

I use the PropertyWhereGlobal here

'construct a where clause as required for the list box
   'if the Manufactur box is blank then all games will show up on the list
   If Mfg = "0" Then Mfg = "*"
   If Denom <> "" Then where = "DenomFix LIKE '" & Denom & "*' AND "
   If Mfg <> "" Then where = where & "MFRCode LIKE '" & Mfg & "' AND "
   If Search <> "" Then where = where & "Description LIKE '*" & Search & "*' AND "
   'This will filter the list with only the property they are from
   If Me.PropertyCheck = True Then
    ApplyPropertyWhere
   where = where & [COLOR="Yellow"][B]PropertyWhereGlobal[/B][/COLOR]
   End If
   'this will filter the list with only the approved games
   Dim Aprvd As String
   Aprvd = "Approved"
   
   If Me.AprrovedCheck = True Then where = where & "Approved LIKE'" & Aprvd & "'  AND "

   'remove the trailing "AND ", if it exists
   If Right(where, 4) = "AND " Then where = Left(where, Len(where) - 4)
   'and if there is any text in the where clause, add the word 'WHERE '
   If where <> "" Then where = "WHERE " & where
   'apply the SQL to the rowsource of the List box to Pick a game
      Me.List8.RowSource = _
      "SELECT Approved, ReelStops As [Corp ID], DenomFix as Denom, Description As Theme, Par, MaxCoins As [Max Coin], PayLines As [Pay Lines]" & _
      "FROM MachineTypeQuery " & _
      where & _
      "ORDER BY Description;"

 

[namliam]

Right it will be either their home property or all

What do you mean! by your remark on me

didn't help just made me look like an idot

Maybe I didnt spell it out like you might want me to, but made you look yourself a little harder....

 

[elfranzen]

Well you did help after all, I did try and change it but it will not allow me to. Sorry but at first your reply didn't help. I figured it out on my own within my code. I don't think anyone reading the first two posts, if they did a search for this would have helped them at all either(or maybe it was just me). You have to remember your not just helping me but other that might have the same problem.

 

[namliam]

My original suggestion was exactly the answer to your problem.

Look at your working code closely, you will notice something behind every "where clause" you need to do that here too

Your working code:
If Mfg <> "" Then where = where & "MFRCode LIKE '" & Mfg & "' AND "

Not working code:
If PropertyGlobal = "DVIA" Then PropertyWhereGlobal = "DVIA > 0"

It looks to me like 'the something behind every "where clause" ' is pretty obvious, but that might be my bad...
So sorry if I offended you, but I still dont see how I "didn't help just made me look like an idot".