Save MSData selector as variable and reuse it

[WeebleSue]

I am using Access 2010, and trying out the Navigation Form feature.

I have a form under a tab where I have created two MS Date/Time Selector ActiveX controls. I have the default value set as Today. One is a Start Date and one is an End Date.

Once the user has made their selections, I want to store the results of these 2 fields in public variables. Then I will use these variables in queries to filter my results for stuff that lies between these 2 dates. I have several reports that need to be run using whatever date range the user selects.

If the user leaves the form and returns, then I want their selection retained so the start date is the value they have chosen, not Today.

I am able to store other user selections (text mainly) as public variables, but not these dates. I'm not sure what I'm doing wrong.

In a Module, I have:

Function Declarate()
    Public varStartDate As Date
    Public varEndDate As Date
End Function

And on the form, I have an Enter script on the StartDate as:

Public Sub StartDate_Enter()
If Not varStartDate Is Null Then   ' ***This line is giving me errors.
    Me.StartDate = varStartDate
Else
    Me.StartDate = Now()
End If
End Sub

On the form's OK button I have:

Private Sub OKDate_Click()
    varStartDate = Me.StartDate
    varEndDate = Me.EndDate
End Sub

Do I have to do it this way? No, I am open to other suggestions. I don't want the user to have to select 3 different variables for the start date, though, so the MSDate ActiveX is really the best option.

The line that is giving me errors is supposed to be looking at the public variable. If there is no value then set it to be today. If there IS a value, use THAT value to prepopulate the field. I have tried If varStartDate = 0 but that didn't work either.

Help? Please?

 

[mdlueck]

Misc input from me....

If there is no value then set it to be today.

The way to test for a non initialized Date variable is as follows:

[COLOR=DarkGreen]  'Note: No need to define an error return value. "12:00:00 AM" will be returned
  'as that is the default value of a Date datatype variable[/COLOR]

I would suggest normal Access fields with the Date Picker option selected rather than use the old Date Picker OCX control which is deprecated.

If you want to use that old control, a trick I remember with that is to momentarily set its value to Now() and then disable it. That way when you enable the control it jumps to the current date right away rather than being stuck in time as to when you created the form. Using the Date Picker capability of the core Access Field does not have to jump through that nonsense.

When returning to the form, if you do not have the default value, then it must have been a real value and you could populate that remembered value. Else default to today's date, per suggestion above. Do this on the Form_Load event.

In fact I think I should copy/paste the rest of the Function I borrowed that comment line from. Here it is...

Public Function datetimeutils_MondayOfThisWeek(Optional ByVal vntInputDate As Variant, Optional ByVal flgEuropeMode As Boolean = False) As Date
  On Error GoTo Err_datetimeutils_MondayOfThisWeek

  'Detect if the vntInputDate arg was not provided
  If IsMissing(vntInputDate) Then
    vntInputDate = Date
  Else
    'Check if the arg received is a valid date
    If Not IsDate(vntInputDate) Then
      Call errorhandler_MsgBox("Module: modshared_datetimeutils, Function: datetimeutils_MondayOfThisWeek(), Error: vntInputDate is not a valid date, received: " & vntInputDate)
      GoTo Exit_datetimeutils_MondayOfThisWeek
    End If
  End If

  If flgEuropeMode = False Then
    'USA Mode
    datetimeutils_MondayOfThisWeek = DateAdd("d", 1 - Weekday(vntInputDate, vbSunday), vntInputDate) + 1
  Else
    'Europe Mode
    datetimeutils_MondayOfThisWeek = DateAdd("d", 1 - Weekday(vntInputDate, vbMonday), vntInputDate)
  End If

Exit_datetimeutils_MondayOfThisWeek:
  Exit Function

Err_datetimeutils_MondayOfThisWeek:
  Call errorhandler_MsgBox("Module: modshared_datetimeutils, Function: datetimeutils_MondayOfThisWeek()")
  'Note: No need to define an error return value. "12:00:00 AM" will be returned
  'as that is the default value of a Date datatype variable
  Resume Exit_datetimeutils_MondayOfThisWeek

End Function

Useful validity checking and what not.

 

[WeebleSue]

Thanks for the post.

Your first

 section contained only 2 lines of comments..... but it looks like you posted the entire function below that, so I suppose it doesn't matter. 
 
If I use regular Access fields defined as Dates, then I have to store them somewhere like in a Table and I was trying to avoid doing that, since the date interval selection will be on the fly. I will give that a try, though, and see if I can make that work.

 

[spikepl]

varStartDate Is Null

aside from anything else, the above is SQL.

VBA test for Null is

IsNull(varStartDate)

 

[WeebleSue]

Got it. Thank you!

 

[WeebleSue]

I was able to get it to work using the new Access feature TempVars. They work like a charm!!

 

[mdlueck]

I was able to get it to work using the new Access feature TempVars.

I had not heard of that, so had to look it up...

http://blogs.office.com/b/microsoft...user-of-tempvars-in-access-2007-and-2010.aspx

Thank you for sharing your solution.