Add a field for calculation in current report

[Zack Barresse]

Hi all,

I have a report based on a query I built. The report works just great.
Although I would like to add a textbox next to the ones currently on my
report of the calculated value which the query was built off of. Right now
it just reports the actual table value. Is there anyway I can do some sort
of Lookup in a query? I just want to display two fields added together
based on a specific 'Well' and a specific "SampleDate' field(s). Is this
doable? Thanks.

Also posted in ms.p.ac.reports

 

[Douglas J Steele]

It's not clear to me what you want this new field to contain.

If it's simply concatenating two fields together, then you can type
something like the following into a vacant cell on the Field row in the
query builder:

NewField: [Well] & [SampleDate]

This will create a new field named NewField in your query. If you want a
space between the value of Well and the value of SampleDate, use

NewField: [Well] & " " & [SampleDate]

If, on the other hand, you're trying to use those two pieces of information
to look up a value in another table, your best bet is to join the two
tables, rather than using DLookup.

 

[Zack Barresse]

Hello Doug,

Thanks for responding. Not sure how to get a query returned to a textbox in
a report. I can get to the Expression Builder no problem though. It's not
a concatenation of two fields, just adding them together. I think I could
do it with a query though. How would I go about building one for this
field?

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)

It's not clear to me what you want this new field to contain.

If it's simply concatenating two fields together, then you can type
something like the following into a vacant cell on the Field row in the
query builder:

NewField: [Well] & [SampleDate]

This will create a new field named NewField in your query. If you want a
space between the value of Well and the value of SampleDate, use

NewField: [Well] & " " & [SampleDate]

If, on the other hand, you're trying to use those two pieces of
information
to look up a value in another table, your best bet is to join the two
tables, rather than using DLookup.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,

I have a report based on a query I built. The report works just great.
Although I would like to add a textbox next to the ones currently on my
report of the calculated value which the query was built off of. Right now
it just reports the actual table value. Is there anyway I can do some sort
of Lookup in a query? I just want to display two fields added together
based on a specific 'Well' and a specific "SampleDate' field(s). Is this
doable? Thanks.

Also posted in ms.p.ac.reports

 

[Douglas J. Steele]

Are you saying that [Well] and [SampleDate] are numeric fields?

Then use

NewField: Nz([Well], 0) + Nz([SampleDate], 0)

To get the value on the report, set the query as the report's RecordSource,
and set the field as the ControlSource for the text box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Doug,

Thanks for responding. Not sure how to get a query returned to a textbox
in a report. I can get to the Expression Builder no problem though. It's
not a concatenation of two fields, just adding them together. I think I
could do it with a query though. How would I go about building one for
this field?

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)

It's not clear to me what you want this new field to contain.

If it's simply concatenating two fields together, then you can type
something like the following into a vacant cell on the Field row in the
query builder:

NewField: [Well] & [SampleDate]

This will create a new field named NewField in your query. If you want a
space between the value of Well and the value of SampleDate, use

NewField: [Well] & " " & [SampleDate]

If, on the other hand, you're trying to use those two pieces of
information
to look up a value in another table, your best bet is to join the two
tables, rather than using DLookup.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,

I have a report based on a query I built. The report works just great.
Although I would like to add a textbox next to the ones currently on my
report of the calculated value which the query was built off of. Right now
it just reports the actual table value. Is there anyway I can do some sort
of Lookup in a query? I just want to display two fields added together
based on a specific 'Well' and a specific "SampleDate' field(s). Is
this
doable? Thanks.

Also posted in ms.p.ac.reports

 

[Zack Barresse]

That was the ticket! Thank you very much. This program is amazing me more
and more everyday. Maybe this does have some advantages over Excel.

Thanks again.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)

Are you saying that [Well] and [SampleDate] are numeric fields?

Then use

NewField: Nz([Well], 0) + Nz([SampleDate], 0)

To get the value on the report, set the query as the report's
RecordSource, and set the field as the ControlSource for the text box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Doug,

Thanks for responding. Not sure how to get a query returned to a textbox
in a report. I can get to the Expression Builder no problem though.
It's not a concatenation of two fields, just adding them together. I
think I could do it with a query though. How would I go about building
one for this field?

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)

It's not clear to me what you want this new field to contain.

If it's simply concatenating two fields together, then you can type
something like the following into a vacant cell on the Field row in the
query builder:

NewField: [Well] & [SampleDate]

This will create a new field named NewField in your query. If you want a
space between the value of Well and the value of SampleDate, use

NewField: [Well] & " " & [SampleDate]

If, on the other hand, you're trying to use those two pieces of
information
to look up a value in another table, your best bet is to join the two
tables, rather than using DLookup.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all,

I have a report based on a query I built. The report works just great.
Although I would like to add a textbox next to the ones currently on my
report of the calculated value which the query was built off of. Right
now
it just reports the actual table value. Is there anyway I can do some
sort
of Lookup in a query? I just want to display two fields added together
based on a specific 'Well' and a specific "SampleDate' field(s). Is
this
doable? Thanks.

Also posted in ms.p.ac.reports