Query that splits digits into 2 seperate fields (1 Viewer)

[fllopez65]

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

 

[vbaInet]

See what you can do with the Left() and Mid() functions.

 

[fllopez65]

VbaInet
Not sure what you mean by Left and Mid functions, could you expand plse?

fllopez65

 

[Alansidman]

A couple of tutorials to help you with this.

http://www.datapigtechnologies.com/flashfiles/datamanipulate.html

http://www.datapigtechnologies.com/flashfiles/leftright.html

Alan

 

[plog]

He means string manipulation functions:

http://lmgtfy.com/?q=access+mid+function

http://lmgtfy.com/?q=access+left+function

 

[fllopez65]

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

fllopez65

 

[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.

 

[Alansidman]

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.

 

[fllopez65]

Will try like you say have to sanitize first.
fllopez65

 

[vbaInet]

The expression Left(...) is not a field in your table. Remove Property from the Table row.

 

[plog]

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

 

[fllopez65]

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?

 

[fllopez65]

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

fllopez65

 

[fllopez65]

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

 

[raskew]

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

 

[vbaInet]

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.

 

[fllopez65]

Will try suggestions. Tks.
fllopez65

 

[Alansidman]

See attached using VBAINET suggestion.

Alan

 

[fllopez65]

Tks to Alansidman,vbaInet,plog. Got it to work.
fllopez65