Access 2007 IIf function in a query

65 pts.
Tags:
IIF function
Please excuse my noobness but I am new to Access and rarely work with it. How do I replace the data in 1 field with the data of another field, if any data exists in the 2nd field? I suspect IIf would be the best way but I get 'NewID: IIf([OffenderID]="0000000000000",[ForeignID],"0000000000000")' is not a valid name - error in Access. My query functoin is: NewID: IIf([OffenderID]="0000000000000",[ForeignID],"0000000000000") If my [OffenderID] field = 0000000000000 and the [ForeignID] field is not Null, then the [OffenderID] must be populated with the value in the [ForeignID] field. If the [ForeignID] is Null, then the data in the [OffenderID] field must remain as is. What am I missing please?

Software/Hardware used:
Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Do you really want to "replace" the data in the table, or this is just for displaying ?
    69,920 pointsBadges:
    report
  • Riaane
    Hi Carlos, sorry for the late reply. I did not notice your reply as I never got the mail even though this question is on my watchlist. sometimes there are valid entries in my ([OffenderID]) field but my ([ForeignID]) field is not Null. In such cases, I need to retain the data in my ([OffenderID]) field. Would there be an additional OR criteria maybe to cater for this? This was the reason why I thought an IIf function would be a safer option.
    65 pointsBadges:
    report
  • Riaane
    Just to expand a little on this issue: my ([OffenderID]) field will contain "0000000000000" falue 90% of the time when my ([ForeignID]) field is not Null. In these cases, the data in ([ForeignID]) must replace the "0000000000000" entries in my ([OffenderID]) field. Otherwise, if my ([OffenderID]) field Is Not = "0000000000000" , retain data in ([OffenderID]). I managed to come up with the following: NewID: IIf((([OffenderID])="0000000000000"),(Nz(([ForeignID]),Null)),([OffenderID])) This is now solved :-)
    65 pointsBadges:
    report
  • carlosdl
    Oh, now it was me who didn't notice your last post. Glad you solved it.
    69,920 pointsBadges:
    report
  • Riaane
    [...] 1. For new Microsoft Access users, look at how Rianne solved his own question regarding data being switched into different fields. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following