Problem entering External Data from Excel into Access field using Input Mask

[Campbell]

Hi,

I'm new to this. I'm trying to enter data (it's actually Latitude and Longitude co-ordinates) from an existing Excel source into an Access database which has input masks of 00\°00\'00.00\"L;0;0 (Latitude) and 000\°00\'00.00\"L;0;0 (Longitude) in the respective fields. However I cannot get the information to import or display correctly. I did an "export data" of the respective table (hence fields) to Excel to try and get the correct entry format. An example of the Lat exported was 24°49'41.81"N and Long was 067°01'44.02"E (but with a very small ' in front but only visible in the data entry line in Excel, not in the actual spreadsheet table???)

However when I try to enter the data (even using the exact same little degree symbol, apostrophe, and quotation marks) it does not enter the access fields correctly. On closer scrutiny of the exported Excel format I note a small ' at the very beginning of the 24°49'41.81"N or 067°01'44.02"E string. But as I said previously only visible in the data entry line next to the formula button. Not on the spreadsheet cell.

However even when I "Paste Special" "values only" my new co-ordinates into the same entry location as one exported, it will still not import, or display correctly. If I go into the Access database directly there is a form where if I need to enter the new co-ordinates (using lat example above) I only have to enter 24 49 41 81 N (spaces between) and it will show correctly as 24°49'41.81"N

I'm getting desparate as I don't want to have to change all the details manually. Anyone know what my correct format from an Excel spreadsheet should be?

Apologies for lengthy story! Difficult to describe problem with degree symbols etc

 

[gemma-the-husky]

excel is a bit of a swine - it forces access to whatever format it thinks is correct

try saving the excel as a csv, then importing the csv with transfertext- you get much more control over the import, that way

 

[neileg]

Excel has no fixed datatypes. You can save anything anywhere. If you precede a cell entry with ' it forces Excel to treat the entry as text, regardless of what it looks like. In Access, you have a formatted number field, hence the missmatch. Neither is correct, of course and you couldn't do any valid calculations based on this data in any event.

When faced with this kind of issue, it is usually preferable to store values as decimal numbers, perhaps whole numbers of degrees, and then format the data for display.