Help with the DCount function

[accessrookie2]

This is driving me crazy, this works the way I want it too:
Nz(DCount([zColumnNumber], "ExcelTable", "F3 = '" & zNumber & "'"), 0)

I want it to count the times that records under column F3 are equal to zNumber which despite the name is actually a string. Now here is my issue. Sometimes the column name won't be F3, but rather F2 or F4 so I need to use a variable called zColumnNumber. I tried this code:

Nz(DCount([zColumnNumber], "ExcelTable", [zColumnNumber] = "'" & zNumber & "'"),0)

But everytime that comes up as 0 even if it should come up as 1. zColumnNumber's value is currently set to "F3" so I don't understand the difference in the code.

If I try something like:

Nz(DCount([zColumnNumber], "ExcelTable", "[zColumnNumber] = '" & zNumber & "'"),0)

I then get the error: "You canceled the previous operation"

Please let me know what I can do to fix this problem.

Thanks!

 

[boblarson]

1. A DCount doesn't need the NZ function.

2. Second, if you are using a field instead of direct value, it has to be OUTSIDE of the quotes.

DCount([zColumnNumber], "ExcelTable", [zColumnNumber] & " = '" & zNumber & "'"),

 

[accessrookie2]

Thank you! I tried many possibilities but never added an "&" to the left side of the equation.

It works now, thanks again (Also for the tip on the Nz)!