using a pre-filled control on form load gives NULL

[Tommo]

I have a form which has unbound controls PeriodStartDate and PeriodEndDate
which are pre-filled on form load. I want to use these dates to filter the
records available on the form. For all the options I have tried I get an
Invalid use of NULL message.
I am fairly certain it is because the user has not entered anything in the
controls and that there is a way of forcing Access to "read" the values in
them.

I have the following (I will only give the start date),
Dim strPeriodStartDate As String
strPeriodStartDate = Me.PeriodStartDate
..... I have also used
strPeriodStartDate = Me.PeriodStartDate.Value

Any ideas?

 

[John W. Vinson]

I have a form which has unbound controls PeriodStartDate and PeriodEndDate
which are pre-filled on form load. I want to use these dates to filter the
records available on the form. For all the options I have tried I get an
Invalid use of NULL message.
I am fairly certain it is because the user has not entered anything in the
controls and that there is a way of forcing Access to "read" the values in
them.

I have the following (I will only give the start date),
Dim strPeriodStartDate As String
strPeriodStartDate = Me.PeriodStartDate
.... I have also used
strPeriodStartDate = Me.PeriodStartDate.Value

Any ideas?

I'd use the NZ() function to return a desired value if the control is NULL. IT
will be NULL if the user hasn't typed anything:

strPeriodStartDate = NZ(Me!PeriodStartDate, #1/1/2010#)

or whatever default date you want.

You can use (say) DateSerial(Year(Date()), 1, 1) to default to the start of
the current year, or DateSerial(Year(Date()), Month(Date()), 1) for the start
of the current month.

 

[Tommo]

John, Thanks for the reply.
My PeriodStartDate and PeriodEndDate have the Row Source property set to an
SQL statement that returns the start and end of the current reporting period
- so that these dates show in the boxes as a default. I want the user to be
able to change these if they want ( the user changes one or both dates and
hits a "re-query" button).
My problem is that I want to use my default dates to filter the form on
load, preferably using the same routine as the "re-query" button.

I can see two ways of doing this
1) force a read of the control (I have seen a thread on this but cannot find
it again)
and set the filtering on the form
2) set two variables with the default dates and use these to set the
controls and to do the filtering

Option 2 might be easiest but I am not sure how to set a variable using an
SQL statement.

 

[Tommo]

Forgot to say that the Start and End dates are for financial years which are
held in a table, hence using an SQL statement to find out which "year" we are
in.