criteria

  • Thread starter Thread starter Christina
  • Start date Start date
C

Christina

I have a field in table , data type is text. For our own specific purpose
the ID in this field can either be a number or a letter. I would like to
return in a query, data from another linked table only if the data in the ID
field is a number. Is that possible?

Thanks
 
You can use IsNumeric to test if the value is a number.
The calculated field would look like:

Field: NumbersOnly: IsNumeric([YourField])

The criteria for that would be True if you only wanted the number fields.

You could also use criteria directly against the field
Field: YourField
Criteria: Not Like "*![0-9]*"

That criteria should exclude any field that has a character that is not a
number character.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks. However maybe I did not fully explain. I am calculating payroll. I
want to calculate pay for all persons, but additionally bonus only for those
persons whose ID is not numeric. As it is it is calculating bonus for all
persons. I would like it to return 0 for persons whose ID is not numeric.

John Spencer (MVP) said:
You can use IsNumeric to test if the value is a number.
The calculated field would look like:

Field: NumbersOnly: IsNumeric([YourField])

The criteria for that would be True if you only wanted the number fields.

You could also use criteria directly against the field
Field: YourField
Criteria: Not Like "*![0-9]*"

That criteria should exclude any field that has a character that is not a
number character.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a field in table , data type is text. For our own specific purpose
the ID in this field can either be a number or a letter. I would like to
return in a query, data from another linked table only if the data in the ID
field is a number. Is that possible?

Thanks
Click to expand...
Click to expand...
 
So use the expression
IsNumeric([YourField])
If it is true then bonus, if it is false then no bonus.

Field: GetBonus(IsNumeric([EmployeeID]),.1*Salary,0)

or whatever you use to calculate the bonus.

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

Thanks. However maybe I did not fully explain. I am calculating payroll. I
want to calculate pay for all persons, but additionally bonus only for those
persons whose ID is not numeric. As it is it is calculating bonus for all
persons. I would like it to return 0 for persons whose ID is not numeric.

John Spencer (MVP) said:
You can use IsNumeric to test if the value is a number.
The calculated field would look like:

Field: NumbersOnly: IsNumeric([YourField])

The criteria for that would be True if you only wanted the number fields.

You could also use criteria directly against the field
Field: YourField
Criteria: Not Like "*![0-9]*"

That criteria should exclude any field that has a character that is not a
number character.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a field in table , data type is text. For our own specific purpose
the ID in this field can either be a number or a letter. I would like to
return in a query, data from another linked table only if the data in the ID
field is a number. Is that possible?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Whoops! I hate when I do leave out part of the expression.

The expression should read more like the following. For illustration purposes
I gave the individuals a 10 percent bonus based on their salary.

Field: GetBonus: IIF(IsNumeric([EmployeeID]),.1*Salary,0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks ever so much. Got it to work.

John Spencer (MVP) said:
Whoops! I hate when I do leave out part of the expression.

The expression should read more like the following. For illustration purposes
I gave the individuals a 10 percent bonus based on their salary.

Field: GetBonus: IIF(IsNumeric([EmployeeID]),.1*Salary,0)

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

John said:
So use the expression
IsNumeric([YourField])
If it is true then bonus, if it is false then no bonus.

Field: GetBonus(IsNumeric([EmployeeID]),.1*Salary,0)

or whatever you use to calculate the bonus.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Click to expand...
Click to expand...
 
Back
Top