Strings which contain Nulls

[winshent]

I am querying full postcodes in our database and it appears some of the postcodes contain a null character as opposed to a space between the first and second part of the postcode..

For example, If use the Ascii Tool on http://easycalculation.com/ascii-hex.php to convert 'GL7 7JY', it returns the Ascii codes 71 76 55 NULL 55 74 89

Even posting the string on this forum, the Null gets converted to an asterisk..

Is there a way to query all records which contain a Null as part of the string?

 

[vbaInet]

I don't see how that is possible. A string cannot contain Null. Are you able to upload a test db so we can find the problem?

 

[tehNellie]

The string's not got a NULL in it, whatever it is that looks like a space presumably can't be recognised/found by that widget so it's returning NULL instead.

32 in decimal should be the space character e.g. chr(32) in VBA

you could try pasting the string into word with formatting turned on and seeing what word thinks the space actually is.

Or in VBA you could see what you get if you try

  strTest as string
  
  strtest = "GL7 7JY" (wherever that value is actually coming from)
  debug.print asc(mid(strtest,4,1))

That should return the Ascii value of the 4th character.

 

[tehNellie]

Inevitably a thought came to me just after I shut down the PC.

Has your address/postcode data originally come from a Non-Windows (Solaris, HPUX, OSX etc) operating system?

If so I think I know what's going on, if not you can probably ignore the rest of this.

Unix, especially, and Windows encode Text slightly differently and if your data has come from a non windows OS what I think is going on is that you've got a slightly different character instead of a "windows space". Windows is (un)Helpfully showing you a space, but behind the scenes it's storing something else which is why that ASCII converter is throwing a paddy when it comes across it and I suspect that VBA snippet will do the same.

Opening your original data in Word and revealing formatting is still a reasonable way to find out what character set windows thinks it is and/or what Word thinks is where you're seeing a space.

It's been a few years since I had to deal with this, but a quick fix that I used to use was a search and replace in Word. I think you can also "Save As" in word and make sure that it's using windows encoding.

I hope that helps.

 

[vbaInet]

Has your address/postcode data originally come from a Non-Windows (Solaris, HPUX, OSX etc) operating system?

That's a good thought. This may well be the problem. I'm not a fan of HP-UX boxes. Solaris are much nicer!

 

[gwunta1907]

This is an issue which really should have been addressed in your database design. In the table that contains the postcodes, you need to define the input mask such that no spaces can be entered in the post code. If you deem it 'too hard' or it is going to take too long to format the post code then another option is to create a parser that will use NULL (or a space) as a delimiter and then translate the postcodes into a 'prefix' and 'suffix' code. You could achieve this easier by exporting the post codes table to excel and then use the Access import wizard, citing a space as a delimiting character and import the post codes into a table which contains two fields, 'prefix' and 'suffix'. The harder way to do it is by using code to build a parser. Either way, if ALL of your postcodes aren't in the same form (ie they ALL have the space OR they ALL don't have a space), then your job will be made far more difficult and really the only way then to automate the process is using code.

 

[winshent]

Thanks for the replies guys..

I don't see how that is possible. A string cannot contain Null. Are you able to upload a test db so we can find the problem?

I've uploaded the file.. A table with two records, one record has a space as a separator, the other has the unknown character.

Or in VBA you could see what you get if you try

  strTest as string
  
  strtest = "GL7 7JY" (wherever that value is actually coming from)
  debug.print asc(mid(strtest,4,1))

That should return the Ascii value of the 4th character.

Ran this, turns out its Ascii Char 160.. which is a 'non-breaking space'..

I'm not sure how it could have got in to the system, but i guess sometime in the past we would have imported a file that we've obtained from somewhere..

This is an issue which really should have been addressed in your database design.

Yeah, I kind of agree with you, but our 'Postcode' field also stores zip codes.. Also, having a space helps with readability for the end user, and the data is used for mailings..

At the end of the day i'm just trying to do some housekeeping, and only a few records out of 400k have this.. And I could not understand what the issue was..

Cheers Guys

Vince

 

[vbaInet]

Ran this, turns out its Ascii Char 160.. which is a 'non-breaking space'..

I'm not sure how it could have got in to the system, but i guess sometime in the past we would have imported a file that we've obtained from somewhere..

Probably came from a web app or a website.