By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Access if-then compile problem

P: 6
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.

Dec 16 '08 #1
Share this Question
Share on Google+
3 Replies

P: 13
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?

Dec 16 '08 #2

Expert 5K+
P: 8,638
Try the following:
Expand|Select|Wrap|Line Numbers
  1. Fine Due: 1 + (([ReturnDate] - IIf([ReturnDate] Is Null, Date, [ReturnDate])) * (0.01 * [DollarValue]))
Dec 16 '08 #3

P: 6
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.


EDIT: Ok, i got it to return the right fine, finally, buy adding a field in the query named [DateReturned] with the expression "DateReturned: 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?

Dec 17 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.