Results 1 to 3 of 3
  1. #1
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    636

    SQL to Update Table

    Hi Everyone

    I am running the code below and get the following error:-

    Click image for larger version. 

Name:	error.PNG 
Views:	12 
Size:	3.8 KB 
ID:	44418



    Any help appreciated

    Code is:-

    Code:
    Dim strSQL As String
    
    
    strSQL = "UPDATE [tblProgListing] SET CustProgID = " & Me.CustProgrammeID & ", ProgNo = " & Me.ProgNo _
    & ", ProgDateCreated = " & Format(Me.ProgDateCreated, "\#dd\-mmm\-yyyy\#") & ",  Code = '" & Me.Code _
    & "' WHERE CustProgID is null And ProgNo is Null And ProgDateCreated is Null And Code is Null;"
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    The Immediate window produces all the correct data

    Code:
    UPDATE [tblProgListing] SET CustProgID = 24, ProgNo = 1, ProgDateCreated = #02-Feb-2021#,  Code = 'L5' WHERE CustProgID is null And ProgNo is Null And ProgDateCreated is Null And Code is Null;

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,923
    Try:
    Code:
    Dim strSQL As String
    
    strSQL = "UPDATE [tblProgListing] SET CustProgID = " & Me.CustProgrammeID & ", ProgNo = " & Me.ProgNo _
    & ", ProgDateCreated = #" & Format(Me.ProgDateCreated,"yyyy/mm/dd") & "#",  Code = '" & Me.Code _
    & "' WHERE CustProgID is null And ProgNo is Null And ProgDateCreated is Null And Code is Null;"
    Debug.Print strSQL CurrentDb.Execute strSQL, dbFailOnError
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    636
    Hi Bob

    My apologies but I found the problem.
    The Control "ProgDateCreated" was "DateCreated" in the table.

    Now all fixed

    Many thanks

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

Similar Threads

  1. UPDATE 3073 error trying to update a table field
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 10-11-2018, 05:11 PM
  2. Replies: 4
    Last Post: 04-27-2016, 04:36 PM
  3. Replies: 7
    Last Post: 09-21-2012, 11:09 AM
  4. Replies: 1
    Last Post: 02-08-2012, 04:50 PM
  5. Replies: 1
    Last Post: 01-22-2011, 12:23 PM

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 - Senior Forums