Solved

VBA syntax question

Posted on 2004-03-30
8
269 Views
Last Modified: 2012-06-21
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
Comment
Question by:iptrader
8 Comments
 

Author Comment

by:iptrader
ID: 10715725
BTW, I have tried different variations of the syntax and have come up empty :)
0
 
LVL 10

Expert Comment

by:jobrienct
ID: 10715876
try replacing # with % ?

John
0
 

Author Comment

by:iptrader
ID: 10715883
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 32

Accepted Solution

by:
jadedata earned 200 total points
ID: 10715889
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
 

Author Comment

by:iptrader
ID: 10715907
Hi jack,

Unfortunately a lot of our users are on Access2K.

Any tips?

Thanks,

IPT
0
 

Author Comment

by:iptrader
ID: 10715915
Jack, also, the data is legacy data.  Nothing can be done about that.  Sorry.

IPT
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11945912
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question