Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    Question Unanswered: Syntax error converting datetime from character string

    hey,
    I got this error while inserting a date in to the database
    "Syntax error converting datetime from character string"

    I tried with declaring the variables as date.
    also tried with CDate(exp)
    but both doesn't work.
    is thre any other way i could insrt this date value into the database.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Is the Date field in the table a fieldtype of Date/Time and that's why you want to convert it?

    Where is the value you're trying to insert coming from? A Form? or a string value from somewhere.

    Regardless, did you try me!MyDateField = "#" & mydatevariable & "#" or me!MyDateField = Format(mydatevariable, "mm/dd/yyyy")

    You can also possibly try using the IsDate(MydateVariable) to check to see if it's a valid date value?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    If pkstormys suggestion doesn't work, perhaps show how the date looks (some samples) and how you're stuffing them into the database. Depending on how your date string looks, you may have to use some string manipulations and perhaps DateSerial, in addition to formatting.
    Roy-Vidar

  4. #4
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    It is a date value..
    I have declare the variable first of date type and then i am recieving the date values from me.txtDat in that variable...
    but when i ran

    Insert into table(date) values (''" & txtDate & ").
    it gives me this error...

    and still i checked the values are corrected date type values but m having error while inserting them in the database..

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    "Insert into table(date) values (#" & txtDate & "#)"
    Paul

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Would check out the date

    Is the feild name call 'date'
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Thx for such a good info all of you.
    Everything is working. what i did i just changed the format of the textbox from mm/\dd/\yyyy
    to 'short' in the design view.
    and it worked well. now its inserting the dte for me.

    and single quote are working pretty good.

Posting Permissions

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