Retrive numeric data in text field

[aanders]

Background
I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.

Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?

 

[pr2-eugin]

If there is always going to be a space character in the Column, you can use the Mid function, to retrieve the Word after the number. To get the Number you can use Val.

 

[aanders]

Thanks pr2-eugin.

 

[pr2-eugin]

Glad to help, The following example should give you an idea what I meant.

? [URL="http://www.techonthenet.com/excel/formulas/val.php"]Val[/URL]("20 Eggs")
 20 
? [URL="http://www.techonthenet.com/access/functions/string/mid.php"]Mid[/URL]("20 Eggs", [URL="http://www.techonthenet.com/access/functions/string/instr.php"]InStr[/URL]("20 Eggs", " ")+1)
Eggs