How to use form inputs in a query?

[UNC_Access]

Hi! Here is my idea/issue:

1. I have created query that has the following expression/field:

Days Outstanding: [Reporting Date] - [Table1].[Filing Date]

2. [Filing Date] is contained in a [Table1] of course. But [Reporting Date] is not in any table. I guess it is a query parameter.

3. I want [Reporting Date] to come from a single source, such as a form input. This [Reporting Date] is the same date for all records (it is the month end). For example, let us say the current reporting date is 1/31/13.

4. My issue is that my form will not save this reporting date of 1/31/13. When I save, exit, and return, it is gone.

How can I save the reporting date in the form?

I would like to do this so that I can run the query in step (1.) above without having to open the form and re-type the date.

Thanks!

 

[Beetle]

If the Reporting Date is going to be a predictable value (i.e. it's always going to be the last day of the previous month or the last day of the current month) then you don't necessarily need to rely on user input of the date via a form. You can use the DateSerial function to calculate the Reporting Date. For example;

To return the last day of the previous month:

DateSerial(Year(Date()), Month(Date()),0)

- would currently return 1/31/2013

To return the last day of the current month:

DateSerial(Year(Date()), Month(Date())+1,0)

- would currently return 2/28/2013

So the calculated field in your query would look something like;

Days Outstanding: DateSerial(Year(Date()),Month(Date())+1,0)-[FilingDate]

On the other hand, if the Reporting Date is more random and you need to rely on user input for the value, then you could create a small one field table and have your form bound to this table/field so the value can be saved. You could then join this table/field in your query.

 

[UNC_Access]

Thanks Beetle! I created a one-field, one-record query table and linked it to the form.

Works now!