Re:SQL syntax for access

[trance4eva]

is it valid syntax to refer to a field in a db ilke this:

adoRecordset!Orders.OrderID ?

i have to do this because my SQL statement needs to distinguish this field form anothre field with the same name in an other table?

[Mc Brain]

I cannot tell but you can always do something like this:

SELECT Orders.OrderID As O_OrderId, Items.OrderID As I_OrderId FROM Orders, Items WHERE SellID = 1

And then you can use it like:

adoRecordset!O_OrderID

[trance4eva]

that did not work

here is my sql statement that is generated from access

SELECT Orders.OrderID, Orders.CustomerID, Orders.StartDate, Orders.ExpiryDate, Magazines.Price, Orders.Status
FROM Magazines INNER JOIN Orders ON Magazines.MagazineID = Orders.MagazineID


Debug.Print adoRecordset!OrderID, adoRecordset!CustomerID, adoRecordset!StartDate, adoRecordset!ExpriyDate,

is there an error in here?

[Mc Brain]

Yes!! The error is that you did not do what I told you to.

"SELECT Orders.OrderID [b]AS O_OrderID[/b], " & _
  "Orders.CustomerID AS [b]O_CustomerID[/b], " & _
  "Orders.StartDate [b]AS O_StartDate[/b], Orders.ExpiryDate [b]AS O_ExpriyDate[/b], " & _
  "Magazines.Price, Orders.Status " & _
  "FROM Magazines INNER JOIN Orders ON Magazines.MagazineID = Orders.MagazineID"
 
Debug.Print adoRecordset!O_OrderID, adoRecordset!O_CustomerID, _
  adoRecordset!O_StartDate, adoRecordset!O_ExpriyDate

[trance4eva]

I DID DO THAT ! I WAS LISTENING! but it aint working ! the error message that i am getting is :

item cannot be found in the collectin corresponding to the requested name or ordinal

[Aaron Young]

As far as I can tell

SELECT Orders.OrderID, Orders.CustomerID, Orders.StartDate, Orders.ExpiryDate, Magazines.Price, Orders.Status
FROM Magazines INNER JOIN Orders ON Magazines.MagazineID = Orders.MagazineID

is a perfectly valid SQL statement.

You're not selecting multiple fields with the same field name.

Use McBrains suggestion if you plan to extract fields from multiple tables where the names of the fields are the same.

[Mc Brain]

Try this:

"SELECT Orders.OrderID AS O_OrderID, " & _
  "Orders.CustomerID AS O_CustomerID, " & _
  "Orders.StartDate AS O_StartDate, Orders.ExpiryDate AS O_ExpriyDate, " & _
  "Magazines.Price, Orders.Status " & _
  "FROM Magazines INNER JOIN Orders ON Magazines.MagazineID = Orders.MagazineID"
 
Debug.Print adoRecordset!O_OrderID
Debug.Print  adoRecordset!O_CustomerID,
Debug.Print adoRecordset!O_StartDate
Debug.Print adoRecordset!O_ExpriyDate

and tell me which line do you get the error at.

[trance4eva]

sorry yall! i forgot to change one of the field names to one of the alisas ! my bad !