Sorting fields (from Excel to Access)

[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

 

[Arvin Meyer]

Just import the cell, don't use an input mask. Build a new field (MyField)
in your table (MyTable) and update it with (air code):

Update MyTable Set MyField = "RFI_" & Format([ImportedField], "000")
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access