Hi Judy,
adding on to what Tom wrote...
.... and in a query, you would do this:
field --> NumWeeks: CalcWeeks([DateStart], [DateEnd])
~~~
"I can type in the DateDiff("w",..."
actually, you would use "ww" for weeks ... "w" is weekdays...
~~~
instead of this:
IIf(IsNull(DateEnd), Now(), DateEnd))
you can do this:
nz(DateEnd, Date())
from Help
'~~~~~~~~~~~~~~~~~~~~~~~~~~``
Nz Function
You can use the Nz function to return zero, a zero-length string ("
"), or another specified value when a Variant is Null. For example, you
can use this function to convert a Null value to another value and
prevent it from propagating through an expression.
Syntax
Nz(variant[, valueifnull])
The Nz function has the following arguments.
Argument
variant
A variable of data type Variant.
valueifnull
Optional (unless used in a query).
A Variant that supplies a value to be returned if the variant
argument is Null. This argument enables you to return a value other than
zero or a zero-length string.
Note If you use the Nz function in an expression in a query without
using the valueifnull argument, the results will be a zero-length string
in the fields that contain null values.
'~~~~~~~~~~~~~~~~~~~~~~~~~~`
~~~
the difference between NOW and DATE is that Now also gives you the
current time ... and if all you want is a date, you do not want to use
Now
~~~
Access stores date/times in a numeric format where the integer portion
of the number represents the date and the decimal portion of the number
represents time:
1/1/100 --> -657,434
1/2/100 --> -657,433
12/30/1899 --> 0
1/1/1950 --> 18,264
1/1/2005 --> 38,353
12/31/2007 --> 39,447
1/1/9999 --> 2,958,101
12/31/9999 --> 2,958,465
the time is a fraction of the day
12 noon is 0.5
6pm is 0.75
1/1/2005, 12 noon --> 38353.5
if you have a control with just a date and you want to make sure it
converts to a whole number (or it is stored in text format), use
DateValue([control_or_fieldname])
or
cLng([control_or_fieldname])
likewise, if you have a time, you can force it to the fractional part by
TimeValue([control_or_fieldname])
since dates are whole numbers and times are the fractions, you can also
do arithmetic operations on them
that is why you can subtract one date from another and get the number of
days between the two.
Because dates can also have a time component, it is handy to use
DateDiff and DateAdd, which let you specify the time increment (year,
month, day, hour, etc) to calculate new dates or get a difference
between dates.
The DateDiff function can be used to specify what time increment you
want returned when you subtract dates
Likewise, there is a DateAdd function to add specific time increments to
a date
Warm Regards,
Crystal
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
Tom said:
I am at the end of my rope and need help desperately. I have a form
and a
query where I would like to use a public function to calculate weeks.
This
is my function:
Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As
Variant
Dim int_Weeks As Integer
I am lost as to what code needs to be put in the control source of the
field
where I want to calculate the weeks. I can type in the DateDiff("w",
DateStart, IIf(IsNull(DateEnd), Now(), DateEnd)) portion of the
function and
it works, so I am left wondering what the purpose of the function is.
Can
anyone set me straight?
In the control source you'd put
=CalcWeeks([DateStart], [DateEnd])
The purpose of defining a function is so you can use it in multiple
places
without having to type in the entire calculation each time.
Tom Lake
