String to Numeric

[JimP]

I have an "order details" table that has a text identifier for the "order
number" (e.g. "abc"). I would like to create a numeric identifier that
uniquely identifies each order (e.g. 1 to 10).

Is there a way to do this via a query?

I can think of a way to do this with code, opening the table as a recordset
and then incrementing a counter each time the text identifier changes - but
this seems inefficient.

 

[Alex Dybenko]

Hi,
you can add an autonumber field to order details table.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[JimP]

There any numerous "order detail" records per order. An autonumber field
would uniquely identify each order detail record. I need to give each group
of "order" records a unique identifier.

 

[Rick Brandt]

There any numerous "order detail" records per order. An autonumber
field would uniquely identify each order detail record. I need to
give each group of "order" records a unique identifier.

What's wrong with just using the text field?

 

[JimP]

This is a retrofit to an existing application that requires a join to a
numeric "order number" field (which I can't change).

 

[Stephen K. Young]

This is a retrofit to an existing application that requires a join to a
numeric "order number" field (which I can't change).

You can create your join, then go to SQL view and convert the join to
something like:
CLng(Table1.TextField) = Table2.NumericField.

Or, if you are hesitant to depend on correct numeric values, you can go the
other way in the join:
Table1.TextField = CStr(Table2.NumericField)

You may also want to add some Nz() conversions also, if you can have Nulls
in the join.

- Steve