Count Expression in Report Footer problem

[Guest]

Hey all

I'm using Count(*) in the Report Footer of one of my report to count each
record in the Details section. I'm having a error messages coming up
effectively saying "Database can't find the fields 'Bank' and 'Branch'
referred to in your expression - you may have misspelled the field name, or
the field may have been renamed or deleted".

I hit "OK" on both of these messages and then my report shows WITHOUT the
Bank and Branch controls. I get a perfect count - but without the information
in these fields (which makes it a little useless :-}).

The Report has a source query which is further filtered via a form dialog
box when the report is initiated. It also refers to the query (Lookup
expressions) to sort Bank and Branch alphabetically rather than referring to
ID number (that took me awhile to figure out!!)

Any ideas out there?

 

[Duane Hookom]

If you look at your field list, do you see the fields "Bank" and "Branch"?

 

[Guest]

Hey Duane - I do indeed.

I have tried deleting the old controls and then redragging the Bank and
Branch fields from the field list - that hasn't worked at this stage.

Cheers

 

[Duane Hookom]

Where else are you using Bank and Branch? Are they in the sorting and
grouping? Have you viewed your report's record source query in datasheet
view to see if the error is generated there rather than in your report?

 

[Guest]

Bank and Branch are not in Report Sorting and Grouping.

Here's a brief Relationship:

Tables:
1. 'Banks/Alliance Partners' (contains field 'Bank' with a BankID autonumber)
2. 'Branches' (contains field 'Branch' with a BranchID autonumber)
These support a further table:
3. 'Bank/Branch Information' (contains field 'Bank' and 'Branch' which are
the dual primary key fields and are sourced via Look Up to the 1st 2 tables)

Query:
1. 'Source QRY for Individual LDC Portfolio Report' is based off Table
'Bank/Branch Information'. The Bank and Branch fields are sorted here in
Ascending Order in design view.

Report:
1. 'Individual LDC Portfolio Report'. Under Report Properties in the Order
By field I have the following expression 'Lookup_Bank.[Bank],
Lookup_Branch.Branch'. This was to help ensure the report would sort
ascending ALPHABETICALLY rather than numerically as per the respective
supporting Autonumber fields. Took me ages to work this out!

As mentioned before - when the Report is run I have VBA code in place for a
dialog (Form) box to open to further sort the underlying query (by Individual
LDC). The display the results in the Report.

Thanks for your quick reply's on this Duane - I'm going to keep playing
around with it to see if I can get it to work - if you can see anything here
that could help, just let me know. Cheers - Jase

 

[Duane Hookom]

If the report query "is based off Table 'Bank/Branch Information'" I would
expect the table has BankID and BranchID, not Bank and Branch.

You should be sorting your report with fields or expression selected in the
Sorting and Grouping dialog. I never use the order by property of a report.

Also, do you have tables in your report's record source named "Lookup_Bank"
and "Lookup_Branch"?

Have you used lookup fields in your table designs? If so, read
http://www.mvps.org/access/lookupfields.htm
--
Duane Hookom
MS Access MVP
--

Bank and Branch are not in Report Sorting and Grouping.

Here's a brief Relationship:

Tables:
1. 'Banks/Alliance Partners' (contains field 'Bank' with a BankID
autonumber)
2. 'Branches' (contains field 'Branch' with a BranchID autonumber)
These support a further table:
3. 'Bank/Branch Information' (contains field 'Bank' and 'Branch' which are
the dual primary key fields and are sourced via Look Up to the 1st 2
tables)

Query:
1. 'Source QRY for Individual LDC Portfolio Report' is based off Table
'Bank/Branch Information'. The Bank and Branch fields are sorted here in
Ascending Order in design view.

Report:
1. 'Individual LDC Portfolio Report'. Under Report Properties in the Order
By field I have the following expression 'Lookup_Bank.[Bank],
Lookup_Branch.Branch'. This was to help ensure the report would sort
ascending ALPHABETICALLY rather than numerically as per the respective
supporting Autonumber fields. Took me ages to work this out!

As mentioned before - when the Report is run I have VBA code in place for
a
dialog (Form) box to open to further sort the underlying query (by
Individual
LDC). The display the results in the Report.

Thanks for your quick reply's on this Duane - I'm going to keep playing
around with it to see if I can get it to work - if you can see anything
here
that could help, just let me know. Cheers - Jase

Where else are you using Bank and Branch? Are they in the sorting and
grouping? Have you viewed your report's record source query in datasheet
view to see if the error is generated there rather than in your report?