VBA DCount() with variable field name

[drh]

Hello,

I have the below code which performs an count on a specified field 'A-S1' in the database. Is it possible to supply a variable (strFC) in place of the static field name? I've tried doing this but I can't get the quotes correct.

Any advice on this much appreciated!

Best

Drh

Public Function getNA(ByVal strSN, ByVal strMC, ByVal strT, ByVal strAC, ByVal strFC) As Integer

On Error GoTo ErrorHandler

getNA = DCount("[S_Number]", "TBL_MSA", _
                      "[S_Number] = '" & strSN & _
                      "' AND [M_Code] = '" & strMC & _
                      "' AND [T_Code] = '" & strTC & _
                      "' AND [A_S1] ='" & strAC & "'")
---

[pdebaets]

This should work:

getNA = DCount("[" & strFC & "]", "TBL_MSA", _
"[S_Number] = '" & strSN & _
"' AND [M_Code] = '" & strMC & _
"' AND [T_Code] = '" & strTC & _
"' AND [A_S1] ='" & strAC & "'")

[maximus]

Are the Field Type integers or text.

if it is Integer single quotes are not required.

"' AND [M_Code] = '" & strMC