date calculations in queries with null fields

  • Thread starter Thread starter Glyn Foster
  • Start date Start date
G

Glyn Foster

Is there any way to assign a value to a null field in an
Access query?

I have a database showing occupancy rates for
properties,using 'Date Occupied' and Date Vacated' which
of course shows a null value in the 'Date Terminated'
field (as the property is still occupied). If I want to
run a report showing occupancy during a particular period
(say, a quarter or a month) those fields containing a null
value in them do not calculate. I know that I can use the
date() function to return today's date for the null field,
but this means that if I want to calculate a particular
period, I have to reset the PC's date to the last date of
the period, which is far from ideal.



Hoping that you can help,





Glyn Foster
 
Dear Glyn:

You could use a text box for the "current date" to use in the
calculation. This would just be an unbound control. You could set
the value in this text box to Date() when the form opens, and alter it
as needed. Then, put the text box value in your query something like
this:

Nz([Date Terminated], [Forms]![YourForm]![DateTextBox])

This assumes you are writing an MDB. Replace YourForm and DateTextBox
above with the actual names of your form and text box. The form must
be open whenever you run the query.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Glyn

I'd use the NZ() function in the format: NZ([DateVacated], Date()).

Since you don't give us any indication of what your query looks like,
I'll assume that you want to know the apartment number of each
apartment that is occupied today. Your where clause would be
different if you want to know which apartments were occupied at the
end of the period of something else. By using the NZ function

Select AptNum
From YourTable
WHERE [DateOccupied] <= Date()
AND NZ([DateVacated], Date()) >= Date()

--
HTH

Dale Fye


Is there any way to assign a value to a null field in an
Access query?

I have a database showing occupancy rates for
properties,using 'Date Occupied' and Date Vacated' which
of course shows a null value in the 'Date Terminated'
field (as the property is still occupied). If I want to
run a report showing occupancy during a particular period
(say, a quarter or a month) those fields containing a null
value in them do not calculate. I know that I can use the
date() function to return today's date for the null field,
but this means that if I want to calculate a particular
period, I have to reset the PC's date to the last date of
the period, which is far from ideal.



Hoping that you can help,





Glyn Foster
 
Back
Top