none
Need to understand Where condition criteria RRS feed

  • Question

  • Could you help me understand and give examples of "Where Condition" criteria when trying to match the field type, say trying to match a field in my form with a field in a report (so I can print or email the same record). How do I write the string in the macro builder in the "where condition" matching the contents in my part number field with the same field in my report? Would you give me examples of matching two number fields and another matching two text fields. Thank you.

    Kevin Landress

    Monday, June 25, 2018 9:22 PM

All replies

  • This was the first hit when I searched online for "access criteria data type"

    https://support.office.com/en-us/article/Examples-of-query-criteria-3197228C-8684-4552-AC03-ABA746FB29D8

    I think it will give you all the answers you're looking for.


    -Tom. Microsoft Access MVP

    Tuesday, June 26, 2018 12:08 AM
  • ................so I can print or email the same record
    For this it's best to create a report based on a query which includes a parameter referencing the primary key of the current record in the form.  You can use the OpenReport method of the DoCmd object to print or preview the report, or you can email the report as a PDF attachment, using the SendObject method of the DoCmd object.

    You might like to take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how to output the current invoice as a report in a variety of ways.


    Ken Sheridan, Stafford, England

    Tuesday, June 26, 2018 12:46 PM
  • Hi Kable7,

    You can set the WHERE condition in different ways. How you do that is up to you. It is about understanding the conditions.

    I never use macros myself, but always use VBA. First I will show you the difference between a String and numerical value. Suppose it is the CustomertID field.

    Example with a value of type String. In this example, the desired CustomerID is displayed on an open form.

    "CustomertID =""" & Forms!YourForm!CustomertID & """"

    Example with a numeric value.
    "CustomertID =" & Forms!YourForm!CustomertID

    As I said, I never work with macros, and also in VBA I use techniques that are much more dynamic than the regular techniques. I will not elaborate on that. If you use a macro, the macro will execute the OpenReport method of the DoCmd object. The point is that you understand those arguments well.

    Go to the next page. What you want is how you can set the WhereCondition argument. That is shown above. Tip: If you go to the link below, you will see that you can also use a query in your database if you use the FilterName argument.

    The info you find on this page corresponds to how you should set the macro.

    https://msdn.microsoft.com/nl-nl/VBA/access-vba/articles/docmd-openreport-method-access


    Tuesday, June 26, 2018 7:48 PM