Date Diff Number 2 query

B

Beccy

opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])

If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = opwl![U_Census_Date] - opwl![U_Ref_Date]
End If

I run the following in a module, but when it goes to the
IF statement then it returns a calulation in days and not
weeks how can I chage this?
 
K

Ken Snell

We do not know what types of values you're stroring in these fields. Without
knowing that, your IF statement is meaningless to us for trying to help you
identify why you're not getting the answer you expect/want.

But, just guessing here.....these fields are storing date/time values? If
yes, subtracting them will give you days, not weeks. Date/time formats do
not store weeks, they store days and time as a floating point number where
the day is the integer portion (number of days since 31 December 1899) and
the time is the fraction of 24 hours represented as the decimal portion.

My suggestion: change your IF statement so that it uses the DateDiff
function to give you the answer you want in weeks. Or, just convert the days
to weeks by dividing by 7.
 
B

Beccy

The fields are in data/time format. Ideally I would like
to adjust the calculation to include datediff function but
don't know how this can be done.

Any suggestions please?

I tried the following but it returns an error message:
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])
If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_Census_Date] -
opwl![U_Ref_Date]))
End If

-----Original Message-----
We do not know what types of values you're stroring in these fields. Without
knowing that, your IF statement is meaningless to us for trying to help you
identify why you're not getting the answer you expect/want.

But, just guessing here.....these fields are storing date/time values? If
yes, subtracting them will give you days, not weeks. Date/time formats do
not store weeks, they store days and time as a floating point number where
the day is the integer portion (number of days since 31 December 1899) and
the time is the fraction of 24 hours represented as the decimal portion.

My suggestion: change your IF statement so that it uses the DateDiff
function to give you the answer you want in weeks. Or, just convert the days
to weeks by dividing by 7.

--
Ken Snell
<MS ACCESS MVP>
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])

If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = opwl![U_Census_Date] - opwl! [U_Ref_Date]
End If

I run the following in a module, but when it goes to the
IF statement then it returns a calulation in days and not
weeks how can I chage this?
Click to expand...


.
Click to expand...
 
K

Ken Snell

What is the error message that you're getting?
What is opwl? an object representing a form? an object representing a
report? an object representing a recordset?

Need a bit more info to try to help you....

--
Ken Snell
<MS ACCESS MVP>
Beccy said:
The fields are in data/time format. Ideally I would like
to adjust the calculation to include datediff function but
don't know how this can be done.

Any suggestions please?

I tried the following but it returns an error message:
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])
If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_Census_Date] -
opwl![U_Ref_Date]))
End If

-----Original Message-----
We do not know what types of values you're stroring in these fields. Without
knowing that, your IF statement is meaningless to us for trying to help you
identify why you're not getting the answer you expect/want.

But, just guessing here.....these fields are storing date/time values? If
yes, subtracting them will give you days, not weeks. Date/time formats do
not store weeks, they store days and time as a floating point number where
the day is the integer portion (number of days since 31 December 1899) and
the time is the fraction of 24 hours represented as the decimal portion.

My suggestion: change your IF statement so that it uses the DateDiff
function to give you the answer you want in weeks. Or, just convert the days
to weeks by dividing by 7.

--
Ken Snell
<MS ACCESS MVP>
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])

If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = opwl![U_Census_Date] - opwl! [U_Ref_Date]
End If

I run the following in a module, but when it goes to the
IF statement then it returns a calulation in days and not
weeks how can I chage this?
Click to expand...


.
Click to expand...
Click to expand...
 
B

Beccy

opwl is a recordset, the error message is 'aruement not
optional' and the second datediff is highlighted.

-----Original Message-----
What is the error message that you're getting?
What is opwl? an object representing a form? an object representing a
report? an object representing a recordset?

Need a bit more info to try to help you....

--
Ken Snell
<MS ACCESS MVP>
The fields are in data/time format. Ideally I would like
to adjust the calculation to include datediff function but
don't know how this can be done.

Any suggestions please?

I tried the following but it returns an error message:
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])
If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl! [U_Census_Date] -
opwl![U_Ref_Date]))
End If

-----Original Message-----
We do not know what types of values you're stroring in these fields. Without
knowing that, your IF statement is meaningless to us
Click to expand...
for
trying to help you
identify why you're not getting the answer you expect/want.

But, just guessing here.....these fields are storing date/time values? If
yes, subtracting them will give you days, not weeks. Date/time formats do
not store weeks, they store days and time as a floating point number where
the day is the integer portion (number of days since 31 December 1899) and
the time is the fraction of 24 hours represented as the decimal portion.

My suggestion: change your IF statement so that it
Click to expand...
uses
the DateDiff
function to give you the answer you want in weeks. Or, just convert the days
to weeks by dividing by 7.

--
Ken Snell
<MS ACCESS MVP>
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])

If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = opwl![U_Census_Date] - opwl! [U_Ref_Date]
End If

I run the following in a module, but when it goes to the
IF statement then it returns a calulation in days and not
weeks how can I chage this?


.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Douglas J. Steele

opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_Census_Date] -
opwl![U_Ref_Date]))

is invalid: you need 3 arguments, not 2.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Beccy said:
opwl is a recordset, the error message is 'aruement not
optional' and the second datediff is highlighted.

-----Original Message-----
What is the error message that you're getting?
What is opwl? an object representing a form? an object representing a
report? an object representing a recordset?

Need a bit more info to try to help you....

--
Ken Snell
<MS ACCESS MVP>
The fields are in data/time format. Ideally I would like
to adjust the calculation to include datediff function but
don't know how this can be done.

Any suggestions please?

I tried the following but it returns an error message:
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])
If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl! [U_Census_Date] -
opwl![U_Ref_Date]))
End If


-----Original Message-----
We do not know what types of values you're stroring in
these fields. Without
knowing that, your IF statement is meaningless to us for
trying to help you
identify why you're not getting the answer you
expect/want.

But, just guessing here.....these fields are storing
date/time values? If
yes, subtracting them will give you days, not weeks.
Date/time formats do
not store weeks, they store days and time as a floating
point number where
the day is the integer portion (number of days since 31
December 1899) and
the time is the fraction of 24 hours represented as the
decimal portion.

My suggestion: change your IF statement so that it uses
the DateDiff
function to give you the answer you want in weeks. Or,
just convert the days
to weeks by dividing by 7.

--
Ken Snell
<MS ACCESS MVP>
message
opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])

If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = opwl![U_Census_Date] - opwl!
[U_Ref_Date]
End If

I run the following in a module, but when it goes to the
IF statement then it returns a calulation in days and
not
weeks how can I chage this?


.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DateDiff Calculation 3
Copying Problem 1
date difference calculation 1
Copying problem!!! 3
Nz Query? 4
query 6
Date Diff- weeks/days 5
Date Query 1

Top