Set data in one field determined by one character in another field??

[Bill Stillwell]

I need to know how to set the value (6 digits) in one field on a report based on the presence of a character in another field (12 characters). If the 12 character field has an "s" in it, then one 6 digit number will be put into the report. If the 12 character field has a "p" in it, then the number in the report will be a different 6 digit number.

 

[FoFa]

iif(0<instr([12char],"s"),"sNum",iif(0<instr([12char],"p"),"pNum",NULL))

just off the top of my head, but that would also put a NULL if it contained neither and s or p, and if it contained both, it would use the s number.

 

[Bill Stillwell]

FoFa,
I set the field up that was to receive the 6 digit number as a bound field, and in the Control Source property, I entered the code you suggested. I replaced the 12char and pnum and snum with the correct data, but I got no response. It looks to me like it should work, but it doesn't. The field to receive the pnum or snum is blank after opening the report. Any ideas what I might've screwed up, or what else I could try.

 

[WayneRyan]

Bill,

Does the line in your query look like:

Expr: IIf(0<InStr([Text],"s"),[Number1],IIf(0<InStr([Text],"p"),[Number2],Null))

What happens if you just run the query? Are you referencing the "Expr:"
name correctly?

Wayne

 

[Bill Stillwell]

Wayne,
I have put that line in the Control Source for the field I am trying to populate with the 6 digits. I don't have a query. Still does not work ??
I have the .mdb file minimized to show one small table, one report and one small macro. That's all. I have neutralized it so anyone can look at it. Is there a way I can attach the .mdb file to this reply? I'll need to email it to someone if I am offered an email address. I appreciate any information I receive on this.
Bill.