Problem with left(text,#) function!

[WannabePrgmr]

I keep seeing that if the "text" part of

 left(text, #) 

is "" or zero, that it will return "text", which is what it is doing in my situation.

These are phone numbers that I'm trying to pull the area codes from.

It's actually starting to return the first 3 characters on the left, until it see's a "0" and then it returns the entire number ("text").

It works fine as long as the area code does not contain a "0".

I have this field set as a text field but apparently that isn't good enough.

Does anyone know how to code it so that it will see a "0" as it would any letter, or other number?

Thanks!

[TheSmileyCoder]

Are you using this from within code, or directly in a query?

[WannabePrgmr]

Directly in a query:

AreaCode: format([Phone Number], left([Phone Number], 3))

Where "Phone Number" is a phone number. The field is a text field and I have an input mask to force the number n the following format:

1234567890

The problem is that if the first three (area code) contain a "0", it will pull the numbers up to the "0" then copy the entire number after that.

Here's an example:

The number 9896664454 returns 989

The number 9906665445 returns 999906665445 (gets up to the 0, then gets the entire number).

Thanks

[missinglinq]

To be honest, I'm totally confused as to what you're trying to do with

format([Phone Number],.

and your problem was not with

left(text, #)

as you said, but rather with

Format(Text,Left(Text,3))

At any rate, this works regardless of whether or not the Area Code has a zero in it

AreaCode: Left([Phone Number],3)

Linq ;0)>

[WannabePrgmr]

Wow, do I feel like a HUGE idiot! I guess I was over doing it, as usual! I had picked up one too many things at once and was trying them together!

Thanks Missinglinq! I appreciate the help very much!