Passing statements like "Or" or regular string values to querycriteria field

[Matt P]

Hello I am sure I was trying to go about this the wrong way and it
almost worked out I have a form with an Option Group which has 8
fields. So I put a "Select Case" statement in vb. And now for my
problem, I have 2 list boxes that I want to get populated off of the
selection they make and only show the results pertaining to that
selection, basically a filter. I got this to work by adding an
unbound text box to my form "txtSortBy" which then in each case I made
it change the value to the selection that they made. With this I got
almost all of them working, only 3 selections didn't work properly.
Here's my sql:
SELECT tblContacts.fldAddress, tblContacts.fldContactsID
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
WHERE (((tblContacts.fldCity)=[cmbSearchComp]) AND
((tblHistory.fldType)=[txtSortBy]))
GROUP BY tblContacts.fldAddress, tblContacts.fldContactsID, Mid$
(tblContacts.fldAddress,InStr(tblContacts.fldAddress," ")+1)
ORDER BY Mid$(tblContacts.fldAddress,InStr(tblContacts.fldAddress," ")
+1);

The ones that didn't work are (these are from the select case
statement):
Me.txtSortBy.Value = "Is Null"
Me.txtSortBy.Value = "APARTMENT OR CONDO"
Me.txtSortBy.Value = "HOTEL OR MOTEL"

The other 5 work, just these 3. I would have skipped this whole
unbound text field if I could have figured out what the proper syntax
was to add this query into vba and still function. Thanks for any
input!

 

[Matt P]

I would like to be something more like:
Me.txtSortBy.Value = Is Null
Me.txtSortBy.Value = "APARTMENT" OR "CONDO"
Me.txtSortBy.Value = "HOTEL" OR "MOTEL"

Maybe put it into a string...?

 

[John W. Vinson]

Hello I am sure I was trying to go about this the wrong way and it
almost worked out I have a form with an Option Group which has 8
fields. So I put a "Select Case" statement in vb. And now for my
problem, I have 2 list boxes that I want to get populated off of the
selection they make and only show the results pertaining to that
selection, basically a filter. I got this to work by adding an
unbound text box to my form "txtSortBy" which then in each case I made
it change the value to the selection that they made. With this I got
almost all of them working, only 3 selections didn't work properly.
Here's my sql:
SELECT tblContacts.fldAddress, tblContacts.fldContactsID
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
WHERE (((tblContacts.fldCity)=[cmbSearchComp]) AND
((tblHistory.fldType)=[txtSortBy]))
GROUP BY tblContacts.fldAddress, tblContacts.fldContactsID, Mid$
(tblContacts.fldAddress,InStr(tblContacts.fldAddress," ")+1)
ORDER BY Mid$(tblContacts.fldAddress,InStr(tblContacts.fldAddress," ")
+1);

You can't actually use parameters in that way; the only place they can be used
is in literal criteria or in calculated fields. In particular parameters
cannot contain operators such as OR or BETWEEN or = or the like.

If you're determined to do this you'll need some VBA code to retrieve the
snippets of SQL code from the table or form and construct your entire SQL
string.

 

[Matt P]

Hello I am sure I was trying to go about this the wrong way and it
almost worked out  I have a form with an Option Group which has 8
fields.  So I put a "Select Case" statement in vb.  And now for my
problem, I have 2 list boxes that I want to get populated off of the
selection they make and only show the results pertaining to that
selection, basically a filter.  I got this to work by adding an
unbound text box to my form "txtSortBy" which then in each case I made
it change the value to the selection that they made.  With this I got
almost all of them working, only 3 selections didn't work properly.
Here's my sql:
SELECT tblContacts.fldAddress, tblContacts.fldContactsID
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
WHERE (((tblContacts.fldCity)=[cmbSearchComp]) AND
((tblHistory.fldType)=[txtSortBy]))
GROUP BY tblContacts.fldAddress, tblContacts.fldContactsID, Mid$
(tblContacts.fldAddress,InStr(tblContacts.fldAddress," ")+1)
ORDER BY Mid$(tblContacts.fldAddress,InStr(tblContacts.fldAddress," ")
+1);

You can't actually use parameters in that way; the only place they can beused
is in literal criteria or in calculated fields. In particular parameters
cannot contain operators such as OR or BETWEEN or = or the like.

If you're determined to do this you'll need some VBA code to retrieve the
snippets of SQL code from the table or form and construct your entire SQL
string.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Alright thanks for clearing that up. These reason I didn't do it that
way was because I added the sql into vb and it gave me an error...
unexpected end of statement. What can I do to get around that?

Me.lstCompanyNames.Recordset = "SELECT tblContacts.fldAddress,
tblContacts.fldContactsID FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID = tblHistory.fldContactID WHERE
(((tblContacts.fldCity)=[cmbSearchComp]) AND ((tblHistory.fldType)=
[txtSortBy])) GROUP BY tblContacts.fldAddress,
tblContacts.fldContactsID, Mid$ (tblContacts.fldAddress,InStr
(tblContacts.fldAddress," ")+1)ORDER BY Mid$
(tblContacts.fldAddress,InStr(tblContacts.fldAddress," ")+1);"

Its the last ," ")+1);" part that gets the error... whats a way around
this?

 

[John W. Vinson]

Alright thanks for clearing that up. These reason I didn't do it that
way was because I added the sql into vb and it gave me an error...
unexpected end of statement. What can I do to get around that?

By adding it correctly!

VBA is one language; SQL is a different language. You can't just put SQL
statements into a VBA procedure, so soll ich nicht plötzlich auf Deutsch
antworten. <g>

A SQL string needs to be assembled as a string variable in your procedure;
that string can then be used in the CreateQuerydef method, or assigned as the
Recordsource of a form or report, etc. Perhaps you could post your code and
we'll see if it can be fixed.

 

[Matt P]

By adding it correctly!

VBA is one language; SQL is a different language. You can't just put SQL
statements into a VBA procedure, so soll ich nicht plötzlich auf Deutsch
antworten. <g>

A SQL string needs to be assembled as a string variable in your procedure;
that string can then be used in the CreateQuerydef method, or assigned asthe
Recordsource of a form or report, etc. Perhaps you could post your code and
we'll see if it can be fixed.

Believe me I am not a "complete idiot" when it comes to
programming... I understand how completely different sql and vba
are. But I added the References in the vba library, and because of
that I am able to use them with vba as the "Recordset" value of the
listbox. This is how I am trying to test it... I only put the sql
string in "Case 2".

Select Case opgSortBy.Value
Case 1
Me.RecordSource = "tblContacts"
Me.cmbSearchComp.RowSource = "SELECT DISTINCT
tblContacts.fldCity FROM tblContacts;"
Me.Refresh
Me.txtSortBy.Value = ""
Case 2
Me.RecordSource = "qryMainSortByInsurance"
Me.cmbSearchComp.RowSource = "qryMainCity_Insurance"
Me.txtSortBy.Value = "INSURANCE"
Me.Refresh

'Here is where I am trying to filter the list box with my sql
in vb

Me.lstCompanyNames.Recordset = "SELECT
tblContacts.fldAddress, tblContacts.fldContactsID " & _
"FROM tblContacts INNER JOIN tblHistory ON
tblContacts.fldContactsID = tblHistory.fldContactID " & _
"WHERE (((tblContacts.fldCity)=[cmbSearchComp]) AND
((tblHistory.fldType)=[txtSortBy])) " & _
"GROUP BY tblContacts.fldAddress, tblContacts.fldContactsID,
Mid$(tblContacts.fldAddress,InStr(tblContacts.fldAddress, " & " " & ")
+1)" & _
"ORDER BY Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress," & " " & ")+1);"
'Going off of other examples I tried changing the blank space
into " & " " & ", thats all I am asking... how do I format this part
correctly?
Case 3
Me.RecordSource = "qryMainSortByFireDepartment"
Me.cmbSearchComp.RowSource = "qryMainCity_FireDepartment"
Me.txtSortBy.Value = "FIRE DEPARTMENT"
Me.Refresh
Case 4
Me.RecordSource = "qryMainSortByRealEstate"
Me.cmbSearchComp.RowSource = "qryMainCity_RealEstate"
Me.txtSortBy.Value = "REAL ESTATE"
Me.cmbSearchComp.Requery
Me.Refresh
Case 5
Me.RecordSource = "qryMainSortByHotelMotel"
Me.cmbSearchComp.RowSource = "qryMainCity_HotelMotel"
Me.txtSortBy.Value = "HOTEL OR MOTEL"
Me.Refresh
Case 6
Me.RecordSource = "qryMainSortBySchool"
Me.cmbSearchComp.RowSource = "qryMainCity_School"
Me.txtSortBy.Value = "SCHOOL"
Me.Refresh
Case 7
Me.RecordSource = "qryMainSortByApt"
Me.cmbSearchComp.RowSource = "qryMainCity_Apt"
Me.txtSortBy.Value = "APARTMENT OR CONDO"
Me.Refresh
Case 8
Me.RecordSource = "qryMainSortByOther"
Me.cmbSearchComp.RowSource = "qryMainCity_Other"
Me.txtSortBy.Value = "Is Null"
Me.Refresh
End Select
"ORDER BY Mid$(tblContacts.fldAddress,InStr
(tblContacts.fldAddress," & " " & ")+1);"

Any input would be greatly appreciated!

 

[Matt P]

By adding it correctly!

VBA is one language; SQL is a different language. You can't just put SQL
statements into a VBA procedure, so soll ich nicht plötzlich auf Deutsch
antworten. <g>

A SQL string needs to be assembled as a string variable in your procedure;
that string can then be used in the CreateQuerydef method, or assigned asthe
Recordsource of a form or report, etc. Perhaps you could post your code and
we'll see if it can be fixed.

Hmm I think I figured it out... all I needed to do was add single
quote marks instead of the double ---> ' ' I used to program a lot
in vba about 2 years ago and forgot a lot of what I had learned.
Thanks for your help!