Using DAvg with variables

Question

I am using Access 2007 to calculate an Average and Median value for a set of values. I have used the AVG in a query and DAvg in VBA and it works if I write the criteria. I want to replace the criteria with a string that I manipulate. I use a function called DMedian that works the same way.

The overall expression  of variable =DAvg([Field], [Table], Criteria) translates to variable=DAvg("[TimeToClose]", "WorkRequests", [Shop] ='Machine'").

If I define a string in vba to use  like Shp = "Machine" and try to run this I can get:

variable=DAvg("[TimeToClose]", "WorkRequests", [Shop] ='& Shp"')

I only get a Null value returned.

A few questions:

1. Can I use a variable in DAvg in this manner?
2. If I can, where I am going wrong on the Syntax?

Any help would be appreciated!

Answer 1

Your syntax is slightly off.  Try:

Shp = "Machine"

variable=DAvg("[TimeToClose]", "WorkRequests", "[Shop] ='" & Shp & "'")