Share via

MS Access Parameter Query

Anonymous
2022-12-13T22:07:42+00:00

I wish to create an Access parameter query. The field is a Date field. I want the user to put in the month and have the query return all records for that month. I haven't been able to create a syntax that makes that happen. I know how to create the query to return all records with a specific month, but I haven't been able to have it request the user to input the month desired.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-12-13T22:17:23+00:00

    Set the parameter to ask for any date in the month.

    In your query, create a calculated column with expression

    Format([DateField], "yyyymm")

    where DateField is the name of the relevant field. In the Criteria row, enter

    Format([Enter a date in the month], "yyyymm")

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-12-14T10:44:59+00:00

    If you don't want to use a form to enter the value, use a simple query like this:

    PARAMETERS [Select month] Short;

SELECT * FROM YourTable

WHERE Month([YourDateField]) = [Select month];
    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2022-12-14T01:45:03+00:00

    First, I do NOT like Parameter queries. They are ver limiting. Instead I use a form to enter the criteria for the query. I then reference that value in the query using the syntax:

    =Forms!formname!controlname

    So, in your situation I would have a comobox that returned a number from 1-12.

    I'm guessing you have a field in your table for Date of Birth. I would add a column to that table:

    BirthMonth: Month(DOB)

    Then set the crieria for that column to reference the combobox on the form.

    0 comments
  3. Anonymous
    2022-12-14T00:12:35+00:00

    So, I created the calc field so it returns only the month by number. December equals 12. Now, I want the user to enter the month they want the data for. It would return all records with birthdays in the month requested. Thanks.

    0 comments
  4. Anonymous
    2022-12-13T22:22:37+00:00

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

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

    If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

    This little demo file includes an option:

         9. Returning rows within a date range defined by parameters

    This illustrates a query which references two unbound text boxes in a form as parameters to define a date range.  This allows you to define any period of time, but a range of one month can defined by the first and last day of the month, each of which can be selected by means of the data picker.  The query is:

    PARAMETERS Forms!frmDateRange!txtStartDate DATETIME,

    Forms!frmDateRange!txtEndDate DATETIME;

    SELECT TransactionDate, FirstName, LastName, TransactionAmount

    FROM Customers INNER JOIN Transactions

    ON Customers.CustomerID = Transactions.CustomerID

    WHERE TransactionDate >= Forms!frmDateRange!txtStartDate

    AND TransactionDate < Forms!frmDateRange!txtEndDate + 1

    ORDER BY TransactionDate;

    0 comments