Data Manipilation II

[Thompyt]

I am trying to get the info to show if it does not equal the information in a previous row where the Sub_No = 0

The original code is:

IIf(([Sub_No]=0),[Unit],"") & Chr(13) & Chr(10) & IIf(([Sub_No]=0),[Section],"")

This is what I have come up with, but I think it is circular and will always find that where Sub_No <> 0, it will be = to the row where Sub_No = 0.

 IIf(([Sub_No]=0),[Unit], (IIf(([Unit]=[Unit]),"",[Unit]))) & Chr(13) & Chr(10) & IIf(([Sub_No]=0),([Section]),(IIf(([Section]=[Section]),"",[Section])))

My intent is if the Sub_No <> 0 it won'tdisplay [Unit] unless it is different from where Sub_No = 0

Data:
CR_No=1
Sub_No = 0....Unit =John
Sub_No = 1....Unit =John


Sub_No = 2....Unit =Jane
Sub_No = 3....Unit =John
Sub_No = 4....Unit =Jay


Output:
1.0....John
.1....
.2....Jane
.3....
.4....Jay

Don't worry about the CR_No, I have that figured out from Snafu assistance already.

Thanks

[Thompyt]

Maybe I need to stick a dlookup statement in there. What I don't know how to do is reference the Unit field i
frm the original CR_No/Sub_No field. Any suggestions?

[Thompyt]

Lets simplify to a single field.

I am trying to get the info to show if it does not equal the information where the Sub_No = 0, CRs have to stay the same. (i.e. 1.0, 1.1)
The original code is:

IIf(([Sub_No]=0),[Unit],"")

This is what I have come up with, but I think it is circular and will always find that where Sub_No <> 0, it will be = to the row where Sub_No = 0.

IIF(Sub_No=0, Unit, IIF(Unit<>Unit where Sub_No=0)

My intent is if the Sub_No <> 0 it won't display [Unit] unless it is different from where Sub_No = 0 How do I correctly put in the "Where" portion of the code? This is going into the Field header as an expression.

[Thompyt]

This is interesting that I seem to have stumped the experts.

[June7]

Maybe not stumped but did not see thread. I often don't view threads that already have replies.

Yes, the expression is circular and won't work. Pulling value from another record of same table often requires a nested subquery.

Is Unit a text type field?

Perhaps:

IIf(Sub_No<>0 AND IsNull(DLookup("Unit", "tablename", "Sub_No=0 AND Unit='" & Unit & "'")), Unit, "")

or

IIf(Sub_No<>0 AND DCount("*", "tablename", "Sub_No=0 AND Unit='" & Unit & "'")=0, Unit, "")

[Thompyt]

Dang, I thought a Dlookup might be in there, but my noviceness would ahve never gotten that. I should know better to call the experts out. :P Yes uUnit is a text field.

Thanks, I'll give it a shot and see what happens.

[Thompyt]

Update

UNITS: IIf([Sub_No]=0 And IsNull(DLookUp("Unit","[Change Request]",[Sub_No]=0 And [Unit]="" & [Unit] & "'")),[Unit],"")

I used this one, but the only thing I can do is determine what is shut off, Sub_No=0 shuts off everything but where SUB_No=0, if I put Sub_No<>0 then the main SUb_No shuts off. No matter what I do to the latter part, one or the other is shut off.

The area I am not understanding full is: [Unit]="" & [Unit] & "'")),[Unit],"")

[Unit]="" Unit is Null
& AND (concantenate)
Unit For which Sub_no?
& AND (concantenate)
""'" ???? This is a wild card?
, Unit The true statement
"" Where the statement is false put a null

[June7]

The doubled quotes is not a Null it is empty string.

The apostrophes are delimiters that define the parameter as a string (text) as opposed to a number or date.

You have replaced an apostrophe with a quote mark. Should be: [Unit]='"

Also missing quote mark at beginning of the WHERE argument. Look carefully again at my examples.

The code takes the Unit of current record and looks for a match in table according to the given parameters.

[Thompyt]

June7,
I tried different connotations of your code, I have a choice of unit where Sub-No=0 showing, or where sub_No<>0 I have cut and pasted the code. What I was looking for was where Sub_No is always displayed, and where Sub_No<>0, if the unit is the same, then do not display, or where the unit is not the same, display.

[June7]

As far as I can tell, my suggested code should accomplish that but I don't know your db.

[Thompyt]

CR-25MAR15.zip

June7,
Example attached.

[June7]

Just opened your db. It immediately wants to run query. Get error message "Undefined function 'Date' in expression. What is going on?

What objects are related to the thread topic?

[Thompyt]

Dunno, I get that each time I change from a work computer to my home computer. Work is Office 2013, Home is Office 2010. What I have to do is to change the references in the VBA from MS Office 15 to MS Office 14.

Try this one. It has the minimal info needed. I forgot to add in the CR+Sub_no code

Minimal.zip

[Thompyt]

What if you used the control CR_Numbers instead of Sub_No=0"

In the same Query whe have the control CR_Numbers: [CR_No]+([Sub_No]*0.01)

Something like IIF([CR_Numbers]="wildcard.00" that would be more specific than Sub_No=0 of which there are possible multiple instances in that query.

What is the wildcard symbol for numbers? Yes I tried Google, Bing, Ask Jeeves....

[June7]

There is no wildcard for numbers and dates, only works for text.

Tried to open the report and get error messages about missing DLLs and undefined function 'Format' in expression.

I looked and the report RecordSource SQL and Format() function is not properly used.