Wildcard for number

[TeddyvN]

I need to enter a wildcard for a number in a function: InstrRev - is this possible?

To keep it short: I need to separate the street from the housenumber in an address field. I wanted to do this by using something like the following expression:

Mid([straat];InStrRev([straat];" ")+1) --> this gives me the space between the street and the number.

But some streetnames have spaces in them too ("Burg. de Vlugtlaan") so the selection is not waterproof.

Now I thought that if I could add a wildcard for a number to the expression, it would select the first space followed by a number - this would bee much better: "Burg. de Vlugtlaan 4". Is this possible?

The following expression doesn't work, but maybe illustrates my thought:

Mid([straat];InStrRev([straat];" "+#)+1)

Any ideas?

PS: this post is a follow up of thread 701-922491...



Teddy

 

[KenReay]

Hi

Not aware you can do that

How about interating along the string until you find a number character (using Instr(), IsNumeric(), Mid()), then looking for a space

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TeddyvN]

Could you please give an example of how I would use this in a query? I'm not yet so familiar with these strings...

Thanks for the help!

Teddy

 

[KenReay]

Hi

You would you have to define a User Function and use that in your SQL

Do you have any VBA expertise?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TeddyvN]

Sorry, not realy...




Teddy

 

[TeddyvN]

Please don't bother anymore - found a way around:

As there are only 10 numbers to find, I created an expression within an updta query that consists of nested IIf functions - solved my issue.

Thanks for your help sofar!



Teddy