Formulae Query

[Enderjit Singh]

I am trying to set up a query ideally as a module in vb to
do the following:

Date1 minus Date 2 (If Date2 IS NULL) then minus Date 3,
giving a return value in weeks or days. Can anyone help?

 

[Brian Camire]

You might try an expression something like this

DateDiff("d", Nz([Date2], [Date3]), [Date1])

for the difference in days. For the difference in weeks, use "ww" instead
of "d".

 

[Brian Camire]

Well, in VBA code, I might simply do this:

If IsNull(rbk![LastDNAdate]) Then
LastDNAdate = ""
Else
LastDNAdate = _
Format(DateSerial(Right(rbk![LastDNAdate], 2), _
Mid(rbk![LastDNAdate], 3, 2), _
Left(rbk![LastDNAdate], 2)), "yyyymmdd")
End If

I assume LastDNAdate is a String. If it's a Variant, you might want to set
it to Null instead of an empty string in the If block.

In a query, I might use an expression like this:

IIf(IsNull(rbk![LastDNAdate]), Null, Format(DateSerial(Right([Your
Table].[LastDNAdate], 2), Mid([Your Table].[LastDNAdate], 3, 2), Left([Your
Table].[LastDNAdate], 2)), "yyyymmdd")

Is there a reason why you're storing dates as String variables and not as
Date variables (or Date/Time fields in your table)?

I run this module to change the date formats of my fields
from DDMMYY to YYYYMMDD, but as I have fields that I have
no values in them I get error messages. How can I get the
module to run ignoring those Null values?

How do I fit the Nz function in the below query:
LastDNAdate = Format(DateSerial(Right(rbk![LastDNAdate],
2), Mid(rbk![LastDNAdate], 3, 2), Left(rbk![LastDNAdate],
2)), "yyyymmdd")

-----Original Message-----
P.S.

The way DateDiff() works for differences in weeks (i.e., using "ww") is
little convoluted. You may need "w" instead, and/or you may need to specify
which day you want to count as the first day of the week using the fourth
argument of DateDiff(), which defaults to whatever your regional settings
specify. Check the help for details. If you're using Access 2000 or later,
you'll need to view the help from the Microsoft Visual Basic Window (which
you can display by pressing Alt+F11) to see the help topic for this
function.

You might try an expression something like this

DateDiff("d", Nz([Date2], [Date3]), [Date1])

for the difference in days. For the difference in weeks, use "ww" instead
of "d".


I am trying to set up a query ideally as a module in vb to
do the following:

Date1 minus Date 2 (If Date2 IS NULL) then minus Date 3,
giving a return value in weeks or days. Can anyone help?

.