• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Trying to write vba to say if this field = this and this other field does not equal this then..

Here is what I'm trying to make work;

If [jmMtlSeq] = "9999" And [jmCommentText] is not like "OV*" Then
    tbxSupp = "Text"
    Else
...

Let me know if you need more information..

0
Jarred Meyer
Asked:
Jarred Meyer
  • 4
  • 2
  • 2
2 Solutions
 
Jarred MeyerProduction ManagerAuthor Commented:
P.S. this should be in just Access and probably VBA, Not SQL Query Syntax..
0
 
gman84Commented:
If (jmMtlSeq = "9999") AND Instr(jmCommentText,"OV") <> 1 Then
    tbxSupp = "Text"
Else
    ......
End If

Open in new window

0
 
Jarred MeyerProduction ManagerAuthor Commented:
That seems to have done the trick, although I'm getting #Error as my result when "Pending" is supposed to display.. Can you look at the complete code below and see if you can figure out what is wrong with the section I have where it should be returning "Pending"?

Thanks for the help!

Private Function txtPackSlip()

If ([jmMtlSeq] = "9999") And InStr([jmCommentText], "OV") <> 1 Then
    txtPackSlip = "S.C.O.C."
   
    Else

        If tbxSupp = "WAITING ON PO" Then
            txtPackSlip = "WAITING"
           
            Else
               
                If Not IsNull([jmCommentText]) Then
                txtPackSlip = [rdPackSlip]
               
                Else
                txtPackSlip = "Pending"

               
                End If
               
        End If
End If
   
   
End Function
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Rey Obrero (Capricorn1)Commented:


If [jmMtlSeq] = "9999" And NOT [jmCommentText] like "OV*" Then
    tbxSupp = "Text"
    Else
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Capricorn.. That works as well..  Either of those methods work:)

Any thoughts on why my Pending part of this formula is returning #Error?
0
 
gman84Commented:
I think it's your use of IsNull()
This may work better
If [jmCommentText] <> vbNullString Then

Open in new window

0
 
Jarred MeyerProduction ManagerAuthor Commented:
That's still giving me #Error
0
 
Rey Obrero (Capricorn1)Commented:
try
Private Function txtPackSlip()

If ([jmMtlSeq] = "9999") And NOT [jmCommentText] like "OV*" Then
    txtPackSlip = "S.C.O.C."
   
    ElseIf tbxSupp = "WAITING ON PO" Then

        
       txtPackSlip = "WAITING"
           
    ElseIf  [jmCommentText] & "" <>"" Then
               
                
       txtPackSlip = [rdPackSlip]
               
    Else
      txtPackSlip = "Pending"
 
End If
   
   
End Function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now