Update query using sql only partially working
I need to run an update query from a procedure.
My update query is not working for one data field. I’ve worked over 4 hours on this and am so frustrated.
Your help would be VERY appreciated!
===================
Have table: tbl_reminders
Fields:
RemindersOn (Yes/No) (user wants all reminders ‘on’?)
On_I_14 (Yes/No) (user wants this specific reminder on)
On_I_5 (Yes/No) (user wants this specific reminder on)
On_I_DayOf (Yes/No) (user wants this specific reminder on)
Rem_I_14 (text, 1 char), default=”N” (Is Rem_I_14 active, remind user?
Rem_I_5 (text, 1 char), default=”N” (Is Rem_I_14 active,remind user?
Rem_I_DayOf (text, 1 char), default=”N” (Is Rem_I_14 activem remind user?
dtInitial14 (date, short) (date we’re tracking—the reminder date)
dtInitial5 (date, short) (date we’re tracking—the reminder date)
Initial Start Date (date, short) (date we’re tracking—the reminder date)
(none of these fields are 'calculated'. User just calls them by this name, e.g., Initial 14 is around 14 days prior to the initital start date--it could be a date 12 days prior or 15 days--they just call it 14Day)
I have two update queries (I actually want just one, but I have to get everything working first).
The 1st update query lsited below does not work for the 3rd field
The 2nd update query listed below works for the 1st two fields (listed just for reference)
===================================
NEED TO SET Rem_I_DayOf to “Y” IF
RemindersOn is ‘on’ (-1) AND
On_I_DayOf is ‘on’ (-1) and [initial start date] EQUALS today
“UPDATE QUERY (DOES NOT WORK)
strSQL = "UPDATE tbl_Reminders SET tbl_Reminders.Rem_I_DayOf = ""Y"" WHERE (((tbl_Reminders.RemindersOn)=-1) AND((tbl_Reminders.on_i_DayOf)=-1) and ((tbl_Reminders.[initial start date])=Date()));"
CurrentDb.Execute strSQL, dbFailOnError
‘ Is there a refresh statement I need to be running after the currentDb. Execute statement?
After it runs it sets
(each record has RemindersOn=-1 & On_i_DayOf=-1 are 'on' )
Rem_I_DayOf = “N” for initial start date of 7/1/10 (this is correct)
Rem_I_DayOf = “Y” for initial start date of 6/13/10 (this is correct)
Rem_I_DayOf = “Y” for initial start date of 7/1/10 (this is correct)
Rem_I_DayOf = “Y” for initial start date of 6/15/10 (this is INCORRECT)
Rem_I_DayOf = “Y” for initial start date of 3/9/10 (this is INCORRECT)
============================================
THE FOLLOWING WORKS (included for reference)
NEED TO SET Rem_I_14 to “Y” IF
RemindersOn is ‘on’ (-1) AND
On_I_14 is ‘on’ (-1) AND dtInitial14 less than today (< date())
OR SET Rem_I_5 to “Y” IF
On_I_5 is ‘on’ (-1) and dtInitial5 less than today (< date())
“UPDATE QUERY (WORKs)‘this WORKS with updating 2 fields—rem_i_14 & rem_i_5.
'strSQL = "UPDATE tbl_Reminders SET tbl_Reminders.Rem_I_14 = ""Y"", tbl_Reminders.Rem_I_5 = ""Y"" WHERE (((tbl_Reminders.RemindersOn)=-1) AND ((tbl_Reminders.on_i_14)=-1) and ((tbl_Reminders.dtInitial_14)<Date()) OR ((tbl_Reminders.on_i_5)=-1) And ((tbl_Reminders.dtInitial_5)<Date()));"
'CurrentDb.Execute strSQL, dbFailOnError
‘ Is there a refresh statement I need to be running after the currentDb. Execute statement?
