Update To line in a Query -- incorrect results!!

[Guest]

Hello,

I'm using an update query in my database and based on the value of "fiscal
month" is in the main form, I want the fields to update to either the Actual
numbers or the budgeted number. Here is an example of my code for October
where Forms!Main!Fiscal Month is the field that houses the current month
which is 3/1/2007.

IIf(Month([Forms]![Main]![Fiscal Month])>="10",[Oct Actual],[Budget Oct])
IIf(Month([Forms]![Main]![Fiscal Month])>="11",[Nov Actual],[Budget Nov])
IIf(Month([Forms]![Main]![Fiscal Month])>="12",[Dec Actual],[Budget Dec])

The problem is with these 3 months. The "actuals" are zeros for October -
December since the month is only March and the budgeted amount has numbers
for these months. For some reason, the code is thinking that "3" (march)

=10, 11 and 12 and putting zeros in these fields.

Does anyone know why this may be happening? Please let me know if I'm being
unclear.

Thanks,
MN

 

[Guest]

Because the field are text the 3 considered to be grater then 1 or 11 or 111
as it compering the first charector.

You need to change it to number using CInt

IIf(CInt(Month([Forms]![Main]![Fiscal Month]))>=10,[Oct Actual],[Budget Oct])
IIf(CInt(Month([Forms]![Main]![Fiscal Month]))>=11,[Nov Actual],[Budget Nov])
IIf(CInt(Month([Forms]![Main]![Fiscal Month]))>=12,[Dec Actual],[Budget Dec])

Sorry, in the first post I added a zero in the end
--
Good Luck
BS"D

Hello,

I'm using an update query in my database and based on the value of "fiscal
month" is in the main form, I want the fields to update to either the Actual
numbers or the budgeted number. Here is an example of my code for October
where Forms!Main!Fiscal Month is the field that houses the current month
which is 3/1/2007.

IIf(Month([Forms]![Main]![Fiscal Month])>="10",[Oct Actual],[Budget Oct])
IIf(Month([Forms]![Main]![Fiscal Month])>="11",[Nov Actual],[Budget Nov])
IIf(Month([Forms]![Main]![Fiscal Month])>="12",[Dec Actual],[Budget Dec])

The problem is with these 3 months. The "actuals" are zeros for October -
December since the month is only March and the budgeted amount has numbers
for these months. For some reason, the code is thinking that "3" (march)

=10, 11 and 12 and putting zeros in these fields.

Does anyone know why this may be happening? Please let me know if I'm being
unclear.

Thanks,
MN

 

[Guest]

Because the field are text the 3 considered to be grater then 1 or 11 or 111
as it compering the first charector.

You need to change it to number using CInt

IIf(CInt(Month([Forms]![Main]![Fiscal Month]))>=10,[Oct Actual],[Budget
Oct],0)
IIf(CInt(Month([Forms]![Main]![Fiscal Month]))>=11,[Nov Actual],[Budget
Nov],0)
IIf(CInt(Month([Forms]![Main]![Fiscal Month]))>=12,[Dec Actual],[Budget
Dec],0)


--
Good Luck
BS"D

Hello,

I'm using an update query in my database and based on the value of "fiscal
month" is in the main form, I want the fields to update to either the Actual
numbers or the budgeted number. Here is an example of my code for October
where Forms!Main!Fiscal Month is the field that houses the current month
which is 3/1/2007.

IIf(Month([Forms]![Main]![Fiscal Month])>="10",[Oct Actual],[Budget Oct])
IIf(Month([Forms]![Main]![Fiscal Month])>="11",[Nov Actual],[Budget Nov])
IIf(Month([Forms]![Main]![Fiscal Month])>="12",[Dec Actual],[Budget Dec])

The problem is with these 3 months. The "actuals" are zeros for October -
December since the month is only March and the budgeted amount has numbers
for these months. For some reason, the code is thinking that "3" (march)

=10, 11 and 12 and putting zeros in these fields.

Does anyone know why this may be happening? Please let me know if I'm being
unclear.

Thanks,
MN

 

[John Spencer]

Try the following:

IIf(Month([Forms]![Main]![Fiscal Month])>=10,[Oct Actual],[Budget Oct])
IIf(Month([Forms]![Main]![Fiscal Month])>=11,[Nov Actual],[Budget Nov])
IIf(Month([Forms]![Main]![Fiscal Month])>=12,[Dec Actual],[Budget Dec])

When you placed quotes around "10" etc. this caused Access to perform a
string comparison and alphabetically "3" is greater than "10". Numerically,
3 is less than 10.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

I'm using an update query in my database and based on the value of "fiscal
month" is in the main form, I want the fields to update to either the
Actual
numbers or the budgeted number. Here is an example of my code for October
where Forms!Main!Fiscal Month is the field that houses the current month
which is 3/1/2007.

IIf(Month([Forms]![Main]![Fiscal Month])>="10",[Oct Actual],[Budget Oct])
IIf(Month([Forms]![Main]![Fiscal Month])>="11",[Nov Actual],[Budget Nov])
IIf(Month([Forms]![Main]![Fiscal Month])>="12",[Dec Actual],[Budget Dec])

The problem is with these 3 months. The "actuals" are zeros for October -
December since the month is only March and the budgeted amount has numbers
for these months. For some reason, the code is thinking that "3" (march)

=10, 11 and 12 and putting zeros in these fields.

Does anyone know why this may be happening? Please let me know if I'm
being
unclear.

Thanks,
MN

 

[Guest]

Thank you -- I had realized that too after I posted this message. Thanks for
the quick response!!!

Try the following:

IIf(Month([Forms]![Main]![Fiscal Month])>=10,[Oct Actual],[Budget Oct])
IIf(Month([Forms]![Main]![Fiscal Month])>=11,[Nov Actual],[Budget Nov])
IIf(Month([Forms]![Main]![Fiscal Month])>=12,[Dec Actual],[Budget Dec])

When you placed quotes around "10" etc. this caused Access to perform a
string comparison and alphabetically "3" is greater than "10". Numerically,
3 is less than 10.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

I'm using an update query in my database and based on the value of "fiscal
month" is in the main form, I want the fields to update to either the
Actual
numbers or the budgeted number. Here is an example of my code for October
where Forms!Main!Fiscal Month is the field that houses the current month
which is 3/1/2007.

IIf(Month([Forms]![Main]![Fiscal Month])>="10",[Oct Actual],[Budget Oct])
IIf(Month([Forms]![Main]![Fiscal Month])>="11",[Nov Actual],[Budget Nov])
IIf(Month([Forms]![Main]![Fiscal Month])>="12",[Dec Actual],[Budget Dec])

The problem is with these 3 months. The "actuals" are zeros for October -
December since the month is only March and the budgeted amount has numbers
for these months. For some reason, the code is thinking that "3" (march)

=10, 11 and 12 and putting zeros in these fields.

Does anyone know why this may be happening? Please let me know if I'm
being
unclear.

Thanks,
MN