Using the Diff2Date with current date & time

B

bladelock

I keep getting and error when using current date & time
I used this before with other fiels and no problem, but now I want to
compare using current date and time. I trying using Date() and Time() but I
get an error. What am I doing wrong? Here is the code:

=Diff2Dates("hns",CDate([Bed Dte] & " " & [Bed Time]),CDate([DATE()] & " " &
[TIME()]))

Thanks
 
R

Rick B

I did a search in help and could not find a "Diff2Dates" or "Diff2Date"
function in Access. I have used the DateDiff function, but never
Diff2Dates.

I did a google search and only found four posts ever that contains that
string.



Brendan Reynold previously posted this advise:

If any of the values involved are Null, that could be the problem. You might
try adding criteria to your query to exclude any records with Null values in
any of the fields involved in the expression, or use the NZ function to
replace Null values with an appropriate alternative.



Doug Steele offered this:

There could be something wrong with your database: I've never heard of a
problem with that code.

Try creating a new database, and importing all of the objects from your
existing database into it.
 
B

Brendan Reynolds

An Access Date/Time field always stores a complete date and time. You may
have formatted the 'Bed Dte' field to show only the date, and the 'Bed Time'
field to show only the time, but this changes only the *display* of the
data, the complete date and time are still stored. If you don't enter a time
part, it defaults to midnight, and if you don't enter a date part, it
defaults to 30th December 1899. So when you concatenate the two fields
together, you don't end up with a valid Date/Time, you end up with something
like "2/9/2005 00:00 30/12/1899 13:15" (that's dd/mm/yyyy format, swap the
days and months if you're in the US).

One possible alternative would be to use DateSerial and TimeSerial and add
(not concatenate) them like so ...

DateSerial(Year(Date), Month(Date), Day(Date)) _
+ TimeSerial(Hour(Time()), Minute(Time()), Second(Time()))

Insert your 'Bed Dte' field where I have 'Date' in the example, and your
'Bed Time' field where I have 'Time()' in the example.
 
B

Brendan Reynolds

It's Doug's enhanced version of DateDiff ...
http://www.accessmvp.com/djsteele/Diff2Dates.html
.... but as you'll see from my other post in this thread, I think the problem
is in the way the two date fields are being concatenated. If I'm right, the
problem isn't specific to Doug's function, but would happen with the
ordinary DateDiff function too.

--
Brendan Reynolds (MVP)

Rick B said:
I did a search in help and could not find a "Diff2Dates" or "Diff2Date"
function in Access. I have used the DateDiff function, but never
Diff2Dates.

I did a google search and only found four posts ever that contains that
string.



Brendan Reynold previously posted this advise:

If any of the values involved are Null, that could be the problem. You
might
try adding criteria to your query to exclude any records with Null values
in
any of the fields involved in the expression, or use the NZ function to
replace Null values with an appropriate alternative.



Doug Steele offered this:

There could be something wrong with your database: I've never heard of a
problem with that code.

Try creating a new database, and importing all of the objects from your
existing database into it.


--
Rick B



bladelock said:
I keep getting and error when using current date & time
I used this before with other fiels and no problem, but now I want to
compare using current date and time. I trying using Date() and Time() but I
get an error. What am I doing wrong? Here is the code:

=Diff2Dates("hns",CDate([Bed Dte] & " " & [Bed Time]),CDate([DATE()] & "
" &
[TIME()]))

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

bladelock

No, the "Bed Dte" and "Bed Time" work fine, the error message is for Date()
and Time(). The function don't understand the Date() and Time()

Brendan Reynolds said:
An Access Date/Time field always stores a complete date and time. You may
have formatted the 'Bed Dte' field to show only the date, and the 'Bed Time'
field to show only the time, but this changes only the *display* of the
data, the complete date and time are still stored. If you don't enter a time
part, it defaults to midnight, and if you don't enter a date part, it
defaults to 30th December 1899. So when you concatenate the two fields
together, you don't end up with a valid Date/Time, you end up with something
like "2/9/2005 00:00 30/12/1899 13:15" (that's dd/mm/yyyy format, swap the
days and months if you're in the US).

One possible alternative would be to use DateSerial and TimeSerial and add
(not concatenate) them like so ...

DateSerial(Year(Date), Month(Date), Day(Date)) _
+ TimeSerial(Hour(Time()), Minute(Time()), Second(Time()))

Insert your 'Bed Dte' field where I have 'Date' in the example, and your
'Bed Time' field where I have 'Time()' in the example.

--
Brendan Reynolds (MVP)


bladelock said:
I keep getting and error when using current date & time
I used this before with other fiels and no problem, but now I want to
compare using current date and time. I trying using Date() and Time() but
I
get an error. What am I doing wrong? Here is the code:

=Diff2Dates("hns",CDate([Bed Dte] & " " & [Bed Time]),CDate([DATE()] & " "
&
[TIME()]))

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

Brendan Reynolds

I can't reproduce that. This works for me in the Immediate window ...

? diff2dates("hns", cdate((date()-1) & " " & time()),cdate(date() & " " &
time()))
24 hours

--
Brendan Reynolds (MVP)


bladelock said:
No, the "Bed Dte" and "Bed Time" work fine, the error message is for
Date()
and Time(). The function don't understand the Date() and Time()

Brendan Reynolds said:
An Access Date/Time field always stores a complete date and time. You may
have formatted the 'Bed Dte' field to show only the date, and the 'Bed
Time'
field to show only the time, but this changes only the *display* of the
data, the complete date and time are still stored. If you don't enter a
time
part, it defaults to midnight, and if you don't enter a date part, it
defaults to 30th December 1899. So when you concatenate the two fields
together, you don't end up with a valid Date/Time, you end up with
something
like "2/9/2005 00:00 30/12/1899 13:15" (that's dd/mm/yyyy format, swap
the
days and months if you're in the US).

One possible alternative would be to use DateSerial and TimeSerial and
add
(not concatenate) them like so ...

DateSerial(Year(Date), Month(Date), Day(Date)) _
+ TimeSerial(Hour(Time()), Minute(Time()), Second(Time()))

Insert your 'Bed Dte' field where I have 'Date' in the example, and your
'Bed Time' field where I have 'Time()' in the example.

--
Brendan Reynolds (MVP)


bladelock said:
I keep getting and error when using current date & time
I used this before with other fiels and no problem, but now I want to
compare using current date and time. I trying using Date() and Time()
but
I
get an error. What am I doing wrong? Here is the code:

=Diff2Dates("hns",CDate([Bed Dte] & " " & [Bed Time]),CDate([DATE()] &
" "
&
[TIME()]))

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

bladelock

That worked, the syntax was wrong on my statement
THANKS

Brendan Reynolds said:
I can't reproduce that. This works for me in the Immediate window ...

? diff2dates("hns", cdate((date()-1) & " " & time()),cdate(date() & " " &
time()))
24 hours

--
Brendan Reynolds (MVP)


bladelock said:
No, the "Bed Dte" and "Bed Time" work fine, the error message is for
Date()
and Time(). The function don't understand the Date() and Time()

Brendan Reynolds said:
An Access Date/Time field always stores a complete date and time. You may
have formatted the 'Bed Dte' field to show only the date, and the 'Bed
Time'
field to show only the time, but this changes only the *display* of the
data, the complete date and time are still stored. If you don't enter a
time
part, it defaults to midnight, and if you don't enter a date part, it
defaults to 30th December 1899. So when you concatenate the two fields
together, you don't end up with a valid Date/Time, you end up with
something
like "2/9/2005 00:00 30/12/1899 13:15" (that's dd/mm/yyyy format, swap
the
days and months if you're in the US).

One possible alternative would be to use DateSerial and TimeSerial and
add
(not concatenate) them like so ...

DateSerial(Year(Date), Month(Date), Day(Date)) _
+ TimeSerial(Hour(Time()), Minute(Time()), Second(Time()))

Insert your 'Bed Dte' field where I have 'Date' in the example, and your
'Bed Time' field where I have 'Time()' in the example.

--
Brendan Reynolds (MVP)


I keep getting and error when using current date & time
I used this before with other fiels and no problem, but now I want to
compare using current date and time. I trying using Date() and Time()
but
I
get an error. What am I doing wrong? Here is the code:

=Diff2Dates("hns",CDate([Bed Dte] & " " & [Bed Time]),CDate([DATE()] &
" "
&
[TIME()]))

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Thanks for stepping in, Brendan. However, why not Now() instead of
cdate(date() & " " & time())?

And you're absolutely right that [Bed Dte] + [Bed Time] is preferable to
CDate([Bed Dte] & " " & [Bed Time]).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Brendan Reynolds said:
I can't reproduce that. This works for me in the Immediate window ...

? diff2dates("hns", cdate((date()-1) & " " & time()),cdate(date() & " " &
time()))
24 hours

--
Brendan Reynolds (MVP)


bladelock said:
No, the "Bed Dte" and "Bed Time" work fine, the error message is for
Date()
and Time(). The function don't understand the Date() and Time()

Brendan Reynolds said:
An Access Date/Time field always stores a complete date and time. You
may
have formatted the 'Bed Dte' field to show only the date, and the 'Bed
Time'
field to show only the time, but this changes only the *display* of the
data, the complete date and time are still stored. If you don't enter a
time
part, it defaults to midnight, and if you don't enter a date part, it
defaults to 30th December 1899. So when you concatenate the two fields
together, you don't end up with a valid Date/Time, you end up with
something
like "2/9/2005 00:00 30/12/1899 13:15" (that's dd/mm/yyyy format, swap
the
days and months if you're in the US).

One possible alternative would be to use DateSerial and TimeSerial and
add
(not concatenate) them like so ...

DateSerial(Year(Date), Month(Date), Day(Date)) _
+ TimeSerial(Hour(Time()), Minute(Time()), Second(Time()))

Insert your 'Bed Dte' field where I have 'Date' in the example, and your
'Bed Time' field where I have 'Time()' in the example.

--
Brendan Reynolds (MVP)


I keep getting and error when using current date & time
I used this before with other fiels and no problem, but now I want to
compare using current date and time. I trying using Date() and Time()
but
I
get an error. What am I doing wrong? Here is the code:

=Diff2Dates("hns",CDate([Bed Dte] & " " & [Bed Time]),CDate([DATE()] &
" "
&
[TIME()]))

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Conrad

Just to add one last comment.

Looks like the OP originally had square brackets
around the Date and Time so Access thought
they were table fields.

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

Douglas J. Steele said:
Thanks for stepping in, Brendan. However, why not Now() instead of
cdate(date() & " " & time())?

And you're absolutely right that [Bed Dte] + [Bed Time] is preferable to
CDate([Bed Dte] & " " & [Bed Time]).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Brendan Reynolds said:
I can't reproduce that. This works for me in the Immediate window ...

? diff2dates("hns", cdate((date()-1) & " " & time()),cdate(date() & " " &
time()))
24 hours

--
Brendan Reynolds (MVP)


bladelock said:
No, the "Bed Dte" and "Bed Time" work fine, the error message is for
Date()
and Time(). The function don't understand the Date() and Time()

:

An Access Date/Time field always stores a complete date and time. You
may
have formatted the 'Bed Dte' field to show only the date, and the 'Bed
Time'
field to show only the time, but this changes only the *display* of the
data, the complete date and time are still stored. If you don't enter a
time
part, it defaults to midnight, and if you don't enter a date part, it
defaults to 30th December 1899. So when you concatenate the two fields
together, you don't end up with a valid Date/Time, you end up with
something
like "2/9/2005 00:00 30/12/1899 13:15" (that's dd/mm/yyyy format, swap
the
days and months if you're in the US).

One possible alternative would be to use DateSerial and TimeSerial and
add
(not concatenate) them like so ...

DateSerial(Year(Date), Month(Date), Day(Date)) _
+ TimeSerial(Hour(Time()), Minute(Time()), Second(Time()))

Insert your 'Bed Dte' field where I have 'Date' in the example, and your
'Bed Time' field where I have 'Time()' in the example.

--
Brendan Reynolds (MVP)


I keep getting and error when using current date & time
I used this before with other fiels and no problem, but now I want to
compare using current date and time. I trying using Date() and Time()
but
I
get an error. What am I doing wrong? Here is the code:

=Diff2Dates("hns",CDate([Bed Dte] & " " & [Bed Time]),CDate([DATE()] &
" "
&
[TIME()]))

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

Brendan Reynolds

I was trying to reproduce the originally reported problem, Doug.

--
Brendan Reynolds (MVP)

Douglas J. Steele said:
Thanks for stepping in, Brendan. However, why not Now() instead of
cdate(date() & " " & time())?

And you're absolutely right that [Bed Dte] + [Bed Time] is preferable to
CDate([Bed Dte] & " " & [Bed Time]).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Brendan Reynolds said:
I can't reproduce that. This works for me in the Immediate window ...

? diff2dates("hns", cdate((date()-1) & " " & time()),cdate(date() & " " &
time()))
24 hours

--
Brendan Reynolds (MVP)


bladelock said:
No, the "Bed Dte" and "Bed Time" work fine, the error message is for
Date()
and Time(). The function don't understand the Date() and Time()

:

An Access Date/Time field always stores a complete date and time. You
may
have formatted the 'Bed Dte' field to show only the date, and the 'Bed
Time'
field to show only the time, but this changes only the *display* of the
data, the complete date and time are still stored. If you don't enter a
time
part, it defaults to midnight, and if you don't enter a date part, it
defaults to 30th December 1899. So when you concatenate the two fields
together, you don't end up with a valid Date/Time, you end up with
something
like "2/9/2005 00:00 30/12/1899 13:15" (that's dd/mm/yyyy format, swap
the
days and months if you're in the US).

One possible alternative would be to use DateSerial and TimeSerial and
add
(not concatenate) them like so ...

DateSerial(Year(Date), Month(Date), Day(Date)) _
+ TimeSerial(Hour(Time()), Minute(Time()), Second(Time()))

Insert your 'Bed Dte' field where I have 'Date' in the example, and
your
'Bed Time' field where I have 'Time()' in the example.

--
Brendan Reynolds (MVP)


I keep getting and error when using current date & time
I used this before with other fiels and no problem, but now I want to
compare using current date and time. I trying using Date() and Time()
but
I
get an error. What am I doing wrong? Here is the code:

=Diff2Dates("hns",CDate([Bed Dte] & " " & [Bed Time]),CDate([DATE()]
& " "
&
[TIME()]))

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

Brendan Reynolds

http://www.pjde.demon.co.uk/sounds/doh_m.wav

--
Brendan Reynolds (MVP)

Jeff Conrad said:
Just to add one last comment.

Looks like the OP originally had square brackets
around the Date and Time so Access thought
they were table fields.

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

Douglas J. Steele said:
Thanks for stepping in, Brendan. However, why not Now() instead of
cdate(date() & " " & time())?

And you're absolutely right that [Bed Dte] + [Bed Time] is preferable to
CDate([Bed Dte] & " " & [Bed Time]).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Brendan Reynolds said:
I can't reproduce that. This works for me in the Immediate window ...

? diff2dates("hns", cdate((date()-1) & " " & time()),cdate(date() & " "
&
time()))
24 hours

--
Brendan Reynolds (MVP)


No, the "Bed Dte" and "Bed Time" work fine, the error message is for
Date()
and Time(). The function don't understand the Date() and Time()

:

An Access Date/Time field always stores a complete date and time. You
may
have formatted the 'Bed Dte' field to show only the date, and the
'Bed
Time'
field to show only the time, but this changes only the *display* of
the
data, the complete date and time are still stored. If you don't enter
a
time
part, it defaults to midnight, and if you don't enter a date part, it
defaults to 30th December 1899. So when you concatenate the two
fields
together, you don't end up with a valid Date/Time, you end up with
something
like "2/9/2005 00:00 30/12/1899 13:15" (that's dd/mm/yyyy format,
swap
the
days and months if you're in the US).

One possible alternative would be to use DateSerial and TimeSerial
and
add
(not concatenate) them like so ...

DateSerial(Year(Date), Month(Date), Day(Date)) _
+ TimeSerial(Hour(Time()), Minute(Time()), Second(Time()))

Insert your 'Bed Dte' field where I have 'Date' in the example, and
your
'Bed Time' field where I have 'Time()' in the example.

--
Brendan Reynolds (MVP)


I keep getting and error when using current date & time
I used this before with other fiels and no problem, but now I want
to
compare using current date and time. I trying using Date() and
Time()
but
I
get an error. What am I doing wrong? Here is the code:

=Diff2Dates("hns",CDate([Bed Dte] & " " & [Bed
Time]),CDate([DATE()] &
" "
&
[TIME()]))

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Top