Type correction help.

[JGalletta]

I have this as the control source for a text box in the detail of a report:

=[Rate]*[Acres Sprayed]*[Cost/Unit]/(Len([Combo50].[Text])-Len(Replace([Combo50].[Text],",",""))+1)

In layout view, it shows the correct values as currency, in report view I get the #Type! error in that control. How do you correct this? I've tried str() and others, but apparently haven't found the correct method to get this value to be displayed as currency ($####.##) in the text box. Quick fix, no?

 

[llkhoutx]

First, make sure all the values in the formula are non-null and not empty. Then use the correct control property value to specify currency, with the correct number of digits.

 

[JGalletta]

So, I've updated the control source to this:

=Nz(Nz([Rate],0)*Nz([Acres Sprayed],0)*Nz([Cost/Unit],0)/(Nz(Len([Combo50].[Text]),0)-Nz(Len(Nz(Replace([Combo50].[Text],",",""),0)),0)+1),0)

I know - overboard with the Nz(), but this still returns the #Type! error. Control format is set to currency and decimal places is set to 2 (changed from auto). Any clue what's going on? Am I attempting to display a string as numbers or vice versa for it to be giving me this error?

 

[pr2-eugin]

Using an Nz() function, sometime might return a String, even though you have used a zero..
And you are forcing an Arithmetic operation with that.. So you have to cast the String into Long/Integer.. Then perform the calculation..

Other option is that.. (It might be a long winded process, but worth it).. Why not calculate step by step? It might shed some light on where the error is..

 

[JGalletta]

I should be returning a number as opposed to a string, right?

 

[llkhoutx]

One of your fields is probably a string.

 

[JGalletta]

So, none of the fields are strings. This error is only happening in report view. I'm beginning to think it is a result of Access 2010's new "calculated" datatype. But, I cannot be sure. I have this returning correct values in layout and print preview:

=CCur(Nz([Rate]*[Acres Sprayed]*[Cost/Unit]/(Len([Combo50].[Text])-Len(Replace([Combo50].[Text],",",""))+1),0))

CCur() is coercing currency datatype.

This is a bit boggling. Also, this options on this field do not allow for subtotals at group level, if you know what I mean. The only available option is count - none of the mathematical operations such as sum, standard deviation, variance, etc are available. This leads me to think that this control is a text type. What the heck is going on here........

 

[llkhoutx]

"[Cost/Unit]" might be the problem. Is it 2 fields or 1? If 1, it's probably an invalid name.

 

[JGalletta]

I hear that, but isn't true that the calculation wouldn't work correctly in the other views if it were an invalid name, i.e. the data is being referenced properly?

 

[llkhoutx]

Logically, yes, but with Access it may not be true.

 

[JGalletta]

My concern is that this control is gathering data from the .text parameter of other controls that is not populated until the report is generated. Therefore, the .text values may not be available/would be null at the time of calculation. Any way I could trigger a requery or other refresh method that could gather the proper data after the report has been created?

 

[JGalletta]

I attempted changing the field name [Cost/Unit] to one without special characters, and this was not the problem. There must be something we're missing.

 

[pr2-eugin]

Could you recreate the same problem in a Stripped down version of your DB and upload it here, so we can see what is happening?

 

[JGalletta]

I'll give it a shot. There's tons of records in the DB, as it's been in use for a year now. So, I'm going to make a copy and clear out the sensitive information, and replace with some dummy info.

 

[JGalletta]

Here you go! The error is occurring in report "Spray Cost by Pest." In control [Text21]. Hopefully you're using A2007 or higher, because this DB relies heavily on multi-valued fields.

What I'm trying to accomplish is somewhat of a weighted average for the cost of applying treatments per pest. Where our issue arises is in the fact that some chemicals target more than one pest, so costs must be divided among the target pests to (more) accurately reflect the economic impact of each pest. This is where the Len(xx)-Len(Replace(xx,",","")) comes into account. This determines the amount of items in a multi-valued field (because I couldn't come up with another method) in order to divide the spray cost by the proper amount of pests it is targeting.

Got me? I hope we figure this out.

Thanks

P.S. Feel free to do a straight open of the DB and check everything out, but in order to get to this report you will need to hold shift while opening the DB in order to see the back-end/navigation panel. Also, don't be alarmed when you can't find the close buttons for your design view panels... they've been disabled by some code that runs upon opening the DB.

 

[pr2-eugin]

Okay the changes are very simple.. Use the following..

=CCur(Nz([Rate]*[Acres Sprayed]*[Cost/Unit]/(Len([Combo50])-Len(Replace([Combo50],",",""))+1),0))

I have taken out all the unwanted .Values and .Text, this is nothing but overload.. By default they are included.. So you do not need them.. Also change the Format of the Text from 'Rich Text' to 'Plain Text'

EDIT: Attached the changed DB.. Just look for the report.. Some forms annoyed me so I deleted Macros.. Sorry..

 

[JGalletta]

That does return a value in report view, but the (Len()-Len(Replace())+1) of Combo50 returns 1 in all cases. Even when there are multiple values in Combo50 separated by commas - This is the reason I went with the .text value for that part of the function. Since this is showing that the Len() function only works with the .text property of Combo50, would I be able to coerce the type of the Len() function to the correct type?

 

[JGalletta]

See what I'm saying?

 

[pr2-eugin]

I really do not understand why you have the Len() and Replace().., In Simple ENGLISH.. can you say what you are doing there??

 

[JGalletta]

The whole Len/Replace mess is my method of determining the amount of entries in a multivalued field. -- Count the amount of commas, and add 1 to determine amount of entries.

So the field containing "Mummy Berry, Phomopsis" would do this:

Len("Mummy Berry, Phomopsis") = 22
Replace("Mummy Berry, Phomopsis",",","") = "Mummy Berry Phomopsis" (Replace commas with empty string to shorten string length by the amount of commas in the string)
Len("Mummy Berry Phomopsis) = 21

First Len - Second Len = 1

Add 1 to correct for the fact that a field with 1 value with have 0 commas, 2 values will have 1 comma, 3 values will have 2 commas, etc. So, it follows that the difference of lengths + 1 = the amount of values listed in the field.

So First Len - Second Len + 1 = 2 values

In this case (Mummy Berry, Phomopsis) the cost will be divided by 2 to attribute half the cost to each pest listed.