DLookup in Group Footer

[m stroup]

rptEngine is called from qryEngine which lists engines, engine runs, and
engine run times. The report is grouped by engine number and has a group
header and footer.
It works fine.

I want to add a calculated field to the group footer:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = ENG")

What this gives me is the [SPChange] value for the first record. I need the
[SPChange] value for the record that matches the ENG in the group header.

the above formula works in my forms - finding the right engine.....

thanks fo ryour help

 

[Duane Hookom]

If ENG is numeric, try:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = " & [ENG])

If ENG is text, try:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = """ & [ENG] & """")

Can't you add tblEngine to your report's record source query so the SPChange
field is in the report's fields?

 

[m stroup]

Thanks Duane,
Yes, I could place it in the query, but it would be the same value for each
entry within the group. I will try those changes.
I am also playing with using a subreport in the group header as well. So
many options..
Teach me to fish! Thanks for the help.
Pax, M

If ENG is numeric, try:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = " & [ENG])

If ENG is text, try:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = """ & [ENG] & """")

Can't you add tblEngine to your report's record source query so the SPChange
field is in the report's fields?

--
Duane Hookom
Microsoft Access MVP

rptEngine is called from qryEngine which lists engines, engine runs, and
engine run times. The report is grouped by engine number and has a group
header and footer.
It works fine.

I want to add a calculated field to the group footer:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = ENG")

What this gives me is the [SPChange] value for the first record. I need the
[SPChange] value for the record that matches the ENG in the group header.

the above formula works in my forms - finding the right engine.....

thanks fo ryour help

 

[m stroup]

That worked perfectly. Can you tell me why so many quotations are needed?
--
Teach me to fish! Thanks for the help.
Pax, M

If ENG is numeric, try:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = " & [ENG])

If ENG is text, try:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = """ & [ENG] & """")

Can't you add tblEngine to your report's record source query so the SPChange
field is in the report's fields?

--
Duane Hookom
Microsoft Access MVP

rptEngine is called from qryEngine which lists engines, engine runs, and
engine run times. The report is grouped by engine number and has a group
header and footer.
It works fine.

I want to add a calculated field to the group footer:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = ENG")

What this gives me is the [SPChange] value for the first record. I need the
[SPChange] value for the record that matches the ENG in the group header.

the above formula works in my forms - finding the right engine.....

thanks fo ryour help

 

[Duane Hookom]

Text/string values require quotes around the literal values.

Let's say the value of the [Eng] field is "Hemi". This would change the
expression to:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = ""Hemi""")

Adjacent double-quotes are treated like an embedded double-quote
This expression would cause an error
=25-DLookUp("[SPChange]","tblEngine","[ENG] = "Hemi"")

You could also use single quotes like:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = '" & [ENG] & "'")
However, this would break if you had an [Eng] value of 'M' type.

I'm not sure how enlightening this was or if I just confused you more.
--
Duane Hookom
Microsoft Access MVP

That worked perfectly. Can you tell me why so many quotations are needed?
--
Teach me to fish! Thanks for the help.
Pax, M

If ENG is numeric, try:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = " & [ENG])

If ENG is text, try:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = """ & [ENG] & """")

Can't you add tblEngine to your report's record source query so the SPChange
field is in the report's fields?

--
Duane Hookom
Microsoft Access MVP

rptEngine is called from qryEngine which lists engines, engine runs, and
engine run times. The report is grouped by engine number and has a group
header and footer.
It works fine.

I want to add a calculated field to the group footer:
=25-DLookUp("[SPChange]","tblEngine","[ENG] = ENG")

What this gives me is the [SPChange] value for the first record. I need the
[SPChange] value for the record that matches the ENG in the group header.

the above formula works in my forms - finding the right engine.....

thanks fo ryour help