VBA syntax question

I have a search form that returns values in a subform.  I recently added a field to this search form, but there is a problem.  Below is the code that applies the LIKE statement to the querydef:

If Not IsNull(Me.VendItem) Then
strWhere = strWhere & "(itemvend1.[vend-item]) Like '*" & Me.VendItem & "*' AND "
End If

If VendItem contains "#", the search will come up null.  The SQL works fine, I am sure it is a syntax issue.  Could someone assist me with this?

Thanks.  Please let me know if you have any questions.

IPT
iptraderAsked:
Who is Participating?
 
jadedataConnect With a Mentor MS Access Systems CreatorCommented:
Greetings iptrader!

 The "#" may be being interpreted as a date delimiter in the context of the sql string when IT is evaluated.
 What are these symbols doing in the data?

this uses embedded double quotes to "wrap" the venditem return string
  strWhere = strWhere & "(itemvend1.[vend-item]) Like " & chr(34) & "*" & Me.VendItem & "*" & chr(34) &" AND "


this removes the "#" from both strings prior to comparison.... (works for AccXP & Up)
  strWhere = strWhere & "(Replace([itemvend1].[vend-item],'#','') Like " & chr(34) & "*" & Replace(Me.VendItem,"#","") & "*" & chr(34) &" AND "


regards
jack
0
 
iptraderAuthor Commented:
BTW, I have tried different variations of the syntax and have come up empty :)
0
 
jobrienctCommented:
try replacing # with % ?

John
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
iptraderAuthor Commented:
John, the field name is set in the table.  I am pulling it in the dropdown box directly with SELECT DISTINCT string.

Thanks,

IPT
0
 
iptraderAuthor Commented:
Hi jack,

Unfortunately a lot of our users are on Access2K.

Any tips?

Thanks,

IPT
0
 
iptraderAuthor Commented:
Jack, also, the data is legacy data.  Nothing can be done about that.  Sorry.

IPT
0
 
Steve BinkCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:

Accept/jadedata

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

RoutineT
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.