Weird query issue (1 Viewer)

smig

Registered User.
Local time
Today, 22:02
Joined
Nov 25, 2009
Messages
2,209
Hi

can anyone tell me why this query won't work and will ask for the [EssentialOilsShow] for the ORDER BY clue ?
It will work perfectly If I remove this field from the ORDER BY
It will sort perfectly for any other field [ProductName], [ProductPrice]...

Thanks
Code:
SELECT [Products].*, fnEssentialOilsShow([EssentialOilsCalc],[EssentialOilsQty],[BaseMaterialsQty],[EssentialOilsPercent]) AS [EssentialOilsShow] FROM [Products] WHERE [Products].[ProductID] Is Not Null  ORDER BY [EssentialOilsShow] DESC, [Products].[ProductID] DESC


Public Function fnEssentialOilsShow(byEssentialOilsCalc As Byte, intEssentialOilsQty, intBaseMaterialsQty, bytEssentialOilsPercent) As String

Select Case byEssentialOilsCalc
    Case 1
        fnEssentialOilsShow = intEssentialOilsQty & " Drops / " & intBaseMaterialsQty & "ml"
    Case 2, 3, 4
        fnEssentialOilsShow = Format(bytEssentialOilsPercent, "0.0") & "%"
    Case 5
        fnEssentialOilsShow = ""
    Case Else
        fnEssentialOilsShow = ""
End Select

End Function
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 16:02
Joined
Jan 23, 2006
Messages
15,364
Please show the details for
fnEssentialOilsShow
 

smig

Registered User.
Local time
Today, 22:02
Joined
Nov 25, 2009
Messages
2,209
Please show the details for
fnEssentialOilsShow

Public Function fnEssentialOilsShow(byEssentialOilsCalc As Byte, intEssentialOilsQty, intBaseMaterialsQty, bytEssentialOilsPercent) As String

Select Case byEssentialOilsCalc
Case 1
fnEssentialOilsShow = intEssentialOilsQty & " Drops / " & intBaseMaterialsQty & "ml"
Case 2, 3, 4
fnEssentialOilsShow = Format(bytEssentialOilsPercent, "0.0") & "%"
Case 5
fnEssentialOilsShow = ""
Case Else
fnEssentialOilsShow = ""
End Select

End Function
 

Minty

AWF VIP
Local time
Today, 20:02
Joined
Jul 26, 2013
Messages
10,355
Don't you have to order by the function Call and not its alias? e.g.

Order By fnEssentialOilsShow([EssentialOilsCalc],[EssentialOilsQty],[BaseMaterialsQty],[EssentialOilsPercent]) DESC, [Products].[ProductID] DESC


You certainly would in SQL server
 

smig

Registered User.
Local time
Today, 22:02
Joined
Nov 25, 2009
Messages
2,209
Don't you have to order by the function Call and not its alias? e.g.

Order By fnEssentialOilsShow([EssentialOilsCalc],[EssentialOilsQty],[BaseMaterialsQty],[EssentialOilsPercent]) DESC, [Products].[ProductID] DESC


You certainly would in SQL server

Not what I hoped, but this one work
 

HiTechCoach

Well-known member
Local time
Today, 15:02
Joined
Mar 6, 2006
Messages
4,357
If the query is a record source for a form or report, I find it better to do the ordering/sorting at the form and report level where you can use the alias name for ordering/sorting.

In your case, doing the ordering at the report/form level will probably prevent the function from running twice.
 

smig

Registered User.
Local time
Today, 22:02
Joined
Nov 25, 2009
Messages
2,209
If the query is a record source for a form or report, I find it better to do the ordering/sorting at the form and report level where you can use the alias name for ordering/sorting.

In your case, doing the ordering at the report/form level will probably prevent the function from running twice.
Thanks, but I'm not sure I understand what you mean

In my case I have an unordered query for the form.
Clicking a button on the form will recreate the Form's record set based on the sort I ask.

Do you mean using the Form.OrderBy Property ?

Does it apply also for Filtering the Form rather then the RecordSource itself?
 
Last edited:

Users who are viewing this thread

Top Bottom