LOOK HERE !!!! phone query problem

[richardmc]

HELP and HELLO ALL,

I have a big list of phone numbers in a table in access. called (phone1). I also have another table of numbers called (freephone) I want to match up the last four digits of the free phone numbers with any telephone numbers in the (phone1) table that end in the same four digits... how can i do this? I need help, please, please, please!

Regards

Richard
This message was edited by richardmc on 2003-01-17 10:18

 

[WillR]

On 2003-01-17 06:16, richardmc wrote:
HELP and HELLO ALL,

I have a big list of phone numbers in a table in access. called (phone1). I also have another table of numbers called (freephone) I want to match up the last four digits of the free phone numbers with any telephone numbers in the (phone1) table that end in the same four digits... how can i do this? I need help, please, please, please!

Regards

Richard
This message was edited by richardmc on 2003-01-17 10:18

you can create a query (using both tables) with fields showing just the last 4 digits of the phone numbers from each of your tables. To get the last 4 digits, use an expression field with the following formula

Right([Phone1],4)

Do the same with the freephone no field and only select the records to your query where your 2 'last4' digit fields are equal.

HTH

 

[richardmc]

thanks, But i cant seem to get that to work!
It keeps bringing up an input box named phone1 ??

Where am I going wrong now?
Can you send me a same or something?

Cheers again

Rich

 

[richardmc]

thanks, But i cant seem to get that to work!
It keeps bringing up an input box named phone1 ??

Where am I going wrong now?
Can you send me a sample or something?

Cheers again

Rich

 

[WillR]

On 2003-01-17 10:57, richardmc wrote:
thanks, But i cant seem to get that to work!
It keeps bringing up an input box named phone1 ??

Where am I going wrong now?
Can you send me a sample or something?

Cheers again

Rich

when I said [phone1] i meant the field name of your phone number (as you didn't quote it). the reason it brings up an input box is that it doesn't recognise [phone1] as an existing field name, so it assumes it's a parameter field.

Replace my [phone1] and [freephone] for the phone no fields in the 2 tables and it should work... what the formula does is take the final 4 digits from each of the fields....

Right([field],4)
Means starting at the right end of the string of digits, return the 4 digits at the right hand end....

the help in Access should explain better.

 

[richardmc]

cheers for that.

I'm nearly there now, it is returning 0 results though?
Does it matter if the original table is not in A-Z order and contains some characters inbetween the numbers?!?!
If so how do i get rid of the characters?

Regards

Richard

 

[WillR]

On 2003-01-17 10:57, richardmc wrote:
thanks, But i cant seem to get that to work!
It keeps bringing up an input box named phone1 ??

Where am I going wrong now?
Can you send me a same or something?

Cheers again

Rich

Rich,

I'm off home now. PM me or reply with your email address and I will have a look at it later or over the weekend - that is if you haven't been able to sort it out yet.

 

[richardmc]

I'm off home now too.

Looking forward to monday morning to sort it out already !!

Laters

 

[WillR]

On 2003-01-17 11:35, WillR wrote:

On 2003-01-17 10:57, richardmc wrote:
thanks, But i cant seem to get that to work!
It keeps bringing up an input box named phone1 ??

Where am I going wrong now?
Can you send me a same or something?

Cheers again

Rich

Rich,

I'm off home now. PM me or reply with your email address and I will have a look at it later or over the weekend - that is if you haven't been able to sort it out yet.

Also....you could email the database to

wriley@orange.net

anyway - i'll pick up on it later unless anyone else can help (if it's urgent - try utteraccess.com - the access forum here is not yet that well known - so you may get a faster response).

Hope this all helps a bit - gotta shoot :wink: