Multi criteria davg

[mendesj1]

Me.Text11 = Nz(DAvg("[final whse-in diff]", "dbo_inventory", "[CAFETYPE]=" & Me.Text7 And "isnull(me.[DATE FIXED])=" & True And "isnull(me.DATE_IN)=" & True), 0)


i am getting a type mismatch error with this.

my question is:
1. is the syntax correct
2. Is my way of checking for a value to be null correct

or any other ideas what i am doing wrong

thanks so much
john

 

[namliam]

Besides the use of a default control name, text11
The probably over use of domain functions (if you use more than one in any sub or function)
The actual syntax should be:

Me.Text11 = Nz(DAvg("[final whse-in diff]" _ 
                  , "dbo_inventory" _ 
                  , "     [CAFETYPE]=" & Me.Text7 & _ 
                    " And isnull(me.[DATE FIXED]) " & _ 
                    " And isnull(me.DATE_IN)") _
              , 0)

Note: The spacing and such to make it more readable! Readable code = maintainable code

 

[mendesj1]

thanks for the help , i tried what you showed me and now come up with a run time error
2471: the expression you entered as a query parameter produced this error : 'me.[date fixed]'

 

[namliam]

Ah I overlooking the me part in there...
You cannot use Me. inside a domain function, you have to check that prior to executing it...

Something like so

If isnull(me.[DATE FIXED]) And isnull(me.DATE_IN) then
   davg
else
   me.txt = 0
endif

 

[namliam]

Also.....
" [CAFETYPE]=" & Me.Text7 & _
will work if cafetype is a number field
For text use:
" [CAFETYPE]=""" & Me.Text7 & """" & _
For date use:
" [CAFETYPE]=#" & Me.Text7 & "#" & _
Remember for dates that they must be in US format or use i.e. the format function to force it into us format.