External field in a query

[Pietro]

Hi,
In my query i've a field called 'service request' this field is formatted
as a general number.
In another query,as i need to add number 9 before the service request
number in certain case,the number format is changed,so whe i try to link both
fields from both queries i get a message "type mismatch' as the SR field
format changed from number to text,i try to do the below code in the query to
reatain the original format "genaral number but i cannot,do you have
suggestions?

SR: Format(IIf([queue]='XCD CAR CONTACT',9 & "" & [Service Request],[Service
Request]),"00000000000")

 

[vbasean]

will this work?

SR: Clng(Format(IIf([queue]='XCD CAR CONTACT',9 & "" & [Service
Request],[Service Request]),"00000000000"))

 

[Pietro]

Hi vbasean,
Thank you for your quick reply...
Unfortunately it gives #Error

will this work?

SR: Clng(Format(IIf([queue]='XCD CAR CONTACT',9 & "" & [Service
Request],[Service Request]),"00000000000"))

Hi,
In my query i've a field called 'service request' this field is formatted
as a general number.
In another query,as i need to add number 9 before the service request
number in certain case,the number format is changed,so whe i try to link both
fields from both queries i get a message "type mismatch' as the SR field
format changed from number to text,i try to do the below code in the query to
reatain the original format "genaral number but i cannot,do you have
suggestions?

SR: Format(IIf([queue]='XCD CAR CONTACT',9 & "" & [Service Request],[Service
Request]),"00000000000")

 

[vbasean]

I'm assuming you're padding the number with '0's

will this work?

clng(IIf([queue]='XCD CAR CONTACT',str([Service Request] +
90000000000),right([Service Request] + 100000000000, 11))

Hi vbasean,
Thank you for your quick reply...
Unfortunately it gives #Error

will this work?

SR: Clng(Format(IIf([queue]='XCD CAR CONTACT',9 & "" & [Service
Request],[Service Request]),"00000000000"))

Hi,
In my query i've a field called 'service request' this field is formatted
as a general number.
In another query,as i need to add number 9 before the service request
number in certain case,the number format is changed,so whe i try to link both
fields from both queries i get a message "type mismatch' as the SR field
format changed from number to text,i try to do the below code in the query to
reatain the original format "genaral number but i cannot,do you have
suggestions?

SR: Format(IIf([queue]='XCD CAR CONTACT',9 & "" & [Service Request],[Service
Request]),"00000000000")

 

[vbasean]

you know, I think I'm coming at this from the wrong direction. Can the field
in the other table be text? Meaning, does it have to be a number?

I'm assuming you're padding the number with '0's

will this work?

clng(IIf([queue]='XCD CAR CONTACT',str([Service Request] +
90000000000),right([Service Request] + 100000000000, 11))

Hi vbasean,
Thank you for your quick reply...
Unfortunately it gives #Error

will this work?

SR: Clng(Format(IIf([queue]='XCD CAR CONTACT',9 & "" & [Service
Request],[Service Request]),"00000000000"))

:

Hi,
In my query i've a field called 'service request' this field is formatted
as a general number.
In another query,as i need to add number 9 before the service request
number in certain case,the number format is changed,so whe i try to link both
fields from both queries i get a message "type mismatch' as the SR field
format changed from number to text,i try to do the below code in the query to
reatain the original format "genaral number but i cannot,do you have
suggestions?

SR: Format(IIf([queue]='XCD CAR CONTACT',9 & "" & [Service Request],[Service
Request]),"00000000000")

 

[John W. Vinson]

Hi,
In my query i've a field called 'service request' this field is formatted
as a general number.

If you won't be doing arithmatic with it, change the datatype to Text. The
largest possible Long Integer number is 2147483647 so your 12-digit number
will NOT work!

In another query,as i need to add number 9 before the service request
number in certain case,the number format is changed,so whe i try to link both
fields from both queries i get a message "type mismatch' as the SR field
format changed from number to text,i try to do the below code in the query to
reatain the original format "genaral number but i cannot,do you have
suggestions?

SR: Format(IIf([queue]='XCD CAR CONTACT',9 & "" & [Service Request],[Service
Request]),"00000000000")

The concatenation operator is creating a Text value; and concatenating an
empty string "" is useless, it's like adding 0 to a number.