VBA procedure in Access query

[Nails1]

Noob here. I did try Search first.
I have an MS Access app developed 2005. Now running it in 2007.
I have a query that calls a procedure:

cfs: bakRoundData((tblDischarge.WaterDischargeRateMeasure*35.3147),3)​

The field name "cfs" is a water flow measurement that I'm converting from "cms", which is what the table stores as "WaterDischargeRateMeasure" (converting to cubic feet per second from cubic meters per second).
bakRoundData is a procedure from a public module. It returns calculations with specified significant figures (in this case, 3 sig figs). The procedure is below -- apologies for losing the indents.

Anyway, the code works fine in Access 2005, but I can't get it to run in 2007. I don't get any errors, it just doesn't work -- the answer table has 8-digit values. When I put a breakpoint in the code, it stops but only several moments after the answer table is generated; and I apparently can step through the code right but that doesn't change the answer table. I tried putting an error in the procedure, compiling (caught the error), fixing it, and recompiling (I mean, I forced a recompile), but it still doesn't work. I haven't yet tested this procedure in other contexts -- will do that now.

I use this query as the recordsource for a report. Is there some option that I need to check in Access 2007 to make this work?

Hope this is enough info.

--
Nails1 (normally Nails, but there appears to be another one already on this forum)

Public Function bakRoundData(inputData As Single, selectedSigFig As Byte) As Single

On Error GoTo ErrorHandler

Dim fTrimData As Boolean
Dim sngTrimData As Single

If inputData = 0 Then
bakRoundData = 0
Else
If selectedSigFig = 0 Then
fTrimData = True
selectedSigFig = 3
Else
fTrimData = False
End If

Select Case inputData
Case Is < 0.00000001
If inputData = 0 Then
bakRoundData = 0
GoTo ExitProc
Else
sngTrimData = 8 + selectedSigFig
bakRoundData = Round(inputData, sngTrimData)
End If
Case Is < 0.0000001
sngTrimData = 7 + selectedSigFig
bakRoundData = Round(inputData, sngTrimData)
Case Is < 0.000001
sngTrimData = 6 + selectedSigFig
bakRoundData = Round(inputData, sngTrimData)
Case Is < 0.00001
sngTrimData = 5 + selectedSigFig
bakRoundData = Round(inputData, sngTrimData)
Case Is < 0.0001
sngTrimData = 4 + selectedSigFig
bakRoundData = Round(inputData, sngTrimData)
Case Is < 0.001
sngTrimData = 3 + selectedSigFig
bakRoundData = Round(inputData, sngTrimData)
Case Is < 0.01
sngTrimData = 2 + selectedSigFig
bakRoundData = Round(inputData, sngTrimData)
Case Is < 0.1
sngTrimData = 1 + selectedSigFig
bakRoundData = Round(inputData, sngTrimData)
Case Is < 1
sngTrimData = selectedSigFig
bakRoundData = Round(inputData, sngTrimData)
Case Is < 10
sngTrimData = -1 + selectedSigFig
If sngTrimData < 0 Then
bakRoundData = Round(inputData * (10 ^ sngTrimData), 0) / (10 ^ sngTrimData)
Else
bakRoundData = Round(inputData, sngTrimData)
End If
Case Is < 100
sngTrimData = -2 + selectedSigFig
If sngTrimData < 0 Then
bakRoundData = Round(inputData * (10 ^ sngTrimData), 0) / (10 ^ sngTrimData)
Else
bakRoundData = Round(inputData, sngTrimData)
End If
Case Is < 1000
sngTrimData = -3 + selectedSigFig
If sngTrimData < 0 Then
bakRoundData = Round(inputData * (10 ^ sngTrimData), 0) / (10 ^ sngTrimData)
Else
bakRoundData = Round(inputData, sngTrimData)
End If
Case Is < 10000
sngTrimData = -4 + selectedSigFig
If sngTrimData < 0 Then
bakRoundData = Round(inputData * (10 ^ sngTrimData), 0) / (10 ^ sngTrimData)
Else
bakRoundData = Round(inputData, sngTrimData)
End If
Case Is < 100000
sngTrimData = -5 + selectedSigFig
If sngTrimData < 0 Then
bakRoundData = Round(inputData * (10 ^ sngTrimData), 0) / (10 ^ sngTrimData)
Else
bakRoundData = Round(inputData, sngTrimData)
End If
Case Is < 1000000
sngTrimData = -6 + selectedSigFig
If sngTrimData < 0 Then
bakRoundData = Round(inputData * (10 ^ sngTrimData), 0) / (10 ^ sngTrimData)
Else
bakRoundData = Round(inputData, sngTrimData)
End If
Case Is < 10000000
sngTrimData = -7 + selectedSigFig
If sngTrimData < 0 Then
bakRoundData = Round(inputData * (10 ^ sngTrimData), 0) / (10 ^ sngTrimData)
Else
bakRoundData = Round(inputData, sngTrimData)
End If
Case Is < 100000000
sngTrimData = -8 + selectedSigFig
If sngTrimData < 0 Then
bakRoundData = Round(inputData * (10 ^ sngTrimData), 0) / (10 ^ sngTrimData)
Else
bakRoundData = Round(inputData, sngTrimData)
End If
Case Else
bakRoundData = inputData
End Select

If bakRoundData = 0 Then
bakRoundData = inputData
End If
End If

ExitProc:
Exit Function

ErrorHandler:
Call bakErrorHandler(Err.Number, Err.Description, "basBackEndStuff.bakRoundData")
Resume ExitProc

End Function​

 

[Nails1]

That procedure runs fine in the Immediate window.

 

[Nails1]

And I should've said that the tables are native Access within this dbf (i.e., not linked anywhere).

 

[PHV]

No null value in tblDischarge.WaterDischargeRateMeasure ?
What happens if you comment out the On Error GoTo statement ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Nails1]

Thanks for the reply.

> No null value in tblDischarge.WaterDischargeRateMeasure ?

No. Normalized, and a measurement is necessary to enter a record. Table rule requires a measurement. Zero is a valid measurement.

> What happens if you comment out the On Error GoTo statement ?

In most cases my error handler mostly just records errors (identifies the module and other debugging info). It isn't being triggered here.

I rem-ed it and it still doesn't work.

Again, this worked in earlier version of Access. I think it's something about 2007 version. I have another PC with 2010 and will see it it's failing there, too.

Thanks again for your reply.

 

[Nails1]

Okay, this is weird.

This is a field in my query:

cfs: bakRoundData((tblDischarge.WaterDischargeRateMeasure*35.3147),3)​

A particular row value of tblDischarge.WaterDischargeRateMeasure is 0.292. Take that times 35.3147 for 10.3118924.

bakRoundData correctly applies Round(10.3118924, 1), correctly returning 10.3.

But it shows up in the query's answer table as 10.3000001907349.

This happens even if I rem all of the bakRoundData procedure except "bakRoundData=10.3" -- every result in the answer table will be 10.3000001907349. (And no, I don't do anything else with that number, besides the "cfs: ..." thing above.)

But many other values of tblDischarge.WaterDischargeRateMeasure work just fine.

I swear, pre-2007 versions of Access didn't do this.

Any ideas? (This is gonna drive me crazy.)

--
Neal

 

[1DMF]

what do you get in the returned resultset if you use

cfs: Round((tblDischarge.WaterDischargeRateMeasure*35.3147),3)

removing the UDF from the query.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Nails1]

I'd get 10.311. In my procedure, the 3 indicates 3 sig figs, not the third decimal place. My procedure figures out that I really need (in this case, 10.3).
Otherwise, the procedure does just what you say; and it definitely returns 10.3 correctly. It goes goofy in the query.
The tblDischarge.WaterDischargeRateMeasure values are all over the board, from thousands to tenths. I need 3 sig figs for all. (Ironically, I did this because a lot of my data get imported from Excel, which notoriously turns 3 sig figs into weird doubles.)
I use this procedure in a million other places in the db in code, but rarely in query defs. This named query is for a report, and I'm looking forward to the day when I can move all of that reporting to xml.
My work-around (so far) is a new bakRoundDataStr which just calls bakRoundData except converts the return value to a string. I change it back in the query def. (I hate hacks like that -- especially since it works fine in earlier Access.)
--
Neal

 

[1DMF]

If you only want one decimal, wouldn't Round(myvar,1) do what you need?

I'm just wondering if the end query is picking up data typing from either the var in your UDF or from the initial SQL column.

Rather than returning stringyfied values from your UDF or parsing using multiple UDF's, if it is 'string' formatting you want, perhaps using Format in the query would be best?

cfs: Format(Round(tblDischarge.WaterDischargeRateMeasure*35.3147),1))

This should give you "10.3"


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Nails1]

> If you only want one decimal,

No, I need three significant figures, for a wide range of numbers.

> I'm just wondering if the end query is picking up data typing from either the var in your UDF or from the initial SQL column.

I don't use vars -- the procedure (before I changed it) returns a single. The procedure definitely works right. I even changed the procedure to explicitly return 10.3 no matter what the inputs, and it displayed as 10.3000001907349.

Many return values display correctly (and in previous versions of Access, they all did). But some, like 10.3, display as 10.3000001907349.

My "stringified" workaround doesn't actually work after all. I made a mistake, recasting the string as a long. When I recast as a single, it displays 10.3000001907349. Arrgh.

But in the immediate window, ? Csng("10.3") correctly returns 10.3.

I just don't get it.

--
Neal

 

[PHV]

And what about this in the immediate window ?
? csng("10.3000001907349")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Perhaps this will help :

http://www.vertex42.com/ExcelTips/significant-figures.html

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Nails1]

? csng("10.3000001907349")
10.3


> Perhaps this will help

Interesting. I could use that in the few times I need this in a query. I'd like to divorce the query defs from the code anyway.

But I'm getting an error: "Undefined function "Log10" in expression." I have Excel 12 Object Library in references -- will check what else I need.

Here's the ugly beast I end up with for that column in the query:
cfs: ROUND(([tblDischarge].[WaterDischargeRateMeasure]*35.3147),3-(1+INT(LOG10(ABS(([tblDischarge].[WaterDischargeRateMeasure]*35.3147))))))

 

[Nails1]

It runs with "LOG" instead of "LOG10". But I get a lot of "#Error" results (looks like all returns > ~20). And many results have the wrong sig figs.

Exporting the data to Excel and then modifying the formulas for correct cell references: it runs great. Even with "LOG" instead of "LOG10".

I don't think Access likes this much math.

 

[Nails1]

In the off-chance anyone's still tuned in ... I found the following chasing links originally provided by 1DMF. It replaces my wonky code, and for some reason it works when mine didn't. Even though I swear both are returning the exact same results!

Public Function bakRoundData(inputData As Single, selectedSigFig As Byte) As Single

On Error GoTo ErrorHandler

Dim sngExponent As Single

bakRoundData = 0

If selectedSigFig >= 0 Then
If inputData <> 0 Then
sngExponent = Int(Log(Abs(inputData)) / Log(10#))
bakRoundData = WorksheetFunction.Round(inputData, selectedSigFig - (1 + sngExponent))
End If
Else
'figure out something intelligent here, else just return 0
End If

ExitProc:
Exit Function

ErrorHandler:
Call bakErrorHandler(Err.Number, Err.Description, "basBackEndStuff.bakRoundData")
Resume ExitProc

End Function

 

[1DMF]

Glad you found the link helpful and achieved a resolve.

It is a bit puzzling the amount of hoops you seem to need to jump through to get to what really is a simple value.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Nails1]

Yeah, I really don't get it. I rem'd the whole thing to expressly return "10.3", and the query changed it into that double. My original procedure returned "10.3", and it showed up goofy in the query. This procedure returns "10.3", and the query gets it right. (Beat head on desk.)
But it works. And this new algorithm is sure prettier.