Syntax issue in query with text form value variable

Posted on 2011-10-13
Medium Priority
Last Modified: 2012-05-12
I have a query that is based on a form.  The field on the form is called Combo1 and is a combo box.  This combo box is populated with the following:  FileNum: Left([tblFiles]![FileNumber],5), so only the first five digits of the FileNumber field are showing up.  I need to grab all file numbers with the same prefix, no matter what the rest of the file number is.  That's why I'm using the LEFT function.

I then need to print a report based on a filtered query, showing only the file numbers that have the prefix selected in the combo box.  Normally, I would do something like this in the query:  WHERE (((tblFiles.fileNumber)=[forms]![frmEnterPrefix]![Combo1]) AND ((tblFiles.inactive)="N"))  but it's not working because (I suspect) I'm only grabbing the first five digits of the file number, and not the file number in its entirety.  

Can I use LIKE instead of = in the query?  Also, FileNumber is a text field, not a numeric field.  I'm just not sure of the syntax.  

Thank you!
Question by:ssailer

Expert Comment

ID: 36965746
you can use tblFiles.fileNumber like ..."Combo1*" And ....

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 36965803

try this

 WHERE (((tblFiles.fileNumber) Like [forms]![frmEnterPrefix]![Combo1] & "*") AND ((tblFiles.inactive)="N"))

Author Closing Comment

ID: 36965971
Worked perfectly.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

624 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