Dcount with 3 criteria

[kcmark]

I have a dcount function that I am running in a query. The dcount has 3 criteria. I can't seem to get the syntax correct. Could someone take a look at this and set me straight.

DCount("[Trans_Key]","[Payment]","[Trans_SR]= " & [Rep_Name_1] And [Trans_Type] = 'Com' And [Trans_Key] = [Rev_Key]") AS Previous_Pmts

When I run the query it asks me to key in the Trans_Type.

Thanks!

 

[PHV]

Try this:
DCount("Trans_Key","Payment","Trans_SR='" & Rep_Name_1 & "' And Trans_Type='Com' And Trans_Key=Rev_Key") AS Previous_Pmts
I assumed the following:
1) Rep_Name_1 is a non numeric field in the query's list of tables.
2) Rev_Key is a field of the Payment table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kcmark]

Thanks - Rep_Name_1 is actually a numeric field. How would the syntax change accordingly?

 

[PHV]

By removing the surrounding single quotes, provided that Trans_SR is a numeric field in Payment:
DCount("Trans_Key","Payment","Trans_SR=" & Rep_Name_1 & " And Trans_Type='Com' And Trans_Key=Rev_Key") AS Previous_Pmts

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kcmark]

Works like a charm - Thanks for your help on this