Unexpected Behavior from Query

[SKV]

In my query I am calculating some fields and calling them in another calculated fields in the same query. When I run the query, it asks me to enter the parameter value for a field which I am calculating the query.

Example - I have 3 fields in the query A, B, C. I am adding new fields as
D = A+B, E=D+C, F = D+E
When I run the query, I am asked to enter value for E etc.

Can you please tell as how to resolve this.l

Thanks
SKV

 

[Joe4]

When I am creating a query with "nested" calculations like that, I find that I need to save the Query first before running it. See if that resolves the issue.

 

[SKV]

I tried that but it is working for first 1 to 2 fields but NOT after that. Also Can I use aliases in the calculations?

 

[Joe4]

Can you post the SQL of you query here so we can see exactly what it is doing?

 

[SKV]

Please see the code below with Red Statement are in question. Below is what I intend to do
[Est T1 Ship Dt] = DateAdd("ww",-4,[Calculated Ship Date])
[Monday E T1 Dt]= [Est T1 Ship Dt]+1-Weekday([Est T1 Ship Dt],2)

Now I want to extract -
[Fuel Surcharge USA tbl].[TL-/mi] from [Fuel Surcharge USA tbl] based on condition that
([Monday E T1 Dt]=[Fuel Surcharge USA tbl]![Monday_DATE])

See the detailed code below -


SELECT [PAID DATA].[Mstr/Chld/Reg Flag], [PAID DATA].[Status Bkg/Frt], [PAID DATA].[Booking Nbr], [Infocus Data].Focus_Rcd_Id, [PAID DATA].[Scac (BKG/FRT)], [PAID DATA].[Carrier Type], [Infocus Data].CARRIER_SCAC_CD, IIf([Infocus Data]![CARRIER_SCAC_CD]=[PAID DATA]![Scac (BKG/FRT)],"Yes","No") AS [SCAC Match], [PAID DATA].Division, [Infocus Data].Shipper, IIf(Left([PAID DATA]![Division],2)=[Infocus Data]![Shipper],"Yes","No") AS [Code Match], [Infocus Data].MFG_LOC_CD, [Infocus Data].[Shipper Type], [Infocus Data].Ext_Points, [Infocus Data].Total_Qty, [Infocus Data].Ext_Weight, [Infocus Data].Tot_Weight, [PAID DATA].[Total Wgt Bkg/Frt], [Infocus Data].ORIG_SHIP_TO_CITY, [Infocus Data].ORIG_SHIP_TO_POST_CD, IIf([Infocus Data]![ORIG_SHIP_TO_POST_CD]=[PAID DATA]![Consignee Zip],"Yes","No") AS [ZIP Matched], [PAID DATA].[Shipper City], [PAID DATA].[Shipper State], [PAID DATA].[Shipper Zip], [PAID DATA].[Consignee City], [PAID DATA].[Consignee State], [PAID DATA].[Consignee Zip], [PAID DATA].[Paid Carrier Line Haul], [PAID DATA].[Paid Carrier Acs], [PAID DATA].[Shipper Reference], [Infocus Data].SHIPPER_NO, [Infocus Data].Miles, [PAID DATA].[Calculated Ship Date], [PAID DATA].[Mon #], ([Infocus Data]![Ext_Weight]/[Infocus Data]![Tot_Weight])*[PAID DATA]![Paid Carrier Line Haul] AS [Alloc LH], ([Infocus Data]![Ext_Weight]/[Infocus Data]![Tot_Weight])*[PAID DATA]![Paid Carrier Acs] AS [Alloc FSC], [PAID DATA].Year, [PAID DATA].Qtr, [ELUX_footprint]![2009 - Hndlg Cost per Unit] AS [Hndlg CPU], [T1 Cost tbl].[CPU wo Fuel] AS T1CPU, [Alloc LH]+[Total_Qty]*([Hndlg CPU]+[T1CPU]) AS [Realised Cost], [Realised Cost]/[Total_Qty] AS [Baseline CPU], DateAdd("ww",-4,[Calculated Ship Date]) AS [Est T1 Ship Dt], [Est T1 Ship Dt]+1-Weekday([Est T1 Ship Dt],2) AS [Monday E T1 Dt], [Fuel Surcharge USA tbl].[TL-/mi]
FROM [Fuel Surcharge USA tbl], ELUX_footprint INNER JOIN ([T1 Cost tbl] INNER JOIN ([PAID DATA] INNER JOIN [Infocus Data] ON ([PAID DATA].[Shipper Reference] = [Infocus Data].SHIPPER_NO) AND ([PAID DATA].[Consignee Zip] = [Infocus Data].ORIG_SHIP_TO_POST_CD)) ON ([T1 Cost tbl].Shipper = [Infocus Data].Shipper) AND ([T1 Cost tbl].MFG_LOC_CD = [Infocus Data].MFG_LOC_CD)) ON ELUX_footprint.Facility_Code = [PAID DATA].Division
WHERE ((([PAID DATA].[Mstr/Chld/Reg Flag])<>"C") AND (([PAID DATA].[Status Bkg/Frt])>"49") AND (([PAID DATA].[Carrier Type])="LTL") AND (([Infocus Data].Shipper) Like "22*") AND (([Infocus Data].MFG_LOC_CD) Like "51*") AND (([Infocus Data].[Shipper Type])="RDC") AND (([PAID DATA].Year)="2008") AND ([Monday E T1 Dt]=[Fuel Surcharge USA tbl]![Monday_DATE]));

Can you post the SQL of you query here so we can see exactly what it is doing?

 

[Joe4]

That's quite the query.
I'll try to disect it tonight, when I have more time to dive into it.

 

[Joe4]

I think I see your problem. You can use Aliases in calculations in your SELECT statements, but you cannot use them in WHERE clauses. So with your last criteria;

AND ([Monday E T1 Dt]=[Fuel Surcharge USA tbl]![Monday_DATE]));

You would need to replace [Monday E T1 Dt] with the actual calculation.

 

[SKV]

Thanks, I was also thinking of the same thing and your confirmation matched my logic too

I think I see your problem. You can use Aliases in calculations in your SELECT statements, but you cannot use them in WHERE clauses. So with your last criteria;

AND ([Monday E T1 Dt]=[Fuel Surcharge USA tbl]![Monday_DATE]));

You would need to replace [Monday E T1 Dt] with the actual calculation.