Querying with Leading Zero & No leading Zero

[Guest]

Hi ,

I have a table of phone numbers in the format 0123456789. I have another
table where the same phone numbers appear but do not have the leading zero
(ie 123456789)

I need to match the phone numbers via a query but the leading zero is not
permitting that. I have tried the input mask to add the leading zero, and
whilst this works when viewing the data, it still does not match the phone
numbers via a query.

I need to either remove the leading zero from one table or remnove it from
the other to make it march via the query, but can't figure it out.

Any assistance would be greatly appreciated.

Cheers
Matt

 

[Graham R Seach]

Matt,

Convert the value to a Double. For example:
SELECT * FROM tblMyTable
WHERE CDbl([PhoneNo]) = " & CDbl(Me!txtPhoneNo)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[onedaywhen]

I have a table of phone numbers in the format 0123456789. I have another
table where the same phone numbers appear but do not have the leading zero
(ie 123456789)

I need to match the phone numbers via a query but the leading zero is not
permitting that.

SELECT * FROM Table1 INNER JOIN Table2
ON Format$(Table1.phone_nbr, '0000000000')
= Format$(Table2.phone_nbr, '0000000000');

Jamie

--