Phantom Fields in Query

[MacDaddyNickP]

I have inherited a database which uses numerous nested queries to execute business logic. In drilling down into the database I have identified some of the logic in the queries, which is implemented using IIf() functions to test the values of fields in the underlying queries/tables against each other. Now my problem is this, when I look at the available fields in the QBE window in one of the underlying queries, four fields are listed that do not correspond to fields in the underlying query when that query is viewed in the QBE grid. In other words, Access is putting four additional fields into the "master" query view of the underlying query. I am stumped... The query is not defined dynamically in code, so its definition isn't changed, it just doesn't make sense. Can anyone offer a suggestion as to how this could be happening. When I delete the underlying query from the master query and then re-add it to the master query QBE screen, the phantom fields are gone! Help!

 

[Remou]

Perhaps you could post the SQL?

 

[MacDaddyNickP]

This is the SQL code for the "master Query":

SELECT DISTINCT qryD3_Install_Data.[Link #], qryD3_Install_Data.[Order #], qryD3_Install_Data.[Customer #], qryD3_Install_Data.[PO #], qryD3_Install_Data.Category, PFR_Date_Range_List.PFR_Name_Common, qryD3_Install_Data.[Sub Category], qryD3_Install_Data.[Invoice Date], qryD3_Install_Data.[Hard Drive 1 Desc], Format(qryD3_Install_Data![Invoice Date],"yyyy-mm") AS Invoice_Month, qryD3_Install_Data.[Service Tag], qryD3_Install_Data.[Ship Company], qryD3_Install_Data.[Ship Address 1], qryD3_Install_Data.[Ship Address 2], qryD3_Install_Data.[Ship City], qryD3_Install_Data.[Ship State], qryD3_Install_Data.Age, qryD3_Install_Data.Chassis
FROM PFR_Date_Range_List, qryD3_Install_Data_Suspect AS qryD3_Install_Data
WHERE (((qryD3_Install_Data.[Service Tag])<>"" And (qryD3_Install_Data.[Service Tag]) Is Not Null) AND ((IIf(IsNull([PFR_Date_Range_List]![Chassis_Inclusion]),1,IIf([qryD3_Install_Data]![Chassis] Like [PFR_Date_Range_List]![Chassis_Inclusion] & "*",1,0)))=1) AND ((IIf(IsNull([PFR_Date_Range_List]![Chassis_Exclusion]),1,IIf([qryD3_Install_Data]![Chassis] Like [PFR_Date_Range_List]![Chassis_Exclusion] & "*",0,1)))=1) AND ((IIf(IsNull([PFR_Date_Range_List]![SubCategory]),1,IIf([qryD3_Install_Data].[Sub Category] Like [PFR_Date_Range_List]![SubCategory] & "*",1,0)))=1) AND ((IIf([PFR_Date_Range_List]![Invoice_Date_Start] Is Null,1,IIf([qryD3_Install_Data]![Invoice Date]>=[PFR_Date_Range_List]![Invoice_Date_Start],IIf([qryD3_Install_Data]![Invoice Date]<=[PFR_Date_Range_List]![Invoice_Date_End],1,0))))=1) AND ((IIf([PFR_Date_Range_List]![Hard_Drive_Exclusion] Is Null,1,IIf([qryD3_Install_Data]![Hard Drive 1 Desc] Like "*" & [PFR_Date_Range_List]![Hard_Drive_Exclusion] & "*",0,0)))=1) AND ((IIf([PFR_Date_Range_List]![Hard_Drive_Inclusion] Is Null,1,IIf([qryD3_Install_Data]![Hard Drive 1 Desc] Like "*" & [PFR_Date_Range_List]![Hard_Drive_Inclusion] & "*",1,0)))=1) AND ((IIf(IsNull([PFR_Date_Range_List]![PPID_Part_Number]),1,IIf([PFR_Date_Range_List]![PPID_Part_Number]=[qryD3_Install_Data]![Item Num],1,0)))=1));

The phantom field is [qryD3_Install_Data]![Item Num]. No such field exists in the query, nor is it in the SELECT clause of the SQL. This field only shows up in the QBE screen in the query I imported.

 

[PHV]

No such field exists in the query
Really ?
AND ((IIf(IsNull([PFR_Date_Range_List]![PPID_Part_Number]),1,IIf([PFR_Date_Range_List]![PPID_Part_Number]=[!][qryD3_Install_Data]![Item Num][/!],1,0)))=1));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MacDaddyNickP]

PHV,

What I'm saying is that the query qryD3_Install_Data does not have a field called [Item Num]. Here is the SQL for that query, you'll note there is no such field.

SELECT DISTINCT D3_TAG_Data.[Link Number] AS [Link #], D3_TAG_Data.[Customer Number] AS [Customer #], D3_TAG_Data.PO AS [PO #], D3_TAG_Data.[Original Order Number] AS [Order #], D3_Install_Data.[Order Date] AS [Order Date], D3_Install_Data.[Ship Date] AS [Ship Date], D3_TAG_Data.[Invoice Date] AS [Invoice Date], D3_Install_Data.[Order Qty], D3_Install_Data.[Report Line Qty], D3_Install_Data.[Report Line Price], D3_Install_Data.[Report Line Tax], D3_Install_Data.[Report Line Shipping], D3_Install_Data.[Report Line Total], D3_TAG_Data.[Service Tag Number] AS [Service Tag], D3_TAG_Data.[Product Description] AS [Sub Category], D3_TAG_Data.[System Name] AS Category, D3_Install_Data.Item, D3_Install_Data.[Sku Long Name], D3_Install_Data.[Processor Type], D3_Install_Data.[Processor Desc], D3_Install_Data.[Processor Speed (MHz)], D3_Install_Data.[Additional Processor Desc], D3_Install_Data.[Monitor Desc], D3_Install_Data.[Monitor Qty], D3_Install_Data.[Memory 1 Desc], D3_Install_Data.[Memory 1 Qty], D3_Install_Data.[Memory 2 Desc], D3_Install_Data.[Memory 2 Qty], D3_Install_Data.[Hard Drive 1 Desc], D3_Install_Data.[Hard Drive 1 Qty], D3_Install_Data.[Hard Drive 2 Desc], D3_Install_Data.[Hard Drive 2 Qty], D3_Install_Data.[CD/DVD 1 Desc], D3_Install_Data.[CD/DVD 1 Qty], D3_Install_Data.[CD/DVD 2 Desc], D3_Install_Data.[CD/DVD 2 Qty], D3_Install_Data.[OS Desc], D3_Install_Data.[OS Qty], D3_TAG_Data.[Service Contract End Date] AS [Warranty End Date], D3_Install_Data.[Ship First Name], D3_Install_Data.[Ship Last Name], D3_TAG_Data.[Company Name] AS [Ship Company], D3_TAG_Data.[Address 1] AS [Ship Address 1], D3_TAG_Data.[Address 2] AS [Ship Address 2], D3_TAG_Data.City AS [Ship City], D3_TAG_Data.State AS [Ship State], D3_TAG_Data.Zip AS [Ship Zip], Date()-D3_Tag_Data.[Invoice Date] AS Age, IIf(IsNull(PFR_Data!chassistype),IIf(D3_Tag_Data![Product Description]="Optiplex GX270",IIf(D3_Install_Data![Sku Long Name] Like "*SMALL*FORM*FACTOR*","Killerbee",IIf(D3_Install_Data![Sku Long Name] Like "*SMALL*DESKTOP*","Jazz",IIf(D3_Install_Data![Sku Long Name] Like "*SMALL*TOWER*","Skydive","??"))),""),PFR_Data!chassistype) AS Chassis_Old, IIf(D3_Tag_Data![Product Description] In ("Optiplex GX280","Optiplex GX270","Optiplex GX260"),IIf(IsNull(D3_Tag_Data!Chassis),IIf(D3_Install_Data![Sku Long Name] Like "*SMALL*FORM*FACTOR*","Killerbee",IIf(D3_Install_Data![Sku Long Name] Like "*SMALL*DESKTOP*","Jazz",IIf(D3_Install_Data![Sku Long Name] Like "*SMALL*TOWER*","Skydive","??"))),IIf(D3_Tag_Data!Chassis Like "Skydive*","Skydive",IIf(D3_Tag_Data!Chassis Like "Jazz*","Jazz",IIf(D3_Tag_Data!Chassis Like "Killerbee*","Killerbee",D3_Tag_Data!Chassis)))),IIf(PFR_Data!chassistype="Jazz SDT","Jazz",PFR_Data!chassistype)) AS Chassis
FROM ((D3_TAG_Data LEFT JOIN D3_Install_Data ON D3_TAG_Data.[Service Tag Number] = D3_Install_Data.[Service Tag]) LEFT JOIN PFR_Data ON D3_TAG_Data.[Service Tag Number] = PFR_Data.svctag) LEFT JOIN qryChassis_Type_GX270 ON D3_TAG_Data.[Service Tag Number] = qryChassis_Type_GX270.[Svc Tag ID]
WHERE (((D3_TAG_Data.[Service Tag Number])<>"" And (D3_TAG_Data.[Service Tag Number]) Is Not Null));


So the question is, how is this field showing up in the QBE screen's query/table display when it doesn't exist in the query?

 

[Golom]

Perhaps it's just a confusion of aliases but you stated thet this is the code for qryD3_Install_Data. However the query being referenced in your original post is qryD3_Install_Data_Suspect.

qryD3_Install_Data is only an alias in that query. Is the last post really the code for qryD3_Install_Data_Suspect?

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[MacDaddyNickP]

Golom,

That was it! I didn't see the Alias. Thanx so much.