Second query in combo box not working

[ambrn14]

I have form with a date range criteria, a combo box with a list of queries to perform and a command button to run the median of a result located in a tablefrom the selected query.

I am a true beginner of code so I have pieced this together. But I don't know how to get it to perform three steps if the second query is selected. I am stumped. I have included my code below.


Private Sub cmbQueries_AfterUpdate()
If IsNull(txtStartDate) Then
MsgBox ("Please specify a start date")
Me!txtStartDate.SetFocus
Exit Sub
End If
If IsNull(txtEndDate) Then
MsgBox ("Please specify an end date")
Me!txtEndDate.SetFocus
End If
Exit Sub

End Sub

Private Sub cmdGetMedian_Click()
On Error GoTo Err_cmdGetMedian_Click
Dim intMedian
If IsNull(cmbQuery) Then
MsgBox ("Please select the number of criteria and edit the query in design view.")
Me!cmbQuery.SetFocus
Exit Sub
End If
DoCmd.SetWarnings False
If Left(cmbQuery, 1) = "Door to Drug Interval" Then
DoCmd.OpenQuery "aqmakDoortoIVtPAStartInterval"

ElseIf Left(cmbQuery, 1) = "Onset to Door Interval" Then
DoCmd.OpenQuery "aqmakOnsettoDoorInterval"
End If
DoCmd.SetWarnings True
intMedian = DMedian("calcvalue", "tmakMedianCalcValue")
MsgBox ("The median is: " & intMedian)
Exit_cmdGetMedian_Click:
DoCmd.SetWarnings True
Exit Sub
Err_cmdGetMedian_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_cmdGetMedian_Click

End Sub

 

[CBrighton]

I'm not sure what you mean, exactly what is it that you want it to do which it currently does not?

 

[ambrn14]

It will not perform -
ElseIf Left(cmbQuery, 1) = "Onset to Door Interval" Then
DoCmd.OpenQuery "aqmakOnsettoDoorInterval"
and then this-
intMedian = DMedian("calcvalue", "tmakMedianCalcValue")
MsgBox ("The median is: " & intMedian)
to give me the median of this selected query. When performed is gives the answer to the 1st query in the list- "Onset to Door Interval"

I hope that makes better sense.
Thanks!

 

[CBrighton]

To be honest I'm not familiar with DMedian so I don't know it's paramaters.

What is "calcvalue" & "tmakMedianCalcValue"?

I'm guessing it'll be a field name & table/query name, in which case that query is likely based on the "Onset to Door Interval" query.

 

[ambrn14]

Yes, that is correct.

So how do I pass to the 2nd selected query?

 

[CBrighton]

If "tmakMedianCalcValue" is based on a specific query then you would need to make a similar query based on the other one.

You would then replace

intMedian = DMedian("calcvalue", "tmakMedianCalcValue")

with something like

If Left(cmbQuery, 1) = "Door to Drug Interval" Then
   intMedian = DMedian("calcvalue", "[B]QUERY1[/B]")
ElseIf Left(cmbQuery, 1) = "Onset to Door Interval" Then
   intMedian = DMedian("calcvalue", "[B]QUERY2[/B]")
End If

Where query1 is based on aqmakDoortoIVtPAStartInterval and query 2 is based on aqmakOnsettoDoorInterval.

 

[ambrn14]

I have to go to a meeting then I will change the code and let you know.

Thank you!

 

[ambrn14]

I changed the code and now it will not return a answer for the either of the queries. Thanks for the suggestion.

This is getting very frustrating!!! I would really love if this would work!!

 

[boblarson]

I think you need to look at this code a little more closely:

If Left(cmbQuery, 1) = "Door to Drug Interval" Then


and

ElseIf Left(cmbQuery, 1) = "Onset to Door Interval" Then


Both of those will NEVER be true. You are essentially saying, with the LEFT function to compare the first character in cmbQuery to the words "Onset to Door Interval", so I don't know about you but

"O" <> "Onset to Door Interval"

You would need something to return "Onset to Door Interval" in order for them to both be equal.

So, what is it do you THINK you were getting by using Left(cmbQuery, 1)?

 

[ambrn14]

Oh my! As I said I have pieced this together from what I thought was similar. I think I understand what you are saying. Here is the code that I copies from-

DoCmd.SetWarnings False
If Left(cmbCriteriaNbr, 1) = "1" Then
DoCmd.OpenQuery "qmakCalcDataOneCriteria"
ElseIf Left(cmbCriteriaNbr, 1) = "2" Then
DoCmd.OpenQuery "qmakCalcDataTwoCriteria"
ElseIf Left(cmbCriteriaNbr, 1) = "4" Then
DoCmd.OpenQuery "qmakCalcDataFourCriteria"
End If

Do I need to make another column in my table for the selection number as above and not use the name. I am going to try that. But I still welcome any other suggestions.

Thank you!!

 

[CBrighton]

You can use the name but you need to check the paramaters of Left.

The second paramater is the number of digits. it is saying "If the first x characters equal the string provided then".

Change the 1's in your Left functions to the number of characters (including whitespace) in the strings you have in the combobox.

:edit:

In fact, I've not tried this but I don't see why it shouldn't work:

Left(cmbQuery,Len(cmbQuery))

That way it will automatically count the characters for you and you can use the same Left function for each query in the combobox.

 

[ambrn14]

I think that I understand what you are saying. Maybe the db will make this a little more clear. I have attached it. After changing the code, I am now getting a "Type match" error. I would like to only have the "user friendly name" display in the combobox. I think I am doing something wrong with the number of columns.

 

[CBrighton]

Sadly I use Access 2003 and cannot open an accdb database.

 

[ambrn14]

Sorry. Here you go.

 

[CBrighton]

Alter your code to:

If Left(cmbQuery, Len(cmbQuery)) = "QUERY1" Then
   DoCmd.OpenQuery "aqmakDoortoIVtPAStartInterval"
ElseIf Left(cmbQuery, Len(cmbQuery)) = "Query2" Then
   DoCmd.OpenQuery "aqmakOnsettoDoorInterval"
End If

However, whichever option you select the queries behind them seem to require paramaters so I did not test further. It resolved the type match error though and it will cause your combobox to correctly define which query is run.

 

[ambrn14]

I have updated the db with you suggestions. And it seems to be running the queries but is there any way that I can clear the queries and or "tmakMedianCalcValue" to be sure that it is not rerunning the previous query? I hope that this makes sense.

I have attached the db with updated suggestions. Thank you!

 

[CBrighton]

What do you mean by "clear the queries"?

Are you looking to delete the queries or delete the criteria of the queries?

Certainly you can overwite the SQL behind a query via VBA if that's what you want to do, but keep in mind that if this is a multi-user database constantly re-defining queries could have unforseen consequences.

 

[ambrn14]

For example, I changed one of the door time values to give a long period of time and thus change the median value. When I test the form, it seems like the form is not clearing between queries. When I run the "aqmakDoortoIVtPAStartInterval", I get a median time of 45 min. from these values- 45,39,75. When I run"aqmakOnsettoDoorInterval", I get a median time of 127.5 min. from these values-30,75,1740,180.

I have that make sense. Thank you for all your help!!

 

[CBrighton]

The medians of both "499,500,9999999999" and "0,500,501" are 500.

The median is the middle number, or between the middle numbers if there are an even number of numbers.

Changing a value to a much larger number will not necessarily effect the median. because if you increase the largest value, or any value over that halfway point, the middle value will not change.

 

[ambrn14]

Here are the results (calculated in excel) of the two "aqmak. . . " queries if ran without the form. So there is a difference in the
two medians.
CodeStrokeDate(Onset to Door)CalcValue03-Jan-114504-Jan-113905-Jan-117545CodeStrokeDate
(Onset to Door)CalcValue
03-Jan-113004-Jan-117505-Jan-11174005-Jan-11180127.5