Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
iptrader
Asked:
iptrader
1 Solution
 
iptraderAuthor Commented:
BTW, I have tried different variations of the syntax and have come up empty :)
0
 
jobrienctCommented:
try replacing # with % ?

John
0
 
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
jadedataMS 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:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now