[AccessD] ADO Problem

Rocky,

    The syntax suggested in my post was duly tested before dispatch. As pointed out by Gustav, the alternative one, involving direct concatenation, that was successfully tried by you, worked because you must have tested it on a system having regional settings conforming to US date format.

    For consistent results internationally, it would be safest to adhere to the syntax mentioned in my post.

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: Beach Access Software 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, November 29, 2006 12:02
  Subject: Re: [AccessD] ADO Problem


  A.D.:

  It seems that the apostrophe solved the problem and that the # didn't work. Do you think it will work in conjunction with Format?

  Regards,

  Rocky


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
  Sent: Tuesday, November 28, 2006 9:29 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] ADO Problem

  Rocky,

      You might recall the thread "Date Comparison Problem" initiated by you in march-2006. It elicited the overall consensus that in order to avoid any possible pitfalls on account of various regional settings (that might differ from US date format), any date variable to be concatenated into a VBA string should first be preformatted to "mm/dd/yyyy" format.

      Accordingly, the following syntax should ideally be adopted:

      "INSERT INTO tWeeklyUpdateSent " & _
      "(DateSent) VALUES (#" & _
      Format(dtToday, "mm/dd/yyyy") & "#);"

  Best wishes,
  A.D.Tejpal
  ---------------

    ----- Original Message ----- 
    From: Beach Access Software 
    To: 'Access Developers discussion and problem solving' 
    Sent: Tuesday, November 28, 2006 23:45
    Subject: [AccessD] ADO Problem

    Dear List:

    A colleague (who thinks I know ADO) writes in to ask why the following:

    Conn.Execute "INSERT INTO tWeeklyUpdateSent ( DateSent ) " & _
    "SELECT " & dtToday & ""

    Inserts a record into tWeeklyUpdateSent so the conection is OK.  But but
  the date is 1/1/1900 instead of today's date.

    I had him check the contents of dtToday and it does indeed have 11/28/2006
  in it.

    Does anyone see what he's doing wrong here?

    MTIA

    Rocky