Can't get the correct quotes or syntax on strSQL multiple WHEREclause

H

heidi

Hello All:

I am struggling getting the correct syntax.

Here is what I have:

THIS WORKS!
Main form with a a list box.
LISTBLOCKSELECTION List box is populated by
qryRecommendationsBlockListBox on the afterupdate of
comboranchselection combo box on my main form and txtConventionalCheck
text box is null or not null.
Afterupdate code: me.LISTBLOCKSELECTION.requery

THIS WORKS!
LISTBLOCKSELECTION List box repopulated by
qryRecommendationsBlockListBox on the afterupdate of second combocrop
combo box on my main form.
Afterupdate code: me.LISTBLOCKSELECTION.requery

WON'T WORK!
Have command buttons above my list box to sort the columns Asc or Desc
order based on the already filtered results.

Here is the code behind one sort button.

Private Sub CMDCOMMODITYASC_Click()
'Set commodity order in ascending order and apply captions

Dim response As Integer
response = basOrderby("[COMMODITY]", "ASC")
Me!CMDCOMMODITYDESC.Visible = True
Me!CMDCOMMODITYDESC.Caption = "COMMODITY"
Me!CMDCOMMODITYDESC.SetFocus
Me!CMDCOMMODITYASC.Visible = False
Me!LISTBLOCKSELECTION.SetFocus
End Sub

When using the basOrderBy code, I can not get the multiple WHERE
clause correct to reset the listbox's recordsource.

Here is the basOrderby code:

Private Function basOrderby(col As String, xorder As String) As
Integer
Dim strSQL As String

'Set row source for list box
strSQL = "SELECT DISTINCTROW [LOTNUMBER],[COMMODITY], [BLOCKNAME],
[VARIETY], [SUMOFACRES],[STATUS],[STATUSCHECK]"
strSQL = strSQL & "FROM [qryRecommendationsBlockListBox] "
strSQL = strSQL & "WHERE [LOTNUMBER]=[forms]!
[frmRecommendationsEntry]![lotnumber] AND [COMMODITY]=[forms]!
[frmRecommendationsEntry]![combocrop] AND [STATUSCHECK]=[forms]!
[frmRecommendationsEntry]![txtConventional] OR [LOTNUMBER]=[forms]!
[frmRecommendationsEntry]![lotnumber] AND [STATUSCHECK]=[forms]!
[frmRecommendationsEntry]![txtConventional] AND isnull([COMMODITY]=
[forms]![frmRecommendationsEntry]![combocrop]) OR [LOTNUMBER]=[forms]!
[frmRecommendationsEntry]![lotnumber] AND [COMMODITY]=[forms]!
[frmRecommendationsEntry]![combocrop]) AND isnull([STATUSCHECK]=
[forms]![frmRecommendationsEntry]![txtConventional]) OR [LOTNUMBER]=
[forms]![frmRecommendationsEntry]![lotnumber] AND ([COMMODITY]=[forms]!
[frmRecommendationsEntry]![combocrop]) Is Null)) AND isnull
([STATUSCHECK]=[forms]![frmRecommendationsEntry]![txtConventional]) "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!LISTBLOCKSELECTION.RowSource = strSQL
Me!LISTBLOCKSELECTION.Requery
End Function

Below is the SQL from my underlying query:

SELECT qryRecommendationsBlockListBox.LotNumber,
qryRecommendationsBlockListBox.Commodity,
qryRecommendationsBlockListBox.BlockName,
qryRecommendationsBlockListBox.Variety,
qryRecommendationsBlockListBox.sumOfAcres,
qryRecommendationsBlockListBox.Status,
qryRecommendationsBlockListBox.StatusCheck
FROM qryRecommendationsBlockListBox
WHERE (((qryRecommendationsBlockListBox.LotNumber)=[forms]!
[frmRecommendationsEntry]![lotnumber]) AND
((qryRecommendationsBlockListBox.Commodity)=[forms]!
[frmRecommendationsEntry]![combocrop]) AND
((qryRecommendationsBlockListBox.StatusCheck)=[forms]!
[frmRecommendationsEntry]![txtConventional])) OR
(((qryRecommendationsBlockListBox.LotNumber)=[forms]!
[frmRecommendationsEntry]![lotnumber]) AND
((qryRecommendationsBlockListBox.StatusCheck)=[forms]!
[frmRecommendationsEntry]![txtConventional]) AND (([forms]!
[frmRecommendationsEntry]![combocrop]) Is Null)) OR
(((qryRecommendationsBlockListBox.LotNumber)=[forms]!
[frmRecommendationsEntry]![lotnumber]) AND
((qryRecommendationsBlockListBox.Commodity)=[forms]!
[frmRecommendationsEntry]![combocrop]) AND (([forms]!
[frmRecommendationsEntry]![txtConventional]) Is Null)) OR
(((qryRecommendationsBlockListBox.LotNumber)=[forms]!
[frmRecommendationsEntry]![lotnumber]) AND (([forms]!
[frmRecommendationsEntry]![combocrop]) Is Null) AND (([forms]!
[frmRecommendationsEntry]![txtConventional]) Is Null))
ORDER BY qryRecommendationsBlockListBox.BlockName,
qryRecommendationsBlockListBox.Variety;

PLEASE HELP

What am I typing incorrectly. I can't find it.

Heidi
 
J

Jeff Boyce

Heidi

That's quite a lot of information! If you don't get many responses right
away, consider reposting with a more condensed description.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

heidi said:
Hello All:

I am struggling getting the correct syntax.

Here is what I have:

THIS WORKS!
Main form with a a list box.
LISTBLOCKSELECTION List box is populated by
qryRecommendationsBlockListBox on the afterupdate of
comboranchselection combo box on my main form and txtConventionalCheck
text box is null or not null.
Afterupdate code: me.LISTBLOCKSELECTION.requery

THIS WORKS!
LISTBLOCKSELECTION List box repopulated by
qryRecommendationsBlockListBox on the afterupdate of second combocrop
combo box on my main form.
Afterupdate code: me.LISTBLOCKSELECTION.requery

WON'T WORK!
Have command buttons above my list box to sort the columns Asc or Desc
order based on the already filtered results.

Here is the code behind one sort button.

Private Sub CMDCOMMODITYASC_Click()
'Set commodity order in ascending order and apply captions

Dim response As Integer
response = basOrderby("[COMMODITY]", "ASC")
Me!CMDCOMMODITYDESC.Visible = True
Me!CMDCOMMODITYDESC.Caption = "COMMODITY"
Me!CMDCOMMODITYDESC.SetFocus
Me!CMDCOMMODITYASC.Visible = False
Me!LISTBLOCKSELECTION.SetFocus
End Sub

When using the basOrderBy code, I can not get the multiple WHERE
clause correct to reset the listbox's recordsource.

Here is the basOrderby code:

Private Function basOrderby(col As String, xorder As String) As
Integer
Dim strSQL As String

'Set row source for list box
strSQL = "SELECT DISTINCTROW [LOTNUMBER],[COMMODITY], [BLOCKNAME],
[VARIETY], [SUMOFACRES],[STATUS],[STATUSCHECK]"
strSQL = strSQL & "FROM [qryRecommendationsBlockListBox] "
strSQL = strSQL & "WHERE [LOTNUMBER]=[forms]!
[frmRecommendationsEntry]![lotnumber] AND [COMMODITY]=[forms]!
[frmRecommendationsEntry]![combocrop] AND [STATUSCHECK]=[forms]!
[frmRecommendationsEntry]![txtConventional] OR [LOTNUMBER]=[forms]!
[frmRecommendationsEntry]![lotnumber] AND [STATUSCHECK]=[forms]!
[frmRecommendationsEntry]![txtConventional] AND isnull([COMMODITY]=
[forms]![frmRecommendationsEntry]![combocrop]) OR [LOTNUMBER]=[forms]!
[frmRecommendationsEntry]![lotnumber] AND [COMMODITY]=[forms]!
[frmRecommendationsEntry]![combocrop]) AND isnull([STATUSCHECK]=
[forms]![frmRecommendationsEntry]![txtConventional]) OR [LOTNUMBER]=
[forms]![frmRecommendationsEntry]![lotnumber] AND ([COMMODITY]=[forms]!
[frmRecommendationsEntry]![combocrop]) Is Null)) AND isnull
([STATUSCHECK]=[forms]![frmRecommendationsEntry]![txtConventional]) "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!LISTBLOCKSELECTION.RowSource = strSQL
Me!LISTBLOCKSELECTION.Requery
End Function

Below is the SQL from my underlying query:

SELECT qryRecommendationsBlockListBox.LotNumber,
qryRecommendationsBlockListBox.Commodity,
qryRecommendationsBlockListBox.BlockName,
qryRecommendationsBlockListBox.Variety,
qryRecommendationsBlockListBox.sumOfAcres,
qryRecommendationsBlockListBox.Status,
qryRecommendationsBlockListBox.StatusCheck
FROM qryRecommendationsBlockListBox
WHERE (((qryRecommendationsBlockListBox.LotNumber)=[forms]!
[frmRecommendationsEntry]![lotnumber]) AND
((qryRecommendationsBlockListBox.Commodity)=[forms]!
[frmRecommendationsEntry]![combocrop]) AND
((qryRecommendationsBlockListBox.StatusCheck)=[forms]!
[frmRecommendationsEntry]![txtConventional])) OR
(((qryRecommendationsBlockListBox.LotNumber)=[forms]!
[frmRecommendationsEntry]![lotnumber]) AND
((qryRecommendationsBlockListBox.StatusCheck)=[forms]!
[frmRecommendationsEntry]![txtConventional]) AND (([forms]!
[frmRecommendationsEntry]![combocrop]) Is Null)) OR
(((qryRecommendationsBlockListBox.LotNumber)=[forms]!
[frmRecommendationsEntry]![lotnumber]) AND
((qryRecommendationsBlockListBox.Commodity)=[forms]!
[frmRecommendationsEntry]![combocrop]) AND (([forms]!
[frmRecommendationsEntry]![txtConventional]) Is Null)) OR
(((qryRecommendationsBlockListBox.LotNumber)=[forms]!
[frmRecommendationsEntry]![lotnumber]) AND (([forms]!
[frmRecommendationsEntry]![combocrop]) Is Null) AND (([forms]!
[frmRecommendationsEntry]![txtConventional]) Is Null))
ORDER BY qryRecommendationsBlockListBox.BlockName,
qryRecommendationsBlockListBox.Variety;

PLEASE HELP

What am I typing incorrectly. I can't find it.

Heidi
Click to expand...
 
H

heidi

the only more condensed I can get is that my code to reset the
rowsource of the list box is not working.

I posted all the code because everyone always asks for it later when
you don't post it initially.

this is the code that doesn't work. It doesn't work on the WHERE
clause.

Private Function basOrderby(col As String, xorder As String) As
Integer
Dim strSQL As String


'Set row source for list box
strSQL = "SELECT DISTINCTROW [LOTNUMBER],[COMMODITY],
[BLOCKNAME],
[VARIETY], [SUMOFACRES],[STATUS],[STATUSCHECK]"
strSQL = strSQL & "FROM [qryRecommendationsBlockListBox] "
strSQL = strSQL & "WHERE [LOTNUMBER]=[forms]!
[frmRecommendationsEntry]![lotnumber] AND [COMMODITY]=[forms]!
[frmRecommendationsEntry]![combocrop] AND [STATUSCHECK]=[forms]!
[frmRecommendationsEntry]![txtConventional] OR [LOTNUMBER]=[forms]!
[frmRecommendationsEntry]![lotnumber] AND [STATUSCHECK]=[forms]!
[frmRecommendationsEntry]![txtConventional] AND isnull([COMMODITY]=
[forms]![frmRecommendationsEntry]![combocrop]) OR [LOTNUMBER]=
[forms]!
[frmRecommendationsEntry]![lotnumber] AND [COMMODITY]=[forms]!
[frmRecommendationsEntry]![combocrop]) AND isnull([STATUSCHECK]=
[forms]![frmRecommendationsEntry]![txtConventional]) OR [LOTNUMBER]=
[forms]![frmRecommendationsEntry]![lotnumber] AND ([COMMODITY]=
[forms]!
[frmRecommendationsEntry]![combocrop]) Is Null)) AND isnull
([STATUSCHECK]=[forms]![frmRecommendationsEntry]![txtConventional]) "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!LISTBLOCKSELECTION.RowSource = strSQL
Me!LISTBLOCKSELECTION.Requery
End Function

Thansk
 
J

Jeff Boyce

If this were mine, I'd start the query over, without any WHERE conditions,
then start adding them one at a time, testing after each addition, until it
"doesn't work".

(by the way, "doesn't work" could mean you get an error message (?saying
what?), or that it returns records, but not the correct ones, or that it
returns correct records, but not enough, or ...) What do you mean by
"doesn't work"?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top