Solved

VBA syntax question

Posted on 2004-03-30
8
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 51

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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

617 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