Null value returned when using instr or mid functions in query

[gem1204]

I'm Using ms access 2010



I have an access query to parse out a complete address field into address, city, state and zip fields. I'm using several instr, left, mid, right and switch functions. when I run the query I see all the data and don't see any errors that might have resulted from my string functions. After a got my query set I wanted to run a make table query to copy all of the data into a new table but when I tried to run the make table query I got an error "Invalid use of Null" but the select query runs just fine.

I think I know what the error is but I'm not sure how to fix it. In my complete address field there can be up to three commas so I'm using the instring function to find the commas, then based on the position of the commas parse the data. My complete address field is not consistent (I don't have control over that) and there may only be no comma so if there are no commas, the start position in my instr function to find comma 2 with be null because there is no comma 1 position to start at. The same would be true for comma 3.

I hope this make sense and some one can help me figure out how to overcome the error of a null value for a strate position in an instr or possibly any of my other functions I'm using in my query

[John_G]

Because your address field is not consistent, you might have to resort to using VBA to parse the data. An immediate question I have is that if there are fewer than three commas, how do you know which parts is/are missing?

Can you provide a few sample lines of the data you are dealing with? There is not much worse than having to work with inconsistent data.

[gem1204]

Here are two sample records, some have zips and some don't have zips

1107 N. Watson Road (1360 & Brown Blvd), SomeWhere, TX
2601 Timberline Drive, SomeWhere, TX  70000

I did instring functions to find Comma1, Comma2 comma3 and I restricted to show only records where

Where comma1>0 and Comma2>0 and comma3 = 0

This will give me the records like the one I showed in my sample "Address",City, State zip. Some have a zip and some don't but it fits the format I'm looking for. This will take care of about 90% of my records

The get the address I'm parsing a field called [EventAddr]
Left([EventAddr],[comma1]-1)
To get the city I'm using
Mid([EventAddr],[comma1]+1,[comma2]-[comma1]-1)

All of the addresses are in Texas so I'm just using TX for state

To get the zip I created an expression called Has zip to look at the last character of[EventAddr] and determine if its numeric

HasZip: IsNumeric(Right([EventAddr],1))

To get the zip I used the switch function

Switch([HasZip]=-1,Right([EventAddr],5))

I don't see why I should be getting an error. My query only looks at records where commas 1 and 2 are valid.

...I'm wondering if it could be the switch function. I've heard some bad things about that function

[John_G]

In my complete address field there can be up to three commas

OK - but the two examples you show both have two commas. What do lines with 1 or 3 commas look like? (Are there ever no commas at all?)

How do you determine the value for [comma2]?

You might want to write a VBA procedur to do this, rather than try to write a rather messy (and hard to debug) make table query. Just make a recordset of your [EventAddr] fields and deal with them one at a time using an Insert Into .... query in VBA.

[gem1204]

My only concern right now are fields with something other than 2 commas. Anything else would be almost impossible to parse. Some have two address lines, some have commas between state and zip, some don't have zip. I have at least 90% with the select query I created now I guess I'll just use vba to finish up with vba

thanks for your help

[John_G]

A bit of a mess!

If you are reasonably comfortable with VBA, check out the Split() function. It will automatically split a string into its parts, based on a specified delimiter (in your case the comma), and put the results into an array. Example:

Dim Address() as string
address = Split("1107 N. Watson Road (1360 & Brown Blvd), SomeWhere, TX", ",")

will give you (arrays in VBA are by default 0-based):

Address(0)=1107 N. Watson Road (1360 & Brown Blvd)
Address(1)=Somewhere
Address(2)=TX

and Ubound(address) tells you how many commas there are.

Really a useful little function.

[gem1204]

Thanks, I could have used that for some times fields I had to split out in this same project. I had a from and to time that I had to split into separate fields. the data was set up like this:

11:00am-01:00PM
05:00-10:00PM
08:00am-04:00PM

I created a function that I could call from a query that would extract the from time and a second function that returned the end time. The only complicated part was determining the AM or the PM of the start time. If the start time didn't have an am or pm then I had to use the am or pm of the end time. The functions worked pretty well because I had to append them into date/time fields in a table. The only errors were user errors like putting a period(A.M.) or no am or pm anywhere in the field or something stupid like 10:62. This data is coming from Excel so if you've even done that you know how much of a pain that can be.

I'm very comfortable with VBA but I never messed much with arrays. I guess I need to check it out.

[orange]

Just a couple of comments.
I agree 100% with John that split() can be very useful in parsing texts with embedded terminators ("," "." ";" etc).
The other point is that people have made careers by parsing names and addresses. It is not trivial and gets quite unwieldy when there is inconsistent data and terminators.