×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

comparing fields, one populated with data and one blank
2

comparing fields, one populated with data and one blank

comparing fields, one populated with data and one blank

(OP)
Hi,
I have two subforms on a form and I have VBA code that compares the fields on one subform to the other subform. If the fields don't match, I have a button for the user to click on to update the data in the field that doesn't match. It's basically:
If [subform1]![fielda]<>[subform2]![fielda] then
me.updatebtn.visible=true
end if

This works fine, unless [subform1]![fielda] has data and [subform2]![fielda] does not have data. It doesn't recognize that they don't match. I'm pretty sure this is dumb question, but why is that? Is there a way to get it to recognize they don't match?
Thank you!

RE: comparing fields, one populated with data and one blank

Could you 'translate' your issue from: 'fields on the forms' to 'fields in tables in the data base'?

Are you saying: Fields in TableA do not match fields in TableB?
If that is the case, my question will be: why do you have the data in more than just one place in your data base? Unless your issue is something else
ponder

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: comparing fields, one populated with data and one blank

(OP)
Thanks for your response. The question is why when comparing data in one field in a table to a field in a different table that has no data, why are the two fields not being recognized as being unequal?

RE: comparing fields, one populated with data and one blank

Quote (atadbitslow)

[subform2]![fielda] does not have data

You may try something like:

CODE

If [subform1]![fielda] & "" <> [subform2]![fielda] & ""  then
    me.updatebtn.visible=true
end if 

In case any of these fields are NULLs

Just a suggestion.

But, you still did not answer my question sad

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: comparing fields, one populated with data and one blank

Null is about the same as “ unknown”. You can’t compare unknown values using = or <>. You should convert the Nulls using NZ() and then compare.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: comparing fields, one populated with data and one blank

(OP)
Thank you both very much! One table is imported data from our online dashboard and is used to update our existing data in our Access database. So not really same data in two places. The imported data table is cleared after each session.

RE: comparing fields, one populated with data and one blank

So is your issue resolved? Did you attempt Andy’s suggestion which should work.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: comparing fields, one populated with data and one blank

I would rather use Duane's suggestion with NZ(), it is more 'elegant' IMO smile

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: comparing fields, one populated with data and one blank

(OP)
Hi,
I think I'll use NZ() which is what I thought of trying on my own, but didn't. I haven't updated the code just yet, will do today.
Thank you both!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close