Sorting fields (from Excel to Access)

  • Thread starter Thread starter John Hipskind
  • Start date Start date
J

John Hipskind

I am importing data from Excel forms into Access tables.

A number is entered in Excel, where the cell is formatted
to display the number entered--such as 32--as RFI_032.
(The actual value in the cell is still just 32.)

I then apply an input mask to the corresponding Access
table field--"RFI_"000--with the result that RFI_32 is
imported into the table. (This is my key field.)

The problem is that the value RFI_32 will sort ahead of,
for example, the value RFI_4 in Access (RFI_4 appears as
RFI_004 in Excel, and then as RFI_4 when imported into
Access). Yet RFI_4 is the fourth RFI, while RFI_32 is the
thirty-second.

I've tried all sorts of format and input masks
combinations, but I have not found a way to force
preceeding 0's in Access so that the numbers will sort
appropriately.

I would prefer not to have to ask the users to enter
preceeding 0's in Excel.

HELP!

Thanks.

John
 
Back
Top