Results 1 to 11 of 11
  1. #1
    John Southern is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    31

    How to run parameter query from VBA

    I want to execute a parameter query using VBA. I have tried using DoCmd.RunSQL with the values of the parameters in the SQL string but without success. In an attempt to find the problem I created a simple select query in access and copied and pasted the SQL view string into my code. I got the same error message. (DoCmd RunSQL requires a valid SQL string).


    Where am I going wrong ?
    Last edited by John Southern; 03-15-2010 at 02:03 PM.

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    you cannon execute SELECT queries in vba

  3. #3
    John Southern is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    31
    Thanks for such a quick clear answer! Is there a work around ? For instance would it work to use VBA code to run a form based on my select query?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    lots of forms are based on queries. what do you mean RUN it? open it? sorry the but the explanation is unclear.

  5. #5
    John Southern is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    31
    I have a query using four parameters as criteria. Three of the parameters are related to the first and can be derived from it using VBA code. I had hoped that I could use VBA to ask for entry of the first criteria, derive the other three and then open the query.
    You have explained that I cannot execute a SELECT query with VBA. I am now asking if I can achieve my aim by using a form having an SQL statement as its Record Source property, And then open the form using DoCmd.OpenForm. I hope this is clearer

  6. #6
    c_smithwick is offline Underpaid Programmer
    Windows 7 Access 2003
    Join Date
    Jan 2010
    Location
    Lakeside, CA
    Posts
    49
    You can do what you want strictly from VBA. First, build a query template in the query designer. Let's call it "qryTemp" for good measure and assume your table is named "tblTest" with three text fields, "Field1", Field2" and "Field3". Then, build your SQL string in code and use the following to run the query.
    Code:
    Public Sub testIt()
    Dim strSQL As String
    Dim strInput As String
    
    'All fields are text data types - modify the syntax for the query to suit your needs
    strInput = InputBox("Enter your criteria", "Input")
    strSQL = "SELECT tblTest.* FROM tblTest WHERE (((tblTest.Field1)='" & strCriteria & "') AND ((tblTest.Field2)='" & someFunction(strCriteria) & "') AND ((tblTest.Field3)='" & someOtherFunction(strCriteria) & "'))"
    CurrentDb.QueryDefs("qryTemp").SQL = strSQL
    DoCmd.OpenQuery "qryTemp"
    
    End Sub

  7. #7
    John Southern is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    31
    Thank you very much for your help. I am not quite there yet though! When I run your code (modified to suit the query I designed in the MSQuery grid) Then I just open this query and am asked to supply values for the four parameters- which is what I am trying to avoid. The query string in the code is just ignored. You write of "build a query template". Is this significant? I am not familiar with query templates.

  8. #8
    c_smithwick is offline Underpaid Programmer
    Windows 7 Access 2003
    Join Date
    Jan 2010
    Location
    Lakeside, CA
    Posts
    49
    Please post your code so I can see exactly what you are doing.

  9. #9
    John Southern is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    31
    Here is the code modified to use your advice. The query "sumSalesComp" is an aggregate query based on a Select query "SalesComp" based on another Select query. Expr4 is just a manipulation of WM_INVOICE_DATE to give month name and year.


    Sub Comparisons()
    Dim FROM1, FROM2, TO1, TO2 As Date
    Dim strSQL As String
    FROM1 = InputBox("Date from", "Input")
    TO1 = InputBox("Date to", "Input")
    FROM2 = DateAdd("yyyy", -1, FROM1)
    TO2 = DateAdd("yyyy", -1, TO1)
    FROM2 = DateValue(FROM2) ' needed to ensure variable is a date
    TO2 = DateValue(TO2)
    FROM1 = DateValue(FROM1)
    TO1 = DateValue(TO1)
    strSQL = "SELECT SalesComp.Expr4, SalesComp.WT_NOMCC, Sum(SalesComp.WT_NET_TOTAL) AS SumOfWT_NET_TOTAL FROM SalesComp WHERE (((SalesComp.WM_INVOICE_DATE) Between # " & FROM1 & " # AND # " & TO1 & "#) OR (( SalesComp.WM_INVOICE_DATE) Between '# & FROM2 & # ' AND ' # & TO2 & # '))"
    CurrentDb.QueryDefs("SumSalesComp").SQL = strSQL
    DoCmd.OpenQuery "SumSalesComp"

    End Sub

  10. #10
    John Southern is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    31
    In sending you the code I have seen the errors I had made. (Getting string quotes and date delimiters mixed up). Having corrected this ,all works exactly as I wanted.
    Thank you very much for your help. CurrentDb.QueryDefs ("queryname").SQL= strSQL was the key. Great stuff, thanks again

  11. #11
    MarvinM's Avatar
    MarvinM is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    30

    Lightbulb

    Hi John,

    I know this is an old thread, but I noticed an unobvious/nonobvious error. Others reading this in the future may benefit. Your code has this Dim statement:
    Quote Originally Posted by John Southern View Post
    Dim FROM1, FROM2, TO1, TO2 As Date
    Now, I know this works in other languages. I've programmed like this myself.
    But, in VBA, you must declare the type for each and every variable, or else Access will self-declare it as a Variant type.

    In your Dim statement, I believe your intention is to have all 4 variables declared As Date types.
    As it is written, you will get the first 3 As Variant (by default) and the last one will be a date type. This will still work, however, it is not what you intended. The correct VBA syntax for this would be:
    Code:
    Dim FROM1 As Date, FROM2 As Date, TO1 As Date, TO2 As Date
    Hope this helps the cause.

    Thanks,
    Marvin

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

Similar Threads

  1. Parameter Query & Wildcard
    By Rick West in forum Queries
    Replies: 8
    Last Post: 12-29-2009, 10:54 AM
  2. Pass a Parameter From a form to a Query
    By DDillesha in forum Forms
    Replies: 1
    Last Post: 10-28-2009, 12:49 PM
  3. Get value typed in for a Parameter query
    By terryvanduzee in forum Forms
    Replies: 1
    Last Post: 10-14-2009, 03:01 AM
  4. Parameter in Crosstab Query
    By RandyG in forum Queries
    Replies: 4
    Last Post: 09-30-2009, 06:40 AM
  5. Add combo box to parameter query
    By louisa14 in forum Queries
    Replies: 1
    Last Post: 12-10-2005, 08:38 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