DateDiff not giving me a number!!

  • Thread starter Thread starter Mojo
  • Start date Start date
M

Mojo

Hi All

I know I'm probably doing something stupid, but I'm really stuck on this
one.

Basically I need to create 2 'caclulating' fields in my Access SQL query
(along with the rest of the query fields), but I'm not getting anywhere.

My query is as follows:

SELECT g.GAMEID, g.GAMETITLE, rl.STOCKID, rl.RETURNDATE,
DateDiff("d",rl.RETURNDATE,r.RENTALDATE) AS DaysRent, (DaysRent *
g.RENTALPRICE) AS Charge
FROM ((GAMES AS g INNER JOIN PRODUCTTRACKING AS pt ON g.GAMEID = pt.GAMEID)
INNER JOIN RENTALLINES AS rl ON pt.STOCKID = rl.STOCKID) INNER JOIN RENTALS
r ON rl.RENTALNO = r.RENTALNO
WHERE (((rl.RENTALNO)=1));

As you can see I've tried to use the DateDiff function to calculate the
number of days rent between 2 dates, but all I get is a bizarre date rather
than an integer result. Have I typed it out correctly?

Confession time! The way my Db/App works I've had to use a dummy return
date value of 01-01-1904 for the items that have been rented out, as a blank
date means the games have just been brought in and 'another' date lets the
user know that they've been brought back in by the customer. Only way I
could think of to flag that they aren't new and they haven't been brought
back in by the customer, ie the customer currently has them, is to put a
specific dummy date in that I know they will never use. Why do I feel a
flame coming on :0)

Is there anyway I can get my query to calc the day diff as an integer for
return dates that are not blank and not 01-01-1904?

Another query, is it possible for my Charge 'calc' field to work out the
rental charge based on the 'DaysRent' result of another field??

Big asks these I know, but I'd appreciate any feedback anybody gives me.
 
Your DateDiff function looks correct. Using the calculated value in a
subsequent field can lead to errors, so it is often necessary to repeat
the calculation instead of using the alias (name) attached to the
calculation.

Also, DateDiff calculates the difference in dates both negative and
positive, so you should for your purposes have Rental date and then the
return date as your arguments.

Based on the remaining information you might try amending your
calculations to

IIF(r1.ReturnDate=#1904-1-1#,Null,DateDiff("d",r.RENTALDATE,rl.RETURNDATE))
as DaysRent

IIF(r1.ReturnDate=#1904-1-1#,Null,DateDiff("d",r.RENTALDATE,rl.RETURNDATE))
* g.RentalPrice as Charge

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Hi All

I know I'm probably doing something stupid, but I'm really stuck
on this one.

Basically I need to create 2 'caclulating' fields in my Access SQL
query (along with the rest of the query fields), but I'm not
getting anywhere.

My query is as follows:

SELECT g.GAMEID, g.GAMETITLE, rl.STOCKID, rl.RETURNDATE,
DateDiff("d",rl.RETURNDATE,r.RENTALDATE) AS DaysRent, (DaysRent *
g.RENTALPRICE) AS Charge
FROM ((GAMES AS g INNER JOIN PRODUCTTRACKING AS pt ON g.GAMEID =
pt.GAMEID) INNER JOIN RENTALLINES AS rl ON pt.STOCKID =
rl.STOCKID) INNER JOIN RENTALS r ON rl.RENTALNO = r.RENTALNO
WHERE (((rl.RENTALNO)=1));

As you can see I've tried to use the DateDiff function to
calculate the number of days rent between 2 dates, but all I get
is a bizarre date rather than an integer result. Have I typed it
out correctly?

Confession time! The way my Db/App works I've had to use a dummy
return date value of 01-01-1904 for the items that have been
rented out, as a blank date means the games have just been brought
in and 'another' date lets the user know that they've been brought
back in by the customer. Only way I could think of to flag that
they aren't new and they haven't been brought back in by the
customer, ie the customer currently has them, is to put a specific
dummy date in that I know they will never use. Why do I feel a
flame coming on :0)

Is there anyway I can get my query to calc the day diff as an
integer for return dates that are not blank and not 01-01-1904?

Another query, is it possible for my Charge 'calc' field to work
out the rental charge based on the 'DaysRent' result of another
field??

Big asks these I know, but I'd appreciate any feedback anybody
gives me.
Click to expand...
When you say bizarre date, is it somewhere in december 1899?
If it is, then your function is correct, but you are applying a date
format to the integer. Access stores a date as the number of days
since or to December 31 1899.

If one of the two dates is blank, datediff() returns an empty string,
To eliminate 01-01-1904, use an iif() statement to prevent your
calculation from happening..
iif(RETURNDATE = #01-01-1904#,"", DateDiff
("d",rl.RETURNDATE,r.RENTALDATE) AS DaysRent

That's 1 line, beware of the wrapping

as to the Charge calc, I see that was answered elsewhere.
 
Mojo,

Where are you looking at the data returned by this query. Query datasheet,
or form, or report?

EIther way, if the Format property of the Charge is set to a date format,
this could result in the calculated data being displayed as a "bizarre
date". It could be that the query is working correctly, and it is a format
related problem.

--
Steve Schapel, Microsoft Access MVP


Mojo said:
Hi All

I know I'm probably doing something stupid, but I'm really stuck on this
one.

Basically I need to create 2 'caclulating' fields in my Access SQL query
(along with the rest of the query fields), but I'm not getting anywhere.

My query is as follows:

SELECT g.GAMEID, g.GAMETITLE, rl.STOCKID, rl.RETURNDATE,
DateDiff("d",rl.RETURNDATE,r.RENTALDATE) AS DaysRent, (DaysRent *
g.RENTALPRICE) AS Charge
FROM ((GAMES AS g INNER JOIN PRODUCTTRACKING AS pt ON g.GAMEID =
pt.GAMEID)
INNER JOIN RENTALLINES AS rl ON pt.STOCKID = rl.STOCKID) INNER JOIN
RENTALS
r ON rl.RENTALNO = r.RENTALNO
WHERE (((rl.RENTALNO)=1));

As you can see I've tried to use the DateDiff function to calculate the
number of days rent between 2 dates, but all I get is a bizarre date
rather
than an integer result. Have I typed it out correctly?
Click to expand...



__________ Information from ESET Smart Security, version of virus signature database 4175 (20090621) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top