Extracting data from string of text within Query

[Guest]

I have a list of numbers over 10,000
Example 1:
200403303000400
200503303000501
200603303000700
200703303000800
I need to run a query that will pull the first seven numbers and create a
new column with all my extracted data saved, but I also need run a second
query that will store the remaining codes within a new column
Example 2:
Column 1
2004033
2005033
2006033
2007033

Column 2
03000400
03000501
03000700
03000800

In the examples above the system would create two new columns from my main
data and store them as new columns based on a predefined data - where I asked
the logic to break them apart after a certain numeric, symbol, etc

 

[Douglas J. Steele]

To get the first 7 characters of a string, use Left([Field], 7)

To get the remainder of the string, use Mid([Field], 8)

Unless you have a real need, though, you don't have to add these as new
fields in the table: simply put those as computed fields in a query, and use
the query wherever you would otherwise have used the table.

 

[John Nurick]

Hi Derrick,

You'll need to create the new column(s) yourself Normally the data type
should be text (unless you're going to do arithmetic with the numbers.
You can then use an update query to populate them. You use VBA
expressions in the update query to extract the bits of text you need.
For example (and assuming the existing field is named XXX):

First 7 characters:
Left([XXX], 7)

Last 7:
Right([XXX], 7)

All characters starting with the one after the first Q:
Mid([XXX], InStr([XXX], "Q") + 1)
Version that doesn't fail if there's no Q in the string:
IIf(Instr([XXX], "Q"),Mid([XXX], InStr([XXX}, "Q") + 1),"")

And so on.