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.

See within.

Mojo wrote:
[snip]
First, I suspect you have transposed the dates in Datediff. The result
will make more sense when the first date is the earlier of the two.
Second, if Datediff is returning something that looks like a date try
wrapping it with CLng(Datediff...).

Using a dummy date isn't the worst thing you could do, but you mention
having "blanks" as well? Blank is an ambiguous term in databases, better
to tell whether this is null or a zero-length string, as they have
different consequences and approaches for analysis. Regardless, it would
make sense to stick to /one/ of these options, then you can simply
exclude the dummy value/null/ZLS in the WHERE clause.

Sure -- just calculate Datediff against the other field.

Hope it helps.

Mojo wrote:

In all of your table references you have the AS keyword (Games AS g,
RENTALLINES AS r1...). But there is no AS for Rentals and the alias R.
Would that make a difference?