Code question

  • Thread starter Thread starter Gary Nelson
  • Start date Start date
G

Gary Nelson

In Access2000, I have a query that I created that lists 155 customers who
last year have placed orders for our product. I created a form based on the
query that lists the customer names and addresses. Some have placed orders
already this year. I also created a form which is filled in once a customer
places an order. This form lists customer name, address, telephone, part
orderdered, quantity, etc. What I am attempting to do is write code in the
query that will place the words "ORDED PLACED" when the customer places an
order and "ORDER NOT PLACED" in the form I created that lists the 155
customers from last year.

As an example of what I am trying to do, see below example:

Mr. Bob Jones
1212 Jones Road
Baltimore, MD 12345

"ORDER PLACED"
___________________

Ms. Shirley Wethers
555 Pink Street
Washington, DE 55555

"ORDER NOT PLACED"
_____________________

Thanks for your help
 
Gary-

Add a text box to your form. In the control source enter something like:

=IIF(IsNull(DLookup("[OrderID]", "tblOrders", "Year([OrderDate]) =
Year(Date()) AND [CustomerID] = " & [CustomerID])), "ORDER NOT PLACED",
"ORDER PLACED")

I'm using DLookup to look for any order in the current year for the current
customer. You'll have to fix the names of the fields and table to match
what you actually have in your database. For example, I'm assuming the
table containing orders is tblOrders, and that table has OrderID, OrderDate,
and CustomerID fields. I'm also assuming that you have a field called
CustomerID on your form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top