# in field name

[lleitzke]

Hello All, I am new to Access having inherited a database I need to make a adjustments to.



I am having an issue where the database is using "#" in its field names in the tables IE "Social Security #" and it seems to be causing issues when I need to call said field in the code. I would just change the name of the field in the database but the database uses linked tables so I cannot Change them (as far as I know).

I know I could use [#] in a search to get the # signs instead of the "double" but I don't know how to make the adjustment in the code, so when I try to use a DLookup to not allow duplicate SSN's I get an error when I call this line

If Me.SocialSecurityNum = DLookup("[Social_Security_#]", "tblClientLog", StLinkCriteria) Then

is there a way to make a that field name work? or is there a way to change the field name even though the tables are linked?

or am I just completely off base with of the DLookup is supposed to work?

[June7]

Enclosing the fieldname in [] should force the # character to be accepted.

What is the backend db? Do you have control of it? Can change field names but then would have to change the field name every place it is used in frontend. I use Rick Fisher's Find & Replace add-in to do this - cost about $50. Another app that has good reviews is called V-Tools and it is free.

Another issue with the expression is DLookup returns Null if no match found. Can't compare a value to Null. Handle possible Null with Nz() function. http://allenbrowne.com/casu-12.html