Results 1 to 5 of 5
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    VBA Query Build


    I built a query using the designer then opened up SQL View and grabbed this syntax, as I thought it would be the correct VBA syntax. However, the VBE gives me an error of "Syntax Error" - what do I need to change in this syntax so that it is valid for VBA?

    Code:
        strSQL = "Select [Emp Name], [Emp Phone], EmpDailySalesQty, ROUND(EmpDailySalePrice) As [Average Price], Format([Sale Date],"mm/dd/yyyy/") As [Sales Date] FROM SalesLog"
    
    
        Set qdf = CurrentDb.CreateQueryDef("Sales Log", strSQL)

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The double quotes in the Format function need to be single quotes.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    That removes the VBE syntax error - but when the form loads (that has this query as a control source) I get [
    Code:
    #Name?
    Displaying for those two fields (Round and Format)

    However, if I run the query manually it displays results as it should

  4. #4
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Oh wait, that is because the "field" name that the form field was pointing to changed. I just had to update that.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So it's working? I wouldn't bother building that in code since there's nothing dynamic in it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Build Expression in Query
    By rwpspackett in forum Queries
    Replies: 1
    Last Post: 11-23-2015, 02:16 PM
  2. Build Query
    By Kyoshikawa in forum Queries
    Replies: 7
    Last Post: 10-06-2015, 10:03 PM
  3. How to build query
    By kashif.special2005 in forum Queries
    Replies: 2
    Last Post: 03-02-2012, 02:18 AM
  4. Access Query build
    By zarfx4 in forum Access
    Replies: 3
    Last Post: 04-08-2010, 09:53 AM
  5. Hellp with Query Build
    By zarfx4 in forum Queries
    Replies: 0
    Last Post: 05-28-2009, 08:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums