query help

[Gurtz]

OK, I'm going to bother you with this one more time:

I am constructing a query in the ROWSOURCE of a listbox. I
want to use the wildcard "*" with a combobox value. For
instance:

SELECT * WHERE row = '*[combobox]*'

However, because I am writing the query in the ROWSOURCE
of a listbox, the string gymnastics are wierd. Can anybody
help me with this? Perhaps with a simple example of how to
do this with a combobox value and also with a simple
string?

Thanks!
Gurtz
[email = no $]

 

[Gurtz]

Something else that I'm trying to do in this same query,
is to use the value in a control as an operator in the
query, so that the user can specify whether to search
for "A AND B" or "A OR B" ..

I've tried many different syntaxes, to no avail.

Any help would render me speechless with excitement.

Gurtz
[email = no $]

 

[Timothy C. Doherty]

Is "row" a field in your source table/query? You have no source table or
query in your SQL. The "*" wildcard is used with the "LIKE" operator.
Assuming "row" is a field in a table or query:

SELECT * FROM <TableOrQueryName> WHERE row LIKE '*combobox*'

 

[Andy Cole]

Gurtz

PMFJI

You say that you are using a *label* control that has its .Caption set to
AND or OR according to some other criteria (presumably set by the User?.
Emelia's first post assumed that you were using something like a combo box
with, say, "And" and "Or" as possible choices.

If you are using a Label control rather than a Combo box for the 'operator'
control then you need to concatenate the label control's .caption property
into your SQL.

There's another problem with your *posted* code - there's no space between
the reference to the Label control and the second field 'row2'. Your SQL
should look more like this (assuming you're building it in code);

strSQL = "SELECT * FROM Table WHERE row1 LIKE '*" & _
Me.CBox1 & "*' " & Me.Logic.Caption & " row2 LIKE '*" & _
Me.CBox2 & "*'"

HTH

Andy

Thanks so much for your response,

Ok, let's call the combobox that holds the criteria for
comparison 'CBox1' and 'CBox2', and the label that holds
the AND or OR 'Logic'. We'd refer to it as '[Logic].
[Caption]', right?

Ok, then

SELECT * FROM table WHERE row1 LIKE "*" & [CBox1] & "* " &
[Logic] & row2 LIKE "*" & [CBox2] & "*"

But I can't get this to work!

Keep in mind I am writing this in the RowSource entry of
the properties dialogue of a listbox. This is why I am
having trouble with the string details.

Thanks so much!
Gurtz
[email = no $]

-----Original Message-----

Something else that I'm trying to do in this same query,
is to use the value in a control as an operator in the
query, so that the user can specify whether to search
for "A AND B" or "A OR B" ..

I've tried many different syntaxes, to no avail.

Gurtz,

you may want to try this one too (assuming, the control with the
operator really contains 'AND' or 'OR' - it should be a combo anyway):

strSQL = "SELECT * FROM MyTable Where Field1 ='" &
Me![MyControlWithAValue] & "' " & Me![MyOperatorControl] & " Field2
='" & Me![MyControlWithBValue] & "'"

Any help would render me speechless with excitement.

You can stay speechless, but you should at least write us the
real names of all relevant controls, field names etc. For ex the above
syntax assumes, A and B are values in some controls and they are both
strings. As you see, it is very cumbersome to give appropriate advice
without this info.

I am constructing a query in the ROWSOURCE of a listbox.
I want to use the wildcard "*" with a combobox value. For
instance:

SELECT * WHERE row = '*[combobox]*'

However, because I am writing the query in the ROWSOURCE
of a listbox, the string gymnastics are wierd. Can
anybody help me with this? Perhaps with a simple example of how
to
do this with a combobox value and also with a simple
string?

Well, this would look something like this:

strSQL = "SELECT * FROM MyTable WHERE row LIKE '*" & Me! [myCombo] &
"*'"

Note the single quotes before and after the stars!

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
.