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?
Printable View
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?
I cannot tell but you can always do something like this:
And then you can use it like:Quote:
SELECT Orders.OrderID As O_OrderId, Items.OrderID As I_OrderId FROM Orders, Items WHERE SellID = 1
VB Code:
adoRecordset!O_OrderID
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?
Yes!! The error is that you did not do what I told you to.
VB Code:
"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
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
As far as I can tellis a perfectly valid SQL statement.Quote:
SELECT Orders.OrderID, Orders.CustomerID, Orders.StartDate, Orders.ExpiryDate, Magazines.Price, Orders.Status
FROM Magazines INNER JOIN Orders ON Magazines.MagazineID = Orders.MagazineID
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.
Try this:
and tell me which line do you get the error at.VB Code:
"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
sorry yall! i forgot to change one of the field names to one of the alisas ! my bad !