Auto Input Value in a Table Using a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like my query to automatically input a specified value in a field on
my table if certain criteria are met. For example: A value <=180 in field
[AHT] would return a value of "32" in field [Points] or a value between 181
and 190 in field [AHT] return a value of "31" in field [Points], and so on.
Can some help me with the expression/formula that will make this happen.
 
If you only have two to work from then an update query with an IIF statement
in the Update To section of the query grid in design view will do it.
IIF([AHT] <=180, 32, 31)

If you have lots of comparisons then you need to use a reference table like --
LowRef HiRef Correction
0 180 32
181 190 31

In the update query have both tables.
Use criteria for [AHT] as --
Between [LowRef] And [HiRef]

Use update to as [Correction]
 
Thanks, Karl! The update query works, sort of. Some of the fields in [Points]
remain blank after I run the query even though there is a valid number in
[AHT]. Why would this be?
--
Michael


KARL DEWEY said:
If you only have two to work from then an update query with an IIF statement
in the Update To section of the query grid in design view will do it.
IIF([AHT] <=180, 32, 31)

If you have lots of comparisons then you need to use a reference table like --
LowRef HiRef Correction
0 180 32
181 190 31

In the update query have both tables.
Use criteria for [AHT] as --
Between [LowRef] And [HiRef]

Use update to as [Correction]

Michael_100 said:
I would like my query to automatically input a specified value in a field on
my table if certain criteria are met. For example: A value <=180 in field
[AHT] would return a value of "32" in field [Points] or a value between 181
and 190 in field [AHT] return a value of "31" in field [Points], and so on.
Can some help me with the expression/formula that will make this happen.
Click to expand...
Click to expand...
 
Never mind. I figured it out. There were decimal points involved. Once I
accounted for them, it worked fine. Thanks again, Karl!
--
Michael


Michael_100 said:
Thanks, Karl! The update query works, sort of. Some of the fields in [Points]
remain blank after I run the query even though there is a valid number in
[AHT]. Why would this be?
--
Michael


KARL DEWEY said:
If you only have two to work from then an update query with an IIF statement
in the Update To section of the query grid in design view will do it.
IIF([AHT] <=180, 32, 31)

If you have lots of comparisons then you need to use a reference table like --
LowRef HiRef Correction
0 180 32
181 190 31

In the update query have both tables.
Use criteria for [AHT] as --
Between [LowRef] And [HiRef]

Use update to as [Correction]

Michael_100 said:
I would like my query to automatically input a specified value in a field on
my table if certain criteria are met. For example: A value <=180 in field
[AHT] would return a value of "32" in field [Points] or a value between 181
and 190 in field [AHT] return a value of "31" in field [Points], and so on.
Can some help me with the expression/formula that will make this happen.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top