Question Conversion from 1997 to 2010 and Issues

[dflamion]

I teach Access, but it has been years since I used it in a business. I am helping a company convert from 1997 to 2010. The conversion itself wasn't too difficult, and I have solved several small issues, but I have one that stumps me. On a report, instead of the answer to a simple calculation, it prints: #Type! or a very small square. The formula is =[AdPkTot]*fnGet_mLngQtyPacksPerMix(). Both of these items [AdPkTot] and fn...() are used in other formulas in the report that work fine. I do not know where the report is coming from - I assumed it was a query, but I don't know for sure. I viewed the VBA code and it appears to have no errors. (ie No debug messages appeared.):banghead:I have been working on this one issue for 2 days now. The report opens from a form with 3 parameters. When I try to open it switching from design view to preview, (not from the form) it also asks for 3 other parameters, but I don't know where to find them - IF that is the problem.

 

[Severin]

Resolve it in the query!~)

 

[pbaldy]

...2010 reports don't like calling functions...

Do you have a cite for that? I haven't had a problem.

 

[boblarson]

Do you have a cite for that? I haven't had a problem.

I agree with Paul. No problem with functions in Reports in 2010 here.

Now, I have had a problem on Reports, at times, when the control is named the same as a field in the record source and that field is being used in a calculation. So, my suggestion is to see if there is a control on the report that is named AdPkTot and change the control name to something like txtAdPkTot and then refer to the FIELD in the calculation (if you have Name Auto Correct enabled, it will change your expression to use the name you have changed so you may have to change that expression to reflect the field name and not the control name.

 

[dflamion]

Resolve it in the query!~)

Since it isn't my database, I don't know which Query it is. I have tried clicking on Object Dependencies, but it doesn't work. It says it may take a few minutes, but I never get the option of choosing the "to or from" option. (I read where if you use code, it won't work.)
I have gone through all queries and clicked on Parameters, and can't find any parameters at all.

 

[dflamion]

I agree with Paul. No problem with functions in Reports in 2010 here.

Now, I have had a problem on Reports, at times, when the control is named the same as a field in the record source and that field is being used in a calculation. So, my suggestion is to see if there is a control on the report that is named AdPkTot and change the control name to something like txtAdPkTot and then refer to the FIELD in the calculation (if you have Name Auto Correct enabled, it will change your expression to use the name you have changed so you may have to change that expression to reflect the field name and not the control name.

I will give this a shot and get back to you - this may be it!

 

[Severin]

I deleted the message... it was incorrect I just tested...

I have had the same issue and solved it in the query and I don't know if the situation Bob is describing was at large. The report was showing that little funny square when I tried to call a function!~)

 

[boblarson]

The names of most of the boxes are Text33, etc. I found two boxes where the field name was the same, so I just typed the txt in front of the Name, but that didn't work (and the field was still in the formula)

First thing that doesn't make sense:
I have two other formulas on the report that use the same exact "fields" and they both work:

1) AdPkQty: [rmqty]/[adpktot] This one works
2) =(Int(((([AdPkQty]/fnGet_mLngQtyPacksPerMix())+0.05)*10)))/10 This one works
3) =[AdPkTot]*fnGet_mLngQtyPacksPerMix() This one does NOT work

In reports you should, if possible, not try to refer to controls in calculations but use the fields and you normally will have to recreate the entire calculation instead of trying to refer to another control

Also, if ANYTHING is wrong on there it can make another control display #Name, #Error, #Type, etc. even though it is really not that control's problem. So you need to find any problems elsewhere.

I would try to create your calculations in the underlying query, if at all possible, as it will more likely work and it will also take less time to display in the report.

I also found this in a query: AdPkTot: [Forms]![PrintRptFmAddPac]![APperMix]

That is fine as long as that form remains open.

Another issue: Users type in 3 parameters in a form to open the report. Works fine. If I try to open the report in preview (bypass the form) it goes through a series of boxes asking for the parameters for those same 3, plus 3 other fields, and repeats some of them once or twice.

That is not an issue. That is an incorrect understanding of how parameters work in queries. If you refer to a form for parameters, you need to keep the form open until the report is closed for it to use the form values. If the query can't find the form, then it pops up the parameter request for each of the missing parameters.

 

[dflamion]

In reports you should, if possible, not try to refer to controls in calculations but use the fields and you normally will have to recreate the entire calculation instead of trying to refer to another control

Also, if ANYTHING is wrong on there it can make another control display #Name, #Error, #Type, etc. even though it is really not that control's problem. So you need to find any problems elsewhere.

I would try to create your calculations in the underlying query, if at all possible, as it will more likely work and it will also take less time to display in the report.


That is fine as long as that form remains open.


That is not an issue. That is an incorrect understanding of how parameters work in queries. If you refer to a form for parameters, you need to keep the form open until the report is closed for it to use the form values. If the query can't find the form, then it pops up the parameter request for each of the missing parameters.

Gotcha! The main problem is that NO ONE in this company knows how to use Access - they all use it by selecting buttons on forms to enter data and print reports. The company fired its IT people and outsourced the job to an outsider who comes in 2 days a week - he doesn't know Access either, which is where I come in. No one knows anything about queries, tables, etc. So, I need to find the fields and query/queries that supply the information for the report...

Where would you look for this? fnGet_mLngQtyPacksPerMix() Is the actual field name mQtyPacksPerMix() - and would it be in a table? Are the parentheses indicative of anything? (like requiring another field?)

 

[dflamion]

I deleted the message... it was incorrect I just tested...

I have had the same issue and solved it in the query and I don't know if the situation Bob is describing was at large. The report was showing that little funny square when I tried to call a function!~)

Just to be sure - you saw a funny little square too? Strange that the same function is called in another calculation and it works fine...

 

[boblarson]

Just to be sure - you saw a funny little square too? Strange that the same function is called in another calculation and it works fine...

Could be a format being applied to something that can't work with that format.

 

[dflamion]

Also - I tested the 2 pieces of the formula returning the #Type! error:
=[AdPkTot]*funGet..." The fnGet... returns a "1" when I remove the [AdPkTot] from the formula. However, when I remove the "fnGet..." and leave only the [AdPkTot] (which points to the APperMix box on the form) I get a small gray box. So I don't think the "fnGet..." is the problem. I left the form open - it actually opens automatically to input the parameters and goes directly to the report.
Would it be easier to recreate a formula on the report itself? And, it worked in '97, but not '10.

 

[dflamion]

I solved my problem by creating a new text box and pointed to the form to create the control source. Thank you all for your help.