Problem with data format (1 Viewer)

Big Pat

Registered User.
Local time
Today, 02:08
Joined
Sep 29, 2004
Messages
555
Hi,

I receive data each week that comes in Excel, but apparently from another system called Qlikview before that (which I don't have access to). One of the columns is called %TE and can have values such as
0%
10%
66.6666667%
or blank

They are left justified, there is no set number of decimal places, though the maximum appears to be six and when I import them to access, they appear as text.

But I need to do calculations on them, apply conditional formatting and display them in a report, preferably with no decimal places.

How should I go about converting this data to numeric in a "master query", the output of which would then be used in subsequent queries and reports.

Apparently changing the source is not an option!
 

Ranman256

Well-known member
Local time
Yesterday, 22:08
Joined
Apr 9, 2015
Messages
4,339
Your target access table needs 2 columns:
[pctString],[pctValue]

After the data imports, run an update query to convert.
It takes the pctString and converts it to numeric....

=CDbl(Left([pctStr],len([pctStr])-1))
 

Big Pat

Registered User.
Local time
Today, 02:08
Joined
Sep 29, 2004
Messages
555
Well they both work equally well in this case, as far as I can see. I didn't know about Val() and can already think of one or two places I might be able to make use of it. Cdbl was probably what I'd been blindly groping towards, as I now recall having used CLng and CDate in the past.

The only difference seems to be in how these functions have treated a rogue record I found which has only % symbol and no numbers. Cdbl returns #Error and Val returns 0%. But I'll need to exclude those from my process anyway.

Thank you both. This has been really helpful!
 

Users who are viewing this thread

Top Bottom