I have an access database system that is being updated by creating a recordset, then opening it using an excel file as the source of the import. Everything is working great except for one field in the import. I have a field in the excel sheet, say "HIERARCHY". This field can contain data in three formats: 999/999/999999, 999/999, 999
In the excel sheet the format for the cells is general for each record in this field. The first two formats, 999/999/999999 and 999/999 are seen as strings. I assume this because under general formatting it is left justified in the cell. The last format, 999, is apparently being recognized as a number format since it is aligned on the right side of the field.
With that in mind, when my code reads through the import .xls file, when a record contains data in the first two formats for the HIERARCHY field it reads into the system perfectly. The problem is when it tries to read a record that has the HIERARCHY field in the third firmat, 999. The line, x = rs.fields("HIERARCHY") or x = rs.fields("HIERARCHY") .value returns NULL instead of the expected 999.
I can't figure out why it's being picky about how the field is formatted, but that seems to the the only possible answer as to why it's doing this. Does anyone have any insight on this problem? Anything will be appreciated. For now, I have a check in place that simply assigns the 999 value because that particular value can only ever be one value. This is fine for now, however, the future could warrant more than one value in this situation which will be a pain to code around.