"LIKE...." Operator Question HELP! 2

[pgh2377]

O.k., here's the situation. I'm building a query from a drug database that contains 16,000 drugs. I'm trying to build a query that reuquests the drug name to run off of which in the criteriea I have [DRUG NAME] in order for a prompt screen to appear. However, 9 out of 10 times the indiviual spells the name incorrectly. Is there any way to build an operator that would pull a partial spelling or similiar drugs. FOR EXAMPLE, the user types in "ADV", and then all ADVIL products would appear. I want to make it a pop up screen for the end user. So everytime they run the query, all they have to do is spell the first few words. ANY HELP PLEASE!!!

 

[Golom]

The usual syntax for a LIKE clause is

    ... WHERE myField LIKE 'ADV*' ...

or, if you are getting the value to search for from a variable

    ... WHERE myField LIKE '" & myVar & "*' " ...

If you are using ADO, then change "*" to "%" since they use different wild card characters.

 

[dhookom]

To add to Golom's response, if this is a parameter query, set the criteria to:
Like [Enter Drug Name] & "*"
or
Like "*" & [Enter Drug Name] & "*"
I suggest using a control on a form rather than a parameter prompt.


Duane
MS Access MVP

 

[jdttek]

Golom / Duane

If you are still on this thread:

I have a similar situation that I am trying to filter with VBA. Have tried just about every combo but none work (syntax?)

-Form with Text box LkUp_PO)
-Enter 123 in LKUp_PO.
-OnUpdate Event (LkUp_PO) should filter records that contain "123" at end of PO field (A999123 , CB888123, etc)

Following works, but only if I type in EXACT match in LkUp_PO (i.e CB888123). Returns nothing if I type in just 123:

Sub LkUp_PO_AfterUpdate()
Me.Filter="[Adj_PO] LIKE '"&Me.[LkUp_PO]&"'"
me.FilterOn=True
End Sub

Returns Mismatch data type error when I throw in "*" in the above code. (Adj_PO is text field):

Me.Filter="[Adj_PO] LIKE '"*" & "&Me.[LkUp_PO]&"'"

How do I include the wildcard * so that it will show records for 123 in the Adj_PO field?

Hope that is clear. Thanks in advance

JDTTEK

 

[dhookom]

Try:
Me.Filter = "[Adj_PO] Like '*" & me.Lkup_PO & "'"
or
Me.Filter = "Right([Adj_PO],3) = '" & me.Lkup_PO & "'"


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jdttek]

Thanks Duane.

# 2 worked, though limited to 3 digits in lookup field. Could never get the * to work after hrs and hrs of experimentation. Pls let me know if you have any more insights on the * issue. MEanwhile, I will use the "Right" solution (pardon the pun). A Star for you. Thanks

 

[Golom]

If you are using ADO (rather than DAO) then the mask characters for LIKE are "%" (matches any number of characters) and "_" (matches a single character). The corresponding DAO characters are "*" and "?".