Help with a me.filter removing spaces

[wackywoo105]

I want to change the following to remove spaces when testing for post codes

Me.Filter = "[Address 1] Like '*" & TEST & "*' Or [Postal Code] Like '*" & TEST & "*'"

so it would be something like

Me.Filter = "[Address 1] Like '*" & TEST & "*' Or replace([Postal Code], " ", "") Like '*" & replace(TEST, " ", "") & "*'"

this doesn't work due to the quotes and I don't know how to fix it. Can anybody help?

 

[jdraw]

If you are storing Postal codes in a table, you might want to remove embedded spaces when storing the values.
If you are comparing an entered(typed in code) with a stored value, you could use some vba code to remove embedded/leading and trailing spaces before doing the compare.

A little more info and some sample values would help get more focused responses.

 

[isladogs]

Suggest you remove the spaces first and then compare with TEST values

Are you using UK postcodes where there is a space before the last 3 characters or another system.

 

[wackywoo105]

Yes it's uk so in the format ST12 1XL. I tried creating a string and storing the values there but that won't work for the table field as it then asks me to enter the value.

I recently did a similar thing with phone numbers so for that I have

Me.Filter = "onlyDigits(nz([Phone 1 No])) Like '*" & onlyDigits(Nz(TEST)) & "*' Or onlyDigits(nz([Phone 2 No])) Like '*" & onlyDigits(Nz(TEST)) & "*' Or onlyDigits(nz([Mobile No])) Like '*" & onlyDigits(Nz(TEST)) & "*'"

I just wanted to do a similar thing with the postcode. Maybe I should get a function to do it and call it just like the phone one from a module.

ok I've just done this:

Function removespaces(s As String) As String
removespaces = Replace(s, " ", "")
End Function

 

[isladogs]

Glad you've solved it.
You may be interested in my example databases for UK postcodes & UK postal addresses
https://www.access-programmers.co.uk/forums/showthread.php?t=295233
https://www.access-programmers.co.uk/forums/showthread.php?t=295378

These are both early versions of a commercial UK postal address finder application now available from my website:
http://www.mendipdatasystems.co.uk/uk-postal-address-finder/4594138311

 

[wackywoo105]

Glad you've solved it.
You may be interested in my example databases for UK postcodes & UK postal addresses
https://www.access-programmers.co.uk/forums/showthread.php?t=295233
https://www.access-programmers.co.uk/forums/showthread.php?t=295378

These are both early versions of a commercial UK postal address finder application now available from my website:
http://www.mendipdatasystems.co.uk/uk-postal-address-finder/4594138311

Thanks for that. I will have a good look at it. I have until now been using a bit of code that looks postcodes up on 192.com. It stopped working at one point, as they changed the website slightly, but I managed to get it going again. I've never been completely happy with it and always wished for a better alternative. It looks like I may now have that. Thanks.

 

[wackywoo105]

Had a look and that's impressive stuff. I wish I was able to create at that level. It really make me fell amateur.

The only issue I have is when it loads up a box come up with "Enter Parameter Value" and "Postcodes.In.Use?"

If I just leave it blank and click ok I then go through to the main page.

If I then try to put a postcode in, the box comes up again. Again leaving blank and clicking ok and then it all works fine.

I would very much like to use this but only need the address production from the postcode. Is this something you be ok for me to use and if so is it easy to strip it down to just this?

Also why is it so small in form design view but then larger in normal view?

 

[isladogs]

Had a look and that's impressive stuff. I wish I was able to create at that level. It really make me fell amateur.

The only issue I have is when it loads up a box come up with "Enter Parameter Value" and "Postcodes.In.Use?"

If I just leave it blank and click ok I then go through to the main page.

If I then try to put a postcode in, the box comes up again. Again leaving blank and clicking ok and then it all works fine.

I would very much like to use this but only need the address production from the postcode. Is this something you be ok for me to use and if so is it easy to strip it down to just this?

Hi
Thanks for the compliments.
It was a lot of work and included a steep learning curve learning how to parse JSON files so the data could be imported into Access

If I'm going off at a tangent below, my apologies

Are the above comments based on one of my databases? If so, which.

If you are getting the parameter for the InUse field in the postcodes table, that's because the source data table has since been modified by Chris Bell who runs the Doogal website. Various changes have been made at my request to remove spaces in field names (including InUse) & question marks, modify datatypes and so on

See this post: https://www.access-programmers.co.uk/forums/showpost.php?p=1553962&postcount=2

Using the latest version of each database should solve the issue - both are available from my website (see link below)

If you want to obtain postal addresses, then you will need to setup an account with Ideal Postcodes so you can download addresses for selected postcodes. Each search costs approx 3p.

You may use any of the code in the versions posted here on the forum PROVIDED you show acknowledgements to the source.
As to whether its easy to extract the relevant sections of code, I would say no. There's a lot going on behind the scenes

Of course you could buy the latest version (blatant product placement plug) from the website
A major update will be released in the next fortnight adding features including
a) nearby places search e.g. find restaurants within 500m of a postcode
b) location finder .... AKA where am I?
and more...

Suggest you send me an email (see link below) if you want to discuss further

 

[Mark_]

Me.Filter = "onlyDigits(nz([Phone 1 No])) Like '*" & onlyDigits(Nz(TEST)) & "*' Or onlyDigits(nz([Phone 2 No])) Like '*" & onlyDigits(Nz(TEST)) & "*' Or onlyDigits(nz([Mobile No])) Like '*" & onlyDigits(Nz(TEST)) & "*'"

You will want to move your contact to their own child table. Having a "Phone 1 No", "Phone 2 No", and "Mobile No" is not normalized and makes doing these types of searches much more difficult than they need to be. More importantly, you do not have space for "Business No", "Brother's No", or "Mother-In-Law's No", all of which would be equally valid to "Phone 2 No".

For myself, I like putting contacts into a simple table that has "ContactType" and ContactValue". This also allows you to include Email addresses, phone numbers, messenger IDs, and the such in a single table rather than having assorted fields floating about.

It also allows you to get away from numbers and spaces in your field names.

 

[wackywoo105]

You will want to move your contact to their own child table. Having a "Phone 1 No", "Phone 2 No", and "Mobile No" is not normalized and makes doing these types of searches much more difficult than they need to be. More importantly, you do not have space for "Business No", "Brother's No", or "Mother-In-Law's No", all of which would be equally valid to "Phone 2 No".

For myself, I like putting contacts into a simple table that has "ContactType" and ContactValue". This also allows you to include Email addresses, phone numbers, messenger IDs, and the such in a single table rather than having assorted fields floating about.

It also allows you to get away from numbers and spaces in your field names.

Is there an example of this anywhere I can have a look at. It's something I have thought of doing in the past. I would like to attach another table so each record can have various notes along with a time and date but attaching table to table is something I haven't done before.

 

[isladogs]

Is there an example of this anywhere I can have a look at. It's something I have thought of doing in the past.

There is an MS Contacts template database you could look at. Have a look on the initial screen when Access is opened. IIRC it's a desktop database (not web)

I would like to attach another table so each record can have various notes along with a time and date but attaching table to table is something I haven't done before.

You mean create a query where you join 2 tables? Its very easy to do

Could you please clarify the point you made in post #7 (and see my response in post #8)

 

[wackywoo105]

Could you please clarify the point you made in post #7 (and see my response in post #8)

I have probably set something up wrongly or am not using the correct version, but on load I get this:

When I first try to enter a postcode I get this:

The box that pops up is this:

After just leaving blank and clicking ok it seems to work. If I click cancel it stops.

 

[isladogs]

You're not using the correct version. See my explanation in post 8.
Suggest you download the current evaluation version from my website