DAvg (1 Viewer)

[border20]

Can the method DAvg be used to calculage the average of a query instead of a table ??

I have problems with the following line of code wich should be very simple....

mo1 = DAvg("[Field]", "Query name", "[criteria] = '2' ")

sais incompatible data type in criteria...

can anyone tell me why??

thx
frank

 

[Jack Cowley]

If "Criteria" is a number, not text, then try this:

mo1 = DAvg("[Field]", "Query name", "[criteria] = 2")

hth,
Jack

 

[border20]

that worked... but i have another question...

in the following:
mo1 = DAvg("[Field]", "Query name", "[criteria] = 2")

What do i have to do to replace the numerical value by a variable ?

tried to things already ...

tried:
dim variable as integer
variable = "1"
mo1 = DAvg("[Field]", "Query name", "[criteria] = variable")
and there is an error

also tried:
dim variable as integer
variable = "1"
variable2 = CStr(variable)
mo1 = DAvg("[Field]", "Query name", "[criteria] = variable2")
also did not work saying there is an incompatible type...

Anyone know what i should do ??

 

[border20]

Sorry second try was actualy

dim variable as integer
variable = "1"
variable2 = CStr(variable)
mo1 = DAvg("[Field]", "Query name", "[criteria] = '" & variable &" ' ")

 

[border20]

again...

dim variable as integer
variable = "1"
variable2 = CStr(variable)
mo1 = DAvg("[Field]", "Query name", "[criteria] = ' " & variable2 & " ' ")

sorry

 

[casey]

Try...

mo1 = DAvg("[Field]", "Query name", "[criteria] = " & variable2)

You were close, but you didn't need the ' in the criteria. You may also need to add Str(variable2) to convert it to a string. You may have to try this a few times b/4 you get it right.

 

[border20]

jst tried

i just tried that,,, still did not work

 

[Jack Cowley]

dim variable as integer
variable = 1
mo1 = DAvg("[Field]", "Query name", "[criteria] = " & variable)

Try that. If it does not work then post your code so we can see what you are doing....

Jack

 

[border20]

Got it !

GOT IT WORKING
thx for your help

 

[casey]

Does [criteria] refer to an actual field in your query? If not, you need to replace it w/ the actual name of that field you're searching on.
ex. (... ,"[InvoiceID] =" & variable)

The reason I'm confused is because [criteria] is also used by the function: (DAvg(expr, domain[, criteria])) and is not meant to be included literally. If [criteria] is the name that you've used for the field, then you're OK.
I'm just making sure because people reqularly make this mistake.

 

[Jack Cowley]

Congratulations! Glad you got it sorted...

Jack

 

[border20]

another monor problem

the following worked
mo1 = DAvg("[Field]", "Query name", "[criteria] = " & variable)

but when i try to add another condition with "AND" it causes an error

ex:
mo1 = DAvg("[Field]", "Query name", "[criteria] = " & variable and "[criteria field 2] = " &variable2 )

where variable 2 is a string that gets its value from a combo box


can anyone spot the problem ?? thx

 

[Jack Cowley]

Try this:

mo1 = DAvg("[Field]", "Query name", "[criteria] = " & variable & " And [criteria field 2] = '" & variable2 & "'" )

The quotes look like this: ' " & variable2 & " ' " . I have added spaces so you can see more clearly. Do NOT add the spaces...

hth,
Jack

 

[border20]

great

That seems to work thx. now i just have to figure out a few more bugs ..; there is for some fields where there is a err #6 where the capacity is exceded...