Need code to update fldFY05 IF fldDate is > 7/01/04

[bebo2]

I know that is kind of a long title...was hoping it would catch quicker attention.
I don't know code very well (not at all - but can understand the concepts) and not sure where to look for help to create a possible query or button to auto update

fldFY05 when fldDatePaid is >7/01/04 but less than 6/30/05
- with the data that has been entered into fldAmtPd.
all fields live in the same table at this point.

Does that make sense?

Thanks for any help!!!!
Barbara

 

[doulostheou]

If I am understanding you, it sounds to me like you want an update query. I am going to assume that you only want to update fldFY05 when it is Null (does not already have a value in it):

UPDATE tblTableName SET fldFY05 = [fldAmtPd] WHERE fldFY05 Is Null AND fldDatePaid Between #7/1/2004# And #6/30/2005#;

Copy and paste the above SQL statement into the SQL view of a new query. Save it and you will have an update query that will perform the operation you were describing on every record between 7/104 and 6/30/05 that does not have a value in fldFY05. If you don't care whether there is already a value in fldFY05 then just remove the part that says "fldFY05 Is Null". Alternatively you can past the follwing code behind a button:

Docmd.RunSQL "UPDATE tblTableName SET fldFY05 = [fldAmtPd] WHERE fldFY05 Is Null AND fldDatePaid Between #7/1/2004# And #6/30/2005#;"

Important: You may want to make a copy of your database before you start playing with update queries, in case your update does not go as planned. You cannot undo the effects of an update query after it has been executed.

 

[bebo2]

Thank you SO MUCH!!! It worked - except it missed one field.
I created a new query, opened the SQL view
and typed in:

UPDATE ProviderResources SET FY2005 = [AMTPAID] WHERE [FY2005] Is Null AND [DATEPD] Between #7/1/2004# And #6/30/2005#;

Then I ran it (yes I did it in a copy as you suggested).

I am not sure why it missed one - I will look closer at that!

I truly thank you!!

Barbara

 

[doulostheou]

No problem.

If it missed one field, you might have an empty string saved in fldFY05. Access distinguishes between fields that are Null (have nothing in them) and fields that are empty, basically meaning they have stored "". You might try adapting the parameters slightly "WHERE ([FY2005] Is Null OR [FY2005] = "") AND [DATEPD] Between #7/1/2004# And #6/30/2005#;"

That would pick up any empty strings. If it still doesn't work, there is something else about the field that is keeping it from fitting the criteria. If the date is correct, then the culprit has to be fldFY05. Something is typed in the field.

 

[bebo2]

oops, I tried it again to see if it changed the one that didn't update and I realized it said that it was about to update (0) fields. So I guess I didn't do it quite right....

 

[doulostheou]

It is going to update 0 fields because you already put values in all the fields that met the previous criteria. So none of those fields are Null. If you added the piece about empty strings, none of them are empty either.

My guess is the one field that did not update has something stored in it. If you cannot see anything, they may have just put a space in the field. Or perhaps they Hit ctrl+enter and added a return character (I am assuming this is a text field).

If the field truly is empty or null, then the only other possibility is that the date is outside of the date range that you specified.

 

[bebo2]

I think you were right about the empty string because I tried this and it worked - giving me the "you are about to update (78) fields - do you want to continue..." message.

This is what I edited the statement to read:
WHERE ([FY2005] Is Null Or "") And [DATEPD] Between #7/1/2004# And #6/30/2005#

Thank you again for your immediate feedback and successful help!!!

Barbara