Access if-then compile problem

[dunerat]

Greetings all.

i am trying to write an expression in a query to return a library fine calculated from the due and return dates of a book. i have the expression below, which was working fine when i had [DueDate] instead of [CalcDate], but was also only showing a fine on one record.

Fine Due: 1+(([ReturnDate]-[CalcDate])*(0.01*[DollarValue]))

Any records which did not have a value in [ReturnDate] would also not have a fine shown. So i changed the second field to [CalcDate] and added the expression below:

CalcDate: IIf([ReturnDate] Is Null,Date(),IIf ([ReturnDate] Is Not Null,[ReturnDate]))

But Access gave me a compile error when i try to run the query. It stopped doing than and now posts a dialogue box asking for a value of [CalcDate] when i try to run or view the query. But no matter what i enter, it tells me that the expression is too complex to be evaluated or is typed incorrectly.

Please help, i need this quickly.

--dunerat

[Darkside12]

Have you tried breaking down the queries into two seperate queries. The first one should calculate the field CalcDate and the second should reference the first query instead of the table?

Darkside

[ADezii]

Greetings all.

i am trying to write an expression in a query to return a library fine calculated from the due and return dates of a book. i have the expression below, which was working fine when i had [DueDate] instead of [CalcDate], but was also only showing a fine on one record.

Fine Due: 1+(([ReturnDate]-[CalcDate])*(0.01*[DollarValue]))

Any records which did not have a value in [ReturnDate] would also not have a fine shown. So i changed the second field to [CalcDate] and added the expression below:

CalcDate: IIf([ReturnDate] Is Null,Date(),IIf ([ReturnDate] Is Not Null,[ReturnDate]))

But Access gave me a compile error when i try to run the query. It stopped doing than and now posts a dialogue box asking for a value of [CalcDate] when i try to run or view the query. But no matter what i enter, it tells me that the expression is too complex to be evaluated or is typed incorrectly.

Please help, i need this quickly.

--dunerat

Try the following:

Fine Due: 1 + (([ReturnDate] - IIf([ReturnDate] Is Null, Date, [ReturnDate])) * (0.01 * [DollarValue]))

[dunerat]

Neither worked. In the first case, a dialogue box opens asking for the value of [CalcDate], and in the second case it still only returns the fine for the books which have a [ReturnDate] and skips any nukk entries. Any other suggestions? Thank you.

--dunerat

EDIT: Ok, i got it to return the right fine, finally, buy adding a field in the query named [DateReturned] with the expression "DateReturn ed: IIf(([ReturnDate] Is Null),Date(),[ReturnDate])" and then using the expression "Fine Due: 1+(([DateReturned]-[DueDate])*(0.01*[DollarValue]))" for [Fine Due]. However, a dialogue box still opens when i run the query asking for the value of [DateReturned]. If i just click ok then it returns the correct results. But how do i make it not open the dialogue box in the first place?

--dunerat