Data Manipilation II

[Thompyt]

I don't get any errors opening it on my work laptop. At home since I use 2010, I do get the date error. I have to change the VBA references from Missing MS Office 15 to MS Office 14. Is this the Record source SQL you are referring to?

[CODE]SELECT [CR_No]+([Sub_No]*0.01) AS CR_Numbers1, IIf([Sub_No]=0,Format([CR_No]," #"),Format([Sub_No]*0.01," " & " .00")AS CR_Numbers, [Level] & Chr(13) & Chr(10) & [Soft Level] AS Levels, [Change Request].Change_Type, [HB_Version] & Chr(13) & Chr(10) & [Approx_Page] AS [HB Vers], [Change Request].Date_ID, [Unit] & Chr(13) & Chr(10) & [Section] AS UNITS, [MTOE_Para] & Chr(13) & Chr(10) & [Bumper_Number] AS [MTOE Paras], [Requestor] & Chr(13) & Chr(10) & [Sponsor] AS People, [Change Request].[Change Requested], [Change Request].Rationale, [Change Request].Final_Vote, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].Attachment, [Change Request].NIE, [Change Request].Action_Complete
FROM [Change Request]
WHERE ((([Change Request].[Change Requested])<>"Do not delete") AND (([Change Request].Action_Complete)=True));



The format not be used correctly is?: , IIf([Sub_No]=0,Format([CR_No]," #"),Format([Sub_No]*0.01," " & " .00")

[June7]

On second look, the Format functions do work. I just tested in the VBA immediate window. I don't know why fails in query.

The report shows a lot of "No such field in field list." errors.

[Thompyt]

IIf(Sub_No=0 AND IsNull(DLookup("Unit", "tablename", "Sub_No=0 AND Unit='" & Unit & "'")), "", Unit) seems to work to put it in every field.

I DL'd the file from my earlier post. I was missing the field Units in the Closed form. I put this in a column to correct it. Units: [Unit] & Chr(13) & Chr(10) & [Section] If you DL'd the file just after I posted it, I had to change it as I forgot the CR_Numbers field with the format.

[June7]

The CR_Numbers field is in the query. I added the Units field and now report runs.

I had to remove the Runtime library reference.

What exactly should I be looking at for this issue?

[Thompyt]

What I was trying to do is have a field display on where CR_No+(Sub_No)*.01= ###.00 and if CR_No+(Sub_No)*.01<> ###.00 and the field is = to the same field oin .00 then do not display, else display. ### is sort of the wildcard for the CR_No as the CR does matter a little, because there will be the possibility of multiple .00 in the table or query.

[Thompyt]

June7,
You code does work, but it takes a long time to compute. Especially on the Levels code. It works when you put it in a sperate query and then use that query in the report. It does not work if you try to use the code inside the report (Control Source) SELECT.......

[June7]

Glad you got it working. I was just about to revisit this (been preoccupied with home reno).

Unfortunately, domain aggregates can be slow in queries or textboxes.