Access 2016 Report Convert Text To Currency

Bob,

  Base the report on a query and in the query, change the field to:

curSelection25: CCur([SELECTION_25])

and remote the SELECTION_25 field.

 Then change the report to look at that field.
 
Jim.

Hi Jim,

I don't really understand what you are suggesting.

There is no query.  The input to the report is data in a generic table for reporting purpose.  The table contains a Key to the data for the specific report and 200 Text Fields of data that can be used for reporting.

Thanks,
Bob C.

<<There is no query.   >>

 Create one based on the table and pull the fields you need for the report into the grid.  Then define a column in that query as I outlined above. Open the report in design view and change its record source to the query instead of the table.

Jim.

Well, why on earth is it a Text column? Text columns are not ment to be summed.

Change the data type to Currency. According to your needs of precisision maybe Double or Long as cents are also an option. But Text is none,

Hi Ste5an,

All of my reports use this method instead of quries because (with the exception of totla which di have few of) Witing the data to my report table and then using the Report On Load Event to read the data for the specific report works extremely well and makes changes easy to do as well as troubleshooting since I can always look at the data in the table.

Thanks,
Bob C.

Simply wrap your text values in a conversion to currency function CCUR() to convert them to numeric before summing them:

=SUM(CCUR(NZ(SELECTION_25,0)))

Here we are substituting zero (0) for any nulls with the NZ() function before applying the conversion function, then summing.

See if that works with what you've got....

Convert the field to a value and sum this:

=Sum(Val(Mid([SELECTION_25],2))

Select allOpen in new window

Then apply a format to the textbox as you like.

Hi Mark / Gustav,

This is the type of solution I was looking for.

I tried each one and they both appeared to work.

In actual fact after doing the coding the first time I ran the report it worked perfectly.

However after I closed the report and ran it a second time I encountered the same error.

Any ideas?

Thanks,
Bob C.

Just an FYI on this:

=SUM(CCUR(NZ(SELECTION_25,0)))
=Sum(Val(Mid([SELECTION_25],2))

I've found a couple times now where Access would not call a function inside a SUM().  Not sure if that was considered an "optimization" or was a bug. This was on a form, never on a report, but as a result, I've always taken care of any data conversion work in the data source with a query and never had a problem as a result, which is why I suggested he go that route.

Jim.

If you get the "data type mismatch", then it means that somewhere in your data you are getting a piece of data that the functions can't handle.  Perhaps some garbage of some kind.

try filtering for just a few records and look at the raw data in the table and then see what/if you get an error in the report for those same records..

The answer is in there somewhere....

Hi Experts,

I gave the solution to ste5an because he was the closest to the solution that I have adopted.

What I did was added 10 Currency Fields to the Report Data Table, used them for the Report Amount Fields and formatted the totalling as a number of you suggested.

As mentioned previously I have found that this architecture for reports has a number of advantages over using Access Queries.  In particular it is managed with VBA Code, is easy to debug and runs very fast.  Coding is quick because I have a Boiler Plate Template that I can copy in an then modify. I delete the report records based on the Report Id and User as soon as the report is closed by the User.

Thanks to all and take care.
Bob C.

You still could have used a query. All you needed to do was add a "type" field and then:

Sel25:IIf([Sel25Type]="C",CCur([SELECTION_25],CStr([SELECTION_25])

 allowing you to return a currency value or a string value based on the type field.

 But glad you got your problem solved.

Jim.

Hi Jim,

Thanks for the insight.

This would have meant creating a query when what I ended up doing bypasses it.

Take care.
Bob C.

Since I always like a challenge, I decided to try and replicate the error you were getting.
I created a table with a short text field named [String], and filled it with 5 records with string values like "$1.01", "$2.02",...$5.05
I created a report and put a textbox control in the footer with this formula:  =SUM([String])
When I ran the report, I got: 15.15 in the footer control - no error.
I then replaced the value "$5.05" with "junk" and reran the report.
I got your "Data type mismatch in criteria expression." error.

One of the problems with using the string data type for numeric data types is you can have values that don't convert to numeric values for functions like SUM(), thus you have issues that are "mystifying".  Just one of the reasons for using proper data types for your values, as you were forced to do in your solution above.

As I said earlier, you're getting this for a reason - one or more of your values are NOT something that is allowed in a SUM().  Fix them somehow so that you have all values that qualify to be acted on by a SUM(), or are converted to something that will not interfere, and you'll get what you need.

Hi Mark,

Thanks for doing this and letting me know.

I'm in the middle of something else right now but will take a look at testing this myself somethime.

Take care.
Bob C.