Comparing strings and other data types

[glgcag]

I was wondering if someone could give me a little guidance on how best to compare strings, other data types and test for nulls or empty strings.

I am using the .tag property of controls to store "pre-edited" data and then checking for any changes upon exit/submit/save and inserting the edits into the table. For some reason I have yet to absorb a rule of thumb for the comparing and I thought someone might be able to direct me.

For example, in the case that a field is not required, it may be blank. How best should I trap for null values or empty strings? Nz(fieldname, "") compared to Nz(fieldsname.tag, "")?

When it comes to ANY field I'm comparing I simply write:

If fieldname <> fieldname.tag then
    'insert the edit into the proper table
End If

Is there a better way to compare strings and other data types (longs, integers, boolean) that is better, or is this OK?

Thanks, I appreciate the help!

 

[gol4]

see strcomp function.
If Trim(myfield & "") = "" is another way
NZ is great as well

If this is a bound control look at the dirty and oldvalue property instead of storing as a tag

if me.dirty then
insert into foobar(myfield) values (field.oldvalue)
end if

 

[glgcag]

All the programming I do is unbound for maximum flexibility. (I really like the ease of using the dirty property, though, I just wish there was something like it for unbound fields.)

So, using Trim handles null values as well as nz then? I do use "if nz(fieldname, "") = "" then action" often, and generally, I don't have any problems. But have you run into the case that the value in the field is " "? In which case trimming it like you did above might be better . . .

 

[CautionMP]

glgcag,
Have you looked at [tt]StrComp()[/tt]?

If [b]StrComp([/b]fieldname[b],[/b] fieldname.Tag[b], vbBinaryCompare)[/b] <> 1 then
    'insert the edit into the proper table
End If

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[glgcag]

CautionMP,

Thanks for the tip, I really like this function! I've been testing it with two fields and according to the help on the strCompare function, if it returns 0, then there hasn't been a change. Should I use it such as:

 If StrComp(Fieldname, Fieldname.Tag, vbBinaryCompare) <> 0 then
    If not isnull(StrComp(Fieldname, Fieldname.Tag, vbBinaryCompare)
        'insert edit into table
    End if 
End if

In other words, if there IS a change between the two values, check to make sure one or both aren't null, then insert the edit. Does that make sense or am I going overboard?

 

[TheAceMan1]

How are ya glgcag . . .

[blue] . . . Does that make sense or am I going overboard?[/blue]

Yeah . . . a bit overboard . . .

Consider there are only two types of string comparison in access:
[ol][li][blue]Binary[/blue] (case sensitive)[/li]
[li][blue]Text[/blue] ( non-case sensitive & default in access).[/li][/ol]

[blue]   [purple][b]abcd = aBCd[/b][/purple] fails for binary, passes for text.
   [purple][b]aBCd = aBCd[/b][/purple] passes for both.
   [purple][b]abcd = abcd[/b][/purple] passes for both.[/blue]

So whats the story on all these options if there's only two comparsion modes? . . . if you look at the options for the [blue]Option Compare Statement[/blue] which usually appears at the top of any module and the last three values you can use for the [blue]compare arguement of StrComp[/blue], you'll find there one and the same:

[tt][blue]OptionCompareStatement > Binary Text Database
StrComp(,, compare) > vbBinaryCompare vbTextCompare vbDatabaseCompare[/blue][/tt]

[blue]With Database defaulting to text![/blue]

There's one more compare value for StrComp to consider and thats [blue]vbUseCompareOption[/blue] which is self explanatory and uses the setting of the [blue]Option Compare Statement[/blue] in the module where the code is currently running.

That pretty much narrows it down:

[blue]Unless you need your comparsion to be case sensitive there's no difference between using a logical comparsion (If String1 = String2 Then) and StrComp.[/blue]

A logical incite to this is to leave the Text default intact and use StrComp whenever you need binary.

As for your last post my read would be:

[blue]   If Trim(Me!Name1 & "") = "" Then
      MsgBox "Data required"
   ElseIf Me!Name1 <> Me!Name1.Tag Then
      [green]'insert edit into table[/green]
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[glgcag]

That straightens it out for me! Thanks!