identifying a field in a recordset consisting of 2 tables 1

[nimarii]

I've created a recordset based on 2 tables (orders & customers) but i can't access a specific field. here's the sql statement:

strsql = "SELECT * from t_orders, t_Customers WHERE t_orders.orderstatus = 'New' AND t_orders.customerid = t_customers.customerid"

in my code, i need to access the field "contactname" which exists in both tables, and i get an error message saying that the field doesn't exist. i tried changing the sql statement to:

strsql = "SELECT *, contactname from t_orders, t_Customers WHERE t_orders.orderstatus = 'New' AND t_orders.customerid = t_customers.customerid"

and afterwards, i received an error message saying: "the specified field could refer to more than one table listed in the FROM clause of your sql statement"

how do i access the field "contactname"....?

any help would be greatly appreciated!

 

[chris153]

Hi,

You need to link the two tables in the sql statement.

Try building it as a query and copying the sql, it'll look something like this :

SELECT t_orders.order_status, t_orders.Contact_name
FROM t_customer INNER JOIN t_orders ON t_customer.customer_ID = t_orders.Customer_ID
WHERE (((t_orders.order_status)="NEW"));

 

[TonyJollans]

Hi nimarii,

The reason is that there are two fields called contactname in your recordset. To differentiate between them, they are qualified with the table name, so one is called [blue]t_orders.contactname[/blue] and the other, [blue]t_Customers.contactname[/blue].

As these names contain periods you must make sure they are properly interpreted, so to reference them use ..

[blue]

objRecordsetName.Fields("t_orders.contactname") [green]' field name in quotes[/green]

[/blue]
.. or ..
[blue]

objRecordsetName![t_orders.contactname] [green]' field name in brackets[/green]

[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[nimarii]

thanks for the replies!
tonyjollans, it worked perfectly as usual!