Does this code look good to you guy

[Kumba1]

Does this code look good to you guys? I'm trying to make a modular function to do monthly averages...Basically, I wanna call the function, passing a specified date, and the field for it to calculate from a predetermined table...

Function MTDAverage(mtdDate As Date, mtdField As String) As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
z = "#01/01/" & Year(mtdDate) & ""
y = DCount("[BuildCode]", "tblBuilds", "" & mtdField & " > " & z & "&quot
x = y / Month(mtdDate)
MTDAverage = x
End Function

 

[vb6novice]

Kumba1,

Your code has a few rough spots. You will not be able to store "#01/01" to z since you have declared it as an integer. It needs to be string. Also, when you concatenate Year(mtdDate) to it, you will not need the extra quotes at the end until later when you use it in DCount function. The criteria for the DCount function must specify a field name in the domain (tblBuilds) that you are trying to match with z. You are using the name of a variable that is the same as an input argument (mtdField). That won't work. Either you need to change the name of the input argument or specify an actual field name to match.

Your DCount should look more like this:

y = DCount("[BuildCode]", "tblBuilds", "[FieldToMatch]>'" & z & "'&quot

Unless the string in the field you want to match has the # symbol infront of it, your count will be zero. Either make sure all of the values in the field you are trying to match have the # character at the front, or eliminate it from the z = "#01/01/" & Year(mtdDate) & ""

Try using z = "01/01/" & Year(mtdDate)

that way, if the string values in the field you want to match have dates as strings without # on the ends, DCount will find a match if one exists.

 

[Kumba1]

Thanks for the Input, I got it up and running, but I got it modified so that it accepts mtdField as the Field to use for the criteria in DCount... here's the final code that seems to work like a champ... It may not be as compact as what everyone with more know-how can do, but it seems to work pretty solid... ]

Function MTDAverage(mtdDate As Date, mtdField As String) As Integer
Dim w As Integer
Dim x As Integer
Dim y As Integer
Dim z As Date
w = Year(mtdDate)
z = "01/01/" & Year(mtdDate) & ""
y = DCount("[BuildCode]", "tblBuilds", "" & mtdField & " > " & z & "&quot
x = y / Month(mtdDate)
MTDAverage = x
End Function

 

[oharab]

How much more compact do you want? 9 lines is pretty good. The only way you could make it any more compact would be:

Function MTDAverage(mtdDate As Date, mtdField As String) As Integer

MTDAverage = (DCount("[BuildCode]", "tblBuilds", "" & mtdField & " > " & "01/01/" & Year(mtdDate) & "" & "&quot) / Month(mtdDate)

End Function

but it's much harder to read & it's debateable how much of an improvement it would make!

B ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------