none
Help with macro Where condition RRS feed

  • Question

  • Hi everyone, 

    I have a BrwoseTo macro action which is triggered by a button on a subform. It essentially acts as a search/filter.

    The Where condition is

    ="[OrderID]=" & [txtOrderIDLookup]

    This works beautifully. In this case, [OrderID] is a number type field (AutoNumber), and txtOrderLookup is a textbox on the same form. If you enter "123" in the textbox and click the button, all orders dissappear except the one with ID 123. Perfect.

    I am trying to accomplish the exact same thing on a text field, and I just can't get it to work. For example:

    ="[OrderNumber]=" & [txtOrderNumLookup]

    Does not work. In this case [OrderNumber] is a short text field since order numbers are alphanumerical. I believe my syntax is wrong for the case where the textbox contains text.

    Any help would e hugely appreciated.

    Thank you!


    Saturday, July 15, 2017 2:07 PM

Answers

  • ="[OrderID]='" & [txtOrderIDLookup] & "'"

    You need to surround text values with single quotes.

     

     

    So the rule is:

    Numbers require nothing

        ="[OrderID]=" & [txtOrderIDLookup]

    Text requires single quote surrounding it

        ="[OrderID]='" & [txtOrderIDLookup] & "'"

    Dates require # to surround the value

        ="[OrderDate]=#" & [txtOrderDateLookup] & "#"


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Saturday, July 15, 2017 2:51 PM

All replies

  • You might need to use LIKE with wild card and apostrophy characters instead of =. Something like ="[OrderNumber] LIKE '*" & [txtOrderNumLookup] & "*'"
    Saturday, July 15, 2017 2:36 PM
  • ="[OrderID]='" & [txtOrderIDLookup] & "'"

    You need to surround text values with single quotes.

     

     

    So the rule is:

    Numbers require nothing

        ="[OrderID]=" & [txtOrderIDLookup]

    Text requires single quote surrounding it

        ="[OrderID]='" & [txtOrderIDLookup] & "'"

    Dates require # to surround the value

        ="[OrderDate]=#" & [txtOrderDateLookup] & "#"


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Saturday, July 15, 2017 2:51 PM
  • Thanks Daniel, 

    I had tried something similar but with double quotes instead of single quotes. Your solution works perfectly!

    Saturday, July 15, 2017 4:28 PM
  • Note also that date literals must be either in US short date format or an otherwise internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD.  So to be reliable on any system, regardless of its regional date/time format, Daniel's example would be:

    ="[OrderDate]=#" & Format([txtOrderDateLookup],"yyyy-mm-dd") & "#"

    Otherwise, here in the UK and elsewhere where the short date format dd/mm/yyyy is used, 4th July would be turned into 7th April.

    Another point worth noting is that some text values might include an apostrophe.  This is particularly true of personal names.  My name in its original non-anglicized form for instance is Cináed O'Siridean.  To cater for this a literal quotes character can be represented in a string expression by a pair of contiguous quotes characters, e.g.

    ="ClientName=""" & txtClientLookup & """"

    Where you might have single and/or double quotes in values a more circumspect solution must be used of course.


    Ken Sheridan, Stafford, England

    Sunday, July 16, 2017 12:59 PM