Help on Query

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Hi everyone,

I am trying to get the latest informtaion from any of the three fields.

Field1 = Field2 and use the latest information on Field2 if Field 3 is
blank. Again, if the Field2 and Field3 are blanks then use the latest
information on Field1.

I'm not too sure if I did the right thing or not.

NewColumn: IIf([Field3] is null,[Field1]=[Field2],[Field2] or IIf([Field3]
is null,IIf([Field2] is null,[Field1]

Your help would be much appreciated.

Thanks
 
Bill said:
Hi everyone,

I am trying to get the latest informtaion from any of the three fields.

Field1 = Field2 and use the latest information on Field2 if Field 3 is
blank. Again, if the Field2 and Field3 are blanks then use the latest
information on Field1.

I'm not too sure if I did the right thing or not.

NewColumn: IIf([Field3] is null,[Field1]=[Field2],[Field2] or IIf([Field3]
is null,IIf([Field2] is null,[Field1]
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You might try this:

NewColumn: Nz(Field3, Nz(Field2, Nz(Field1, "Unknown")))

Translation: Use Field3; if Field3 is null use Field2; if Field2 is
null use Field1; if Field1 is null return "Unknown." IOW, use the first
field in the series that is NOT null.

See the Access VBA help article on the Nz() function.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGOKXYechKqOuFEgEQLayQCfa3K/yfFquKFyuMQMF7b+ULN+SagAoN3k
5HQDPnl9/hHVNPJOSg1y8EKT
=6tHr
-----END PGP SIGNATURE-----
 
Hi everyone,

I am trying to get the latest informtaion from any of the three fields.

Field1 = Field2 and use the latest information on Field2 if Field 3 is
blank. Again, if the Field2 and Field3 are blanks then use the latest
information on Field1.

I'm not too sure if I did the right thing or not.

NewColumn: IIf([Field3] is null,[Field1]=[Field2],[Field2] or IIf([Field3]
is null,IIf([Field2] is null,[Field1]

Your help would be much appreciated.

Thanks
Click to expand...
Well, no; you didn't. As it says in the VBA online help, the IIF
function has three arguments: a logical expression, the value to
return if it's true, and the value to return if it's false. You're
giving IIF five arguments and unbalanced parentheses!

Could you explain what you mean by "the latest information"? Are you
trying to update the value in Field1 to be equal to Field2? If so an
IIF won't do it, you need an Update query.

What is actually in these fields, and what real-life problem are you
trying to solve? I can perhaps guess (rightly or wrongly) at what
you're trying to accomplish, but I'd prefer to understand the problem
more clearly and probably suggest a quite different table structure;
this one does not seem properly normalized!
 
Hi, Bill

Thank you for posting in the community.

From your description, however, I'm not sure of how you want to deal with
the data. What will happen if Field3 is not blank? "Field1 = Field2" means
evaluate Field2 with Field 1 or when Field1 equals Field2? Would you please
give me some sample data of your table and the result you want to get for
all kinds of situations? The detailed information from you will make us
understand your problem more clearly.

Based on my current understanding of your question, the blank may be
zero-length string or NULL, please confirm what it is. Assuming the blank
menas NULL, you may try this:
Iif(([Field2] is null and [Field3] is null), [Field1], IIf(([Field3] is
null), [Field2], [Field3]))

When Field2 and Field3 are all null, NewColum will be evaluated as Field1
When only Field3 is null, NewColum will be evaluated as Field2
When no field is null, New Column will be evaluated as Field3.

Please take my suggestion above and let me know whether it works for you.
If not, please give me some sample data and describe your requirement as
detailed as possible and I am glad to assist you with it. Looking forward
to your reply.





Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Hello Bill,

I am just writing in to checking on your progress regarding the information
that was sent you. How is this issue going on your side currently? Could
you give me more detail descriptions for what will happen under all kinds
of conditions? for instance, how about Field3 is not blank?

Would you please give me the sample data so that I can make further help

Please feel free to leave a short message by posting it in the newsgroup or
replying this email directly letting us know if we help solve the problem
or you need further assistance.

Best regards,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Back
Top