Reference a Field not on the report

[Guest]

Re Access 97

I have a query bound to a report which contains 8 different charging methods
in any combination.
e.g. No of Pallets * Cost per Pallet
No of Tonnes * Cost per Tonne
No of Hours * Cost per Hour
No Of Nights * Cost per Night
Fuel Surcharge
Cost Of Load etc

I need to show only those costs entered for the job on the Report (Invoice)
My current solution is to create a textbox and add the relevant charges to
this where applicable.

I could add invisible boxes to the report for each of the 14 fields and
reference these in turn, but this seems messy.

Is there a way to reference the fields in the Detail_Format Event without
having to add them first to the report? Space on the report is at a premium.

Many thanks.

 

[Allen Browne]

The key issue here is to get the table structure right. Then the report will
fall together easily.

Firstly you need a table of the possible units-of-measure. This Unit table
might have just one field:
UnitID Text primary key.
It will have records such as:
Pallet
Tonne
Hour
Each

If one product can be sold by multiple units-of-measure, you need a related
table named (say) ProductUnit, with fields like this:
ProductID foreign key to Product.ProductID
UnitID foreign key to Unit.UnitID.
PriceEach the current default price each for this unit of this
product.

Your OrderDetail table will have fields:
Quantity
ProductID
UnitID
PriceEach

When you create the query for your report, it will have all these tables, so
the text boxes in the Detail section of your report will look like this in
design view:
Quantity ProductName PriceEach =" per " & [UnitID]

 

[Guest]

Allen,

Many thanks for your reply.

Your solution is very elegant and would br preferable under notmal
cercumstances. The problem is that my client needs to Shoe-horn as many jobes
per page as possible. This is why I am trying to detail the relevany charges
on a sinble line using abbreviation:-

CL=£180.00 (Cost of Load)
NP=20 * CP=£15.00 (No Of Pallets * Cost per Pallet)
etc...

The descriptions in brackets (above) would show as a key at the foot of the
page where there is room.

Is it possible to reference a field in the underlying query from the
Detail_Format event, without firstly having to add the field as a bound
control?

I tried using;

If [CostOfLoad] > 0 then...

in this event but Access reported it couldn't find the field.

Thanks again.

Rgds,
Pete.

The key issue here is to get the table structure right. Then the report will
fall together easily.

Firstly you need a table of the possible units-of-measure. This Unit table
might have just one field:
UnitID Text primary key.
It will have records such as:
Pallet
Tonne
Hour
Each

If one product can be sold by multiple units-of-measure, you need a related
table named (say) ProductUnit, with fields like this:
ProductID foreign key to Product.ProductID
UnitID foreign key to Unit.UnitID.
PriceEach the current default price each for this unit of this
product.

Your OrderDetail table will have fields:
Quantity
ProductID
UnitID
PriceEach

When you create the query for your report, it will have all these tables, so
the text boxes in the Detail section of your report will look like this in
design view:
Quantity ProductName PriceEach =" per " & [UnitID]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Re Access 97

I have a query bound to a report which contains 8 different charging
methods
in any combination.
e.g. No of Pallets * Cost per Pallet
No of Tonnes * Cost per Tonne
No of Hours * Cost per Hour
No Of Nights * Cost per Night
Fuel Surcharge
Cost Of Load etc

I need to show only those costs entered for the job on the Report
(Invoice)
My current solution is to create a textbox and add the relevant charges to
this where applicable.

I could add invisible boxes to the report for each of the 14 fields and
reference these in turn, but this seems messy.

Is there a way to reference the fields in the Detail_Format Event without
having to add them first to the report? Space on the report is at a
premium.

Many thanks.

 

[Allen Browne]

If you want shorter names, you could add fields to the tables to store the
abbreviated names, and return the from the query.

It's easy enough to combine the values from several fields into one text box
on the report. For your example, the ControlSource of the text box would be
something like this:
=[Quantity] & " * " & [UnitID] & "=" & Format([UnitPrice], "Currency")

It is possible to use a DLookup() expression in the Control Source instead
of including the table in the report's source query, but that is
considerably less efficient.

It is also possible to DLookup() a value in Detail_Format, but that's also
inefficient.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Many thanks for your reply.

Your solution is very elegant and would br preferable under notmal
cercumstances. The problem is that my client needs to Shoe-horn as many
jobes
per page as possible. This is why I am trying to detail the relevany
charges
on a sinble line using abbreviation:-

CL=£180.00 (Cost of Load)
NP=20 * CP=£15.00 (No Of Pallets * Cost per Pallet)
etc...

The descriptions in brackets (above) would show as a key at the foot of
the
page where there is room.

Is it possible to reference a field in the underlying query from the
Detail_Format event, without firstly having to add the field as a bound
control?

I tried using;

If [CostOfLoad] > 0 then...

in this event but Access reported it couldn't find the field.

Thanks again.

Rgds,
Pete.

The key issue here is to get the table structure right. Then the report
will
fall together easily.

Firstly you need a table of the possible units-of-measure. This Unit
table
might have just one field:
UnitID Text primary key.
It will have records such as:
Pallet
Tonne
Hour
Each

If one product can be sold by multiple units-of-measure, you need a
related
table named (say) ProductUnit, with fields like this:
ProductID foreign key to Product.ProductID
UnitID foreign key to Unit.UnitID.
PriceEach the current default price each for this unit of this
product.

Your OrderDetail table will have fields:
Quantity
ProductID
UnitID
PriceEach

When you create the query for your report, it will have all these tables,
so
the text boxes in the Detail section of your report will look like this
in
design view:
Quantity ProductName PriceEach =" per " & [UnitID]

Re Access 97

I have a query bound to a report which contains 8 different charging
methods
in any combination.
e.g. No of Pallets * Cost per Pallet
No of Tonnes * Cost per Tonne
No of Hours * Cost per Hour
No Of Nights * Cost per Night
Fuel Surcharge
Cost Of Load etc

I need to show only those costs entered for the job on the Report
(Invoice)
My current solution is to create a textbox and add the relevant charges
to
this where applicable.

I could add invisible boxes to the report for each of the 14 fields and
reference these in turn, but this seems messy.

Is there a way to reference the fields in the Detail_Format Event
without
having to add them first to the report? Space on the report is at a
premium.

 

[Guest]

Allen,

Thanks again for your reply.

The question in my previous post was asking if it is possible to reference a
field in the underlying query, using vba, without firstly having to add a
bound control for each of the fields to be referenced?

Many thanks.

If you want shorter names, you could add fields to the tables to store the
abbreviated names, and return the from the query.

It's easy enough to combine the values from several fields into one text box
on the report. For your example, the ControlSource of the text box would be
something like this:
=[Quantity] & " * " & [UnitID] & "=" & Format([UnitPrice], "Currency")

It is possible to use a DLookup() expression in the Control Source instead
of including the table in the report's source query, but that is
considerably less efficient.

It is also possible to DLookup() a value in Detail_Format, but that's also
inefficient.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Many thanks for your reply.

Your solution is very elegant and would br preferable under notmal
cercumstances. The problem is that my client needs to Shoe-horn as many
jobes
per page as possible. This is why I am trying to detail the relevany
charges
on a sinble line using abbreviation:-

CL=£180.00 (Cost of Load)
NP=20 * CP=£15.00 (No Of Pallets * Cost per Pallet)
etc...

The descriptions in brackets (above) would show as a key at the foot of
the
page where there is room.

Is it possible to reference a field in the underlying query from the
Detail_Format event, without firstly having to add the field as a bound
control?

I tried using;

If [CostOfLoad] > 0 then...

in this event but Access reported it couldn't find the field.

Thanks again.

Rgds,
Pete.

The key issue here is to get the table structure right. Then the report
will
fall together easily.

Firstly you need a table of the possible units-of-measure. This Unit
table
might have just one field:
UnitID Text primary key.
It will have records such as:
Pallet
Tonne
Hour
Each

If one product can be sold by multiple units-of-measure, you need a
related
table named (say) ProductUnit, with fields like this:
ProductID foreign key to Product.ProductID
UnitID foreign key to Unit.UnitID.
PriceEach the current default price each for this unit of this
product.

Your OrderDetail table will have fields:
Quantity
ProductID
UnitID
PriceEach

When you create the query for your report, it will have all these tables,
so
the text boxes in the Detail section of your report will look like this
in
design view:
Quantity ProductName PriceEach =" per " & [UnitID]

Re Access 97

I have a query bound to a report which contains 8 different charging
methods
in any combination.
e.g. No of Pallets * Cost per Pallet
No of Tonnes * Cost per Tonne
No of Hours * Cost per Hour
No Of Nights * Cost per Night
Fuel Surcharge
Cost Of Load etc

I need to show only those costs entered for the job on the Report
(Invoice)
My current solution is to create a textbox and add the relevant charges
to
this where applicable.

I could add invisible boxes to the report for each of the 14 fields and
reference these in turn, but this seems messy.

Is there a way to reference the fields in the Detail_Format Event
without
having to add them first to the report? Space on the report is at a
premium.

 

[Allen Browne]

In theory, yes.

In practice, it works with forms, and it sometimes works with reports. I
have not been able to nail down why Access reports sometimes seem to require
the field to be a control before recognising the reference.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks again for your reply.

The question in my previous post was asking if it is possible to reference
a
field in the underlying query, using vba, without firstly having to add a
bound control for each of the fields to be referenced?

Many thanks.

If you want shorter names, you could add fields to the tables to store
the
abbreviated names, and return the from the query.

It's easy enough to combine the values from several fields into one text
box
on the report. For your example, the ControlSource of the text box would
be
something like this:
=[Quantity] & " * " & [UnitID] & "=" & Format([UnitPrice],
"Currency")

It is possible to use a DLookup() expression in the Control Source
instead
of including the table in the report's source query, but that is
considerably less efficient.

It is also possible to DLookup() a value in Detail_Format, but that's
also
inefficient.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Many thanks for your reply.

Your solution is very elegant and would br preferable under notmal
cercumstances. The problem is that my client needs to Shoe-horn as many
jobes
per page as possible. This is why I am trying to detail the relevany
charges
on a sinble line using abbreviation:-

CL=£180.00 (Cost of Load)
NP=20 * CP=£15.00 (No Of Pallets * Cost per Pallet)
etc...

The descriptions in brackets (above) would show as a key at the foot of
the
page where there is room.

Is it possible to reference a field in the underlying query from the
Detail_Format event, without firstly having to add the field as a
bound
control?

I tried using;

If [CostOfLoad] > 0 then...

in this event but Access reported it couldn't find the field.

Thanks again.

Rgds,
Pete.

:

The key issue here is to get the table structure right. Then the
report
will
fall together easily.

Firstly you need a table of the possible units-of-measure. This Unit
table
might have just one field:
UnitID Text primary key.
It will have records such as:
Pallet
Tonne
Hour
Each

If one product can be sold by multiple units-of-measure, you need a
related
table named (say) ProductUnit, with fields like this:
ProductID foreign key to Product.ProductID
UnitID foreign key to Unit.UnitID.
PriceEach the current default price each for this unit of
this
product.

Your OrderDetail table will have fields:
Quantity
ProductID
UnitID
PriceEach

When you create the query for your report, it will have all these
tables,
so
the text boxes in the Detail section of your report will look like
this
in
design view:
Quantity ProductName PriceEach =" per " & [UnitID]

Re Access 97

I have a query bound to a report which contains 8 different charging
methods
in any combination.
e.g. No of Pallets * Cost per Pallet
No of Tonnes * Cost per Tonne
No of Hours * Cost per Hour
No Of Nights * Cost per Night
Fuel Surcharge
Cost Of Load etc

I need to show only those costs entered for the job on the Report
(Invoice)
My current solution is to create a textbox and add the relevant
charges
to
this where applicable.

I could add invisible boxes to the report for each of the 14 fields
and
reference these in turn, but this seems messy.

Is there a way to reference the fields in the Detail_Format Event
without
having to add them first to the report? Space on the report is at a
premium.