Convert Text to Number (1 Viewer)

[ggj13557]

I have a table with the following fields:
Tool Text
Tool_ID Text
Description Text

The Tool_ID field holds a number if the tool provides a tool id. However, if the tool doesn't provide a tool id, the text "No tool ID given" is automatically placed into the field. Therefore, it needs to remain a text field for this reason and others that are too long to go into.
Anyway, I created a query that pulls only one specific tool which always provides a tool ID. Therefore, the results of my query will always have a number in the text field "Tool_ID".

Question: Is there a way to convert the text field to a number field within the query?

Thanks in advance!
~ Greg

 

[RV]

Therefore, it needs to remain a text field for this reason and others that are too long to go into.

So I'm not going to tell you whatever the reasons it's actually bad practice what you've done with your table definition

You could use the IIF function to convert "No tool ID given" to a number:

IIF(Tool_ID = 'No tool ID given',9999, Tool_ID)

Could be you have to replace the comma's with semicolons

RV

 

[ggj13557]

I could use the IIF statement like you suggested but it still won't change the type to a number. Within the query the new field will still be "text" or am I missing something?

 

[RV]

Look up CInt and CLng in Access Help.

RV

 

[ggj13557]

Also, the Tool_ID field contains tool ID's such as G004, AIS018, ...etc. However, the query that I created to pull only the information from one tool has tool ID's that are all numbers and I need the field to be treated as a number in that instance only.