Order by IIf statement?

T

Tieval

Still Clueless
Local time
Today, 15:47
Joined
Jun 26, 2015
Messages
475
I have a function which generates sub-form data and although it looks extremely complicated it works perfectly. The function is called in the on-load of the sub-form and can therefore be modified and the sub-form re-queried to update the data.

Code: 
Public Function scanresults()
'Populate results according to criteria on main form
Dim scandata As String
   scandata = "SELECT  qryScans.Scanned, " & _
                "qryScans.ID, qryScans.Blade, qryScans.Engine, " & _
                "qryScans.Part, qryScans.Hours, qryScans.Cycles, " & _
                "qryScans.Result, qryScans.Tank, qryScans.Operator, " & _
                "qryScans.Details, qryScans.Co, MRO.MRO " & _
                "FROM qryScans INNER JOIN MRO ON qryScans.Co = MRO.ID " & _
                "WHERE (qryScans.Scanned BETWEEN Forms!frmMain!tStartDate And Forms!frmMain!tEndDate)" & _
                "AND qryScans.Blade LIKE '*' &(Forms!frmMain!tBlade) & '*'" & _
                "AND qryScans.Engine LIKE '*' &(Forms!frmMain!tEngine) & '*'" & _
                "AND qryScans.Part LIKE '*' &(Forms!frmMain!tPart) & '*'" & _
                "AND qryScans.Hours >= (Forms!frmMain!tHours)" & _
                "AND qryScans.Cycles >= (Forms!frmMain!tCycles)" & _
                "AND qryScans.Co LIKE (Forms!frmMain!cboMRO)" & _
                "AND qryScans.Tank LIKE '*' &(Forms!frmMain!tSystem) & '*'" & _
                "AND qryScans.Operator LIKE '*' &(Forms!frmMain!tUser) & '*'" & _
                "AND qryScans.Result LIKE '*' &(Forms!frmMain!tStatus) & '*'" & _
                "ORDER BY qryScans.Scanned;"
                
                Forms!FRmMain!test.Form.RecordSource = scandata

End Function

What I want to do is add a checkbox to the main form that allows me to modify the sort order and for the life of me cannot even get close. I am thinking something along the lines of:

Code: 
"ORDER BY IIf forms!frmMain!checkbox.value = -1,  qryScans.Scanned IIf forms!frmMain!checkbox.value <> -1,  qryScans.ID ;"

Any help would be greatly appreciated.
 
your construct would be something like

"ORDER BY qryScans." & iif(forms!frmMain!checkbox,"Scanned","ID")

Note if this code is in your form 'frmMain', you don't need Forms!frmMain! which is a roundabout way of getting the value from the form you are in.

Or since your recordset is the same, you can use the sort using the form Orderby property

if me.checkbox then me.orderby="Scanned" Else me .Orderby="ID"
me.orderbyon=true

another comment

this
"AND qryScans.Co LIKE (Forms!frmMain!cboMRO)" &

should be

"AND qryScans.Co = (Forms!frmMain!cboMRO)" &
 
Don't order by the IIf(), add the appropriate field to the SQL:

...
"ORDER BY " & IIf(...)
 
Dont include the order by on scandata string. Instead a a new string variable for the sort:
Code: 
Public Function scanresults()
'Populate results according to criteria on main form
Dim scandata As String
Dim OrderBy As String
   scandata = "SELECT  qryScans.Scanned, " & _
                "qryScans.ID, qryScans.Blade, qryScans.Engine, " & _
                "qryScans.Part, qryScans.Hours, qryScans.Cycles, " & _
                "qryScans.Result, qryScans.Tank, qryScans.Operator, " & _
                "qryScans.Details, qryScans.Co, MRO.MRO " & _
                "FROM qryScans INNER JOIN MRO ON qryScans.Co = MRO.ID " & _
                "WHERE (qryScans.Scanned BETWEEN Forms!frmMain!tStartDate And Forms!frmMain!tEndDate)" & _
                "AND qryScans.Blade LIKE '*' &(Forms!frmMain!tBlade) & '*'" & _
                "AND qryScans.Engine LIKE '*' &(Forms!frmMain!tEngine) & '*'" & _
                "AND qryScans.Part LIKE '*' &(Forms!frmMain!tPart) & '*'" & _
                "AND qryScans.Hours >= (Forms!frmMain!tHours)" & _
                "AND qryScans.Cycles >= (Forms!frmMain!tCycles)" & _
                "AND qryScans.Co LIKE (Forms!frmMain!cboMRO)" & _
                "AND qryScans.Tank LIKE '*' &(Forms!frmMain!tSystem) & '*'" & _
                "AND qryScans.Operator LIKE '*' &(Forms!frmMain!tUser) & '*'" & _
                "AND qryScans.Result LIKE '*' &(Forms!frmMain!tStatus) & '*'" 
                
                If forms!frmMain!checkbox.value = -1
                        OrderBy = "qryScans.Scanned"
                Else
                        OrderBy="qryScans.ID"
                 End If

                Forms!FRmMain!test.Form.RecordSource = scandata & " Order By " & OrderBy

End Function
 
Hi CJ, many thanks, however:
Code: 
Public Function scanresults()
'Populate results according to criteria on main form
Dim scandata As String
   scandata = "SELECT  qryScans.Scanned, " & _
                "qryScans.ID, qryScans.Blade, qryScans.Engine, " & _
                "qryScans.Part, qryScans.Hours, qryScans.Cycles, " & _
                "qryScans.Result, qryScans.Tank, qryScans.Operator, " & _
                "qryScans.Details, qryScans.Co, MRO.MRO " & _
                "FROM qryScans INNER JOIN MRO ON qryScans.Co = MRO.ID " & _
                "WHERE (qryScans.Scanned BETWEEN Forms!frmMain!tStartDate And Forms!frmMain!tEndDate)" & _
                "AND qryScans.Blade LIKE '*' &(Forms!frmMain!tBlade) & '*'" & _
                "AND qryScans.Engine LIKE '*' &(Forms!frmMain!tEngine) & '*'" & _
                "AND qryScans.Part LIKE '*' &(Forms!frmMain!tPart) & '*'" & _
                "AND qryScans.Hours >= (Forms!frmMain!tHours)" & _
                "AND qryScans.Cycles >= (Forms!frmMain!tCycles)" & _
                "AND qryScans.Co LIKE (Forms!frmMain!cboMRO)" & _
                "AND qryScans.Tank LIKE '*' &(Forms!frmMain!tSystem) & '*'" & _
                "AND qryScans.Operator LIKE '*' &(Forms!frmMain!tUser) & '*'" & _
                "AND qryScans.Result LIKE '*' &(Forms!frmMain!tStatus) & '*'" & _
                "ORDER BY qryScans." & iif(forms!frmMain!checkbox,"Scanned","ID")";
                
                Forms!frmMain!test.Form.RecordSource = scandata

End Function
This errors out without an end statement?
 
Hi Arnel,

The following is error free but always sorts by ID (or doesn't sort at all as this would logically be the order).

Code: 
Public Function scanresults()
'Populate results according to criteria on main form
Dim scandata As String
Dim OrderBy As String
   scandata = "SELECT  qryScans.Scanned, " & _
                "qryScans.ID, qryScans.Blade, qryScans.Engine, " & _
                "qryScans.Part, qryScans.Hours, qryScans.Cycles, " & _
                "qryScans.Result, qryScans.Tank, qryScans.Operator, " & _
                "qryScans.Details, qryScans.Co, MRO.MRO " & _
                "FROM qryScans INNER JOIN MRO ON qryScans.Co = MRO.ID " & _
                "WHERE (qryScans.Scanned BETWEEN Forms!frmMain!tStartDate And Forms!frmMain!tEndDate)" & _
                "AND qryScans.Blade LIKE '*' &(Forms!frmMain!tBlade) & '*'" & _
                "AND qryScans.Engine LIKE '*' &(Forms!frmMain!tEngine) & '*'" & _
                "AND qryScans.Part LIKE '*' &(Forms!frmMain!tPart) & '*'" & _
                "AND qryScans.Hours >= (Forms!frmMain!tHours)" & _
                "AND qryScans.Cycles >= (Forms!frmMain!tCycles)" & _
                "AND qryScans.Co LIKE (Forms!frmMain!cboMRO)" & _
                "AND qryScans.Tank LIKE '*' &(Forms!frmMain!tSystem) & '*'" & _
                "AND qryScans.Operator LIKE '*' &(Forms!frmMain!tUser) & '*'" & _
                "AND qryScans.Result LIKE '*' &(Forms!frmMain!tStatus) & '*'"
                
                If Forms!frmMain!Check59.Value = -1 Then
                        OrderBy = "qryScans.Scanned"
                Else
                        OrderBy = "qryScans.ID"
                 End If

                Forms!frmMain!test.Form.RecordSource = scandata & " Order By " & OrderBy

End Function
 
Inpect your subform in design view specially Order on load or similar property, set it to no.

Also edit this portion to:



If Nz(Forms!frmMain!Check59.Value, 0) = -1 Then
 
Have tried both these things with still no effect.
 
Interestingly, if I invert the ifs it sticks the other way around. It isn't a re-query issue as I can force this and it still makes no difference.
 
Currently:
Code: 
Public Function scanresults()
'Populate results according to criteria on main form
Dim scandata As String
Dim OrderBy As String
   scandata = "SELECT  qryScans.Scanned, " & _
                "qryScans.ID, qryScans.Blade, qryScans.Engine, " & _
                "qryScans.Part, qryScans.Hours, qryScans.Cycles, " & _
                "qryScans.Result, qryScans.Tank, qryScans.Operator, " & _
                "qryScans.Details, qryScans.Co, MRO.MRO " & _
                "FROM qryScans INNER JOIN MRO ON qryScans.Co = MRO.ID " & _
                "WHERE (qryScans.Scanned BETWEEN Forms!frmMain!tStartDate And Forms!frmMain!tEndDate)" & _
                "AND qryScans.Blade LIKE '*' &(Forms!frmMain!tBlade) & '*'" & _
                "AND qryScans.Engine LIKE '*' &(Forms!frmMain!tEngine) & '*'" & _
                "AND qryScans.Part LIKE '*' &(Forms!frmMain!tPart) & '*'" & _
                "AND qryScans.Hours >= (Forms!frmMain!tHours)" & _
                "AND qryScans.Cycles >= (Forms!frmMain!tCycles)" & _
                "AND qryScans.Co LIKE (Forms!frmMain!cboMRO)" & _
                "AND qryScans.Tank LIKE '*' &(Forms!frmMain!tSystem) & '*'" & _
                "AND qryScans.Operator LIKE '*' &(Forms!frmMain!tUser) & '*'" & _
                "AND qryScans.Result LIKE '*' &(Forms!frmMain!tStatus) & '*'"
                
                If Nz(Forms!frmMain!Check59.Value, 0) = -1 Then
                        OrderBy = "qryScans.ID"
                Else
                        OrderBy = "qryScans.Scanned"
                 End If

                Forms!frmMain!test.Form.RecordSource = scandata & " Order By " & OrderBy

End Function
 
Are you sure its check59 is the control?
.go to the code again.
Click on this line

If Nz(Forms!frmMain!Check59.Value, 0) = -1 Then

Then presd F9 (debug mode).

Run your form as normal chk the checkbox.
Press F8 to step through the code.
Does it pick the value if you checked the checkbox. If not then check your checkbox name again.
 
I have a textbox on my form for diagnostic purposes with it's control source =Nz(Forms!frmMain!Check59.Value, 0) so I am sure it is correct.
 
You also call the that function on the ClickEvent of the checkbox.
 
Oops, no it wasn't. As I was using an underlying query (qryscans) I assumed it would force on the form re-query but it didn't, obviously the form re-queried but the criteria for the query were unchanged.

Now works a treat and many thanks for all your help, now I may just use the same logic and remove the underlying query.
 
Hi CJ,

Many thanks for your help but I opted to go with the solution from ArnelGP.

CJ_London said:
another comment

this
"AND qryScans.Co LIKE (Forms!frmMain!cboMRO)" &

should be

"AND qryScans.Co = (Forms!frmMain!cboMRO)" &
Click to expand...

This is probably due to a bodge from long ago, to make the form load all data if no selection was made (on form load), I had a * in the default value of the combobox so it gave a value of * until something was selected, the Like used with the * made all data load.
 
One final question, I am trying to get rid of a pre-query to speed things up and have tried to add a string using the same methodology but keep falling over on the syntax.

What I am trying to do is have a search box on the main form which searches the blade field for any value but also have a checkbox that restricts it to values of eight characters if ticked.
Code: 
Public Function scanresults()
'Populate results according to criteria on main form
Dim scandata As String
Dim OrderBy As String
Dim BladesOnly As String

   scandata = "SELECT  Scans.Scanned, Len([Scans]![Blade]) AS Expr1, " & _
                "Scans.ID, Scans.Blade, Scans.Engine, " & _
                "Scans.Part, Scans.Hours, Scans.Cycles, " & _
                "Scans.Result, Scans.Tank, Scans.Operator, " & _
                "Scans.Details, Scans.Co, MRO.MRO " & _
                "FROM Scans INNER JOIN MRO ON Scans.Co = MRO.ID " & _
                "WHERE (Scans.Scanned BETWEEN Forms!frmMain!tStartDate And Forms!frmMain!tEndDate +1)" & _
                "AND  (((Scans.Blade) Like & BladesOnly & ))" & _
                "AND Scans.Engine LIKE '*' &(Forms!frmMain!tEngine) & '*'" & _
                "AND Scans.Part LIKE '*' &(Forms!frmMain!tPart) & '*'" & _
                "AND Scans.Hours >= (Forms!frmMain!tHours)" & _
                "AND Scans.Cycles >= (Forms!frmMain!tCycles)" & _
                "AND Scans.Co LIKE (Forms!frmMain!cboMRO)" & _
                "AND Scans.Tank LIKE '*' &(Forms!frmMain!tSystem) & '*'" & _
                "AND Scans.Operator LIKE '*' &(Forms!frmMain!tUser) & '*'" & _
                "AND Scans.Result LIKE '*' &(Forms!frmMain!tStatus) & '*'"
                
                If Nz(Forms!FRmMain!Check59.Value, 0) = -1 Then
                        OrderBy = "Scans.Scanned"
                Else
                        OrderBy = "DateValue(Scans.Scanned), Scans.ID"
                 End If
                 
                If Nz(Forms!FRmMain!chkFan.Value, 0) = -1 Then
                        BladesOnly = "'*' & [Forms]![frmMain]![tBlade] & '*') AND ((Len([Scans]![Blade]))=8"
                Else
                        BladesOnly = "'*' & [Forms]![frmMain]![tBlade] & '*')"
                 End If
                 
                Forms!FRmMain!test.Form.RecordSource = scandata & " Order By " & OrderBy

End Function
Any help would be greatly appreciated.
 
sorry i edit my post you already has the criteria on scandata.

Put your If...End if, for BladesOnly before your scandata:

If ... (BadesOnly test(

Else...

End If

scandata = ....


Also edit this:

"AND (((Scans.Blade) Like & BladesOnly & ))" & _




To:


"AND (((Scans.Blade) Like " & BladesOnly & "))" & _
 
Last edited:
This now works except for a syntax error when chkfan is ticked:
Code: 
Public Function scanresults()
'Populate results according to criteria on main form
Dim scandata As String
Dim OrderBy As String
Dim BladesOnly As String

   scandata = "SELECT  Scans.Scanned, Len([Scans]![Blade]) AS Expr1, " & _
                "Scans.ID, Scans.Blade, Scans.Engine, " & _
                "Scans.Part, Scans.Hours, Scans.Cycles, " & _
                "Scans.Result, Scans.Tank, Scans.Operator, " & _
                "Scans.Details, Scans.Co, MRO.MRO " & _
                "FROM Scans INNER JOIN MRO ON Scans.Co = MRO.ID " & _
                "WHERE (Scans.Scanned BETWEEN Forms!frmMain!tStartDate And Forms!frmMain!tEndDate +1)" & _
                "AND Scans.Engine LIKE '*' &(Forms!frmMain!tEngine) & '*'" & _
                "AND Scans.Part LIKE '*' &(Forms!frmMain!tPart) & '*'" & _
                "AND Scans.Hours >= (Forms!frmMain!tHours)" & _
                "AND Scans.Cycles >= (Forms!frmMain!tCycles)" & _
                "AND Scans.Co LIKE (Forms!frmMain!cboMRO)" & _
                "AND Scans.Tank LIKE '*' &(Forms!frmMain!tSystem) & '*'" & _
                "AND Scans.Operator LIKE '*' &(Forms!frmMain!tUser) & '*'" & _
                "AND Scans.Result LIKE '*' &(Forms!frmMain!tStatus) & '*'"
                
                If Nz(Forms!FRmMain!Check59.Value, 0) = -1 Then
                        OrderBy = "Scans.Scanned"
                Else
                        OrderBy = "DateValue(Scans.Scanned), Scans.ID"
                 End If
                 
                If Nz(Forms!FRmMain!chkFan.Value, 0) = -1 Then
                        BladesOnly = "Scans.Blade Like '*' & [Forms]![frmMain]![tBlade] & '*') AND ((Len([Scans]![Blade]))=8"
                Else
                        BladesOnly = "Scans.Blade Like '*' & [Forms]![frmMain]![tBlade] & '*'"
                 End If
                 
                Forms!FRmMain!test.Form.RecordSource = scandata & " And " & BladesOnly & " Order By " & OrderBy

End Function
 
This seems to work:
Code: 
Public Function scanresults()
'Populate results according to criteria on main form
Dim scandata As String
Dim OrderBy As String
Dim BladesOnly As String

   scandata = "SELECT  Scans.Scanned, Len([Scans]![Blade]) AS Expr1, " & _
                "Scans.ID, Scans.Blade, Scans.Engine, " & _
                "Scans.Part, Scans.Hours, Scans.Cycles, " & _
                "Scans.Result, Scans.Tank, Scans.Operator, " & _
                "Scans.Details, Scans.Co, MRO.MRO " & _
                "FROM Scans INNER JOIN MRO ON Scans.Co = MRO.ID " & _
                "WHERE (Scans.Scanned BETWEEN Forms!frmMain!tStartDate And Forms!frmMain!tEndDate +1)" & _
                "AND Scans.Engine LIKE '*' &(Forms!frmMain!tEngine) & '*'" & _
                "AND Scans.Part LIKE '*' &(Forms!frmMain!tPart) & '*'" & _
                "AND Scans.Hours >= (Forms!frmMain!tHours)" & _
                "AND Scans.Cycles >= (Forms!frmMain!tCycles)" & _
                "AND Scans.Co LIKE (Forms!frmMain!cboMRO)" & _
                "AND Scans.Tank LIKE '*' &(Forms!frmMain!tSystem) & '*'" & _
                "AND Scans.Operator LIKE '*' &(Forms!frmMain!tUser) & '*'" & _
                "AND Scans.Result LIKE '*' &(Forms!frmMain!tStatus) & '*'"
                
                If Nz(Forms!FRmMain!Check59.Value, 0) = -1 Then
                        OrderBy = "Scans.Scanned"
                Else
                        OrderBy = "DateValue(Scans.Scanned), Scans.ID"
                 End If
                 
                If Nz(Forms!FRmMain!chkFan.Value, 0) = -1 Then
                        BladesOnly = "Scans.Blade Like '*' & [Forms]![frmMain]![tBlade] & '*' AND (Len([Scans]![Blade]))=8"
                Else
                        BladesOnly = "Scans.Blade Like '*' & [Forms]![frmMain]![tBlade] & '*'"
                 End If
                 
                Forms!FRmMain!test.Form.RecordSource = scandata & " And " & BladesOnly & " Order By " & OrderBy

End Function
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom