Results 1 to 3 of 3
  1. #1
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    Update query only partially working using SQL statement

    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)



    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?





  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Well, I've found a couple of things, but I'm not sure if any of them will really make a difference. . .

    First, I'd strongly recommend changing your Initial Start Date field to get rid of the spaces. Possibly by just replacing them with underscores.

    Second, any time you have a Date/Time field, you want to encapsulate the value you're setting (or comparing) in hashes (=#date# instead of just =date).

    Third, boolean variables should always be checked against TRUE or FALSE. Don't assume that the database will know what you mean if you use -1, 0, 1, etc.

    And fourth, use single quotes to encapsulate text strings (='Y' instead of ="Y"). VBA Uses double quotes as a text delimiter which can lead to confusion if you try and use it for SQL too.

    So, like I said, I'm not sure these will make a difference, but try the following:
    Code:
    strSQL = "UPDATE tbl_Reminders SET tbl_Reminders.Rem_I_DayOf = 'Y' WHERE (tbl_Reminders.RemindersOn=False AND tbl_Reminders.on_i_DayOf=False AND tbl_Reminders.[initial start date])=#" & Date() & "#)"
    P.S.
    Just noticed you left Date() inside the variable's text. This means you're checking for an Initial Start Date of the string "Date()"

  3. #3
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    Thanks!

    Thank you so much for taking the time to reply. Your advice is great, and I will try everything you suggested.

    THANK YOU again!!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Statement in form code
    By ksmith in forum Programming
    Replies: 9
    Last Post: 11-07-2011, 12:04 PM
  2. IIf Update Query working OK but a better way?
    By dreamweaver547 in forum Queries
    Replies: 7
    Last Post: 04-19-2010, 09:47 AM
  3. Query filter statement
    By Brian62 in forum Access
    Replies: 1
    Last Post: 02-16-2010, 02:34 PM
  4. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 PM
  5. If then statement in query
    By ronnie4 in forum Queries
    Replies: 1
    Last Post: 01-20-2009, 10:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums