Syntax issue in query with text form value variable

Posted on 2011-10-13
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
    LVL 9

    Expert Comment

    you can use tblFiles.fileNumber like ..."Combo1*" And ....

    LVL 119

    Accepted Solution


    try this

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

    Author Closing Comment

    Worked perfectly.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now