Results 1 to 2 of 2
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Macro to help design a query?

    I have been creating a LOT of queries recently dealing with filtering things by month and by year.
    Transactions By Month By Year
    Loan payments By Month By Year
    Deposits By Month By year
    etc...

    we'll get to how I'm probably doing this wrong in a minuet but first...

    First I create a query in design view, add the fields including the Date. then save the qry.
    then I add 3 columns,
    One to use the year function to pull the year out of the date
    one to use the month function to pull the month number (for sorting numerically by month)
    one to use the month name function to pull the month name

    now shamefully I know and understand nothing about how macros work in Access
    but I was wondering if (using macros or another tool) there was a way to automate the creation of these 3 "Function" Columns in the query.

    Now, second, about my process for example

    I have a form that shows dues paid by individual chapters on the left and all dues paid on the right (see form below)



    all the data is being pulled from one tbl_transactions that has all transaction data not just transactions regarding dues paid.
    so I create on qry_DuesPaid and use it for the subform on the right
    and another qry_DuesByChapter which allows for the form to filter the data to show an individual chapters dues payments on the left

    is there a better way to do this with a single query that serves both subforms?
    and is this the best way to pull and show the date information (wanting to only show the year and month for simplicity)


    As always thanks for the help
    Click image for larger version. 

Name:	Capture.jpg 
Views:	14 
Size:	157.9 KB 
ID:	42447

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Instead of creating a queries for every month, depending how you call the query:
    a) Create a parametrized query. I prefer SQL view, so you create something like
    Code:
    SELECT YourDate,  ... FROM YourTable WHERE Format(YourDate, "yyyymm") = [Enter month in format YYYYMM]
    and save it. Now when you ran this saved query, you are asked for month. You enter it like 202006, and you get the result for previous month.
    b) Set report parameters in some form, and call the report from click event of button on same form. for this
    1. You create a report with Source like "SELECT YourDate, CInt(Format(YourDate, "yyyymm")) AS parMonth, ... FROM YourTable";
    2. You add a control (text box or combo box), where you enter integer report parameter in format "YYYYMM". E.g. txtYYYYMM ;
    3. You add a button, with OnClick event calling the report. The code for this will be like
    Code:
    DoCmd.OpenReport "YourReport", acViewNormal, "parMonth = " & Me.txtYYYYMM
    Now you enter into control a value like 202006, and click the button - and the report for previous month is returned.
    3. Like 2, but clicking button calls a VBA procedure, which reads parameter value from control, then creates a QueryString variable(s) with parameter value taken into account (there is no need to calculate parMonth field, as a WHERE clause for QueryString is constructed instead), and then updates source of some form to display result data, or runs some action(s) with results of query, e.g. updates tables, exports query results to some 3rd-party database, or saves query results e.g. as text/csv/html files, etc.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-08-2020, 09:20 AM
  2. Replies: 2
    Last Post: 11-07-2013, 12:13 PM
  3. Replies: 4
    Last Post: 10-30-2013, 07:51 AM
  4. Replies: 2
    Last Post: 05-05-2012, 02:34 AM
  5. Replies: 3
    Last Post: 12-06-2010, 04:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums