damned Access date problem

Results 1 to 2 of 2

Thread: damned Access date problem

  1. #1
    Join Date
    Dec 1969

    Default damned Access date problem

    I&#039;ve got a db, into it I insert amongst other things, the date, via <BR><BR>insert into table (fldDAte) VALUES (#"&FormatDateTime(Date, 1)&"#)<BR><BR>The reason I&#039;m using the FormatDateTime as above is to force the long Date into Access to avoid it confusing my days with my months. <BR><BR>Fine, most of the time this works, why then do I sometimes get this error msg when running the above:<BR><BR>[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression &#039;#Saturday&#039;. <BR><BR>Which I&#039;ve just got now, hence the Saturday. <BR><BR>In the Access db, I&#039;ve got the date field set up with a &#039;Long Date&#039; format, which I though would be able to handle the dateformat I&#039;m inserting into the date field.<BR><BR>What am I doing wrong, or is it just one of the pleasures of working with Dates? The problem is that it&#039;s intermittent, hence totally unreliable, sometimes it works, sometimes it doesn&#039;t. <BR><BR>Thanks<BR><BR>Jim

  2. #2
    Join Date
    Dec 1969

    Default Read the Access docs...

    Access *ONLY* accepts US English date format.<BR><BR>But it&#039;s not supposed to accept the day of the week in that.<BR><BR>Unless you are using a machine that is located someplace *other* than in the USA, you should *NOT* need to do anything special with your dates. Just <BR>SQL = "insert into table (fldDAte) VALUES (#" & someDateVariable & "#)"<BR>will work fine.<BR><BR>If you *are* outside the USA, then do this:<BR><BR>&#060;%<BR>SaveLCID = Session.LCID<BR>Session.LCID = &H0409 &#039; switch to USA Locale<BR>SQL = "insert into table (fldDAte) VALUES (#" & someDateVariable & "#)"<BR>Session.LCID = SaveLCID<BR>%&#062;<BR><BR>Note that the default conversion of a date variable to a string is, when the Locale is USA, "mm/dd/yy" (or "mm/dd/yyyy", depending on the year), which is exactly what Access wants.<BR><BR>Let&#039;s say you are in the UK or Australia, and you enter:<BR><BR>&#060;%<BR>theDate = "9/11/01" <BR>%&#062;<BR>meaning, of course 9 November 2001. If you then follow that code with what I showed above, your SQL statement *will* become<BR>&nbsp; &nbsp; insert into table (fldDAte) VALUES (#11/9/2001#)<BR>which is exactly what Access needs and wants.<BR><BR>BUT FINALLY...<BR><BR>You showed this as your code:<BR><BR>SQL = "insert into table (fldDAte) VALUES (#"&FormatDateTime(Date, 1)&"#)"<BR><BR>If you really *MEAN* that you want to insert TODAY&#039;S DATE into Access, then you are doing it the hard way. You get the Date as a date/time variable in VBS, convert it to a string, and then access has to convert the string value back to a date/time variable. <BR><BR>Go look at the date/time functions available in Access. Guess what? Almost without exception, they match those of VBS!<BR><BR>So...<BR><BR>SQL = "insert into table (fldDAte) VALUES ( Date() )"<BR><BR>Presto. Use the Date() function *in Access* and avoid all the silly conversion!<BR><BR>

Posting Permissions

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