Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Datepart (Access XP)

    I am using a VBA/Access front end for a SQL Database. The code I am using is:

    strSQL = "insert into cx_monthlyDepSpend (assetno, amount, depdate) " & _
    " select assetno, case when firstmonthspend = 1 then firstmonth else OtherMonths end, '" & strDate1 & "'" & _
    " from cx_depreciation Where getdate() < lastdate or datepart(mm,getdate()) = datepart(mm,lastdate) " & _
    " and datepart(yy,getdate()) = datepart(yy,lastdate)"


    DoCmd.RunSQL strSQL

    and it comes up with the following message:

    Runtime error 3075 Syntax error

    I have run the script straight in SQL and it runs fine. Has anyone any ideas?

    Many thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Datepart (Access XP)

    The first argument of DatePart is a string, so it must be surrounded by quotes. Since you use it in a quoted string, it's best to use single quotes (as you do for strDate1). For example
    <code>
    DatePart('mm',GetDate())
    </code>
    and similar for the other occurrences of DatePart.

  3. #3
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Datepart (Access XP)

    Even though when you run it as SQL you don't need the quotes?

  4. #4
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Datepart (Access XP)

    I've changed it to this:


    strSQL = "insert into cx_monthlyDepSpend (assetno, amount, depdate) " & _
    " select assetno, case when firstmonthspend = 1 then firstmonth else OtherMonths end, '" & strDate1 & "'" & _
    " from cx_depreciation Where getdate() < lastdate or datepart('m',getdate()) = datepart('m',lastdate) " & _
    " and datepart('yyyy',getdate()) = datepart('yyyy',lastdate)"

    and it's still coming up with the same error. I know when use datepart straight in vba I put " round the m or yyyy however not in sql. I think I must be missing something.

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Datepart (Access XP)

    The quotes are needed in query design or SQL view too, otherwise I get a parameter prompt.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Datepart (Access XP)

    Case When is not valid in Access SQL. Try

    strSQL = "insert into cx_monthlyDepSpend (assetno, amount, depdate) " & _
    " select assetno, iif(firstmonthspend = 1, firstmonth, OtherMonths), '" & strDate1 & "'" & _
    " from cx_depreciation Where getdate() < lastdate or datepart('m',getdate()) = datepart('m',lastdate) " & _
    " and datepart('yyyy',getdate()) = datepart('yyyy',lastdate)"

    (Did you mean SQL Server when you wrote SQL?)

  7. #7
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Datepart (Access XP)

    That would make sense, yes I did mean SQL server, sorry. I'll try it thank you.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Datepart (Access XP)

    You can't use the RunSQL method to run a pass-through query. You must have a named query. What I do is I create a named query as a passthrough query and call it something like "qryPassThru". Then after creating my SQL string (much like you did), I then use this code to run the query.

    Currentdb.querydefs("qryPassThru").SQL = strsql
    Currentdb.execute "qryPassThru",dbseechanges
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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