many criteria

[deekras]

this is probably simple, but ...
i can do this in crystal reports, however, it does not seem to work in access:

if {tbl_transactions.tnyint_transaction_status} in [8,4,10,15,20,25,30,35]
then .....

meaning if status is one of those numbers, then ...


how do i do that in access?
is there an equivalent to the 'in [ ]' function in access?

 

[ghudson]

Check out the In Operator in the Access help files.

HTH

 

[WayneRyan]

deekras,

Access supports the construct:

Select Field
From   Table
Where  Field2 in ('A', 'B', 'C');

Select Field1
From   Table1
Where  Field1 in (Select Field1
                  From   Table2);

Both types of "IN" are accepted.

Wayne

 

[deekras]

i am using it on a report.

if the status is one of those numbers then i need comment 1.
if the mny_amount <> monthly_amt*payment_schedule then comment 2.
else
comment 3.

i don't think i can use select case for this scenario either.

 

[WayneRyan]

deekras,

If your status codes are consistent, then you do want the
Select Case statement. See the Help files for an example.
You can have many values for each case.

Wayne

 

[deekras]

i tried the select case. it doesn't help in this case.

i have a paragraph and one of the sentences changes based on certain criteria.

if the status is one of those numbers then i need sentence 1
else
(if the status is not on of those numbers and) the money amts are not equal then i need sentence 2
else
sentence 3

so i am not working only with the status field, so select case doesn't seem to work.

 

[WayneRyan]

deekras,

This construct is possible:

If Sts In (1,3,5,7,9) Then
   ' statement 1
ElseIf Sts Not In (1,3,5,7,9) and Money1 <> Money2 Then
   ' statement 2
Else
   ' statement 3
End If

Wayne

 

[WayneRyan]

deekras,

This construct is possible:

If Sts In (1,3,5,7,9) Then
   ' statement 1
ElseIf Sts Not In (1,3,5,7,9) and Money1 <> Money2 Then
   ' statement 2
Else
   ' statement 3
End If

Wayne

 

[deekras]

i tried :
if me.status in (8,4,10,15,20,25,30,35) then ...

as i type it in, i get an error:
compile error:
Expected: Then or GoTo

what am i doing wrong?

 

[WayneRyan]

deekras,

Can you post (paste) some of your code?

Wayne

 

[deekras]

here is the code, as i have it now. it works fine, it just seems so cumbersome. i want to use the IN function.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.tnyint_transaction_status = 8 Then
Me.bill_date_comment1 = "balance due for failed transaction on " & Me.sdt_transaction_date
Else
If Me.tnyint_transaction_status = 4 Then
Me.bill_date_comment1 = "balance due for failed transaction on " & Me.sdt_transaction_date
Else
If Me.tnyint_transaction_status = 10 Then
Me.bill_date_comment1 = "balance due for failed transaction on " & Me.sdt_transaction_date
Else
If Me.tnyint_transaction_status = 15 Then
Me.bill_date_comment1 = "balance due for failed transaction on " & Me.sdt_transaction_date
Else
If Me.tnyint_transaction_status = 20 Then
Me.bill_date_comment1 = "balance due for failed transaction on " & Me.sdt_transaction_date
Else
If Me.tnyint_transaction_status = 25 Then
Me.bill_date_comment1 = "balance due for failed transaction on " & Me.sdt_transaction_date
Else
If Me.tnyint_transaction_status = 30 Then
Me.bill_date_comment1 = "balance due for failed transaction on " & Me.sdt_transaction_date
Else
If Me.tnyint_transaction_status = 35 Then
Me.bill_date_comment1 = "balance due for failed transaction on " & Me.sdt_transaction_date
Else
If Me.mny_amount <> Me.mny_monthly_fee * Me.tnyint_payment_schedule Then
Me.bill_date_comment1 = "balance due for " & trans_date & " - " & thru_date3

Else
Me.bill_date_comment1 = trans_date & " - " & thru_date3

End If
End If
End If
End If
End If
End If
End If
End If
End If

 

[WayneRyan]

deekras,

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 

If Me.tnyint_transaction_status In (8, 4, 10, 15, 20, 25, 30, 35) Then 
  Me.bill_date_comment1 = "balance due for failed transaction on " & Me.sdt_transaction_date 
Else 
  If Me.mny_amount <> Me.mny_monthly_fee * Me.tnyint_payment_schedule Then 
     Me.bill_date_comment1 = "balance due for " & trans_date & " - " & thru_date3 
  Else 
     Me.bill_date_comment1 = trans_date & " - " & thru_date3 
  End If 
End If 
End Sub

Wayne

 

[deekras]

i tried that in the first place. doesn't work for me. i can't figure out why?!

 

[WayneRyan]

deekras,

Can you post a sample?

Compact --> ZIP --> post

Wayne