If not (item1 ) or not (item2) then 3

[Rooster1947]

I am having trouble with the If....or....then part of a code written in MS Access for an ADO query of database records.

The ADO query obtains Invoice data to be sent as an 810 invoice for EDI purposes.

The portion of the code which gets the line item details is:

i = 1
'Print Detail Records
With CreateObject("ADODB.Recordset")
.ActiveConnection = CurrentProject.Connection
.Source = "Select * FROM SALES_HISTORY_DETAIL Where SALES_HISTORY_DETAIL.NUMBER = '" & RS1.Fields.Item("NUMBER").Value & "'"
.Source = .Source & " And SALES_HISTORY_DETAIL.RECNO BETWEEN 1 AND 999"
.Source = .Source & " And SALES_HISTORY_DETAIL.WHSE Between '00' and '01';"
.Open

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 Not (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) & "|" & vbCrLf
End If

.MoveNext
i = i + 1
Loop


Everything works except the following:

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


The purpose is to exclude these items from the body of the report if they exist in the recordset..they will be included in a different part of the report.

Written in the above fashion the code 8 is excluded but not the 9001 if I put brackets around all then it excludes all items.

My problem I believe is with the placement of the brackets but I am unable to come up with a solution to bet both items filtered out of the recordset if they exist.

 

[HarleyQuinn]

I'd use AND instead of OR.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[huggyboy]

I believe it's a bit of boolean algebra called deMorgan's theorem

 

[PHV]

If not (item1 ) or not (item2) then
Use either this:
If not (item1 or item2) then

or this:
If not item1 and not item2 then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rooster1947]

Thanks to all for your helpful suggestions...really liked the DeMorgans Theorum...( although it made my eyes cross and water...very deep) the last post was the one that tripped it for me...the following code worked:

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

Thank you all so much...it is extremely appreciated