Not Equal To VBA

[Novice1]

I'm lost. I have a text field named "Status." The first set of code works fine. The second set doesn't. Is it because I'm using "<>" with a text field? If so, what should I use instead? Any help would be appreciated.

Me.SectionClearText = "Contractors"
If IsNull([TimeOut]) Then Me.TimeOut = Format(Time(), "hh:nn")
If Me.CACorIDcard = -1 And Me.Status = "Family Member" Then TimeIDRecd = Me.TimeOut + TimeValue("00:08")
Forms.Item("frmNextCustomerContractors").RecordSource = "qryNextCustomerContractors"
DoCmd.Requery
Forms![frmNextCustomerContractors]![frmNextCustomerContractorsSubForm].SetFocus


Me.SectionClearText = "Contractors"
If IsNull([TimeOut]) Then Me.TimeOut = Format(Time(), "hh:nn")
If Me.CACorIDcard = -1 And Me.Status <> "Family Member" Then TimeIDRecd = Me.TimeOut + TimeValue("00:13")
Forms.Item("frmNextCustomerContractors").RecordSource = "qryNextCustomerContractors"
DoCmd.Requery
Forms![frmNextCustomerContractors]![frmNextCustomerContractorsSubForm].SetFocus

 

[theDBguy]

Hi. What does "not work" mean? Are you getting an error message? Are you getting no/wrong result? Can you show us some sample data as a result of both code?

 

[GPGeorge]

To help us get off the starting line, please explain what it means to say that "The first set of code works fine. The second set doesn't. "

What DOES happen? What did you EXPECT to happen? How are they different? Describing behaviors, in addition to statements of outcomes, is a good way to help others understand the problem.

 

[theDBguy]

If you're literally using "< >" (with a space in between them), then you should probably get a syntax error. If so, try removing the space.

 

[LarryE]

I'm lost. I have a text field named "Status." The first set of code works fine. The second set doesn't. Is it because I'm using "<>" with a text field? If so, what should I use instead? Any help would be appreciated.

Me.SectionClearText = "Contractors"
If IsNull([TimeOut]) Then Me.TimeOut = Format(Time(), "hh:nn")
If Me.CACorIDcard = -1 And Me.Status = "Family Member" Then TimeIDRecd = Me.TimeOut + TimeValue("00:08")
Forms.Item("frmNextCustomerContractors").RecordSource = "qryNextCustomerContractors"
DoCmd.Requery
Forms![frmNextCustomerContractors]![frmNextCustomerContractorsSubForm].SetFocus


Me.SectionClearText = "Contractors"
If IsNull([TimeOut]) Then Me.TimeOut = Format(Time(), "hh:nn")
If Me.CACorIDcard = -1 And Me.Status <> "Family Member" Then TimeIDRecd = Me.TimeOut + TimeValue("00:13")
Forms.Item("frmNextCustomerContractors").RecordSource = "qryNextCustomerContractors"
DoCmd.Requery
Forms![frmNextCustomerContractors]![frmNextCustomerContractorsSubForm].SetFocus

Try:

Me.SectionClearText = "Contractors"
If IsNull([TimeOut]) Then
    Me.TimeOut = Format(Time(), "hh:nn")
    If Me.CACorIDcard = -1 And Me.Status = "Family Member" Then
        TimeIDRecd = Me.TimeOut + TimeValue("00:08")
    Else
        TimeIDRecd = Me.TimeOut + TimeValue("00:13")
    End If
End If
Forms.Item("frmNextCustomerContractors").RecordSource = "qryNextCustomerContractors"
DoCmd.Requery
Forms![frmNextCustomerContractors]![frmNextCustomerContractorsSubForm].SetFocus

 

[pbaldy]

If you're missing nulls, try

And (Me.Status <> "Family Member" OR IsNull(Me.Status))

 

[The_Doc_Man]

If you're literally using "< >" (with a space in between them), then you should probably get a syntax error. If so, try removing the space.

Assuming the posted code was a cut/paste, I checked and there is no extraneous space between the <> bracketing.

 

[theDBguy]

Assuming the posted code was a cut/paste, I checked and there is no extraneous space between the <> bracketing.

Thanks. I guess it just looked like it did. Cheers!

 

[The_Doc_Man]

Looking at the code in post #1, I'm going to suggest - as did pbaldy - that you may have an issue with NULL in Me.Status sometimes. If so, then you would get a TRUE result from Me.Status <> "Family Member" because a NULL is NEVER equal to anything - including itself. Using an NZ to encapsulate Me.Status might help. Might not, either, but from here this looks like a data issue, not a syntax issue.

 

[arnelgp]

try adding a Null String to your Expression:

f Me.CACorIDcard = -1 And (Me.Status & "") = "Family Member"

 

[gemma-the-husky]

is the field name Status, or is that the name of the bound field?

I would generally use a look up table, rather than hard code text, so option 1 might be "family member", option 2 "friend" and so on, and then you don't get a problem if you want to change "family member" to something slightly different.

are you sure the error isn't with this expression

If Me.CACorIDcard = -1

alternatively maybe these 3 lines

Forms.Item("frmNextCustomerContractors").RecordSource = "qryNextCustomerContractors"
DoCmd.Requery
Forms![frmNextCustomerContractors]![frmNextCustomerContractorsSubForm].SetFocus

cause the second block of code to fail


why not just

If Me.CACorIDcard = -1
        if Me.Status = "Family Member" Then
              TimeIDRecd = Me.TimeOut + TimeValue("00:08")
        Else
              TimeIDRecd = Me.TimeOut + TimeValue("00:13")
        End If
   else
        'what if CACorIDCard is false?
   end if
end if