Results 1 to 10 of 10
  1. #1
    SiebeD is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14

    Question Error when changing SQL code in VBA

    Hello everyone.



    I am trying to visualize data in a graph with MS access. The data that i am trying to visualize is hourly data from diffirent sensors.
    To do this I made a Query that comines all the data that needs to be on the graph. My idea was to make a Form where the user can chose what data to show (so which sensor they want to see) and I also need an option here where the user can chose to see the data hourly, monthly of yearly. I am trying to do this by changing the SQL code. so if i want to see the data hourly i need this:
    Code:
     SELECT (Format([Datum],"ddddd hh")) AS Expr1, Avg(Vermogen.PS_Buggenhout_Vermogen) AS GemVanPS_Buggenhout_Vermogen, Avg(Vermogen.PS_Hautrage_Vermogen) AS GemVanPS_Hautrage_Vermogen, Avg(Vermogen.PS_Mainvault_Vermogen) AS GemVanPS_Mainvault_Vermogen, Avg(Vermogen.PS_BrakelKerkhofstrt_Vermogen) AS GemVanPS_BrakelKerkhofstrt_VermogenFROM Vermogen
    WHERE (((Vermogen.Datum) Between [Formulieren]![Grafiek_menu]![Datum1] And [Formulieren]![Grafiek_menu]![Datum2]))
    GROUP BY (Format([Datum],"ddddd hh")), (Int([Datum]));
    But when i try to run this code in VBA like this: (and fyi 'Vermogen' is the name of the query that i use to visualize the data)

    Code:
     Private Sub btn_Grafiek_Click()    Dim db As DAO.Database
        Set db = CurrentDb
        Dim qdf As DAO.QueryDef
        Dim SQLstring As String
        
        Set qdf = db.QueryDefs("Gegevens Grafiek")
    
    
         SQLstring = "SELECT Format([Datum],"ddddd hh") AS Expr1, Avg(Vermogen.[PS_BrakelKerkhofstrt_Vermogen]) AS GemVanPS_BrakelKerkhofstrt_Vermogen, Avg(Vermogen.[PS_Buggenhout_Vermogen]) AS GemVanPS_Buggenhout_Vermogen, Avg(Vermogen.[PS_Hautrage_Vermogen]) AS GemVanPS_Hautrage_Vermogen, Avg(Vermogen.[PS_Mainvault_Vermogen]) AS GemVanPS_Mainvault_Vermogen FROM Vermogen WHERE (((Vermogen.[Datum]) Between [Formulieren]![Grafiek_menu]![Datum1] And [Formulieren]![Grafiek_menu]![Datum2])) GROUP BY (Format([Datum],"ddddd hh")), (Int([Datum]));"
         qdf.SQL = SQLstring
        
    
    
        DoCmd.OpenForm "Grafiek"
        
    End Sub
    It keeps giving me an erro that i cant use "Format([Datum],"ddddd hh")". But in what other way can i put in the SQL code that i need to filter that data.

    Thanks in advance!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why not put the sql into a query, and see what the error is? (or design it in query design)
    the query wont get the sql wrong.

  3. #3
    SiebeD is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    I did that, and then it works fine, the query sorts himself to hour day or month without problem. So that is the ting that i find so stange.

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I've never seen that format used before, and I had to google it to make sure It was correct - learn something everyday.

    What happens if you use

    Expr1: Format([datum],"dd/mm/yy hh")
    or
    Expr1: Datevalue([datum]) & " " & Format([datum],"hh")

    instead?

    The alternative would simply group on the hour part as you are already grouping on the date part?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Don't you need to concatenate the values with the sql string?
    Have you tried single quotes for the format?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    SiebeD is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Where do you suggest that I use those other formats? Beceause I can't use them in my VBA code (I just tried it). And you mean that i make another query where i group on the day part? thanks for answering!

  7. #7
    SiebeD is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Quote Originally Posted by Welshgasman View Post
    Don't you need to concatenate the values with the sql string?
    Have you tried single quotes for the format?
    I just tried single quotes and then this error shows up

    (error 3075, syntax error (operator missing))

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    @Welshgasman - I missed that of course it won't work.

    Add a Debug.Print SQLstring and see what you get.

    Code:
    
    
    Code:
     SQLstring = "SELECT Format([Datum],'ddddd hh') AS Expr1, Avg(Vermogen.[PS_BrakelKerkhofstrt_Vermogen]) AS GemVanPS_BrakelKerkhofstrt_Vermogen, " 
     SQLstring = SQLstring  & " Avg(Vermogen.[PS_Buggenhout_Vermogen]) AS GemVanPS_Buggenhout_Vermogen, Avg(Vermogen.[PS_Hautrage_Vermogen]) AS GemVanPS_Hautrage_Vermogen, "
     SQLstring = SQLstring  & " Avg(Vermogen.[PS_Mainvault_Vermogen]) AS GemVanPS_Mainvault_Vermogen "
     SQLstring = SQLstring  & " FROM Vermogen "
     SQLstring = SQLstring  & " WHERE (((Vermogen.[Datum]) Between [Formulieren]![Grafiek_menu]![Datum1] "
     SQLstring = SQLstring  & " And [Formulieren]![Grafiek_menu]![Datum2])) "
     SQLstring = SQLstring  & " GROUP BY (Format([Datum],'ddddd hh')), (Int([Datum]));"
    	
     Debug.Print SQLstring 
     
     qdf.SQL = SQLstring



    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    SiebeD is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Quote Originally Posted by Minty View Post
    @Welshgasman - I missed that of course it won't work.

    Add a Debug.Print SQLstring and see what you get.

    Code:
    
    
    Code:
     SQLstring = "SELECT Format([Datum],'ddddd hh') AS Expr1, Avg(Vermogen.[PS_BrakelKerkhofstrt_Vermogen]) AS GemVanPS_BrakelKerkhofstrt_Vermogen, " 
     SQLstring = SQLstring  & " Avg(Vermogen.[PS_Buggenhout_Vermogen]) AS GemVanPS_Buggenhout_Vermogen, Avg(Vermogen.[PS_Hautrage_Vermogen]) AS GemVanPS_Hautrage_Vermogen, "
     SQLstring = SQLstring  & " Avg(Vermogen.[PS_Mainvault_Vermogen]) AS GemVanPS_Mainvault_Vermogen "
     SQLstring = SQLstring  & " FROM Vermogen "
     SQLstring = SQLstring  & " WHERE (((Vermogen.[Datum]) Between [Formulieren]![Grafiek_menu]![Datum1] "
     SQLstring = SQLstring  & " And [Formulieren]![Grafiek_menu]![Datum2])) "
     SQLstring = SQLstring  & " GROUP BY (Format([Datum],'ddddd hh')), (Int([Datum]));"
        
     Debug.Print SQLstring 
     
     qdf.SQL = SQLstring



    This worked! so apparently the single quotes are okay? Thank you so much both of you for helping me!

  10. #10
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    When you create a string the WHOLE thing is enclosed in " " but if something in the string has those, the string contents get broken up, and your string breaks.
    So within the string anything that needs escaping in strings should be enclosed in single quotes instead.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. VBA Novice needs help changing code
    By ckulow in forum Programming
    Replies: 10
    Last Post: 02-14-2018, 10:27 AM
  2. Changing iff into VBA code
    By masond in forum Access
    Replies: 1
    Last Post: 02-21-2014, 08:28 AM
  3. Changing order by using code
    By FRAZ in forum Queries
    Replies: 4
    Last Post: 01-22-2014, 12:54 PM
  4. Changing Printers Code produces an error
    By Perceptus in forum Programming
    Replies: 4
    Last Post: 01-15-2013, 08:33 PM
  5. Replies: 8
    Last Post: 04-03-2012, 12:00 PM

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