Bug in Calculated Field?

[Guest]

I'm noticing strange behavior with a calculated field in Access 2003. I'm
making a select query that summarizes a table. The table has a column for
each of the two formats of ID numbers that our system has used - one from the
old system and one from the new system. So for each record, only one of the
two fields with have data. The calculated field just checks to see if the
first field is null and then uses returns the one that isn't.

The problem I'm having is that sometimes the returned value on one of the
fields excludes the first digit in the ID number. The format for this field
is "A99999", with the nines being numbers and the "A" being a letter.

The strangest thing is that the error seems to be dependant upon the name of
the calculated field. When it didn't work at first, I started trying
different variations on the formula. It finally worked when I changed the
name of the field to "test". Then I changed the name back to "RefNum" and it
stopped working. I changed it back to "test" and it worked again. But then,
I closed the query, reopened it and it had stopped working again.

 

[Wayne Morgan]

If I had to guess, I would guess that there is something else called RefNum
somewhere in the database.

 

[Guest]

There was at one time...but that was split in RefNum_C and RefNum_D. But the
error still happens when i use the name "test" for the calculated field.

Each field has a different input mask, one is all numbers and the one with
the problem is numbers and letters.

 

[Wayne Morgan]

Verify that the "something else with the same name" isn't the control itself
(i.e. the control and the field it is bound to have the same name). That
said, it may be something corrupted. 1) Try deleting and recreating the text
box. Don't cut/paste, delete and create a new one. 2) Try creating a new,
blank database and import the objects from this one and see if the problem
goes away. For more information on corruption, follow this link.

http://members.iinet.net.au/~allenbrowne/ser-47.html