Problem with If statement 2

[brendalanigan]

Hello,

I am using Crystal Reports 9 to report on an Access database.

I have been asked to produce a report which will display the number of books that were on loan on a given date (I am reporting on a library system)

The formula I had devised to do it was as follows:

If {QRY_PastLoans.PastLoans_IssueDate} <= {?Loandate} AND
{QRY_PastLoans.PastLoans_ReturnDate} > {?Loandate}) OR
IsNull ({QRY_PastLoans.PastLoans_ReturnDate})
Then True Else False

I had created a parameter called Loandate which the user enters to supply the date they want the information for.

The first section of the If statement displays the loan if it was issued on or before the date specified by the user
and returned after the date specified. The section after OR should display books that are still on loan (as the return date is empty they are still on loan) . The report appears to work for dates in the past but when I run it for todays date it doesnt display any current loans at all - there should be several thousand.

I suspect that this is a result of the and statement being processed before the or statement and I have tried bracketing the statements and got either the same result or an error that the keyword 'then' is missing.

Apologies for the length of this post, and thanks in advance for any assistance you can give me.

 

[synapsevampire]

Parentheticals:

If {QRY_PastLoans.PastLoans_IssueDate} <= {?Loandate}
AND
(
{QRY_PastLoans.PastLoans_ReturnDate} > {?Loandate}
OR
IsNull ({QRY_PastLoans.PastLoans_ReturnDate}) Then
True
Else
False

-k

 

[lbass]

Except null checks should be the first reference to a field, so try:

If {QRY_PastLoans.PastLoans_IssueDate} <= {?Loandate}
AND
(
IsNull ({QRY_PastLoans.PastLoans_ReturnDate}) or
{QRY_PastLoans.PastLoans_ReturnDate} > {?Loandate}
)
Then
True
Else
False

-LB

 

[synapsevampire]

Ahhhh, good catch LB, thx!

-k

 

[brendalanigan]

Thanks very much synapsevampire and lbass, that has worked!