Query Syntax Multiple criteria

[Little Penny]

This is an area that I strugle with

How do wite this query so that the syntax is correct.


GCriteria = Forms![frmRequest]![RequestName] & " = " &
Forms![frmSearchRequest]![txtSearchString1] "AND" & _
Forms![frmRequest]![RequestGroup] & " = " &
Forms![frmSearchRequest]![txtSearchString2] "AND" & _
Forms![frmRequest]![RequestColor] & " = " &
Forms![frmSearchRequest]![txtSearchString3]


I have trouble with using the carrage return

Also is the syntax different if I use LIKE

Thanks


Little Penny

 

[John W. Vinson]

This is an area that I strugle with

How do wite this query so that the syntax is correct.


GCriteria = Forms![frmRequest]![RequestName] & " = " &
Forms![frmSearchRequest]![txtSearchString1] "AND" & _
Forms![frmRequest]![RequestGroup] & " = " &
Forms![frmSearchRequest]![txtSearchString2] "AND" & _
Forms![frmRequest]![RequestColor] & " = " &
Forms![frmSearchRequest]![txtSearchString3]


I have trouble with using the carrage return

Also is the syntax different if I use LIKE

Thanks


Little Penny

Since we don't know anything about the structure of your tables, your
fieldnames, the meanings or datatypes of the fields, or the values in the form
controls, it's more than a bit difficult to say; but the criteria need to end
up being a valid SQL WHERE clause without the word WHERE. You can build a
query in the query grid showing an example of what you want to see. This would
include *the names of fields in your table* - unless your textbox RequestName
contains the name of a table field, this query of yours will NOT work.

Another problem is that you're not leaving spaces. The word AND is going to be
jammed against your fieldname - your criterion will end up something like

RequestName = JonesANDRequestGroup = SomeGroupANDRequestColor = Blue

Thirdly, if you are passing criteria to search Text fields the criterion must
be delimited by quotemarks - either " or (especially if the search term might
include apostrophes) ". A singlequote can be included in a string literal just
like any other character, but to include a doublequote you must use TWO
doublequotes.

So guessing that your table fields are RequestName, RequestGroup and
RequestColor, and that these are all Text (not lookup!) fields, a better
criterion construction might be

GCriteria = "[RequestName] = """ &
Forms![frmSearchRequest]![txtSearchString1] & _
""" AND [RequestGroup] = """ & _
Forms![frmSearchRequest]![txtSearchString2] & _
""" AND [RequestColor] = """ & _
Forms![frmSearchRequest]![txtSearchString3] & """"

This will construct a string like

[RequestName] = "Jones" AND [RequestGroup] = "SomeGroup" AND [RequestColor] =
"Blue"

If you'll explain what you want to do with LIKE - what kind of field you'll be
searching and what kind of wildcards you need - we can help with that.

 

[Little Penny]

Thanks for yous assistance. What I mean with LIKE is starts with. The
feilds are text boxes. Is there a difference syntax for combo, numeric
or dates.

This is an area that I strugle with

How do wite this query so that the syntax is correct.


GCriteria = Forms![frmRequest]![RequestName] & " = " &
Forms![frmSearchRequest]![txtSearchString1] "AND" & _
Forms![frmRequest]![RequestGroup] & " = " &
Forms![frmSearchRequest]![txtSearchString2] "AND" & _
Forms![frmRequest]![RequestColor] & " = " &
Forms![frmSearchRequest]![txtSearchString3]


I have trouble with using the carrage return

Also is the syntax different if I use LIKE

Thanks


Little Penny

Since we don't know anything about the structure of your tables, your
fieldnames, the meanings or datatypes of the fields, or the values in the form
controls, it's more than a bit difficult to say; but the criteria need to end
up being a valid SQL WHERE clause without the word WHERE. You can build a
query in the query grid showing an example of what you want to see. This would
include *the names of fields in your table* - unless your textbox RequestName
contains the name of a table field, this query of yours will NOT work.

Another problem is that you're not leaving spaces. The word AND is going to be
jammed against your fieldname - your criterion will end up something like

RequestName = JonesANDRequestGroup = SomeGroupANDRequestColor = Blue

Thirdly, if you are passing criteria to search Text fields the criterion must
be delimited by quotemarks - either " or (especially if the search term might
include apostrophes) ". A singlequote can be included in a string literal just
like any other character, but to include a doublequote you must use TWO
doublequotes.

So guessing that your table fields are RequestName, RequestGroup and
RequestColor, and that these are all Text (not lookup!) fields, a better
criterion construction might be

GCriteria = "[RequestName] = """ &
Forms![frmSearchRequest]![txtSearchString1] & _
""" AND [RequestGroup] = """ & _
Forms![frmSearchRequest]![txtSearchString2] & _
""" AND [RequestColor] = """ & _
Forms![frmSearchRequest]![txtSearchString3] & """"

This will construct a string like

[RequestName] = "Jones" AND [RequestGroup] = "SomeGroup" AND [RequestColor] =
"Blue"

If you'll explain what you want to do with LIKE - what kind of field you'll be
searching and what kind of wildcards you need - we can help with that.

 

[John W. Vinson]

Thanks for yous assistance. What I mean with LIKE is starts with. The
feilds are text boxes. Is there a difference syntax for combo, numeric
or dates.

The LIKE operator applies only to text.

Combo box fields ARE NOT COMBO BOX FIELDS. Read
http://www.mvps.org/access/lookupfields.htm
for a critique of the Lookup Field misfeature. A Lookup Field contains a Long
Integer number - and if you're searching the table with a text criterion or a
Like operation, *IT WILL NEVER FIND ANYTHING* because the text that you see is
not actually there. What's there is 318 or some other number.

You can search a Text field - either a text field in your table, or the text
field in the Lookup Table in a *QUERY* joining your main table to the lookup
table - with a criterion such as

LIKE [Forms]![YourFormName]![txtSearch1] & "*"

If the field being searched contains (say) names, and you put "SM" into
txtSearch1, this will find records containing Smart, Smith, Smolinsky and so
on.

To search dates you can use date criteria delimited by # or specified as Date
in the query's Parameters property.

 

[Little Penny]

This is great stuff thanks. Just to explain what I'm trying to do. I
have a form with 32 fields. I want to create a way for the user to
search for records using multiple fields but using a copy of the form
that they are use to seeing. So I copied the main form and disable
more than 2/3 of the fields, leavening 12 fields that the user can
select from a drop down list in some fields or type data into others
and hit ok. Some fields will use the "=" operator in the select
statement, because the user will choose from a drop down list. The
fields where the user will type data I thought the "LIKE*" operator
will help if the user chooses the leave that field blank during there
search. If a field is left blank I want the query to behave as if
there is no reference to that field in the select statement.

My question is if I use the LIKE operator in a field that is blank.
What is the behavior of the query? Will the results include records of
the fields that may have been left blank in the table?

Year ago I use a product call ACT. At that time is served its purpose,
but I always remember being able to search using the main form.

Is there a better approach to accomplish this?


Thanks


Little Penny

Thanks for yous assistance. What I mean with LIKE is starts with. The
feilds are text boxes. Is there a difference syntax for combo, numeric
or dates.

The LIKE operator applies only to text.

Combo box fields ARE NOT COMBO BOX FIELDS. Read
http://www.mvps.org/access/lookupfields.htm
for a critique of the Lookup Field misfeature. A Lookup Field contains a Long
Integer number - and if you're searching the table with a text criterion or a
Like operation, *IT WILL NEVER FIND ANYTHING* because the text that you see is
not actually there. What's there is 318 or some other number.

You can search a Text field - either a text field in your table, or the text
field in the Lookup Table in a *QUERY* joining your main table to the lookup
table - with a criterion such as

LIKE [Forms]![YourFormName]![txtSearch1] & "*"

If the field being searched contains (say) names, and you put "SM" into
txtSearch1, this will find records containing Smart, Smith, Smolinsky and so
on.

To search dates you can use date criteria delimited by # or specified as Date
in the query's Parameters property.

 

[John W. Vinson]

This is great stuff thanks. Just to explain what I'm trying to do. I
have a form with 32 fields. I want to create a way for the user to
search for records using multiple fields but using a copy of the form
that they are use to seeing. So I copied the main form and disable
more than 2/3 of the fields, leavening 12 fields that the user can
select from a drop down list in some fields or type data into others
and hit ok. Some fields will use the "=" operator in the select
statement, because the user will choose from a drop down list. The
fields where the user will type data I thought the "LIKE*" operator
will help if the user chooses the leave that field blank during there
search. If a field is left blank I want the query to behave as if
there is no reference to that field in the select statement.

My question is if I use the LIKE operator in a field that is blank.
What is the behavior of the query? Will the results include records of
the fields that may have been left blank in the table?

Year ago I use a product call ACT. At that time is served its purpose,
but I always remember being able to search using the main form.

Is there a better approach to accomplish this?

Yes. A Query with 32 different optional criteria will be an absolute MESS to
design and to work with.

One suggestion would be simply to use the builtin Query By Form feature. This
may involve some user training but it's pretty effective and doesn't require
changing the form at all.

Alternatively you can write some VBA code to loop through the controls on your
query form and build up a SQL string. My friend Tom Wickerath has some
examples:

QBF Examples
http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip


http://www.accessmvp.com/TWickerath/downloads/elements.zip


http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip


http://www.seattleaccess.org/downloads.htm