Not Equal To VBA (1 Viewer)

[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

 

[Pat Hartman]

Also, dates are NOT strings. Using the Format() function turns a date into a string. Strings act like strings. They do not act like dates. How a date is formatted has nothing whatsoever to do with how it is stored. The format property of a date defines how it is displayed for human consumption, NOT how it is stored. NEVER, EVER use the format property on a date field on the table definition. WHY? Because you always want to see the actual value of a field, not the formatted value. For example, novices make the mistake of using Now() rather than Date() and so they end up with time values in date fields where it makes no sense to have a time value. So, rather than fixing the problem by changing the code and by updating existing data to remove the time part, they hide the problem by changing the format to short date. Looks swell, those pesky time values are gone but they really aren't because if you are selecting based on date, you may be including or excluding certain records because the time value is still there and it counts in these types of operations.

And that leads us to how do we do arithmetic with dates? We use the appropriate date function. In your case, you would use DateAdd() with Minutes as the increment. BTW since "mm" is used for month, the functions use "nn" for minutes. Sometimes Access will get it right but you should always use the correct increment.

Here's a link to bookmark. It will help you to find the function you need even if you don't know its name or even if it exists
Functions (category list) | Microsoft Docs