Changing a calculated field to an Expression

[Benjamin Bolduc]

Hi everyone!

Thanks to the forums help, I've made considerable progress on my latest project. As usual, however, I'm having another problem.

I have a calculated control on my report that does exactly what I want it to do.

Here is the control:

=DLookUp("[Vouttotal]","Main","[ID#]= " & Reports![Acetator Report- First Draft]![Id#] & " AND [Acetator#] = '" & Reports![Acetator Report- First Draft]![Acetator#] & "'")

Now, I need to make this into an expression on the forms underlying query. I need this figure for charts and graphs I'd like to run. As far as I can see, if it's not on the query, you can't make a chart out of it. If there is another way someone can think of, please let me know.

Otherwise, I've tried cutting and pasting it into an expression column, but that didn't work. There must be a way of acheiving the same figure in an expression.

Any help would greatly appreciated.

Thanks!

 

[Fizzio]

Are you trying to filter the query or just add fields to the query? If so, just add the "Main" table with the fields you want to filter on and in the criteria of [ID#] and [Acetator#] enter the criteria you want to filter on.
Hope this is clear

 

[Benjamin Bolduc]

Thanks for responding,

This report has a lot of feilds, and unfortunatly the most important one is a calculated feild. I need to set up a graph with this feild, but I can't because all graphable feilds have to be in a query.
I figured i would just recreate all the calculations from the report into the query and I'd be all set, but one of the key elements of the important feild is the "DLookup" calculated control mentioned above.

To answer your question, I'm not trying filter anything, just add calculations to the query.

Thank You for helping me!

 

[Fizzio]

With graphs, they ultimately have their own rowsource. Have you tried copying the SQL of your initial query and pasting it into the rowsource of the graph?

 

[Benjamin Bolduc]

But if the feild that I want to run the graph on is a calculated one, how would it be mentioned in a query if I didnt put it there? I'm sorry, I'm a little dense when it comes to Access Graphs.

Thanks Again
-Ben

 

[Fizzio]

Calculate the field in a query not on the report (unless it is a running sum etc) and use that query to feed the graph (I only have limited experience with grphs myself)

 

[Benjamin Bolduc]

Now if I could only figure out how to put

=DLookUp("[Vouttotal]","Main","[ID#]= " & Reports![Acetator Report- First Draft]![Id#] & " AND [Acetator#] = '" & Reports![Acetator Report- First Draft]![Acetator#] & "'")

into a query, I'd be all set. Anyone have any ideas?

 

[Fornatian]

=DLookUp("[Vouttotal]","Main","[ID#]= " & Reports![Acetator Report- First Draft]![Id#] & " AND [Acetator#] = '" & Reports![Acetator Report- First Draft]![Acetator#] & "'")

1.Is the table "Main", included in the underlying query?
2.Where do
Reports![Acetator Report- First Draft]![Id#]
AND
[Acetator#]
get their values from?

I suggest including the MAIN table in the queries recordsource and input the above values as criteria for the appropriate fields - if the above values are calculated from other fields in the query you should be able to recreate them in the query.

Make sense?

 

[Benjamin Bolduc]

The ID# and the Acetator# both get thier values directly from the [Main] table, which is the only table I have for this DB.

I know I can re-create all the calculations in the query, and thats what I intend on doing. But my only obstacle is this Dlookup feild. It's the only one that I cant seem to re-create. First I get a syntax error, then I get a message saying my expression is too complex. I figure there must be a certain way of wording it that I just don't know.

Do you have any ideas?

Thanks!
-Ben

 

[Benjamin Bolduc]