Combo Box, Query & Wildcard

  • Thread starter Thread starter Joe Gieder
  • Start date Start date
J

Joe Gieder

I created a form that has four unbound Combo Boxes and
links to a query. The problem I have is when I use a
wildcard with my criteria "Bolt*" the query comes back
blank but when I don't use the * it comes back with all
information listed as "Bolt". Is there a way to enter
widcards or >, > <> in the form combo box as part of my
criteria to get the results? The other combo boxes are
date, qty and state.

TIA for your help
Joe
 
I figured out what you were talking about by putting like
in front of the query criteria. Thank you.
How would I use >, < or <> in the same problem example if
I wanted all items > "Bolt"?

Thanks again.

Joe
-----Original Message-----
How would I do that in my query or my combo box? I use
[Forms]![Advanced SAP Criteria Selections]![Nomenclature]
in my query in the criteria box.

Joe
-----Original Message-----
Hi,
Are you using Like instead of = ?

--
HTH
Dan Artuso, Access MVP


"Joe Gieder" <[email protected]>
Click to expand...
wrote
in message news:[email protected]...
.
Click to expand...
.
Click to expand...
 
I created a form that has four unbound Combo Boxes and
links to a query. The problem I have is when I use a
wildcard with my criteria "Bolt*" the query comes back
blank but when I don't use the * it comes back with all
information listed as "Bolt". Is there a way to enter
widcards or >, > <> in the form combo box as part of my
criteria to get the results? The other combo boxes are
date, qty and state.

TIA for your help
Joe
Click to expand...

Use a criterion such as

LIKE [Forms]![yourform]![cboParttype] & "*"

If the user selects BOLT from the combo, this will find any value
starting with BOLT.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I could put >, < or <> in front of the query criteria for
example > [Forms]![Advanced SAP Criteria Selections]!
[Nomenclature] but is there a way so I don't have to hard
code this in the query since the end user may want to use
, < or <>? Can I type it in my combo box criteria for
Click to expand...
example "> Bolt" and have the query list all those > than
Bolt?

Joe
-----Original Message-----
I figured out what you were talking about by putting like
in front of the query criteria. Thank you.
How would I use >, < or <> in the same problem example if
I wanted all items > "Bolt"?

Thanks again.

Joe
-----Original Message-----
How would I do that in my query or my combo box? I use
[Forms]![Advanced SAP Criteria Selections]! [Nomenclature]
in my query in the criteria box.

Joe
-----Original Message-----
Hi,
Are you using Like instead of = ?

--
HTH
Dan Artuso, Access MVP


"Joe Gieder" <[email protected]>
Click to expand...
wrote
in message news:02e401c49f59$5ae7e870 [email protected]...
I created a form that has four unbound Combo Boxes and
links to a query. The problem I have is when I use a
wildcard with my criteria "Bolt*" the query comes back
blank but when I don't use the * it comes back with all
information listed as "Bolt". Is there a way to enter
widcards or >, > <> in the form combo box as part of my
criteria to get the results? The other combo boxes are
date, qty and state.

TIA for your help
Joe


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hi,
No, if you want to offer the user their choice of operator you have to
resort to modifying the SQL of the query in code.
It can be done though. You'd need two combo boxes, one to choose the operator
(<,> or <>) and one for the actual criteria.

Anyway, I can't give an actual implementation without knowing more details about your query.

--
HTH
Dan Artuso, Access MVP


Joe Gieder said:
I could put >, < or <> in front of the query criteria for
example > [Forms]![Advanced SAP Criteria Selections]!
[Nomenclature] but is there a way so I don't have to hard
code this in the query since the end user may want to use
, < or <>? Can I type it in my combo box criteria for
Click to expand...
example "> Bolt" and have the query list all those > than
Bolt?

Joe
-----Original Message-----
I figured out what you were talking about by putting like
in front of the query criteria. Thank you.
How would I use >, < or <> in the same problem example if
I wanted all items > "Bolt"?

Thanks again.

Joe
-----Original Message-----
How would I do that in my query or my combo box? I use
[Forms]![Advanced SAP Criteria Selections]! [Nomenclature]
in my query in the criteria box.

Joe
-----Original Message-----
Hi,
Are you using Like instead of = ?

--
HTH
Dan Artuso, Access MVP


in message [email protected]...
I created a form that has four unbound Combo Boxes and
links to a query. The problem I have is when I use a
wildcard with my criteria "Bolt*" the query comes back
blank but when I don't use the * it comes back with all
information listed as "Bolt". Is there a way to enter
widcards or >, > <> in the form combo box as part of my
criteria to get the results? The other combo boxes are
date, qty and state.

TIA for your help
Joe


.

.
Click to expand...
.
Click to expand...
Click to expand...
 
This is the SQL of my query: SELECT ZZZ.MATERIAL,
ZZZ.NOMENCLATURE, ZZZ.[PURCHASING DOC], ZZZ.ITEM, ZZZ.
[DOC TYPE], ZZZ.PGP, ZZZ.PLANT, ZZZ.[CREATE DATE], ZZZ.
[PO QTY] AS Expr1, ZZZ.[RFQ QTY] AS Expr2, ZZZ.[NET
PRICE], ZZZ.VENDOR, ZZZ.[VENDOR NAME], ZZZ.STATE, ZZZ.
[Telephone 1], ZZZ.BLOCK, ZZZ.[MINORITY INDICATOR]
FROM ZZZ
WHERE (((ZZZ.NOMENCLATURE) Like [Forms]![Advanced SAP
Criteria Selections]![Nomenclature]) AND ((ZZZ.[DOC TYPE])
="cpo") AND ((ZZZ.PLANT)="ac01") AND ((ZZZ.[CREATE DATE])
#1/1/2004#) AND ((ZZZ.STATE)="ca"));
Thanks
Joe


-----Original Message-----
Hi,
No, if you want to offer the user their choice of operator you have to
resort to modifying the SQL of the query in code.
It can be done though. You'd need two combo boxes, one to choose the operator
(<,> or <>) and one for the actual criteria.

Anyway, I can't give an actual implementation without
Click to expand...
knowing more details about your query.
--
HTH
Dan Artuso, Access MVP


"Joe Gieder" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
I could put >, < or <> in front of the query criteria for
example > [Forms]![Advanced SAP Criteria Selections]!
[Nomenclature] but is there a way so I don't have to hard
code this in the query since the end user may want to use
, < or <>? Can I type it in my combo box criteria for
Click to expand...
example "> Bolt" and have the query list all those > than
Bolt?

Joe
-----Original Message-----
I figured out what you were talking about by putting like
in front of the query criteria. Thank you.
How would I use >, < or <> in the same problem example if
I wanted all items > "Bolt"?

Thanks again.

Joe
-----Original Message-----
How would I do that in my query or my combo box? I use
[Forms]![Advanced SAP Criteria Selections]! [Nomenclature]
in my query in the criteria box.

Joe
-----Original Message-----
Hi,
Are you using Like instead of = ?

--
HTH
Dan Artuso, Access MVP


"Joe Gieder" <[email protected]>
wrote
in message [email protected]...
I created a form that has four unbound Combo Boxes and
links to a query. The problem I have is when I use a
wildcard with my criteria "Bolt*" the query comes back
blank but when I don't use the * it comes back with
all
information listed as "Bolt". Is there a way to enter
widcards or >, > <> in the form combo box as part of
my
criteria to get the results? The other combo boxes are
date, qty and state.

TIA for your help
Joe


.

.

.
Click to expand...
Click to expand...


.
Click to expand...
 
HI,
I'll give it a shot. Just substitute the real name for the query and the
name of the combo that will contain your operator choices.

Dim strSql As String
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("yourQueryName")

strSql = "SELECT ZZZ.MATERIAL, ZZZ.NOMENCLATURE, " & _
"ZZZ.[PURCHASING DOC], ZZZ.ITEM, ZZZ.[DOC TYPE], " & _
"ZZZ.PGP, ZZZ.PLANT, ZZZ.[CREATE DATE], " & _
"ZZZ.[PO QTY] AS Expr1, ZZZ.[RFQ QTY] AS Expr2, " & _
"ZZZ.[NET PRICE], ZZZ.VENDOR, ZZZ.[VENDOR NAME], " & _
"ZZZ.STATE, ZZZ.[Telephone 1], ZZZ.BLOCK, " & _
"ZZZ.[MINORITY INDICATOR] FROM ZZZ " & _
"WHERE (((ZZZ.NOMENCLATURE) " & Me.yourOperatorCombo & _
" [Forms]![Advanced SAP Criteria Selections]![Nomenclature]) AND " & _
"((ZZZ.[DOC TYPE]) ="cpo") AND ((ZZZ.PLANT)="ac01") AND " & _
"((ZZZ.[CREATE DATE]) >#1/1/2004#) AND ((ZZZ.STATE)="ca"))"

qdf.SQL = strSql

HTH
Dan Artuso, MVP


Joe Gieder said:
This is the SQL of my query: SELECT ZZZ.MATERIAL,
ZZZ.NOMENCLATURE, ZZZ.[PURCHASING DOC], ZZZ.ITEM, ZZZ.
[DOC TYPE], ZZZ.PGP, ZZZ.PLANT, ZZZ.[CREATE DATE], ZZZ.
[PO QTY] AS Expr1, ZZZ.[RFQ QTY] AS Expr2, ZZZ.[NET
PRICE], ZZZ.VENDOR, ZZZ.[VENDOR NAME], ZZZ.STATE, ZZZ.
[Telephone 1], ZZZ.BLOCK, ZZZ.[MINORITY INDICATOR]
FROM ZZZ
WHERE (((ZZZ.NOMENCLATURE) Like [Forms]![Advanced SAP
Criteria Selections]![Nomenclature]) AND ((ZZZ.[DOC TYPE])
="cpo") AND ((ZZZ.PLANT)="ac01") AND ((ZZZ.[CREATE DATE])
#1/1/2004#) AND ((ZZZ.STATE)="ca"));
Thanks
Joe


-----Original Message-----
Hi,
No, if you want to offer the user their choice of operator you have to
resort to modifying the SQL of the query in code.
It can be done though. You'd need two combo boxes, one to choose the operator
(<,> or <>) and one for the actual criteria.

Anyway, I can't give an actual implementation without
Click to expand...
knowing more details about your query.
--
HTH
Dan Artuso, Access MVP


"Joe Gieder" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
I could put >, < or <> in front of the query criteria for
example > [Forms]![Advanced SAP Criteria Selections]!
[Nomenclature] but is there a way so I don't have to hard
code this in the query since the end user may want to use
, < or <>? Can I type it in my combo box criteria for
example "> Bolt" and have the query list all those > than
Bolt?

Joe
-----Original Message-----
I figured out what you were talking about by putting
like
in front of the query criteria. Thank you.
How would I use >, < or <> in the same problem example
if
I wanted all items > "Bolt"?

Thanks again.

Joe
-----Original Message-----
How would I do that in my query or my combo box? I use
[Forms]![Advanced SAP Criteria Selections]!
[Nomenclature]
in my query in the criteria box.

Joe
-----Original Message-----
Hi,
Are you using Like instead of = ?

--
HTH
Dan Artuso, Access MVP


"Joe Gieder" <[email protected]>
wrote
in message [email protected]...
I created a form that has four unbound Combo Boxes
and
links to a query. The problem I have is when I use a
wildcard with my criteria "Bolt*" the query comes
back
blank but when I don't use the * it comes back with
all
information listed as "Bolt". Is there a way to enter
widcards or >, > <> in the form combo box as part of
my
criteria to get the results? The other combo boxes
are
date, qty and state.

TIA for your help
Joe


.

.

.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top