Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: Writing a string to a table

    I am covering someone who is sick at work and I am having trouble getting the following piece of code working correctly, I have followed code that my colleague has used previously(as I am no expert in this) but the code keeps failing at the same point: -

    Code:
    Private Sub InsertOutboundDOBDetails()
    
    Dim SQL As String
    
    SQL = ("INSERT INTO tblOutboundDOB (AggNo, DOB, CustomerName, User, Date) " & _
        " Values(" & txtAggNo & ", " & txtDOB & ", " & txtCustName & ", " & txtUser & ", " & txtDate & ")")
        
    CurrentDb.Execute SQL
    
    End Sub
    Debug keeps pulling me back to the red line saying there is a syntax error in INSERT INTO statement. When I hover over the variables(txtAggNo, txtDOB etc.) the information from the form is being caputured. I just cannot get it to write to the table. I have double checked the field spellings in the table etc and can see nothing obvious.

    Any help on this would be greatly appreciated and I apologise for my novice take on it all.


  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Text values need to be surrounded by single quotes, so this should get you somewhere:
    Code:
    tblOutboundDOB (AggNo, DOB, CustomerName, User, Date) " & _
        " Values('" & txtAggNo & "', " & txtDOB & ", '" & txtCustName & "', '" & txtUser & "', " & txtDate & ")")
    As for dates, it depends on what database you're using - in access it requires dates to be surrounded by hashes e.g. #31/01/2009#

    I take it you're working with Access?

    Hope this helps
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Sorry should have mentioned that, yes I am working with access.

    I left the DOB field in the table as a text field, would I still need the #'s round the date?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    DOB = date of birth

    You should really update your database table appropriately.

    Moving question to the Access topic (because you're more likely to get targetted replies there!)
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2009
    Posts
    3
    Got it working, thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •