search criteria in query

  • Thread starter Thread starter Todd
  • Start date Start date
T

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.
 
In design view use this calculated field --
My_Check: Len([assetid])

and use criteria <8
 
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.
 
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
 
Will this also include any assetid fields with letters/symbols in it?
--
Todd


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

and use criteria <8


--
Build a little, test a little.


Todd said:
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.
Click to expand...
Click to expand...
 
Try it Mikee!
--
Build a little, test a little.


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


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

and use criteria <8


--
Build a little, test a little.


Todd said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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


KARL DEWEY said:
Try it Mikee!
--
Build a little, test a little.


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


KARL DEWEY said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.


Todd said:
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


KARL DEWEY said:
Try it Mikee!
--
Build a little, test a little.


Todd said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top