Access SubForms Columns Not Computing Values in SQL Server

• crhill6

  Grasshopper

  Points: 23

  More actions

  July 26, 2004 at 3:56 pm

  #66094

  Hello,

  Can anyone tell me why I am getting this error on my Order SubForm?

  --- Error: "The column prefix Order Details does not match with a table name or alias name use in the query."

  The original SQL Stmt is Select * from Order Details Extended.  The Discount Quantity and ExtendedPrice are not computing the correct values.   They are display from the original SQL, but when inserting a record the values do not compute.

  -- Therefore I create a store procedure based on the original Access DB and called the sp from the form and I get the error above.  Here is the code:

  SELECT [Order Details].OrderID,

  [Order Details].ProductID, Products.ProductName,

  [Order Details].UnitPrice, [Order Details].Quantity,

  [Order Details].Discount,

  ([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice

  FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID

  ORDER BY [Order Details].OrderID;

  Thanks.

   

• Jonathan Stokes

  SSCrazy Eights

  Points: 9861

  More actions

  July 28, 2004 at 3:00 am

  #516516

  One thing that stands out is the line -

  ([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice.

  You are not qualifying the quantity or discount by giving them table names. Could be picking the wring column out.

  You are also using [] for one table and not the other. I would put brackets around both tables and also the column names.

  Also your logic does not work. Try this -

  ([Order Details].[UnitPrice]*[Order Details].[Quantity])*(100-[Order Details].[Discount])/100 AS ExtendedPrice

  ---

  ------------------------------
  The Users are always right - when I'm not wrong!