SQL Statement

[gmaxey]

I'm a novice so please be gentle.

I'm trying to learning a little about retrieving data from as Access database. and loading it into a Word UserForm listbox. I've downloaded the Northwind Traders example database and I'm having trouble getting some data with a statement that "looks" like it should work.

In this database there is a table called "Purchase Order Details" I can run the following statement and it works without error

strSQL = "SELECT * From [Purchase Order Details];"

When I modify that statement to:

'strSQL = "SELECT [Product] From [Purchase Order Details];"

It errors with "No value given for one or more required parameters."

I know that there is a column named "Product" in the table because I can see it. However, I think, part of the problem is that while the items in the table are text (e.g., Northwind Traders Hot Sauce"), the data type for the column is number!

My objective is to get a listing of all products and quantity from this table when the received date is between 4/4/2006 and 4/17/2006.

This is the first statement that I tried:
'strSQL = "SELECT [Product],[Quantity] From [Purchase Order Details] WHERE [Date Recieve] BETWEEN '4/4/2006' AND '4/17/2006';"

What am I doing wrong? Why does a field with the data type "Number" show a text value?

Thanks.

[Doug Robbins]

Greg,

The easiest way to get the SQL code is to use the Query Design facility to create a query, and then use the SQL view of the Query. In this case, it gives you:

[vba]SELECT Products.[Product Name]
FROM Products INNER JOIN [Purchase Order Details] ON Products.ID = [Purchase Order Details].[Product ID]
WHERE ((([Purchase Order Details].[Date Received])>=#4/4/2006#) AND (([Purchase Order Details].[Date Received])<=#4/17/2006#));
[/vba]

Note, the Purchase Order Details table only contains the ID of the Products and you need to link that table to the Product Table to get the Name of the Product

[Doug Robbins]

Greg,

The easiest way to get the SQL is to use the Query Design Facility and then use the SQL view of the query. In this case, it displays:

[VBA]SELECT Products.[Product Name]
FROM Products INNER JOIN [Purchase Order Details] ON Products.ID = [Purchase Order Details].[Product ID]
WHERE ((([Purchase Order Details].[Date Received])>=#4/4/2006#) AND (([Purchase Order Details].[Date Received])<=#4/17/2006#));
[/VBA]

Note that the Purchase Order Details table only contains the ID of the product and it is necessary to link that table to the Products table on the ID to be able to return the names of the products.

[gmaxey]

Doug,

As I'm a beginning would you considering explaining how I would use the Querry design facility to create the querry in the first place?

Thanks. It does work.