exclude certain data in query

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

I have a field called DistrictCode

For all of our district managers they have a 4 digit number
For managers out on sick leave/training etc. they have LOA/DSMA

All Division managers have a 3 digit number (which are all different) and
their admins have the same 3 digit number with .5 added

The field must be text to have the LOA etc show up. I want to only pull the
Divison Managers in a query. How can I set it to pull only the 3 digit
numbers?
 
Assuming that it's a number data type field, < 1000 won't work due to the
decimal points. Therefore let's convert the number field to a string and see
how long it is then. In the field of a query put something like:

DCLength: Len(CStr([DistrictCode]))

It should return values of 3, 4, and 5 according to your description of the
data.

In the criteria for the above field, put 3 (no quotation marks).
 
Perfect! Thank you!

Jerry Whittle said:
Assuming that it's a number data type field, < 1000 won't work due to the
decimal points. Therefore let's convert the number field to a string and see
how long it is then. In the field of a query put something like:

DCLength: Len(CStr([DistrictCode]))

It should return values of 3, 4, and 5 according to your description of the
data.

In the criteria for the above field, put 3 (no quotation marks).
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jennifer said:
I have a field called DistrictCode

For all of our district managers they have a 4 digit number
For managers out on sick leave/training etc. they have LOA/DSMA

All Division managers have a 3 digit number (which are all different) and
their admins have the same 3 digit number with .5 added

The field must be text to have the LOA etc show up. I want to only pull the
Divison Managers in a query. How can I set it to pull only the 3 digit
numbers?
Click to expand...
Click to expand...
 
If the field is really only a number then you should be able to use
criteria like

Field: DistrictCode
Criteria: Between 100 and 999 AND Int([DistrictCode]) = [DistrictCode]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Okay, now I'm getting an error message that it is to complex to be
evaluated.
A numeric expression may contain too many complicated elements. Or I'll
get an erro message "invalid use of null".

I've tried simplifying the query be getting the same message. I can get it
to pull the 3,4 or 5 just not only the 3's.
Jerry Whittle said:
Assuming that it's a number data type field, < 1000 won't work due to the
decimal points. Therefore let's convert the number field to a string and see
how long it is then. In the field of a query put something like:

DCLength: Len(CStr([DistrictCode]))

It should return values of 3, 4, and 5 according to your description of the
data.

In the criteria for the above field, put 3 (no quotation marks).
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jennifer said:
I have a field called DistrictCode

For all of our district managers they have a 4 digit number
For managers out on sick leave/training etc. they have LOA/DSMA

All Division managers have a 3 digit number (which are all different) and
their admins have the same 3 digit number with .5 added

The field must be text to have the LOA etc show up. I want to only pull the
Divison Managers in a query. How can I set it to pull only the 3 digit
numbers?
Click to expand...
Click to expand...
 
I get an error type mismacth in expression with this one.

John Spencer said:
If the field is really only a number then you should be able to use
criteria like

Field: DistrictCode
Criteria: Between 100 and 999 AND Int([DistrictCode]) = [DistrictCode]

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

I have a field called DistrictCode

For all of our district managers they have a 4 digit number
For managers out on sick leave/training etc. they have LOA/DSMA

All Division managers have a 3 digit number (which are all different) and
their admins have the same 3 digit number with .5 added

The field must be text to have the LOA etc show up. I want to only pull the
Divison Managers in a query. How can I set it to pull only the 3 digit
numbers?
Click to expand...
Click to expand...
 
Dang! I forgot to trap for nulls. Evidently you have some records with null
values in the DistrictCode. Below should handle the null values by coverting
them to 1 which will return a length of 1. The criteria of 3 should exclude
them.

DCLength: Len(CStr(IsNull([DistrictCode],1)))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jennifer said:
Okay, now I'm getting an error message that it is to complex to be
evaluated.
A numeric expression may contain too many complicated elements. Or I'll
get an erro message "invalid use of null".

I've tried simplifying the query be getting the same message. I can get it
to pull the 3,4 or 5 just not only the 3's.
Jerry Whittle said:
Assuming that it's a number data type field, < 1000 won't work due to the
decimal points. Therefore let's convert the number field to a string and see
how long it is then. In the field of a query put something like:

DCLength: Len(CStr([DistrictCode]))

It should return values of 3, 4, and 5 according to your description of the
data.

In the criteria for the above field, put 3 (no quotation marks).
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jennifer said:
I have a field called DistrictCode

For all of our district managers they have a 4 digit number
For managers out on sick leave/training etc. they have LOA/DSMA

All Division managers have a 3 digit number (which are all different) and
their admins have the same 3 digit number with .5 added

The field must be text to have the LOA etc show up. I want to only pull the
Divison Managers in a query. How can I set it to pull only the 3 digit
numbers?
Click to expand...
Click to expand...
Click to expand...
 
Do you have nulls (blanks) in the DistrictCode field? What is the field
type you are using? I suspect that it is a text field and can contain a
zero length string or a non-numeric character. If it is a text field
then try criteria like the following

Between "100" and "999" AND Not Like "*.*"

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

I get an error type mismacth in expression with this one.

John Spencer said:
If the field is really only a number then you should be able to use
criteria like

Field: DistrictCode
Criteria: Between 100 and 999 AND Int([DistrictCode]) = [DistrictCode]

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

I have a field called DistrictCode

For all of our district managers they have a 4 digit number
For managers out on sick leave/training etc. they have LOA/DSMA

All Division managers have a 3 digit number (which are all different) and
their admins have the same 3 digit number with .5 added

The field must be text to have the LOA etc show up. I want to only pull the
Divison Managers in a query. How can I set it to pull only the 3 digit
numbers?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top