Talking Telephone numbers

[ChrisM]

Hi,

I have an Access (Jet) database that holds (amongst other things) telephone
numbers.
They are in the format xxxxx xxxxxx (eg 09876 999999)

I am calling a query from VB6 via ADODB, Jet

I am trying to find a phone number or partial number in this table

ie
select customerId from MainTable
where MainTable.PhoneNumber = "%" & [pPhoneNumber] & "%"

['%' not '*' because I'm using Jet]

so if I was looking for 09876 999999

I could pass '09876 999999' or '9999' or '09876' as a parameter and all
would match.

Problem is '09876999999' won't match and I don't want to insist that the
user gets the space in the right place.

I can't use:
select customerId from MainTable
where Replace(MainTable.PhoneNumber," ","") = "%" & [pPhoneNumber] & "%"

because Jet doesn't know about the Access 'Replace()' function.

Jet does know about 'Format()' though. Wondering if I can use that...?

Anyone got a suggestion as to how to make this work?

Thanks,

ChrisM

 

[Dmitriy Antonov]

Try this
....where Str(Val(MainTable.PhoneNumber)) =...

Dmitriy.

 

[ChrisM]

Cheers Dmitriy,

You Da Man!

That works great.

I did consider Val() but didn't even try it, I thought val("123 456") would
throw an error. (Didn't think it would like the space)

Thanks again,

ChrisM

Try this
...where Str(Val(MainTable.PhoneNumber)) =...

Dmitriy.

Hi,

I have an Access (Jet) database that holds (amongst other things) telephone
numbers.
They are in the format xxxxx xxxxxx (eg 09876 999999)

I am calling a query from VB6 via ADODB, Jet

I am trying to find a phone number or partial number in this table

ie
select customerId from MainTable
where MainTable.PhoneNumber = "%" & [pPhoneNumber] & "%"

['%' not '*' because I'm using Jet]

so if I was looking for 09876 999999

I could pass '09876 999999' or '9999' or '09876' as a parameter and all
would match.

Problem is '09876999999' won't match and I don't want to insist that the
user gets the space in the right place.

I can't use:
select customerId from MainTable
where Replace(MainTable.PhoneNumber," ","") = "%" & [pPhoneNumber] & "%"

because Jet doesn't know about the Access 'Replace()' function.

Jet does know about 'Format()' though. Wondering if I can use that...?

Anyone got a suggestion as to how to make this work?

Thanks,

ChrisM

 

[Dmitriy Antonov]

Just make sure that spaces is the only issue. This will not work with most
of other non-digital characters, like comas, brackets and others, so comas.
According to MSDN only spaces, tabs and linefeeds should work here.
Additionally you may have one (and no more that one) dot (.), as I think.


So its probably better to place some data validation and/or transformation
logic, when this data is inputted by users or injected from other sources.

Dmitriy.

Cheers Dmitriy,

You Da Man!

That works great.

I did consider Val() but didn't even try it, I thought val("123 456") would
throw an error. (Didn't think it would like the space)

Thanks again,

ChrisM

Try this
...where Str(Val(MainTable.PhoneNumber)) =...

Dmitriy.

Hi,

I have an Access (Jet) database that holds (amongst other things) telephone
numbers.
They are in the format xxxxx xxxxxx (eg 09876 999999)

I am calling a query from VB6 via ADODB, Jet

I am trying to find a phone number or partial number in this table

ie
select customerId from MainTable
where MainTable.PhoneNumber = "%" & [pPhoneNumber] & "%"

['%' not '*' because I'm using Jet]

so if I was looking for 09876 999999

I could pass '09876 999999' or '9999' or '09876' as a parameter and all
would match.

Problem is '09876999999' won't match and I don't want to insist that the
user gets the space in the right place.

I can't use:
select customerId from MainTable
where Replace(MainTable.PhoneNumber," ","") = "%" & [pPhoneNumber] & "%"

because Jet doesn't know about the Access 'Replace()' function.

Jet does know about 'Format()' though. Wondering if I can use that...?

Anyone got a suggestion as to how to make this work?

Thanks,

ChrisM