Concatenate two phone number columns by distinct! ARRRH!

[Nicholas Davenport]

Hi i hope someone can help!

I need to concatenate two phone number columns from within the same table from an update query. I know how to create a query with concatenate on two columns but i need more than that. I need the second phone number field to replace the first phone number if the first number field doesn't consist of starting with "07" and containing 11 digits to varify the mobile number is valid. I have tried a few things but i am getting nowhere fast!

I think an IIf statement is needed but i'm not all that up on IIf statements, any help would be greatly appreciated. The below example is what i used to pull the fields together but how to make the code distictive to my requirments i'll have to admit am abit clueless!

Mobile: [MobileNumber1]&""&[MobileNumber2]

[jimatqsi]

Is [MobileNumber1] a numeric or alpha string?

What do you mean by "from an update query?" Do you mean this code will exist as part of the update query? If not, is this happening on a form or report or query?

It could be something like this if we're dealing with strings:

iif(len(MobileNumber1])=11,iif(left([MobileNumber1],2)="07",[MobileNumber1],[MobileNumber2]),[MobileNumber2])

Another way to do the same thing and use only 1 iif is

iif(len([MobileNumber1])=11 and left([MobileNumber1],2)="07",[MobileNumber1],[MobileNumber2])

If the strings might possibly be NULL then you'll want to reference each one as NZ([stringname])

Jim

[NeoPa]

If you could just explain your question so that it is clear and makes sense then I'm someone could help you :)

What exactly do you need? Please use terms that are clear and mean the same to any of us as they would to you.

Welcome to Bytes!

[Nicholas Davenport]

Thanks for the quick reply

The [MobileNumber1] is a numeric field.

I have an import routine that picks up data that is needed to be send by a SMS message to customers but the data has two fields of data (Mobilenumber1 & MobileNumber2). any data that doesn't contain a number beginning with "07" is deleted but in some cases there is a number starting "07" in the MobileNumber2 field but not in the MobileNumber1 field so I would like the MobileNumber2 field to overwrite the MobileNumber1 field but only if their is no number starting "07" in the MobileNumber1 field so that I have one overall field called MobileNumber containing all records beginning "07" with only 11 character to ensure the mobile number is valid. Sorry for the long winded explanation, that’s for any help in advance!

[NeoPa]

So all entries, in either column, should only be considered valid if the first two chars are "07" and the length, trimmed for all spaces, is 11. Yes?

Can you say how a record with two valid numbers should be treated?

[Nicholas Davenport]

yes that's correct, so then once the two criteria's have been meet all the the mobilenumber1's and the mobilenumber2's will be in one column named mobilenumber. This column will then be used in the process of sending an sms message. I tried the iif formula but there was an argument error, so I played with it for a while and it still wouldn't work! Thanks again in advance

[NeoPa]

Nicholas: so then once the two criteria's have been meet all the the mobilenumber1's and the mobilenumber2's will be in one column named mobilenumber.

Are you attempting to say that each record with two valid mobile numbers would need to resolve to two separate records in the output with a number in each?