Update Query

  • Thread starter Thread starter cwoelfe
  • Start date Start date
C

cwoelfe

I have a table called "Students - Enrolled" and I need to create an update
query that will put a "90%" in the [REFUND] column/field for the records that
have an "092" in the [TERM] column/field and the date in the [REG_AUDIT_DATE]
is between "5/12/09" and "5/13/09"

I created the following query but it just put zeros in the the [REFUND]
column/field... I can't seem to get it to actually put the "90%"

IIf("TERM_YYT","092" And "LastOfREG_AUDIT_DATE" Between #1/1/2008# And
#5/12/2009#,"100")

why is it putting a 0% instead of 100%?
 
Make a backup, so if something goes wrong, you can get the data back.


UPDATE [students - enrolled]
SET refund = "90%"
WHERE term = "092"
AND (reg_audit_date BETWEEN #1/1/2008# And #5/12/2009#)



assuming you meant the string "90%", rather than the numerical value, 0.90
(once formatted, would give 90%)


Vanderghast, Access MVP
 
no actually i do mean the numerical value... I am going to use that column to
determine the retained amount of tuition.. so i t does need to be .90 right?

Also... I put your code in... it worked.. except it is STILL putting in 0's
for 100% and when I had your .90 in the code and ran it, it put 100% in the
field... WEIRD.. I mean .9 isnt 100%.. there has to be something somewhere in
the table or query that is getting the math wrong.. any ideas?





vanderghast said:
Make a backup, so if something goes wrong, you can get the data back.


UPDATE [students - enrolled]
SET refund = "90%"
WHERE term = "092"
AND (reg_audit_date BETWEEN #1/1/2008# And #5/12/2009#)



assuming you meant the string "90%", rather than the numerical value, 0.90
(once formatted, would give 90%)


Vanderghast, Access MVP



cwoelfe said:
I have a table called "Students - Enrolled" and I need to create an update
query that will put a "90%" in the [REFUND] column/field for the records
that
have an "092" in the [TERM] column/field and the date in the
[REG_AUDIT_DATE]
is between "5/12/09" and "5/13/09"

I created the following query but it just put zeros in the the [REFUND]
column/field... I can't seem to get it to actually put the "90%"

IIf("TERM_YYT","092" And "LastOfREG_AUDIT_DATE" Between #1/1/2008# And
#5/12/2009#,"100")

why is it putting a 0% instead of 100%?
Click to expand...
Click to expand...
 
Also... I put your code in... it worked.. except it is STILL putting in 0's
for 100% and when I had your .90 in the code and ran it, it put 100% in the
field... WEIRD.. I mean .9 isnt 100%.. there has to be something somewhere in
the table or query that is getting the math wrong.. any ideas?
Click to expand...

This strongly suggests that the *table* design is at fault: what is the
datatype of the field into which you're putting this? It appears to be a
Number, of the Long Integer default size. An Integer is by definition a whole
number so it can hold either 0 or 1 (100%), and 0.9 (90 %) is not a valid
value.

If that's the case, change the field size from Long Integer to Single or
Decimal with appropriate properties.
 
Back
Top