search criteria in query

[Todd]

I've got an access dbase that has a link to an sql dbase table. I didn't
create the sql table and I'm just linked to it so I can run a couple custom
reports in access. My question is that I'd like to run a query on a field
(column) from the sql table that will only display the records that don't
have 8 digits (numbers) and/or letters and/or symbols. Basically, anything
that don't have 8 numbers.

The data type of the sql table column called assetid is text but the only
information that is used for the field is 8 numbers (00000123). We use the
sql app for barcoding and sometimes people have to manually enter the assetid
via a handheld device and they get a little fat fingered thus making
mistakes.

Any suggestions are appreciated.

 

[KARL DEWEY]

In design view use this calculated field --
My_Check: Len([assetid])

and use criteria <8

 

[Daryl S]

Todd -

Add a new column to your query with the Len([FieldName]) for the field
(changing FieldName to your real field name), and set the criteria to <> 8.
After you test this out, you can hide this extra column in your report by
unchecking the 'show' box in the query for this new field.

 

[John Spencer]

Try the following criteria

Not Like "########" Or Is Null

or optionally you could use

Not Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]" or Is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Todd]

Will this also include any assetid fields with letters/symbols in it?
--
Todd

In design view use this calculated field --
My_Check: Len([assetid])

and use criteria <8


--
Build a little, test a little.

I've got an access dbase that has a link to an sql dbase table. I didn't
create the sql table and I'm just linked to it so I can run a couple custom
reports in access. My question is that I'd like to run a query on a field
(column) from the sql table that will only display the records that don't
have 8 digits (numbers) and/or letters and/or symbols. Basically, anything
that don't have 8 numbers.

The data type of the sql table column called assetid is text but the only
information that is used for the field is 8 numbers (00000123). We use the
sql app for barcoding and sometimes people have to manually enter the assetid
via a handheld device and they get a little fat fingered thus making
mistakes.

Any suggestions are appreciated.

 

[KARL DEWEY]

Try it Mikee!
--
Build a little, test a little.

Will this also include any assetid fields with letters/symbols in it?
--
Todd

In design view use this calculated field --
My_Check: Len([assetid])

and use criteria <8


--
Build a little, test a little.

I've got an access dbase that has a link to an sql dbase table. I didn't
create the sql table and I'm just linked to it so I can run a couple custom
reports in access. My question is that I'd like to run a query on a field
(column) from the sql table that will only display the records that don't
have 8 digits (numbers) and/or letters and/or symbols. Basically, anything
that don't have 8 numbers.

The data type of the sql table column called assetid is text but the only
information that is used for the field is 8 numbers (00000123). We use the
sql app for barcoding and sometimes people have to manually enter the assetid
via a handheld device and they get a little fat fingered thus making
mistakes.

Any suggestions are appreciated.

 

[Todd]

I tried it before I sent the reply post and no records showed up that had a
symbol or letter in it so I just wanted to make sure that it worked for these
as well. Thanks!
--
Todd

Try it Mikee!
--
Build a little, test a little.

Will this also include any assetid fields with letters/symbols in it?
--
Todd

In design view use this calculated field --
My_Check: Len([assetid])

and use criteria <8


--
Build a little, test a little.


:

I've got an access dbase that has a link to an sql dbase table. I didn't
create the sql table and I'm just linked to it so I can run a couple custom
reports in access. My question is that I'd like to run a query on a field
(column) from the sql table that will only display the records that don't
have 8 digits (numbers) and/or letters and/or symbols. Basically, anything
that don't have 8 numbers.

The data type of the sql table column called assetid is text but the only
information that is used for the field is 8 numbers (00000123). We use the
sql app for barcoding and sometimes people have to manually enter the assetid
via a handheld device and they get a little fat fingered thus making
mistakes.

Any suggestions are appreciated.

 

[KARL DEWEY]

Something is wrong then like maybe you have leading or trailing spaces.
Try it with out the criteria and sort. Count how many characters you see
versus the lenght count shown. If they are different put your cursor in the
text field of the query and actually count the characters one by one using
the arrow keys to move the cursor one character at a time.

--
Build a little, test a little.

I tried it before I sent the reply post and no records showed up that had a
symbol or letter in it so I just wanted to make sure that it worked for these
as well. Thanks!
--
Todd

Try it Mikee!
--
Build a little, test a little.

Will this also include any assetid fields with letters/symbols in it?
--
Todd


:

In design view use this calculated field --
My_Check: Len([assetid])

and use criteria <8


--
Build a little, test a little.


:

I've got an access dbase that has a link to an sql dbase table. I didn't
create the sql table and I'm just linked to it so I can run a couple custom
reports in access. My question is that I'd like to run a query on a field
(column) from the sql table that will only display the records that don't
have 8 digits (numbers) and/or letters and/or symbols. Basically, anything
that don't have 8 numbers.

The data type of the sql table column called assetid is text but the only
information that is used for the field is 8 numbers (00000123). We use the
sql app for barcoding and sometimes people have to manually enter the assetid
via a handheld device and they get a little fat fingered thus making
mistakes.

Any suggestions are appreciated.