Microsoft Access Defining date parameter in a form

Posted on 2015-01-22
Last Modified: 2015-01-23
I have a form in access in which I have several dates saved that are then used throughout the database.  However, I am having issues that from time to time when I run queries access will ask me to define the parameter instead of it reading the date defined in the form.  If I go into the form, change the name of the date field and then open the query in design mode and rerun, then it is fine.  However i want to solve the issue.
Question by:krfunk76
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40564221
I have generally stopped using form controls to store global variables, and have replaced that technique with using TempVars.  TempVars is a collection of variables which can be set in design view as well as via code.  They retain their values when your code stops running, and even when the code encounters a unhandled error.  To set a Tempvars value you can use syntax like:

TempVars.Add "SomeDate", #1/22/15#
Tempvars!SomeDate = #1/22/15#
Tempvars("SomeDate") = #1/22/15#

If you want to set the value of a TempVars variable from a forms control, you must use the .Value property of the controls or you will raise an error.
Tempvars!SomeDate = me.txt_SomeDate.Value
or perhaps
Tempvars!SomeDate = cdate(me.txt_SomeDate.Value)

Open in new window

To use Tempvars variable in a query, the syntax looks like: [Tempvars]![SomeDate]

However, I have found that occassionally, this syntax does not work, so I created a wrapper function that returns the value of the TempVars as a variant.
Public Function fnTempvars(VarName as string) as Variant

    fnTempvars = Tempvars(VarName)

End Function

Open in new window

Which will return the value of the TempVar or a NULL if that Tempvar Variable name has not been defined.  In your query, you would simply refer to:

WHERE [SomeDate] = fnTempvars("SomeDate")
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40564226
It is not clear how, (or if), you are storing these dates...
It is also not clear how you are referencing the dates in your query.

One common approach is to create a Public variable to store the dates.
Then use a Public Function to retrieve it (for use as a parameter in a query)

Another issue may be that you are running the queries before the date textbox "Updates"
Hence the date may not be available to the query.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40564230
You can ignore my post above, ..Dale has this all covered.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

ID: 40564363
Thanks Dale.  I need some help though as I am a bit of a access novice.  Outside of the GUI controls, I am outside my comfort zone.  I have the below now:

The benefit that I get with this is I have a default value that will be used in my queries.  It also allows me to quickly change the value using the form to run different scenarios.

If I understand you correctly, you are saying that I can maintain this however, I should reference the form value through code and then reference the code in my queries?

LVL 36

Expert Comment

ID: 40564864
Taking the form variables which already exist and which you change manually and copying them to tempvars is overkill.  There is nothing wrong with referencing the form directly.  I think you may be running into trouble when you are relying on a default.  Can you verify that?

I use defaults all the time for form supplied date parms but I don't use the default property to supply them, I use code in the form's Current event.  So, you could try moving the expression from the default property and adding it as a line of code in the Current event.

Me.date1 = someexpression
Me.date2 = someexpression
LVL 50

Expert Comment

by:Gustav Brock
ID: 40564900
Your DefaultValue expression can be simplified to:


Your problem is that the field is unbound so the query may not know what to expect.
Go to your query in design mode and open Parameters and insert:

    [Forms]![frm_Control]![DT_SETTLE] and specify Date for data type.

Also make sure, it cannot be Null.


Author Comment

ID: 40565047
I run into problems when I link to the Access database through excel and return results using the "get external data" set-up in excel.  At least there seems to be a connection to the issues I face when the param stops working and accessing it through excel.  

I did try to add the parameter info to the query in design mode but that did not seem to help.

I appreciate the simplified expression, I didn't realize that by subtracting from the month that the function would adjust the year also.

I will try to add the expression as an event instead of the default.
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40565089
Going back to my suggestion to use TempVars.

Pat may consider it overkill, but when you want to test a query in design view, and don't have the main parameters form open, it can be a pain in the butt.  Additionally, whenever you refer to a forms control in a query, you should parameterize the query, and if you want to execute an action query, you have to define the querydef and either set the value of the parameters or Eval( ) them, before you run:


When you use tempvars, you don't have to do that, as fnTempVars() can be used in the query without having to redefine it.

I would add a line of code to the AfterUpdate event of each of the controls on that form; something like:

Private Sub txt_DT_Settle_AfterUpdate

    Tempvars!DT_Settle = me.txt_DT_Settle.Value

End Sub

Open in new window

Then, in the Form Load event, I would set the value of those fields, similar to the method Pat mentioned above, and would then call the AfterUpdate event of each control.
LVL 36

Expert Comment

ID: 40565304
I think TempVars have a place.  Just not this one.

If the dates are entered into the form then, you need the form open so trying to run the queries when the form isn't open won't happen except perhaps during testing and that would be a programmer error.  The programmer would hit his head with his hand, say a few curse words, and open the form.  This is exactly why I have a dent in the middle of my forehead.  If you want to use the dates for many reports run from various places once the dates are entered, it is reasonable to make them TempVars but for one-time use when the form has to be open anyway, I wouldn't use TempVars.  That is simply an extra step of abstraction that isn't necessary.

If you do elect to use TempVars for this situation, you have to deal with the possibility that no one ever opened the form to set the value of the TempVar.

I don't think you can reference queries that require parameters from Excel.  You might have to push the export from Access to Excel so that the query runs when Access (and the form) are open.  If you need to run the query from Excel, I think you need to create it in Excel so it will prompt you there for the parameter.

Author Comment

ID: 40565405
To be clear, i am not trying to refer to parameters that are in excel to run the queries, i have simply linked to tables in the db to return values in excel for analysis purposes.

I am open to using Pat's suggestion of not using the "default value", however as I said I have never used code before in Access.  So Pat I see how I can go into the "On Current" to put the suggested code in Me.date1 = someexpression, therefore defining the expression here.  This may seem very basic, but where in the form do i reference "Me.date1" so that will be the default value?  I would assume that has to go into an unbound text box?  Then when i open the form it will populate with the date based upon the expression in the code, and allow me to change it?

I appreciate the help.
LVL 36

Accepted Solution

PatHartman earned 500 total points
ID: 40565525
In design view, look at the Name property of each control.  If you didn't specificly name them, they will be some generated name like text48.  You should change the name of each date to be something meaningful.  You don't want to write code referring to text08.  Who can read that and know what it means.  I prefix my textbox controls with "txt" so on my form I might have txtStartDate and txtEndDate

After you change the names of all the relevant controls, then go into the Current event and set the values.  You would cut them from where they are now and paste them into the code.

Me.txtStartDate = pasted from default property

Notice as you are typing that you get intellisense after the Me.  -  that helps you to avoid typos.

It sounded like you were trying to run these parameter driven queries FROM Excel.  Are you trying to do that?

Author Comment

ID: 40565646
In excel I simply have connections to the database to retrieve data that has been saved out to tables from the series of queries that run analysis.  

I want to use VBA code to pull the information from access but I haven't figured that out yet so I am using the "get external data" process within excel.  However, there are some issues with it because it creates a live connection and so if I am in both at the same time, it causes some issues.  

Thank you for the instructions on how to setup the dates in the current region, it works as described and it seems to have solved the issue that I was facing previously.
LVL 36

Expert Comment

ID: 40567870
This question seems to have morphed from a question about using a form to supply dates to a report to something to do with Excel.  Please start a new question with a new topic.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question