Results 1 to 7 of 7
  1. #1
    zephyr223 is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2016
    Posts
    2

    VBA Update Statement to update Date field

    Hi All,
    I have a form that a user users to update records in the database. This form has all unbound fields. These unbound fields are populated from a listbox by the user double clicking on the appropriate record for UDPATE from the listbox. The form contains some date fields and these dates can be left blank or without a date if the user so chooses. My code to update a record works perfect if all the date fields are populated, but if one is left blank or empty or without a date, I get the following error:

    "Run-time error '3075: Syntax error in date in query expression '#'. I have searched the internet before posting on here, to figure out a solution to accept a null value or empty value for the dates, but no luck. Has anyone run into this before or can provide some help/direction.

    Here is my sql update statement



    sql = "UPDATE [corrlog] SET [unitcode] = '" & txtUnitCodeInput & "', subject = '" & Me.txtSubjectInput & _
    "',received = #" & txtReceivedInput & "#,assigned = #" & txtAssignedInput & "#,duedate = #" & txtDueDateInput & _
    "#,reviewdate = #" & txtReviewedInput & "#,returneddate = #" & txtReturnedInput & _
    "#,closeddate = #" & txtClosedInput & "' WHERE [caseno] ='" & Me.lstInfo & "'"


    I have checked the table structure to make sure all columns are not required and they are set as such. Any help will be greatly appreciated. Thanks in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    you can either use the edit method of a recordset or only add fields to your update SQL if the control is populated.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Odd to edit a record with unbound controls - easier to do with recordsets as Paul mentioned.

    To your specific problem:
    "' WHERE [caseno] ='" & Me.lstInfo & "'"
    should be:
    "# WHERE [caseno] ='" & Me.lstInfo & "'"

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I assumed that was a typo given "My code to update a record works perfect if all the date fields are populated".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Ah, yes. Then at the point to generate the SQL statement the OP will need to NOT include the field update in the SQL statement if the value for that 'field' onscreen is Null.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, that was one of the options I mentioned.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    zephyr223 is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2016
    Posts
    2
    Hi Everyone,
    Thanks for the replies. I am now going back to work on this project. I will take the suggestions that both of you have given me and try to work on that. Honestly, I don't do coding at all, and what I copied to your in my original thread was just one of the ways I saw that someone else had gone about the issue and I was trying it out that same way.

    I will work on moving towards using a recordset. Thanks

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

Similar Threads

  1. Update field by Date Range
    By soldat452002 in forum Queries
    Replies: 1
    Last Post: 07-31-2016, 03:46 PM
  2. Replies: 11
    Last Post: 11-17-2015, 04:47 PM
  3. Auto update of date field
    By ase33592 in forum Forms
    Replies: 1
    Last Post: 08-05-2013, 02:32 PM
  4. Update date field
    By Loucorea in forum Access
    Replies: 4
    Last Post: 09-26-2012, 08:22 PM
  5. Extra date field update
    By georgft in forum Forms
    Replies: 0
    Last Post: 09-17-2008, 06:51 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