Setting a specific date as the default value (1 Viewer)

mollycase · Sep 17, 2009

Hi -

I know this is probably incredibly easy, but I'm either searching wrong or completely missing something.

I am using the Expense Reports template and have modified it for what I need. It's all working just fine except...(btw, Access 2003)

There is a form to "view reports" that has two fields: begin date and end date.

I would like to set the begin date to a specific date (beginning of our fiscal year, 9/1/09) so that i don't have to type it every time.

All I can find when searching is how to set the default value to various things, like today's date, or the beginning of the current month, etc.

I need to be recognized as a date in order for the report query to work correctly.

Any ideas?

Thanks!

statsman · Sep 17, 2009

You have two ways of doing this.

Open your table in design view and select the begin date field. In default value you can set the default date to "01/01/2009" (no quotes). Every record entered will have this date entered automatically in the begin date field. Remember to change it on Sept. 1 2010.

You may also set the default date to "01/09/2009" in the form you are using to enter new records. With the form in design view, right click the begin date text box and select properties. In Control Source you can enter the desired start date. Again, you will have to remember to change it when you start a new fiscal year.

USE ONE METHOD OR THE OTHER, NOT BOTH.

Kryst51 · Sep 17, 2009

Also, if you are trying to filter dates for your report. In your query you can put in the criteria >=#9/1/09

And the the only records in your query will be those where the start date is that date or after.

Poppa Smurf · Sep 19, 2009

1. Create a table with one field to store the beginning of your fiscal year. For this example, I will use start_date as the field name and tbl_parameters as the table.

2. Now using the form Report Date Range change the Form Open event as shown in bold.
When the form opens, the date stored in the table will be displayed as the value for the start date on the form.

Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
Me![Beginning Rpt Date] = DLookup("[start_Date]", "tbl_parameters")
End Sub

Each year you will need to change the date in the table.

TraceSL · Sep 12, 2017

I am trying to determine best way to default a specific date onto a form, where they date will change each month for reports I generate.
I tried using the sub routine from Poppa Smurf but it's not working error says there is a null.

Any suggestions. I have the date in a table, I want to call the date to the form of various databases to run sets of reports each month.

I tried building it using expression builder on the actual field with the table name and field label but receiving the #Name? ERROR.

Any help appreciated.

pbaldy · Sep 12, 2017

Did you create the table he mentioned? What line throws the error? I'd use the load event, as I'm not sure you can add data in the open event (could be wrong).

TraceSL · Sep 12, 2017

I did create table and wrote sub routine to match my table and field name inserted on the form open command. I usually put events on load option maybe I should try that.

I use the FMS Total visual agent to schedule and run these reports from 10-15 access databases and in the past I changed the computer date to match the ending date of the reports however now the TVA client running on a virtual server so I can't change the computer date so being forced to build this out with another solution where I can change end date and a few other parameters yearly too. Right now I open all the databases each year to update these.

I'd like to push the arguments in from one table or source.

I started with the end date since it changes every month.

Thanks for the help

Runtime error 2428 invalid argument domain aggregate function

Private Sub Form_Open(Cancel As Integer)
Me![txtLastDate] = DLookup("[Enddate]", tblDivRptDates)
End Sub

txtLastDate is the name of the text box where Ending date is populated on the form

pbaldy · Sep 12, 2017

All arguments are strings:

DLookup("[Enddate]", "tblDivRptDates")

TraceSL · Sep 13, 2017

I thought the quotes were around the table name

is the argument right with the Me? I am primarily an Accountant never get enough time to dig deep into this fun stuff.

Private Sub Form_Open(Cancel As Integer)
Me![txtLastDate] = DLookup("[Enddate]", "tblDivRptDates")
End Sub

pbaldy · Sep 13, 2017

Does that work? Did you try the load event?

Mark_ · Sep 13, 2017

To figure out your default date, you can use DateSerial(Year(Now),1,1) to return the first day of the current year.

Using the value returned by Month(Now) would let you figure which quarter (if applicable) or if you are breaking a fiscal year. For the later,

DefaultDate = DateSerial(Year(Now),10,1)' Set to the beginning of the fiscal year.
If Month(Now) > 9 'If we've finished with September then use the current year.
DefaultDate = DateSerial(Year(Now)-1),10,1)
EndIf

This would give you a fiscal calendar from 1 OCT to 30 SEPT to work with.

TraceSL · Sep 14, 2017

out for meeting going to try to give it a try tonight, thanks for the help

TraceSL · Sep 14, 2017

Perfect
The on load worked

Thanks for the help

Private Sub Form_Load()
Me![txtLastDate] = DLookup("[Enddate]", "tblDivRptDates")
End Sub

Tracy

pbaldy · Sep 14, 2017

Happy to help. I thought the open event was too soon. Mark raises a good point. If the date can be derived from the current date, like the "last day of the previous month", you may be able to just code it and not have to maintain it in a table.

TraceSL · Sep 14, 2017

I will keep Mark's suggestion in mind, the date will always be the end of a specific month but the day I generate the reports varies based on when the books closed, so I will consider this in future or for other needs but for now I think using the table to prepopulate the end date on the form for all the databases that run automated with the Total Visual Agent program, this solution will work for now.

Thanks again

Setting a specific date as the default value (1 Viewer)

mollycase

Registered User.

statsman

Active member

Kryst51

Singin' in the Hou. Rain

Poppa Smurf

Registered User.

TraceSL

Registered User.

pbaldy

Wino Moderator

TraceSL

Registered User.

pbaldy

Wino Moderator

TraceSL

Registered User.

pbaldy

Wino Moderator

Mark_

Longboard on the internet

TraceSL

Registered User.

TraceSL

Registered User.

pbaldy

Wino Moderator

TraceSL

Registered User.

Similar threads

Users who are viewing this thread