Query

I have this type of value in a field.
"18202 70TH AVE N"

I need to create a query that will go to the first space and grab all characters until the next space. (70TH)

Then have to do it witht he next spaces (AVE)

THen (N)

And place these in different fields...anyone know the syntax?

THANKS

NOTE another value could be

734 Mariman ST NE

The easiest way, would be to use the split function, but it would be vba, not a query.

Split(me.yourtextfield, " ")

But, to do it in a query, you would have to locate the position of each space using the instr function.

The following would be the expressions in your query:
Space1: Instr(1, [yourtextfield], chr(32))
Space2: Instr([Space1]+1, [yourtextfield], chr(32))
Space3: Instr([Space2]+1, [yourtextfield], chr(32))

Then you would use the mid() function to parse it out.
Part1: Mid([yourtextfield], 1, [space1]-1)
Part2: Mid([yourtextfield], [space1]+1, [space2]-[space1]-1)
Part3: Mid([yourtextfield], [space2]+1, [space3]-[space2]-1)
Part4: Mid([yourtextfield], [space3]+1)

This is assuming a max of 4 splits....(3 spaces) if you want more, you make more space expressions, and then more part expressions.

If you want more info on string functions, click on the following link:

String Functions Listed By Name

Edit: Don't forget to save your query each time you make a new expression, so that expression will be available in the expression builder.

Hope that helps!

So in the query wizard how do I get your examples to work?

I am truly confused here...it understands "Space1" and "Space2"??

Please See attachment


THANK YOU


The following would be the expressions in your query:
Space1: Instr(1, [yourtextfield], chr(32))
Space2: Instr([Space1]+1, [yourtextfield], chr(32))
Space3: Instr([Space2]+1, [yourtextfield], chr(32))

Then you would use the mid() function to parse it out.
Part1: Mid([yourtextfield], 1, [space1]-1)
Part2: Mid([yourtextfield], [space1]+1, [space2]-[space1]-1)
Part3: Mid([yourtextfield], [space2]+1, [space3]-[space2]-1)
Part4: Mid([yourtextfield], [space3]+1)

I have this?

Am I geting closer?

Still dosent work

Say my streetname is: "1703 North 12th Street"

The following would be the expressions in your query:

Expr1: nz(Instr(1, [streetname], chr(32)),1)
Save your query
Expression1 will start at position 1, looking in the field streetname, looking for the first space (chr(32)) that it finds. In this case, the first space it finds is at position 5. So Expr1=5

Expr2: nz(Instr([Expr1]+1, [streetname], chr(32)),2)
Save your query
Epression2 will start at position 5+1, looking in the field streetname, looking for the first space it finds. The first space it finds after position 6 is at position 11. Expr2=11

Expr3: nz(Instr([Expr2]+1, [streetname], chr(32)),3)
Save your query
Expression3 will start at position 11+1, looking in the field streetname, looking for the first space it finds. The first space it finds after position 12 is at position 16. Expr3=16

Now to use the locations of the spaces to separate the different parts.

You would use the mid() function to parse it out.
Expr101: nz(Mid([streetname], 1, [Expr1]-1)," ")
save your query
In the field streetname, I will start at position 1 and extract 5-1 characters. That's four characters, and from position 1 in the string, the four characters will be "1703". Expr101="1703"

Expr102: nz(Mid([streetname], [Expr1]+1, [Expr2]-[Expr1]-1)," ")
save your query
In the field streetname, I will start at position 5+1 and extract 11-5-1 characters. Starting at position 6, I will extract 5 characters, which will be "North". Expr102="North"

Expr103: nz(Mid([streetname], [Expr2]+1, [Expr3]-[Expr2]-1)," ")
save your query
In the field streetname, I will start at position 11+1 and extract 16-11-1 characters. Starting at position 12, I will extract 4 characters, which will be "12th". Expr103="12th"

Expr104: nz(Mid([streetname], [Expr3]+1)," ")
In the field streetname, I will start at position 16+1 and go to the end. So position 17 to the end is "Street". Expr104="Street"

Now, the reason an atomized database is painfully obvious here. What happens if there is 2 spaces between some of the addresses? Well, using this method, it completely skews the results. What if one address has like 5 or 6 different spaces? Well, this is set up for 3 right now.....

the nz functions were thrown in there so if a value was null, it shouldn't error out.

Hope that explains it a bit better.

Actually these expressions go in the "Field" row of your query. Right click in an empty field box and choose build. After you build each expression, save your query.

What you're doing is inserting these expressions in the criteria of your field, when these should be fields (expressions).

Wow thank you for your patience and explination...

I ran that and this is what I got...see attachement

I think I have to get rid of the 4th Expr???

OOPS there is not House Number there...so I have to get rid of Expressino 4...workign on that...

Seems my Expression 3 is busted...all I get are 0s

Lemme work on a sample after lunch.... I know why it's erroring on the last field....

GOT IT...ugggggg

Changed Expr 103 to this and deleted Expr 104


Expr103: nz(Mid([STREETNAME_TO_CHANGE],[Expr2]+1)," ")

So for my examples I did this...with your example of course



Expr1: nz(InStr(1,[STREETNAME_TO_CHANGE],Chr(32)),1)
Expr2: nz(InStr([Expr1]+1,[STREETNAME_TO_CHANGE],Chr(32)),2)
Expr3: nz(InStr([Expr2]+1,[STREETNAME_TO_CHANGE],Chr(32)),3)
Expr101: nz(Mid([STREETNAME_TO_CHANGE],1,[Expr1]-1)," ")
Expr102: nz(Mid([STREETNAME_TO_CHANGE],[Expr1]+1,[Expr2]-[Expr1]-1)," ")
Expr103: nz(Mid([STREETNAME_TO_CHANGE],[Expr2]+1)," ")

will have to change the nz's to iif statements.... I'll get on it after lunch

OK Everything looks good there...
One last question....not that I can get that up in the Select Query how do I point the Expr101, 102, 103 to fields in the table...

Do I put something int he Criteria? "Expr101" just gives me Expr101 value in the field....

I feel really stuipd asking this....I have never worked with Expressions though

Thank you for your thoughts and help here

Ok, I'll get on it before lunch..... instead of listing everything again, I'll include a sample db, you can build your expressions accordingly.


Here's the sample: