Combo box fields selecting certain records 1

[LaurelLee]

Hi,
I am trying to get this query to work, and it won't budge. Please enlighten me!!
On a form, I have a combo box in which there are two fields, <>100 and >=0. I take this value and use it as my criteria within my query, but for some reason it is not pulling any records. When I hard code the <>100 or >=0, it works just fine. My boss seems to think it is interpreting these values as strings when they come from the form, and therefore not returning any values from the numeric field. Does this seem right to you all? And if so, what are the steps I need to take to get this to evaluate to a numeric field?

 

[LaurelLee]

I am not sure what I want to do. The selection for the where clause is within a combobox, and the order by is an option group, both on the same form. The WHERE statement is determined within the AfterUpdate procedure of that combobox, and the string for the order by is being determined within the click event for the button that runs the report. I need to keep both the combobox and the option group seperate as per the request of my users.
Here is the code from each of the procedures:

Private Sub cboWP_PCT_C_AfterUpdate()

Dim db As DAO.Database
Set db = CurrentDb
'below is not working.....
db.QueryDefs("qryReportDatawithTimsaDates").sql = Mid$(db.QueryDefs("qryReportDatawithTimsaDates").sql, 1, InStr(1, db.QueryDefs("qryReportDatawithTimsaDates").sql, "WHERE") + 5) & " [PCT_C] " & FORMS![frmSort1]![cbxPCT_C] & strSort & Mid$(db.QueryDefs("qryReportDatawithTimsaDates").sql, InStr(1, db.QueryDefs("qryReportDatawithTimsaDates").sql, "ORDER BY") - 1) & ";"
db.Close
DoCmd.OpenQuery "qryReportDatawithTimsaDates"

End Sub

and the sort options within the _click event:

Private Sub cmdRun_Dispatch_Report_Click()
On Error GoTo Err_cmdRun_Dispatch_Report_Click

    Dim stDocName As String
    Dim intResponse As Integer
    Dim strSQL As String
    Dim strSort As String
    Dim qdf As QueryDef
    
    DoCmd.SetWarnings False
    
    intResponse = MsgBox("This report may take several minutes to create.  Do you wish to continue?", vbOKCancel + vbInformation)
                If intResponse = 2 Then Exit Sub 'User Canceled
    

'run some queries here to update data...

Select Case frmSortOptions
        Case 1 'UBZ sort
            strSort = "block"
        Case 2 'PLN_C(default sort)
            strSort = "PLN_C"
        Case 3 'Decide sort
            strSort = "Decide"
        Case 4 'PLN_S sort
            strSort = "PLN_S"
        Case 5 'ECD sort
            strSort = "ECD"
        Case 6 'T_ACT_S sort
            strSort = "T_ACT_S"
        Case 7 'T_PLN_C sort
            strSort = "T_PLN_S"
        Case 8 'Delivered sort
            strSort = "T_Delivered"
        Case Else
            MsgBox "No sort order selected!", vbExclamation
    End Select
            
    strSQL = "SELECT qryReportDatawithTimsaDates.*" & _
            " FROM qryReportDatawithTimsaDates" & _
            " ORDER BY qryReportDatawithTimsaDates." & strSort & ", qryReportDatawithTimsaDates.wp, qryReportDatawithTimsaDates.ZONE DESC;"
    Debug.Print strSQL
    
    DeleteQueryIfExists ("qdfDispatch_Open_Pkgs")
    Set qdf = CurrentDb.CreateQueryDef("qdfDispatch_Open_Pkgs", strSQL)
    
    stDocName = "rptDispatch_Pkgs"
    
    DoCmd.OpenReport stDocName, acPreview

Err_cmdRun_Dispatch_Report_Click:
    If Err <> 0 Then
            Select Case Err
                Case 2501 'Canceled action
                    Resume Next
                Case Else
                    MsgBox Err.Number & ":  " & Err.Description, vbCritical, Me.Name & " cmdRun_Dispatch_Report_Click()"
            End Select
        End If
        
    DoCmd.SetWarnings True
    
End Sub

Sorry if that is overkill, I just want you to see all that I have.

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

LauraLee: I want to understand just what you want before I post the code. You have a combobox for the Where clause slection and you have a Option Group for the ORDER BY.

If a selection is made in the ComboBox then modify the WHERE clause to select based upon the value of the combobox.

When you click the command button to print the report you want to modify the ORDER BY of the same query if a selection has been made. If the option box was not updated then don't modify the SQL. Just leave it as is or should I remove the ORDER BY?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

Hi Bob--
You are correct. The ORDER BY clause will always have a value in it, as there is a default selected (PLN_C). In addition, there are subsequent order bys that are always there after the sort option is selected. For example, SELECT......WHERE [cboboxvalue].....ORDER BY [optionValue], ORDER BY wp, ORDER BY ZONE. (as shown in code above) The wp and the ZONE will always be the second and third sorts.

I need this to run in the run report button click because that is where I am running the queries to ensure the data is fresh, so all of this would happen after the queries are ran.

This may not be the best way to do this, but this is the way I see it.

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

This code is for the AfterUpdate of the ComboBox(see RED code which is the missing operator)

Private Sub cboWP_PCT_C_AfterUpdate()
Dim db As DAO.Database
Set db = CurrentDb
'below is not working.....
db.QueryDefs("qryReportDatawithTimsaDates").sql = Mid$(db.QueryDefs("qryReportDatawithTimsaDates").sql, 1, InStr(1, db.QueryDefs("qryReportDatawithTimsaDates").sql, "WHERE") + 5) & " [PCT_C] [red][b]=[/b][/red] " & FORMS![frmSort1]![cbxPCT_C] & ";"
db.Close
DoCmd.OpenQuery "qryReportDatawithTimsaDates"
End Sub

This is the code to update the original query after the ComboBox update. Rather than creating another query by using qryReportdatawithTimesaDates as input let's just append the ORDER BY clause on the end of it.

db.QueryDefs("qryReportDatawithTimsaDates").sql = db.QueryDefs("qryReportDatawithTimsaDates").sql & "ORDER BY qryReportDatawithTimsaDates." & strSort & ", qryReportDatawithTimsaDates.wp, qryReportDatawithTimsaDates.ZONE DESC; & ";"

Now you can just use this qry to as the RecordSource for the report.

Let me know if this makes sense to you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

Our glorious network was hosed all day yesterday, so I am only now able to test this bugger out. When I run it, I get the error "object variable or with block not set". Here is the line of code:

db.QueryDefs("qryReportDatawithTimsaDates").sql = db.QueryDefs("qryReportDatawithTimsaDates").sql & "ORDER BY qryReportDatawithTimsaDates." & strSort & ", qryReportDatawithTimsaDates.wp, qryReportDatawithTimsaDates.ZONE DESC;"

This is within the click event to run the report. Am I forgetting something?

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

The following code must be present in your module.

Dim db As DAO.Database
Set db = CurrentDb

Did you somehow leave this out?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

Ooops, I remembered to declare but I forgot the set.

It is telling me I have characters found after the end of the SQL statement. When I used the exact code you gave me, it highlighted it red And errored: "Expected: End of statement." and highlighted the last ";". So, I took out the last semicolon like so: .....ZONE DESC;"
and then it balked about extra characters.

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

I got it. There is automatically a semi-colon at the end of the .SQL property string and we have cancatenated on the end of that so use this code:

db.QueryDefs("qryReportDatawithTimsaDates").sql = Left(db.QueryDefs("qryReportDatawithTimsaDates").sql, Len(db.QueryDefs("qryReportDatawithTimsaDates").sql)-1) & " ORDER BY qryReportDatawithTimsaDates." & strSort & ", qryReportDatawithTimsaDates.wp, qryReportDatawithTimsaDates.ZONE DESC;"

I think that should strip off the ending semi-colon.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

I was working on that very same thing (however, in a much less efficient way)!
Alas, I still have the same error. I went into my query and made sure there were not any other spaces included after the semicolon, that checked out okay, and I still have the same issue. ??

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

Open your query and copy/paste the SQL from your query here so I may see it please.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

No prob...

SELECT mtblParentData4.*, 
IIf(Right(mtblParentData4.wp,2)="26" Or Right(mtblParentData4.wp,2)="31" Or Right(mtblParentData4.wp,2)="51" Or Right(mtblParentData4.wp,2)="61" Or Right(mtblParentData4.wp,2)="71",mtblTimsa_Info.Prev_WP_Received,mtblTimsa_Info.WP_Received) AS T_Received, 
IIf(Right(mtblParentData4.wp,2)="26" Or Right(mtblParentData4.wp,2)="31" Or Right(mtblParentData4.wp,2)="51" Or Right(mtblParentData4.wp,2)="61" Or Right(mtblParentData4.wp,2)="71",mtblTimsa_Info.Prev_WP_T_ACT_S,mtblTimsa_Info.WP_T_ACT_S) AS T_ACT_S, 
IIf(Right(mtblParentData4.wp,2)="26" Or Right(mtblParentData4.wp,2)="31" Or Right(mtblParentData4.wp,2)="51" Or Right(mtblParentData4.wp,2)="61" Or Right(mtblParentData4.wp,2)="71",mtblTimsa_Info.Prev_WP_T_PLN_C,mtblTimsa_Info.WP_T_PLN_C) AS T_PLN_C, 
IIf(Right(mtblParentData4.wp,2)="26" Or Right(mtblParentData4.wp,2)="31" Or Right(mtblParentData4.wp,2)="51" Or Right(mtblParentData4.wp,2)="61" Or Right(mtblParentData4.wp,2)="71",mtblTimsa_Info.Prev_WP_Delivered,mtblTimsa_Info.WP_Delivered) AS T_Delivered
FROM mtblTimsa_Info INNER JOIN mtblParentData4 ON (mtblTimsa_Info.HULL=mtblParentData4.HULL) AND (mtblTimsa_Info.wp=mtblParentData4.wp)
WHERE [PCT_C]>=0;

Sorry about the length, it has quite a few criteria in there.

Thanks for your patience!

Laurel

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

Let create the SQL in a variable so we can see what the code is creating. You see when the error occurs it won't save the changes as there is an error so we can't see the changes. Create a string variable and execute the following code:

vSQLstring = Left(db.QueryDefs("qryReportDatawithTimsaDates").sql, Len(db.QueryDefs("qryReportDatawithTimsaDates").sql)-1) & " ORDER BY qryReportDatawithTimsaDates." & strSort & ", qryReportDatawithTimsaDates.wp, qryReportDatawithTimsaDates.ZONE DESC;"

Put a stop in the code and with the immediate window take a look at just what the code is creating. There has to be an extra semi-colon somewhere. Post the string when you get it.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

SELECT qryReportDatawithTimsaDates.* FROM qryReportDatawithTimsaDates ORDER BY qryReportDatawithTimsaDates.PLN_C, qryReportDatawithTimsaDates.wp, qryReportDatawithTimsaDates.ZONE DESC;



This is what it printed in the Immediate Window. I notice that it did not catch the sort that I picked, as PLN_C is the default and I picked UBZ. (Not that this is related to the issue at hand...)

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

Shouldn't we have picked up all of the SQL code that you posted above? Where did that code come from? This latest string should have included all of the IIF's and stuff. What's happening here?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

The query is originally modified in the AfterUpdate event for the combobox to select the <>100 or the >=0. The order by option group is located in the click event to run the report, which is where the orderby is concatenated onto the original query from the combobox's event.

On the outset, it seems to me that this query would be saved from the combobox's event to the run button's click event.

Do you think that the problem may be rooted here?

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

Okay i remember now. Perform the selection of the combobox value. Then take a look at the SQL for the query at that time. See if you can run that query without the option group update. Let's make sure that that part of the query is getting updated correctly. If you can copy the SQL from that query and post. Then perform the command button but only with the variable and then post that SQL. This final SQL should look just like the first one but with the option group soring added to it. The semi-colon should be dropped from the first SQL and the ORDER BY added in. Copy and post that SQL .



Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

the sql from the combo box query:

SELECT mtblParentData4.*, IIf(Right(mtblParentData4.wp,2)="26" Or Right(mtblParentData4.wp,2)="31" Or Right(mtblParentData4.wp,2)="51" Or Right(mtblParentData4.wp,2)="61" Or Right(mtblParentData4.wp,2)="71",mtblTimsa_Info.Prev_WP_Received,mtblTimsa_Info.WP_Received) AS T_Received, IIf(Right(mtblParentData4.wp,2)="26" Or Right(mtblParentData4.wp,2)="31" Or Right(mtblParentData4.wp,2)="51" Or Right(mtblParentData4.wp,2)="61" Or Right(mtblParentData4.wp,2)="71",mtblTimsa_Info.Prev_WP_T_ACT_S,mtblTimsa_Info.WP_T_ACT_S) AS T_ACT_S, IIf(Right(mtblParentData4.wp,2)="26" Or Right(mtblParentData4.wp,2)="31" Or Right(mtblParentData4.wp,2)="51" Or Right(mtblParentData4.wp,2)="61" Or Right(mtblParentData4.wp,2)="71",mtblTimsa_Info.Prev_WP_T_PLN_C,mtblTimsa_Info.WP_T_PLN_C) AS T_PLN_C, IIf(Right(mtblParentData4.wp,2)="26" Or Right(mtblParentData4.wp,2)="31" Or Right(mtblParentData4.wp,2)="51" Or Right(mtblParentData4.wp,2)="61" Or Right(mtblParentData4.wp,2)="71",mtblTimsa_Info.Prev_WP_Delivered,mtblTimsa_Info.WP_Deliver
ed) AS T_Delivered, Right(mtblParentData4.wp,2) AS Pallet
FROM mtblTimsa_Info INNER JOIN mtblParentData4 ON (mtblTimsa_Info.wp=mtblParentData4.wp) AND (mtblTimsa_Info.HULL=mtblParentData4.HULL)
WHERE  [PCT_C] <>100;

looks good, but....

the odd part is that when I then go to the vsqlstring where it should be concatenating the order by, it says

vSQLstring = ""

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

Try running the above query all by itself and verify that it works as expected.

And then what is the name of this query show above? Are we going to append the ORDER BY to this query or are we going to use another query name that uses this one above as input to it?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

The query listed above is called qryReportDatawithTimsaDates. I just copied that SQL and it ran fine on its own.

I don't know which method would work best. Appending seems to be hanging me up, so maybe we should try a different way.

___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[scriverb]

Appending onto that query should be easy to do. All we need to do is trim off the semi-colon and add the ORDER BY on the end. This is what you want to do with the option group info, correct yes.

The following should do that:

db.QueryDefs("qryReportDatawithTimsaDates").sql = Left(db.QueryDefs("qryReportDatawithTimsaDates").sql, Len(db.QueryDefs("qryReportDatawithTimsaDates").sql)-1) & " ORDER BY qryReportDatawithTimsaDates." & strSort & ", qryReportDatawithTimsaDates.wp, qryReportDatawithTimsaDates.ZONE DESC;"

Keeping my fingers crossed.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]