Text to Currency formatting issue VBA

[denise132008]

i have code that creates a new table with designated fields(see below). The rstDest table has Currency values for each month and the rstSource table has text values. I need to change these text values to currency and am running into some issues. One thing is yes, the text field can be null, there won't always be a value. Any help is greatly appreciated!!

I have tried this:
!Jan.Value = CCur(rstSource.Fields(5).Value)

And it doesn't work. Below is the code.........

Do
With rstDest
.AddNew
!CompanyEntity.Value = rstSource.Fields(0).Value
!PrimComm.Value = rstSource.Fields(1).Value
!SecComm.Value = rstSource.Fields(2).Value
!F4.Value = rstSource.Fields(3).Value
!F5.Value = rstSource.Fields(4).Value
!Jan.Value = rstSource.Fields(5).Value
!Feb.Value = rstSource.Fields(6).Value
!Mar.Value = rstSource.Fields(7).Value
!Apr.Value = rstSource.Fields(8).Value
!May.Value = rstSource.Fields(9).Value
!Jun.Value = rstSource.Fields(10).Value
!Jul.Value = rstSource.Fields(11).Value
!Aug.Value = rstSource.Fields(12).Value
!Sep.Value = rstSource.Fields(13).Value
!Oct.Value = rstSource.Fields(14).Value
!Nov.Value = rstSource.Fields(15).Value
!Dec.Value = rstSource.Fields(16).Value

.Update
rstSource.MoveNext
End With
Loop Until rstSource.EOF

[HiTechCoach]

Have you tried using CCur(rstSource.Fields(x).Value)?

[denise132008]

Yes, and i get a Type Mismatch error.

[hansup]

Yes, and i get a Type Mismatch error. Which code line gives you the Type Mismatch error? In debug mode, the problem line should be highlighted in yellow.

The CCur() function will accept text arguments which represent valid currency values. For example, this one works:
Debug.Print CCur("27.75") But this one triggers error #94, "Invalid use of Null":
Debug.Print CCur(Null) You can get error #13, "Type mismatch", from CCur() by feeding it an empty string:
Debug.Print CCur("") But an empty string is not the same as Null. Please give us more specific information about the values which are causing you trouble with CCur().

[denise132008]

!Jan.Value = CCur(rstSource.Fields(5).Value) gives me the #13 error Type mismatch and it is probably due to it being an empty string. So is there a way around this using an If statement perhaps?

[hansup]

!Jan.Value = CCur(rstSource.Fields(5).Value) gives me the #13 error Type mismatch and it is probably due to it being an empty string. So is there a way around this using an If statement perhaps?

Good! Trouble is I'm unsure about the best way for you to deal with this.

You could try an IIf() expression.
CCur(IIf(Len(rstSource.Fields(5).Value)=0, 0, rstSource.Fields(5).Value))

However, that looks more challenging than I'd like ... without testing, it's hard for me to tell whether I even have the right number of parentheses in that expression. And you may need to deal with empty strings in several of those fields you want converted to currency, right?

Would you consider running an update query to replace the empty string values with Null? If so, we can use the Nz() function with CCur(). That should be easier to cope with than the IIf() approach.

[hansup]

Consider a SQL set-based approach. Create a new query and paste this into the query's SQL View, substituting your actual field and table names.
SELECT
CompanyEntity,
PrimComm,
SecComm,
F4,
F5,
CCur(Jan & "0") AS Jan_Value
FROM
SourceTable; If that SELECT query approach works, you could convert it into a "make table" query. I think the SQL approach could be easier than opening recordsets for two tables and copying transformed values from each row from one into the other.

[denise132008]

I would be willing to try an update query, but have never used one in VBA before?

[hansup]

I would be willing to try an update query, but have never used one in VBA before?
OK. Let's give it a try:
Dim strSql As String
strSql = "UPDATE SourceTable " & _
"SET Jan = Null " & _
"WHERE Len(Jan) = 0;"

CurrentDb.Execute strSql, dbFailOnError

Or you could create a new update query in the query designer to do the same thing ... and run the query interactively.

But, if it were me, I would use the "make table" approach I mentioned earlier. Or rather create the empty destination table and use an append query (INSERT INTO ...) rather than a make table query.

[denise132008]

one thing i forgot to mention, the field names in the source data will change from month to month. So for example, Jan (A) might change to Jan (P) depending if it is an Actual or a Projection in revenue. So this may make this approach not feasible? This is why i chose to open 2 recordsets and transform the values. Make sense? Did i totally mess you up now? lol :beg:

[hansup]

Did i totally mess you up now? lol :beg:Not totally, Ms. 132008.

But variable field names does make it more challenging.

Here's what I'd do:

First make sure you really, really want separate columns for each month of the year. Generally, that is a design error. The rule of thumb is "rows are cheap; columns are expensive".

If you do need separate columns for each month, you can open SourceTable and determine which names you're currently dealing with. Then use those field names to build the SQL statement. It's more challenging than what I anticipated, but it's achievable. Wanna try?

[hansup]

Hmm. Maybe it would be simpler for you to just try changing this:!Jan.Value = rstSource.Fields(5).Value
to this:!Jan.Value = CCur(rstSource.Fields(5).Value & "0")

[denise132008]

Thank you SOOOOOO much hansup!!! That worked!!! No more errors!! Yeahh!!! :)

[hansup]

Good! Sorry it took so long for me to get there. It seemed so darn obvious in hindsight!

Did you ever get your export XML problem resolved?

[Imdabaum]

!Jan.Value = CCur(rstSource.Fields(5).Value) gives me the #13 error Type mismatch and it is probably due to it being an empty string. So is there a way around this using an If statement perhaps?


!Jan.Value = CCur(Nz(rstSource.Fields(5).Value,0))


If the string is Null, then it Casts 0 as the currency and no type mismatch.

Ah didn't read all the way down to see the solution... if there is a chance that the value = "" instead of just being null, then disregard my proposed solution. NZ only detects null.

[hansup]

!Jan.Value = CCur(Nz(rstSource.Fields(5).Value,0))

If the string is Null, then it Casts 0 as the currency and no type mismatch.

Ah didn't read all the way down to see the solution... if there is a chance that the value = "" instead of just being null, then disregard my proposed solution. NZ only detects null.
You're right that Null vs. empty string is key. I suggested rstSource.Fields(5).Value & "0" because that expression will return the character "0" whether the field contains Null or an empty string.

And CCur("0") produces a valid currency value, zero. No Nz() required.

Another text value, say "123.45" will just get an extra zero tacked on -> "123.450", and still result in a text value which CCur() can use.