Deriving Month from the date 2

[mikelev]

I have a subform (clinttrans) that i use to enter checks cashed by clients into. This data resides on the 'clienttrans' table. Occasionally a check is returned from the bank and column (returndate) is poulated with the current date by the user.

I am trying to have the report pull all returned checks for a given month, for all clients, and place the month designation on the report (April).

I am new to Access and need descriptive help on:

1. How to get a 'Month' prompt before runing the report, that will only pull reports for the variable entered.

2. How to get the 'Month' to show in the upper right corner of the report (i.e 'April')

I have been able to get all checks with a date in the 'retchk' field to show up, but cannot figure out how limit the results.

Table (clienttrans)
check#
amount
date
enteredby
retchk
retdate

Thanks in advance

 

[THWatson]

mikelev
I am assuming that your report is based on a query. One of the columns in the query will be "returndate." Make a new column MonthToCheck: Format([returndate],"mmmm"). That will extract the Month from each return date.

The next step is to put criteria in the MonthToCheck column. You can do it this way...
1. Put a parameter in the criteria, something like [Enter Month to check]. This parameter will pop up every time the report is run.

However, it occurs to me you may wish to deal with a month that is in an earlier year. Rather than using Month criteria you might wish to enter the first and last dates of a particular month as criteria.

You can do that by setting parameter criteria in your "returndate" column. One of 2 ways will do it.
Method 1.
Enter criteria Between [Enter Start Date] And [Enter End Date]. Again this parameter will pop up whenever the report is run.
Method 2.
Use a form to select your parameters. Have two text boxes - one in which you fill in the Start Date, and the second in which you will in the End Date. Then reference those text boxes in your query. In the criteria in the "returndate" column, put
Between [Forms]![YourFormName]![txtStartDate] And [Forms]![YourFormName]![txtEndDate]

You will, of course, have to change these things to match your own text box names.

One last piece...how to get the Month to show in the upper right corner of the report. Assuming that the "returndate" is one of the fields that shows in the Field List for your report, put a text box where you want it, in the upper right corner of your report, and use this structure...
= Format([returndate],"mmmm")
Or if you put the MonthToCheck column in your query, you could put this as the control source for the text box.

Hope that helps nudge things in the right direction for you.

Tom

 

[THWatson]

mikelev
I should have mentioned one more thing...

If you go the way of the form in which to enter your parameters, then in addition to the 2 text boxes for the Start Date and End Date you would put a command button that runs the report.

Regards.

Tom

 

[mikelev]

Tom:

MANY thanks for helping!! What seems very simple to experienced users, is a major hurdle for us new guys.

 

[THWatson]

mikelev
I've been there! Lots of times!

If you run into a problem with this, post back.

Tom