I haven’t posted in this forum for a while now but I also haven’t run into a problem like this for a while…It has been driving me nuts!!
I am importing an excel spreadsheet into a table in my access database and everything is working fine except for one field giving me blanks when the data contains both numbers and letters.
The field is called “Number” in my table as that’s what the column name is in excel. The column is formatted as standard text and so is the “number” field in my table that I’m importing into. If it helps, I am just using the command:
The problem occurs when the Number field indicates a phone number however there are times when this may also contain letters and this is when the field results in being importing as a null. For example, the number field may contain “01471234567” which imports fine however it may also contain “01471234567MNET”….this is when it doesn’t import at all and the field is blank in the corresponding table. All the other fields import fine in that row.
Does anyone have any ideas why this may be or how to get around it? Any help would be greatly appreciated as it is starting to drive me crazy after many hours of trying different things.
The "number" field in your destination table in Access must be set as text. If it is set as a number you will get exactly the symptoms you describe. You say this is already the case but double/treble check.
Try checking field size.
Check there are no constraints set up on the field.
Also try transferring manually (right-click, import) which should give you an error message if there are problems. The error messages aren't terribly helpful but they'll at least confirm what you know to be happening !!
If none of this works, sleep on it for 24hrs and look with fresh eyes.