Showing a formatted field in a report (1 Viewer)

I am trying to show the Current Repair Estimate total in currency form on my report. The attachment shows my criteria, which works on the query, but does not show in the report. The second attachment shows what pops up when I try to view the report. What can I do to get this current rep estimate to show correctly in currency form on the report?

Kinda lost in where all this is happening (talk about a query and a report, but posted in Forms sub-forum), but I do know what's going on. [Current rep estimate] isn't a field in the object you are referencing it in. Possibly that's the underlying query, maybe its the report, but [Current rep estimate] isn't where it should be.

Does the data source the report is based on run by itself without this issue?

Yes the data comes out correctly on the query. My query has the field as Current Repair Estimate: Format([Current rep estimate],"Currency") - works just fine. When I try to enter that same thing in a text box for the Report it gives me trouble. The Report is pulling directly from the query so I feel like its a problem with the naming of the field, right?

Thanks for replying plog.

So when you run the query no dialog box asking for input comes up?

That means, somewhere on the Report its trying to reference [Current rep estimate]--(maybe its sorting by it, grouping by it, or has a totals field based on it.), but [Current rep estimate] isn't available to the report because its not in the query.

Your calculated field named [Current Repair Estimate] is, but not the field it is based on [Current rep estimate]. My advice is to add [Current rep estimate] to the query.

And to remove spaces from your field names. It just makes writing and debugging code that much harder. Either use underscores (Current_rep_estimate) or capitalization (CurrentRepEstimate).

Ahhh the dialogue box is popping up on the queries - My brain is currently melting lol I apologize. What is wrong with that criteria?

Nothing to do with criteria. This is about the query not finding a field you are using. [Current rep Estimate] is not in any of the data sources the query is built upon. Most likely its a calculated field in the same query and you are trying to reference it.

You shouldn't use a calculated field in the same query you calculate it. Post your full SQL of that query and I can help more.

SELECT Now()-[DISPO_CORE REVIEW_]![Document Date] AS Aging, Count([DISPO_CORE REVIEW_]![Customer Purchase Order]) AS [NO of Parts], [DISPO_CORE REVIEW_].[Current Disposition], Format([Current rep estimate],"Currency") AS [Current repair estimate], [DISPO_CORE REVIEW_]![Customer Number] & " " & [DISPO_CORE REVIEW_]![Name 1] AS Cust, [DISPO_CORE REVIEW_].Material, [DISPO_CORE REVIEW_].[Customer Purchase Order], [DISPO_CORE REVIEW_].[Disposition due], VA05.[Sales Document]
FROM VA05 RIGHT JOIN [DISPO_CORE REVIEW_] ON (VA05.[Purchase order number] = [DISPO_CORE REVIEW_].[Customer Purchase Order]) AND (VA05.[Name 1] = [DISPO_CORE REVIEW_].[Name 1])
WHERE ((([DISPO_CORE REVIEW_].[Customer Number])=[What is the customer name?]))
GROUP BY Now()-[DISPO_CORE REVIEW_]![Document Date], [DISPO_CORE REVIEW_].[Current Disposition], Format([Current rep estimate],"Currency"), [DISPO_CORE REVIEW_]![Customer Number] & " " & [DISPO_CORE REVIEW_]![Name 1], [DISPO_CORE REVIEW_].Material, [DISPO_CORE REVIEW_].[Customer Purchase Order], [DISPO_CORE REVIEW_].[Disposition due], VA05.[Sales Document]
HAVING ((([DISPO_CORE REVIEW_].[Current Disposition]) Is Not Null) AND ((Format([Current rep estimate],"Currency"))>"1"));

Thats the SQL view. I am in my first month of learning access so bear with me.

That's pretty convoluted.

What datasource is [Current rep estimate] in? VA05 or DISPO_CORE REVIEW_?

okay i figured out the query portion, that is settled now i just need to figure out how to get it to show in the report. The current repair estimate is in the DISPO_CORE_REVIEW Query, I use that DISPO CORE query in a different query that the report pulls from.