I have 2 tables

Table 1 contains all customer information including custVisitID


Table 2 contains 2 fields (custVisitID, Coupon)


I need to create a custom field that shows the number '1' if coupon
code '1008' is found in Table2.coupon where Table1.custVisitID =
Table2.custVisitID

Here is my failed attempt
UsedCoupon:If [CouponID] = "1008" then "1" End IF


Can anyone help?
Steve1040

CustomerTbl
CustID, Name
a1 steve
b1 Tom
c1 Sue
d1 Mary
...

DetailDataTbl
CustID, Coupn
a1 12345
a1 1008
a1 2348
b1 2348
b1 12345
c1 2348
c1 1008
d1 12345
d1 2348
d1 3779

In this sample in the CustomerTbl (the Master Table) CustID is unique.
You could have several names that are the same, to be distinguished by
say Address, but the custID would distinguish Tom Smith at 1234 a
street, from Tom Smith at 789 b street.

In the Detail data table, the custID may occur several times for
different coupons. In my sample above, only custID a1 and c1 contain
coupon # 1008. Note: we will say that the Coupon field is a Text
field, rather than a numeric field (you won't be performing calculations
with the coupon number). Then you would write a code like this behind a
form for If Then Else

Dim s1 As String
If textBoxCoupon = "1008" Then s1 = "1"

Alternatively, you could write it like this:
If textboxCoupon = "1008" Then
s1 = "1"
End If

If the "If Then" statement is a single liner - you don't use "End If".
The "End If" is used when it is a multiline statement. Also, you need
to assign the value of "1" to something - like a variable - in this case
s1.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

steve1040 wrote:

UsedCoupon : IIF([CouponID] = "1008","1")
or
UsedCoupon : IIF([CouponID] = "1008","1",Null)
if you wanted a true/false value