Average on date only not record? (1 Viewer)

[MajP]

No problem. Hope that is answering the right question for you.

 

[jillnoble]

Hiya, me again. It is, perfectly within that DB, however, when I've now tried to bring it into my DB, the 'qryDailyPay_Not2018' is fine but as soon as I move onto 'qryWeeklyPay_Not2018_5orMore' I get -"Undefined function 'format' in expression" as an error message?

 

[MajP]

Check that you do not have a broken reference.
When the vb code looks for a function it goes through the libraries in alphabetical order. "Format" is in the VBA library which is going to be alphabetically near the end. If it hits a broken reference it stops and never gets to the VBA library or any other library below where it hits the broken reference. It never gets to the library and says "undefined function" because it cannot find it..

 

[jillnoble]

Thanks again, I think I've now sorted this but am now getting "Data type mismatch in expression" I've tried lots of things looking at different problem solvers but can't seem to sort it and I really don't think I'll have any hair left after this! Again Its happening on the weekly query. Could this be to do with my relationships or forms used to fill in the data that then goes into the tables?

 

[MajP]

A datatype mismatch is normally trying to return a string from a numeric field or vice versa.

Example: if Somefield is text then
Where SomeField = 1
is a datatype mistmatch
Where somefield = "1"
is needed.