Val Function Issue

[mrssevans]

I am trying to use a form to sort and filter a report with an underlying query. Everything works great except one thing. I have a field, Lot #, that I had to setup as a text field because it can contain a letter at the end from time to time, so when I sort by that field it sort incorrectly. (ie, 102, 1102, 11102, etc) I know that I can fix it with the val function, but when I put it in my query it doesn't make any difference. I will list my sql for the query below.

SELECT Closeout.ID, Closeout.Owner, Closeout.Feedyard, Closeout.[Lot #], Closeout.[Ctl Cost], Closeout.[Sales Proceeds], Closeout.[Lot Chgs], Closeout.[P/L], Closeout.Hedging, Closeout.[# Head Out], Closeout.Sex, Closeout.[$$$ Avg In], Closeout.[LBS Avg In], Closeout.[Proj Av In], Closeout.[$$$ Avg Out], Format([Act Date In],"mm-yy") AS Act_Date_In, Closeout.[Proj Date In], Closeout.[Act Avg Out], Closeout.[OKC Avg Out], Closeout.[Fdyd Proj Avg Out], Closeout.[Act DL %], Closeout.[OKC DL %], Closeout.[Fdyd Proj DL %], Closeout.[Act DOF], Closeout.[OKC DOF], Closeout.[Fdyd Proj DOF], Closeout.[Act ADG], Closeout.[OKC ADG], Closeout.[Fdyd Proj ADG], Closeout.[Act/Dry CONV], Closeout.[Act/AF Conv], Closeout.[OKC CONV], Closeout.[Fdyd Proj CONV], Closeout.[Act COG], Closeout.[OKC COG], Closeout.[Fdyd Proj COG], Closeout.[Act BE], Closeout.[OKC BE], Closeout.[Fdyd Proj BE], Closeout.Buyer, Closeout.[Act Feed $], Closeout.[Act Consumption], Closeout.[Proj Ration Price], Closeout.[Positions On], Closeout.Short, Closeout.Long, Format(Closeout![Act Feed $]/(Closeout![Act Consumption]*(Closeout![Act/Dry CONV]/Closeout![Act/AF Conv]))*100,"#.00") AS [Act Ration Price], Format(Closeout![# Head Out]/33,"#.00") AS [Positions Req], Closeout![$$$ Avg Out]-Closeout!Long AS [Basis/Cwt], WeightRange([LBS Avg In]) AS WeightGroup, [Short]-[OKC BE] AS [BE vs SHT], [$$$ Avg In]*[# Head Out] AS [$ AVG IN], [LBS Avg In]*[# Head Out] AS LBS_AVG_IN, [Proj Av In]*[# Head Out] AS PROJ_LBS_IN, [$$$ Avg Out]*[# Head Out] AS [$ AVG OUT], [Act Avg Out]*[# Head Out] AS Act_Avg_Out, [OKC Avg Out]*[# Head Out] AS OKC_AVG_OUT, [Fdyd Proj Avg Out]*[# Head Out] AS Fdyd_Proj_Avg_Out, [Act DL %]*[# Head Out] AS [ACT_DL_%], [OKC DL %]*[# Head Out] AS [OKC_DL_%], [Fdyd Proj DL %]*[# Head Out] AS [FDYD_PROJ_DL_%], [Act DOF]*[# Head Out] AS ACT_DOF, [OKC DOF]*[# Head Out] AS OKC_DOF, [Fdyd Proj DOF]*[# Head Out] AS FDYD_PROJ_DOF, [Act ADG]*[# Head Out] AS ACT_ADG, [OKC ADG]*[# Head Out] AS OKC_ADG, [Fdyd Proj ADG]*[# Head Out] AS FDYD_PROJ_ADG, [Act/Dry CONV]*[# Head Out] AS ACT_CONV, [Act/AF Conv]*[# Head Out] AS ACT_AF_CONV, [OKC CONV]*[# Head Out] AS OKC_CONV, [Fdyd Proj CONV]*[# Head Out] AS FDYD_PROJ_CONV, [Act COG]*[# Head Out] AS ACT_COG, [OKC COG]*[# Head Out] AS OKC_COG, [Fdyd Proj COG]*[# Head Out] AS FDYD_PROJ_COG, [Act BE]*[# Head Out] AS ACT_BE, [OKC BE]*[# Head Out] AS OKC_BE, [Fdyd Proj BE]*[# Head Out] AS FDYD_PROJ_BE, [Proj Ration Price]*[# Head Out] AS PROJ_RATION_PRICE, ([Act Feed $]/([Act Consumption]*([Act/Dry CONV]/[Act/AF Conv]))*100)*[# Head Out] AS ACT_RATION_PRICE, [Short]*[Positions On] AS SHORTS, [Long]*[Positions On] AS LONGS, Format([DateOut],"mm-yy") AS [Date Out], IIf([Forms]![Report Sort]![Combo7]="Buyer",[Buyer],IIf([Forms]![Report Sort]![Combo7]="Feedyard",[Feedyard],IIf([Forms]![Report Sort]![Combo7]="Sex",[Sex],IIf([Forms]![Report Sort]![Combo7]="WeightGroup",[LBS Avg In],IIf([Forms]![Report Sort]![Combo7]="DateOut",[DateOut],IIf([Forms]![Report Sort]![Combo7]="Lot #",Val([Lot #]))))))) AS Expr2, IIf([Forms]![Report Sort]![Combo9]="Buyer",[Buyer],IIf([Forms]![Report Sort]![Combo9]="Feedyard",[Feedyard],IIf([Forms]![Report Sort]![Combo9]="Sex",[Sex],IIf([Forms]![Report Sort]![Combo9]="WeightGroup",[LBS Avg In],IIf([Forms]![Report Sort]![Combo9]="DateOut",[DateOut],IIf([Forms]![Report Sort]![Combo9]="Lot #",Val([Lot #]))))))) AS Expr1, IIf([Forms]![Report Sort]![Combo10]="Buyer",[Buyer],IIf([Forms]![Report Sort]![Combo10]="Feedyard",[Feedyard],IIf([Forms]![Report Sort]![Combo10]="Sex",[Sex],IIf([Forms]![Report Sort]![Combo10]="WeightGroup",[LBS Avg In],IIf([Forms]![Report Sort]![Combo10]="DateOut",[DateOut],IIf([Forms]![Report Sort]![Combo10]="Lot #",Val([Lot #]))))))) AS Expr3
FROM Closeout
ORDER BY IIf([Forms]![Report Sort]![Combo7]="Buyer",[Buyer],IIf([Forms]![Report Sort]![Combo7]="Feedyard",[Feedyard],IIf([Forms]![Report Sort]![Combo7]="Sex",[Sex],IIf([Forms]![Report Sort]![Combo7]="WeightGroup",[LBS Avg In],IIf([Forms]![Report Sort]![Combo7]="DateOut",[DateOut],IIf([Forms]![Report Sort]![Combo7]="Lot #",Val([Lot #]))))))), IIf([Forms]![Report Sort]![Combo9]="Buyer",[Buyer],IIf([Forms]![Report Sort]![Combo9]="Feedyard",[Feedyard],IIf([Forms]![Report Sort]![Combo9]="Sex",[Sex],IIf([Forms]![Report Sort]![Combo9]="WeightGroup",[LBS Avg In],IIf([Forms]![Report Sort]![Combo9]="DateOut",[DateOut],IIf([Forms]![Report Sort]![Combo9]="Lot #",Val([Lot #]))))))), IIf([Forms]![Report Sort]![Combo10]="Buyer",[Buyer],IIf([Forms]![Report Sort]![Combo10]="Feedyard",[Feedyard],IIf([Forms]![Report Sort]![Combo10]="Sex",[Sex],IIf([Forms]![Report Sort]![Combo10]="WeightGroup",[LBS Avg In],IIf([Forms]![Report Sort]![Combo10]="DateOut",[DateOut],IIf([Forms]![Report Sort]![Combo10]="Lot #",Val([Lot #])))))));

 

[Eddie Mason]

try sorting the text field that you have applied the Val function to:

SELECT Val([Test]) AS fldTest
FROM tbltest
ORDER BY Val([Test]);

Regards

Eddie

 

[mrssevans]

Thanks for the reply Eddie...It will sort just fine if I make the field val([lot #]), but I don't always want to sort this field. The user will define which fields they want to sort by selecting from a combo box. I ended up making another query and making one field that is just the val([lot #]) and then querying that query for the report. Not the best way, but I don't know any other way. Open to suggestions...