Help with Function in query 1

[NeilT123]

I use a field ApplicationOrder to sort the records in my database. ApplicationOrder is numeric and I want to sort and name the ApplicationOrder and save the name as TimingLabel in a query.

I am stepping into new ground but have created the following function

Public Function CreateTimingLabel(ApplicationOrder As Double) As String

    Select Case ApplicationOrder
        Case Is < 0.9
            ShortTimingComment = "Sept/Oct"
        Case Is < 1.9
            ShortTimingComment = "Jan"
        Case Is < 2.29
            ShortTimingComment = "E Feb"
        Case Is < 2.69
            ShortTimingComment = "Feb"
        Case Is < 2.99
            ShortTimingComment = "L Feb"
        Case Is < 3.29
            ShortTimingComment = "E Mar"
        Case Is < 3.69
            ShortTimingComment = "Mar"
        Case Is < 3.99
            ShortTimingComment = "L Mar"
        Case Is < 4.29
            ShortTimingComment = "E Apr"
        Case Is < 4.69
            ShortTimingComment = "Apr"
        Case Is < 4.99
            ShortTimingComment = "L Apr"
        Case Is < 5.29
            ShortTimingComment = "E May"
        Case Is < 5.69
            ShortTimingComment = "May"
        Case Is < 5.99
            ShortTimingComment = "L May"
        Case Is < 6.99
            ShortTimingComment = "June"
        Case Is < 7.99
            ShortTimingComment = "July"
        Case Is < 8.99
            ShortTimingComment = "Aug"
        Case Else 'this means the case is something unexpected
            ShortTimingComment = "Unspec"
End Select

End Function

and then my query is

SELECT qrySLTReport.CroppingYear, qrySLTReport.AccountName, tblFieldDetails.FieldName, tblVariety.Variety, Round(([NContent]*[ApplicationRate])/1000) AS NApplied, tblFertApplns.ApplicationIndex, tblFertApplns.ApplicationOrder, CreateTimingLabel([ApplicationOrder]) AS ShortTimingComment
FROM tblFertProduct INNER JOIN (((qrySLTReport INNER JOIN tblFieldDetails ON qrySLTReport.FarmAccountNumber = tblFieldDetails.FarmAccountNumber) INNER JOIN (tblCropping INNER JOIN tblVariety ON tblCropping.VarietyID = tblVariety.VarietyID) ON (tblFieldDetails.FieldCode = tblCropping.FieldCode) AND (qrySLTReport.CroppingYear = tblCropping.CroppingYear)) INNER JOIN (tblApplnTimings INNER JOIN tblFertApplns ON tblApplnTimings.TimingCode = tblFertApplns.TimingCode) ON tblCropping.CroppingNumber = tblFertApplns.CroppingNumber) ON tblFertProduct.ProductIndex = tblFertApplns.ProductIndex
WHERE (((Round(([NContent]*[ApplicationRate])/1000))>0))
ORDER BY tblFieldDetails.FieldName;

but when I run the query ShortTimingComment is empty.

As said earlier this is new to me and had there not been so many "cases" I would have gone with a massive IIF statement but does anyone have any suggestions or observations as to what I have done wrong?

Thank you in advance for any suggestions.

 

[dhookom]

NealT1123,
IMO, Data belongs in your tables. Congratulation on not attempting to do this with a massive IIf() statement. Do you plan on changing your code when your Short Timing ranges or comments change?

BTW: your function names don't match. ShortTimingComment and CreateTimingLabel

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

Thanks Duane for pointing me in the right direction. After lots of searching my function works except for one glitch. The code is as below and the problem is that when I run the query based on Shortlabel: FertApplnLabel([ApplicationOrder]) it doesn't return any of the results with a space so "E Feb" and "L Feb" comes back as "Feb".

Is this because I am saving FertApplnLabel as a string? Are there any options I can use to get the correct results?

Public Function FertApplnLabel(ApplicationOrder As Integer) As String

'FertApplnLabel() converts an ApplicationOrder to a short description.
'For example, FertApplnLabel(2.1) returns "E Feb", (2) = Feb (2.8) = L Feb
           
    Select Case ApplicationOrder
        Case Is < 0.9
            FertApplnLabel = "Sept/Oct"
        Case Is < 1.9
            FertApplnLabel = "Jan"
        Case Is < 2.29
            FertApplnLabel = "E Feb"
        Case Is < 2.69
            FertApplnLabel = "Feb"
        Case Is < 2.99
            FertApplnLabel = "L Feb"
        Case Is < 3.29
            FertApplnLabel = "E Mar"
        Case Is < 3.69
            FertApplnLabel = "Mar"
        Case Is < 3.99
            FertApplnLabel = "L Mar"
        Case Is < 4.29
            FertApplnLabel = "E Apr"
        Case Is < 4.69
            FertApplnLabel = "Apr"
        Case Is < 4.99
            FertApplnLabel = "L Apr"
        Case Is < 5.29
            FertApplnLabel = "E May"
        Case Is < 5.69
            FertApplnLabel = "May"
        Case Is < 5.99
            FertApplnLabel = "L May"
        Case Is < 6.99
            FertApplnLabel = "June"
        Case Is < 7.99
            FertApplnLabel = "July"
        Case Is < 8.99
            FertApplnLabel = "Aug"
        Case Else 'this means the case is something unexpected
            FertApplnLabel = "Unspec"
            
End Select

End Function

 

[NeilT123]

Sorted by changing

Public Function FertApplnLabel(ApplicationOrder As Integer) As String

to

Public Function FertApplnLabel(ApplicationOrder As Double) As String