Solved: Evaluating expression If ..or..then

[Rooster1947]

I am using MS Access 2003 and have an ADO to query a record set building a "flat file" for EDI submission. I am trying to write an evaluating expression which will exclude two specific items. In other one or both are present in the recordset..ignore them write all others.

part of the code is:

If Not (.BOF = True And .EOF = True) Then
.MoveFirst
Do Until .EOF

If Left(.Fields.Item("CODE").Value, 4) = "9001" Then HOMEDELIVERY = "YES"
If Left(.Fields.Item("CODE").Value, 4) = "9001" Then DelChg = (.Fields.Item("BVORDQTY").Value * _
.Fields.Item("BVUNITPRICE").Value)
If Left(.Fields.Item("CODE").Value, 1) = "8" Then ScrapCredit = "PRESENT"
If Left(.Fields.Item("CODE").Value, 1) = "8" Then Scrap = (-(.Fields.Item("BVORDQTY").Value) * _
.Fields.Item("BVUNITPRICE").Value)

'ITEM

If Not (Left(.Fields.Item("CODE").Value, 1) = "8") Or (Left(.Fields.Item("CODE").Value, 4) = "9001") Then



myText = myText & "ITEM|"
myText = myText & i & "|"
myText = myText & .Fields.Item("BVORDQTY").Value & "|"
myText = myText & "EA|"
myText = myText & .Fields.Item("BVUNITPRICE").Value & "|"
myText = myText & "VN|"
myText = myText & Trim(.Fields.Item("CODE").Value) & "|SK|" & CUSTSKU & vbCrLf
'DESC
myText = myText & "DESC|"
myText = myText & Trim(.Fields.Item("SHD_DESCRIPTION").Value) & "|" & ScrapCredit & vbCrLf
End If

.MoveNext
i = i + 1
Loop
End If

The file works but will only recognize the evaluating statement before the "or" it does not recognize the statement after the "or".

Can't seem to read anything on this so trying this method to see if I can solve my problem

[Norie]

Are you sure you have the correct logic operators/brackets etc?

What, in words, is the logic?

[CreganTur]

Welcome to the forum- it's always good to see new members.

Whenever you are evaluating multiple conditions on the same If line, you have to encapsulate all of your Or statements within a single set of parentheses... like this:
If Not (Left(.Fields.Item("CODE").Value, 1) = "8" Or Left(.Fields.Item("CODE").Value, 4) = "9001") Then

Also, with this example, if you want the second condition to be evaluated as a NOT, then you need to add it in as well:
If Not (Left(.Fields.Item("CODE").Value, 1) = "8" Or Not Left(.Fields.Item("CODE").Value, 4) = "9001") Then

Another, and bigger, issue: are you certain you want to evaluate this many separate If statements, or should you be using an If..ElseIf set of conditionals?

HTH:thumb

[Rooster1947]

Yes the brackets are correct the statement that is giving issue is:

If Not (Left(.Fields.Item("CODE").Value, 1) = "8") Or (Left(.Fields.Item("CODE").Value, 4) = "9001") Then

The logic is for it to ignore the items in the recordset with a SKU of "8" or "9001" and include all other items. With this code written in this fashion it ignores the SKU "8" but includes the "9001"...if I reverse the order then it ignores the "9001" and includes the "8" ..so as I said it recognizes the first portion but does not use the portion after the or criterea

[CreganTur]

Yes the brackets are correct the statement that is giving issue is:

If Not (Left(.Fields.Item("CODE").Value, 1) = "8") Or (Left(.Fields.Item("CODE").Value, 4) = "9001") Then


Did you notice that that's the statement I rewrote in my examples? Have you tried using it, to see if it works?

The parentheses are not correct, because you are not encapsulating all of your conditionals within a single set of parentheses.

[Rooster1947]

Thanks for the response...I would have never come up with the Or Not which makes sense however the code is still not co-operating.

This works:
If Not (Left(.Fields.Item("CODE").Value, 1) = "8") Then
'Or Not (Left(.Fields.Item("CODE").Value, 4) = "9001")

and this works:
If Not (Left(.Fields.Item("CODE").Value, 4) = "9001") Then
'Or Not(Left(.Fields.Item("CODE").Value, 1) = "8")

This excludes all items from the body of the report:

If Not (Left(.Fields.Item("CODE").Value, 1) = "8" Or Not Left(.Fields.Item("CODE").Value, 4) = "9001") Then

which is the code posted by Cregan

This excludes only the first item and not the second:

If Not (Left(.Fields.Item("CODE").Value, 1) = "8") Or Not(Left(.Fields.Item("CODE").Value, 4) = "9001") Then

This ignores nothing and lets all through the filter:

If (Not (Left(.Fields.Item("CODE").Value, 1) = "8") Or Not (Left(.Fields.Item("CODE").Value, 4) = "9001")) Then

So thanks to both who responded. To the first...you are correct I don't have the brackets in the correct spot but have yet to discover where my error is.

[Rooster1947]

Cregan
Yes I copied and pasted your code direct in and it blocked all items from the body of the report not just those two items.

[OBP]

Why not try reversing the sequence like this
If ((Left(.Fields.Item("CODE").Value, 1)<> "8") and (Left(.Fields.Item("CODE").Value, 4) <> "9001")) Then

[Rooster1947]

Thanks to all who posted your responses were helpful and appreciated..I did come up with the right code ...this worked for me:

If Not (Left(.Fields.Item("CODE").Value, 1) = "8") And Not (Left(.Fields.Item("CODE").Value, 4) = "9001") Then

Again thank you for your input I couldn't have got it without it