Query that splits digits into 2 seperate fields

fllopez65

Registered User.
Local time
Today, 01:05
Joined
Sep 22, 2011
Messages
43
Hello Everyone
I use access 2007 and need to run query that extracts data from a field which has 10 digits(numbers) in it but the first digits(numbers) mean something different from the next 2 digits(numbers). Does anyone how to run a query that would extract those 4 digits(numbers) from the that field and put them into 2 seperate fields which for now I will call field "A" and field "B"?

Tks for any tips.

fllopez65
 
See what you can do with the Left() and Mid() functions.
 
VbaInet
Not sure what you mean by Left and Mid functions, could you expand plse?

fllopez65
 
Tks to both Alansidman and Plog, I'll try your suggestions.

fllopez65
 
Alansidman and Plog
I keep getting an error message on my second expression of my query(see attached). Note the original field is 19digits long, my left expression is supposed to pull the first 2 digits and my mid expression is supposed to start at the 3rd digit and pull it and the 4th digit. Any suggestions.
 

Attachments

  • extracting digits to 2 seperate fields..PNG
    extracting digits to 2 seperate fields..PNG
    66.5 KB · Views: 172
Difficult to say what is happening. Suggest you post a copy of your data base -- sanitized for confidential information. Formula look correct. Got to be something else.
 
Will try like you say have to sanitize first.
fllopez65
 
The expression Left(...) is not a field in your table. Remove Property from the Table row.
 
There are certain words that Access looks at and treats specially (Date, Int, Mid) and shouldn't be used as field, table or query names. These are called reserved words and 'Property' is one of them. You need to rename your table.

I suggest you rename your table either 'Properties' or add another word that helps describe the data (i.e. 'Rental_Property', 'Owned_Property', etc.). Here's a list of Access reserved words--as long as you name it something not on this list your query should work:

http://support.microsoft.com/kb/286335
 
Alansidman/VbaInet
Have attached a very sanitized version of the database,take a look and plse let me know what you think maybe causing the error message encountered. Don't you need tell access what table to that the expression needs to be linked to ie:table?
 

Attachments

Plog
Tried renaming the table to "properties", result was same syntax error message.Thanks anyway. Still trying.

fllopez65
 
FYI...Tried renaming the table to "Heritage", regardless what the table is called still leads to the same syntax error message.
 
Hi -

Here are some examples returning text or digits from a string, depending if your number is actually digits or text:

*********************
'Convert number to text
x = 234568
? cstr(left(x,2))
23
? cstr(mid(x, 3,2))
45
'Extracting part of number
x = 12345
? left(x, 2)
12
? mid(x,3,2)
34
'Extracting part of text string
y = "3456789"
? left(y, 2)
34
? mid(y, 3,2)
56
*********************
HTH - Bob
 
I think you missed what I said in my previous post.
The expression Left(...) is not a field in your table. Remove Property from the Table row.
That is, under the Left(...) expression, remove (or clear) the table name from the Table row.
 

Users who are viewing this thread

Back
Top Bottom