weird error on Dlookup

[crabback]

Hi all!
I have a report built on a union query thats pulls data from 2 tables - Faculative and SpecialAcceptances. Each table has requestDate and answerDate fields, and my query works out the delay in days between the two in a calculated field called 'Delay'. The query also has a calculated field that pulls in the name of the table the record is coming from. The only other 2 fields are CedantName and CedantID.
So the rpt has 3 columns: first one for the CedantName, second for the Faculative response, and third for the SpecialAcceptance response.

The Report footer has 2 txboxes one to work out the average delay of Faculative responses and the other works out the average delay of SpecialAcceptances. I'm using a dlookup on both of these. It works for Faculative. but I get error! in the txbox in report view for SpecialAcceptance??? I've checked my query and swapped the order of the union around. I've checked and double checked my spelling. Deleted and recreated the txbox. even tried removing faculative average to see if that was throwing it. I even tried building a new report thats only txbox was set to this dlookup - and I still got the error; that is until i changed 'SpecialAcceptances' to 'Faculative'.. now i'm lost. Can anyone help???
here is my dlookup syntax...

=DLookUp("AVG([Delay])","qrySAFAvgDelay","tbl='SpecialAcceptances'")

Crabback

 

[PaulBricker]

Well, AVG([Delay]) looks like an expression, not a field name in a query which is what DLookup() is looking for. I would expect the field name in the query to be something like

AverageOfDelay:AVG([Delay])

That's my first take

Paul

 

[crabback]

Thanks Paul. You can use an expression in the Dlookup. Delay is a calculated field in the query. The problem was it was working on the textbox that was looking for tbl='Faculative' - which led me to believe the problem was with my data and not my report... ish! What happened was if there was a null value in Delay it threw back an error. I fixed it with...
=DLookUp("AVG([Delay])","qrySAFAvgDelay","tbl='SpecialAcceptances' And [Delay]<>''")
now everythings hunky dory in the universe again... for now.
Thanks tho.

Crabback

 

[PaulBricker]

I thought you could also, but when I tested it against a small table, I kept returning the value from the first record. Thought maybe I misunderstood how to use it. Probably was a typo in my expression. Sorry for the misinformation. Glad you got it.


Paul

 

[dhookom]

crabback,
Null is not the same as ''. Nulls in Delay should have no effect on the calculation. If the Delay field is text, then it might be storing a '' (zero-length-string) which would cause an issue. Also, if Delay is text and not numeric, I would convert it to numeric inside the Avg() function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[crabback]

Thanks Duane, that seems really simple now that its explained, obviously I tried it and it worked like a charm.
Cheers!

Crabback