Search code examples
ms-accessvbadcount

#ERROR on =IIf(DCount [...] with Alphanumeric field

I am using a DCOUNT function to look at a table in access and see if a record exists - if it does then return a yes value.

=IIf(DCount("*","COMMENTS","[PROJECTID] = " & [PROJECTID])>0,"YES","")

This works great if my ProjectID is all numeric values - I only get #Error on the alphanumeric values.

I know that I have to make the ProjectID criteria a string but am having trouble placing the quotes.

Solution

  • First, break out the DCount piece and work on that by itself.

    It sounds like your COMMENTS.PROJECTID field is text datatype, so add quotes before and after the value you concatenate into the third DCount argument (Criteria):

    DCount("*", "COMMENTS", "[PROJECTID] = '" & [PROJECTID] & "'")

    After you have that piece working correctly, add it into your IIf() function.