Setting field formats for linked tables

[accessaspire219]

Hi,
I have a linked xls file which contains bills of lading along with other details. Bill of lading could be either numeric or alpha-numeric (starting with an alphabet). In the current setting my database ignores (blanks) out all the alpha-numeric bills of ladings and retains only the numeric ones. I guess this would have to do with the way the excel file is formatted, I checked the formatting on the bill of lading column in excel - it is formatted as text.
Does anyone know how could the access database be set up so that it does not blank out the alpha-numeric bills of lading? (I need a count of the bills of lading)
Thanks!

 

[DCrake]

This could be because the first bill of lading code is numeric so access thinks they are all numeric. to prove this have an alpha-numeric one in the first record and see if that is the issue.

You could insert a dummy bill of lading in the first row to determine the col types and have your queries disregard this in its totals, etc.

David

 

[Togwda]

Had this problem a few times! It sounds exactly as DCrake says. Just put an apostrophe before the value in the first cell of the offending column in the spreadsheet. (e.g. '1234 instead of 1234). Access should then recognise that and all successive cells in the column as alphanumeric.

 

[accessaspire219]

Yes, DCrake is right, I am sure that is the issue, but the spreadsheet is linked and is dynamic(updated on every run) so I will need a more permanent fix probably on access side?