Results 1 to 5 of 5
  1. #1
    Mercure67 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    4

    Question Access VBA Sql syntax problem

    Hello,

    I have a problem using sql in an Access (2016) module. I’m completely lost trying to elaborate a sql statement like INSERT INTO … VALUES.
    Here is the routine :


    Private Sub Nom_AfterUpdate()

    Dim CeJour As Date
    CeJour = Format(Date, "dd/mm/yyyy")
    '
    Ajout = Me.Controls![N°].Value
    '
    'Following sql statement works !


    'StrSQL = "INSERT INTO tbl_data (Id) VALUES ('" & Ajout & "' );"
    '
    ' This one doesn't
    '

    StrSQL = "INSERT INTO tbl_data (Id,Date_1er_Contact) VALUES ('" & Ajout & "', Cejour );"
    '
    '
    DoCmd.SetWarnings False
    DoCmd.RunSQL StrSQL
    DoCmd.SetWarnings True
    '
    End Sub

    The first sql is running perfectly :
    StrSQL = "INSERT INTO tbl_data (Id) VALUES ('" & Ajout & "' );"

    But I have two values to add to a table. So I try to have the correct syntax. I tried all possibility but without success. The ‘best’ statement leads to popups asking for values …

    StrSQL = "INSERT INTO tbl_data (Id,Date_1er_Contact) VALUES ('" & Ajout & "', Cejour );"


    ‘Ajout’ is a number taken from a field in a form. Supposed to be added in a numerical field.
    ‘CeJour’ is the current date. Supposed to be added in a date formated field.


    Could you please help ? I surfed on the web for half a night and maybe not far from a whole day to find the correct syntax....


    Many thanks in advance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dates in Access are a specific datatype.
    This CeJour = Format(Date, "dd/mm/yyyy") is putting a string into a date field, since Format returns a string???
    What is id in your table? If it is an autonumber, then you do NOT need to supply a value. Access will populate autonumber fields automatically when new record is created.

    When supplying a value for a Date datatype, you enclose that value with #
    eg MyDate =#4/21/2017#

    You should test your SQL syntax before trying to execute it by using

    Debug.Print strSQL

    Good luck.

  3. #3
    knarfreppep is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Location
    Adelaide, Australia
    Posts
    106
    CeJour = Format(Date, "mm/dd/yyyy")

    StrSQL = "INSERT INTO tbl_data (Id, Date_1er_Contact) VALUES ('" & Ajout & "', #" & Cejour & "#)"

    Better : DoCmd.RunSQL "INSERT INTO tbl_data (Id, Date_1er_Contact) VALUES ('" & Ajout & "', #" & Cejour & "#)"

    Bonjour

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Or do you have to format the date into the table? You can just add the date as it is and then when you use that value on a form, format it there if needed.

  5. #5
    Mercure67 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    4
    To : knarfreppep
    Many thanks. The sql works perfectly.
    I left the date format as was (dd/mm/yyyy) and it works fine. I’m using W10 Fr – and Office Pro 2016 – Fr.

    To : orange
    Id and outnumber.
    Yes ‘Ajout’ is an outnumber generated when I create a new record (new user). That new record is saved in a table called tbl_Demandeurs. In the same time a new record in another table (tbl_data) is created using this outnumber in order to use it as a link between the two tables. Id is a numerical field.
    There are probably better solutions … but it works !

    Many thanks to everybody for your help.

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

Similar Threads

  1. Convert SQL Server Syntax To Access Query Syntax
    By chalupabatman in forum Queries
    Replies: 1
    Last Post: 10-18-2017, 08:53 PM
  2. Replies: 10
    Last Post: 11-21-2014, 04:49 PM
  3. Syntax problem with IIF statement
    By supracharger in forum Reports
    Replies: 12
    Last Post: 06-03-2012, 05:10 PM
  4. Syntax problem
    By cheyanne in forum Access
    Replies: 7
    Last Post: 05-20-2012, 11:30 PM
  5. Syntax Contains Problem
    By pimlicosnail in forum Forms
    Replies: 9
    Last Post: 02-18-2010, 12:16 PM

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