[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Access 2010

I have a paramter query that needs to be run from a form.  Too many things to type into a message box to make it useable.  Once I get the dropdowns to select the values I want how do I program a button to run the query with it taking the values that have been selected from the form?

Any help is appreciated
John
0
JohnMac328
Asked:
JohnMac328
  • 20
  • 9
  • 6
2 Solutions
 
Dale FyeCommented:
Several ways to do this, but it is usually done by building the SQL string in code, similar to:

Private Sub cmd_Filter_Click

    Dim strSQL as string, varSQL as Variant

    'assumes [Field1] is a text field
    if Len(me.txt_Criteria1 & "") > 0 then
        varSQL = (varSQL + " AND ") & "[Field1] = """ & me.txt_Criteria1 & """"
    endif

    'assumes [Field2] is numeric
    if Len(me.txt_Criteria2 & "") > 0 then
        varSQL = (varSQL + " AND ") & "[Field1] = " & me.txt_Criteria1
    endif

    'assumes Field3 is a date field
    if Len(me.txt_Criteria3 & "") > 0 then
        varSQL = (varSQL + " AND ") & "[Field1] = #" & me.txt_Criteria1 & "#"
    endif
 
    strSQL = "SELECT * FROM yourTable"  & (" WHERE " + varSQL)

    'do something with the SQL statement here
    currentdb.Querydefs("queryname").SQL = strSQL

End Sub

 
0
 
JohnMac328Author Commented:
Thats a good start - there are three tables in the query - does that change the syntax?
0
 
Nick67Commented:
You can also use a saved query, and in the criteria of the appropriate fields you use the following syntax
Forms!YourFormName!YourControlName
If the controls are comboboxes (and they are), be certain that you understand that the bound column is what is returned (not necessarily the first visible column) and place your criteria or structure your comboboxes appropriately.

You then just create a button to open the query with code in the OnClick event.
You can drag a button to the form and go through the wizard to do that.
Or in th e code window of the OnClick event of the button, start with DoCmd.Op... and choose OpenQuery from Intellisense and work in rest of the syntax as necessary
Running queries was under the 'miscellaneous' option in the Access 2003 wizard
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Dale FyeCommented:
JohnMac,

With a query that contains 3 tables, the best way to do it would be to create the query in the query designer, to get all the fields, joins, ... correct.  Don't add any of the criteria at this point.

You can then go one of two routes.

1.  Copy the SQL string and past that in place of the first part of my SQL string (not varSQL) build (previous post).

2.  Another way I do this, is to save the query you created above.  Then, in the code, instead of:

    strSQL = "SELECT * FROM yourTable"  & (" WHERE " + varSQL)

    'do something with the SQL statement here
    currentdb.Querydefs("queryname").SQL = strSQL

I do something like:

    strSQL = currentdb.Querydefs("queryname").SQL
    if instr(strSQL, "WHERE") > 0 then strSQL = Left(strSQL, instr(strSQL, "WHERE")-1)
    strSQL = strSQL & (" WHERE " + varSQL)
    Currentdb.Querydefs("queryname").sql = strSQL

This latter technique gets the SQL from the saved query, strips off the WHERE clause that is already saved (if there is one), and then appends the new criteria and saves the new query.

Nick's advise is good too, and I use that technique frequently.  However, in this situation, where you want to create a criteria by allowing users to select which fields they want to filter on, I prefer to not refer to form controls directly.  I find that this techniques limits the querys usefullness and makes me create another query if I want the same basic query but with different parameters, or a different form).  By building the criteria string on the fly, I have a lot more flexibility.
0
 
Nick67Commented:
<However, in this situation>
What is the situation?
<run the query with it taking the values that have been selected from the form>
What does this query do?

Are you opening it in datasheet view?
Is it an action query?
Is it a filter?

or something else?
0
 
Dale FyeCommented:
Nick,

<What is the situation?>

From my read, the OP wants to be able to filter a recordset on a variety of criteria, where not all of the criteria would have values entered.
0
 
JohnMac328Author Commented:
I am starting out with just the date range and one parameter.  I have this in the query criteria and these are also setup under the Parameters option

Between [Forms]![KellyForm]![BeginDate] And [Forms]![KellyForm]![EndDate]
[Forms]![KellyForm]![BROKER]

When I open the form it still gives me the pop up asking for the values - Why won't it wait until the form picks the values?
0
 
JohnMac328Author Commented:
This is my onclick event

Private Sub Command15_Click()
strSQL = CurrentDb.QueryDefs("KellyQuery").SQL
    If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("KellyQuery").SQL = strSQL

End Sub
0
 
JohnMac328Author Commented:
Wait - I forgot some areas of the onclick
0
 
JohnMac328Author Commented:
Same thing - here is the complete onclick sub

Private Sub Command15_Click()
Dim strSQL As String, varSQL As Variant

    'assumes [Field1] is a text field
    If Len(Me.txt_Criteria1 & "") > 0 Then
        varSQL = (varSQL + " AND ") & "[BROKER] = """ & Me.txt_Criteria1 & """"
    End If


strSQL = CurrentDb.QueryDefs("KellyQuery").SQL
    If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("KellyQuery").SQL = strSQL

End Sub
0
 
JohnMac328Author Commented:
I removed the options from the parameters section of the query since the form is asking for it.  I get this from the text box where there should be a broker name to choose from

#Name?

I have this as the control source since the values are in the BrokerName table

[BrokerName].[Broker]
0
 
Nick67Commented:
If you build the SQL string then dates MUST be enclosed in hashes(#)
String values must be enclosed by double quotes (") or more preferrably for readability, Chr(34)
Also, I don't see your sub actually DOING anything with your query.
You're changing it's SQL, but I don't see an execute or open, so I assume you are doing it elsewhere

Now

Private Sub Command15_Click()
Dim strSQL As String,
Dim varSQL As string
'SQL is string not variant!

varSQL ="" 'explicitly set it to empty-string

'you haven't Built your date part!
'replace [WhateverDate] with the actual field
VarSQL = "[WhateverDate] Between #" & [Forms]![KellyForm]![BeginDate] "# And #" & [Forms]![KellyForm]![EndDate] & "#"

    'assumes [Field1] is a text field
    If nz(Me.txt_Criteria1, "") <> "" Then
        'varSQL = (varSQL + " AND ") & "[BROKER] = """ & Me.txt_Criteria1 & """"  'this may work but it's hard to maintain
        varSQL = (varSQL + " AND ") & "[BROKER] = " & Chr(34) & Me.txt_Criteria1 & Chr(34) 'this is a lot easier to maintain
    End If

'now, here you deep-six the ENTIRE where--but you hadn't put the Date Values in!
'to test msgbox, comment it out after
MsgBox varSQL
'is it right?

strSQL = CurrentDb.QueryDefs("KellyQuery").SQL
    If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("KellyQuery").SQL = strSQL

End Sub
0
 
JohnMac328Author Commented:
I changed the control source to

SELECT Broker FROM BrokerName ORDER BY Broker;

and I still get the #Name?
0
 
JohnMac328Author Commented:
Now I am getting confused - this from Microsofts site says how to make the dates from the form work with the query

Step 5: Use the form data as query criteria

    Open the query in Design view.
    Enter the criteria for the data. Use the Forms object, the name of the form, and the name of the control:
        For example, in an Access database (.accdb or .mdb), for a form named Date Range, you use the following expression to refer to controls named Start Date and End Date in the query:

Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate]
0
 
JohnMac328Author Commented:
I am getting an "Expected end of statment" here

"# And #" &

I copied it and replaced the values you instructed
0
 
Nick67Commented:
IF you were douing this in the query editor, like I had first suggested, then this
Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate]
would go in the criteria row of the desired field.

You are NOT doing this in the query editor, you are doing this in VBA.
When you build up SQL strings then the delimiters (hash # and double-quotes) are required
http://msdn.microsoft.com/en-us/library/dd627355(v=office.12).aspx
0
 
JohnMac328Author Commented:
Wait - found it
0
 
JohnMac328Author Commented:
I got "Method or data member not found" here

Me.txt_Criteria1, "")
0
 
Dale FyeCommented:
If you are using Nick's code then

nz(Me.txt_Criteria1, "")  should work.

If you are using my code, which actually checks for empty strings as well as NULL values, the syntax should be:

Len(me.txt_Criteria1 & "")
0
 
Nick67Commented:
The whole of that line is
If nz(Me.txt_Criteria1, "") <> "" Then

Nz checks if the control is null, if it is, it substitutes "".
If the control was blanked by the user or null it is "" and if it is NOT "" then it constructs the broker part
0
 
JohnMac328Author Commented:
IF you were douing this in the query editor, like I had first suggested, then this
Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate]
would go in the criteria row of the desired field.

I am doing that - please see screen shot   screen
0
 
JohnMac328Author Commented:
Here is what I have and it still bombs with the Method or data member...
Private Sub Command15_Click()
Dim strSQL As String
Dim varSQL As String 'SQL is string not variant!

varSQL = "" 'explicitly set it to empty-string

'you haven't Built your date part!
'replace [WhateverDate] with the actual field
  varSQL = "[TRADE_DATE] Between #" & [Forms]![KellyForm]![BeginDate] & "# And #" & [Forms]![KellyForm]![EndDate] & "#"

    'assumes [Field1] is a text field
   If Nz(Me.txt_Criteria1, "") <> "" Then
        'varSQL = (varSQL + " AND ") & "[BROKER] = """ & Me.txt_Criteria1 & """"  'this may work but it's hard to maintain
        varSQL = (varSQL + " AND ") & "[BROKER] = " & Chr(34) & Me.txt_Criteria1 & Chr(34) 'this is a lot easier to maintain
    End If

'now, here you deep-six the ENTIRE where--but you hadn't put the Date Values in!
'to test msgbox, comment it out after
'MsgBox varSQL
'is it right?

strSQL = CurrentDb.QueryDefs("KellyQuery").SQL
    If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("KellyQuery").SQL = strSQL

End Sub

Open in new window

0
 
Nick67Commented:
No you aren't.
When you do this
CurrentDb.QueryDefs("KellyQuery").SQL = strSQL
You are replacing the entirety of what you created in the query editor with the string you construct.

Go ahead and try it!
Comment out everything and put this in to test
CurrentDb.QueryDefs("KellyQuery").SQL = "Select "Me" as WhoIsLearning;"
and then run KellyQuery

Your code
If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
rubs out the entire WHERE clause (the criteria from query editior)
0
 
JohnMac328Author Commented:
I'm just trying to get it to work - I didn't even notice I was talking to two different people - no wonder it's all jacked up
0
 
Nick67Commented:
Dumb question
You do have a control named txt_Criteria1 on the form, right?

Compile your code.
Does it compile?
If not, on what typo does it stop?
0
 
Dale FyeCommented:
I'm going to bow out and let you work exclusively with Nick at this point.
0
 
Nick67Commented:
@fyed
Hey man!
Don't go.
More sets of eyes catch typos

And hey
Len(me.txt_Criteria1 & "") >0

I've never used that.
I always use
nz(me.txt_Criteria1 , "") <> ""
to test sting controls for null or empty string
Doesn't NULL & "" return null?

Or is Len null-aware and returns 0 instead of bombing?
0
 
JohnMac328Author Commented:
Ok, I missed the txt_Criteria1 the second time around.  Now I have the problem of getting the values of SELECT Broker FROM BrokerName ORDER BY Broker; from the combo box
0
 
JohnMac328Author Commented:
wait - fixed that
0
 
JohnMac328Author Commented:
Ok, the query by itself works fine but when I choose the fields and click the button nothing happens.  Here is the code
Private Sub Command15_Click()
Dim strSQL As String
Dim varSQL As String 'SQL is string not variant!

varSQL = "" 'explicitly set it to empty-string

'you haven't Built your date part!
'replace [WhateverDate] with the actual field
  varSQL = "[TRADE_DATE] Between #" & [Forms]![KellyForm]![BeginDate] & "# And #" & [Forms]![KellyForm]![EndDate] & "#"

    'assumes [Field1] is a text field
   If Nz(Me.EXEC_BROKER, "") <> "" Then
        'varSQL = (varSQL + " AND ") & "[BROKER] = """ & Me.txt_Criteria1 & """"  'this may work but it's hard to maintain
        varSQL = (varSQL + " AND ") & "[EXEC_BROKER] = " & Chr(34) & Me.EXEC_BROKER & Chr(34) 'this is a lot easier to maintain
    End If

'now, here you deep-six the ENTIRE where--but you hadn't put the Date Values in!
'to test msgbox, comment it out after
'MsgBox varSQL
'is it right?

strSQL = CurrentDb.QueryDefs("KellyQuery").SQL
    If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("KellyQuery").SQL = strSQL

End Sub

Open in new window

0
 
JohnMac328Author Commented:
It is a combo box - does that make a difference?

'assumes [Field1] is a text field
   If Nz(Me.EXEC_BROKER, "") <> "" Then
0
 
Nick67Commented:
Again

<Also, I don't see your sub actually DOING anything with your query.
You're changing it's SQL, but I don't see an execute or open, so I assume you are doing it elsewhere>

Your sub ha not code to open the query
You stop here
CurrentDb.QueryDefs("KellyQuery").SQL = strSQL
Fine, that sets the query's SQL.

Now what do you want to do with it?
Open it in datasheet view?

You'd need to add
DoCmd.OpenQuery "KellyQuery", acViewNormal
0
 
JohnMac328Author Commented:
Great thanks - I am not much of an Access person
0
 
Dale FyeCommented:
Nick,

NULL & "" = ""

NULL + 3 = NULL
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 20
  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now