Hi I have the following records in my table:-

PTR22334
AA0123
HDA/C001
'
E/Y001
FG
Z12345
000000123456
000078910112

How can my query return only 123456 & 78910112 only.

On Aug 13, 6:08*pm, Prav <P...@discussions.microsoft.com> wrote:
> Hi I have the following records in my table:-
>
> PTR22334
> AA0123
> HDA/C001
> '
> E/Y001
> FG
> Z12345
> 000000123456
> 000078910112
>
> How can my query return only 123456 & 78910112 only.

Am I missing the point?

SELECT DesiredField
from MyTable
where left( DesiredField,1) = '0'

or

SELECT DesiredField
from MyTable
where left( DesiredField,4) = '0000'

If leading zeroes are to be eliminated

SELECT CInt( DesiredField )
from MyTable
where left( DesiredField,1) = '0'

Cheers

"(E-Mail Removed)" wrote:

> On Aug 13, 6:08 pm, Prav <P...@discussions.microsoft.com> wrote:
> > Hi I have the following records in my table:-
> >
> > PTR22334
> > AA0123
> > HDA/C001
> > '
> > E/Y001
> > FG
> > Z12345
> > 000000123456
> > 000078910112
> >
> > How can my query return only 123456 & 78910112 only.
>
> Am I missing the point?
>
> SELECT DesiredField
> from MyTable
> where left( DesiredField,1) = '0'
>
> or
>
> SELECT DesiredField
> from MyTable
> where left( DesiredField,4) = '0000'
>
> If leading zeroes are to be eliminated
>
> SELECT CInt( DesiredField )
> from MyTable
> where left( DesiredField,1) = '0'
>

On Wed, 13 Aug 2008 18:08:01 -0700, Prav <(E-Mail Removed)>
wrote:

>Hi I have the following records in my table:-
>
>PTR22334
>AA0123
>HDA/C001
>'
>E/Y001
>FG
>Z12345
>000000123456
>000078910112
>
>How can my query return only 123456 & 78910112 only.

As an alternative which doesn't depend on leading zeros, use a calculated
field

IsNumeric([fieldname])

with a criterion of True.

The only "gotcha" I can think of is that numbers in scientific notation such
as 3105E2 or 1154D28 will be seen as numeric.
--

John W. Vinson [MVP]