Subtle Formatting Issues with Percent data

[flyinghippo99]

Hi All,

I'm not sure where to put this question - forms, VBA, or query. So I put it here.

So, I'm trying to display data that is in "percent" format in Textboxes on a form using a DLookUp of a query.

Each textbox on the form has the following VBA code as its ControlSource:

=IIf(DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])=-0.001,"na",IIf(DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])=-0.002,"nr",DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])))

Don't mind the names etc. It's just the sample. The logic here is simple. If the underlying data has -0.001 then display "na". If the underlying data has -.002 then display "nr". All else display the underlying data. Now, the underlying data is in
a query called QryMetric120055Combine(just naming convention).

Now, this VBA code above works for ALL data types(currency$, standard, etc.) but it fails for percent data. Why?
Note: All other data types are in a different query i.e. other than QryMetric120055Combine.


Here's what QryMetric120055Combine looks like:

SELECT MetricDate, FormatPercent(PercentCol,3) AS [Concentration Business Relationships]
FROM MetricValueTbl
WHERE (KeyNum=120055);

I used the FormatPercent function so that at least it shows the right amount of digits like -.001% or -.002%(but this imply that underlying data is -.00001 and -.00002).

The reason I had to use FormatPercent is because before I used the plain vanilla
Percent it was just showing 100% or 0%.

Anyhow, the DLookUp above doesn't work. It's showing #Error message in each of the textboxes instead of 2.3% , na, na, .9%, etc.

I even tried to play around with the VBA logic above so that instead of introducing the additional logic check for -.001 or -.002 and just :

DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])

And it gave me 0.000% instead of -.001%. Reducing it to the simplest case here.

 

[stopher]

FormatPercent returns a string so trying to then equate it to a number just won't work.

Did you try:

=IIf(DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])=-0.00001,"na",IIf(DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])=-0.00002,"nr",DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])))

As an aside, I would suggest you add/join MetricValueTbl to form's data source. This will avoid the multiple DLookups.

Post your database if it is still not working.

hth
Chris

 

[flyinghippo99]

FormatPercent returns a string so trying to then equate it to a number just won't work.

Did you try:

=IIf(DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])=-0.00001,"na",IIf(DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])=-0.00002,"nr",DLookUp("[Concentration Business Relationships]","[QryMetric120055Combine]","[MetricDate]=" & [Forms]![FrmDataValidationAdvisorMetrics1]![MonthT5])))

As an aside, I would suggest you add/join MetricValueTbl to form's data source. This will avoid the multiple DLookups.

Post your database if it is still not working.

hth
Chris

Chris,

Thanks for that. I hadn't realize that FormatPercent returns a string. Interesting.
I had tried using -.00001 and it still returned #Error# message but that was with FormatPercent.

So, I went back to my original formulation of QryMetric120055Combine:

SELECT MetricDate, Format(PercentCol,'Percent') AS [Concentration Business Relationships] FROM MetricValueTbl
WHERE (KeyNum=120055);

The problem with this original formulation is when you run it all the -.00001s just shows up as 0.00%!!! Since Format doesn't have decimal precision(right?).

So, when I do the DLookUp it still comes up with #Error#, which is odd since the underlying values in MetricValueTbl is still -0.00001. Just the display of it in QryMetric120055Combine is showing 0.00%.

So, I'm stuck in this dilemma. But your idea of adding MetricValueTble to the form to increase performance sounds interesting.

Here's what MetricValueTbl looks like(list of fields)

MetricDate ,KeyNum, CurrencyCol, StandardCol, PercentCol, TextCol,etc.

Then I have a separate VBA code that auto-generates the QryMetric120055Combine (or QryMetricXXXXXXCombine queries) based
on a lookup of an Attribute table that matches formatype to the right columns in the MetricValueTble like if it's currency then look under CurrencyCol, if it's standard formatype then look under StandardCol,etc. You get the drift.

That's why I don't think I can(?) directly lookup the MetricValueTble using DLookUp in the textboxes. So I had to go through these intermediate QryMetricCombines. And thus that's where I'm running into formatting % issues.


thanks!

flyinghippo99

 

[flyinghippo99]

oh, btw, in MetricValueTbl the PercentCol is of the following Data Type: Number, Field Size: Double, Format: Percent, Decimal Places 3

Maybe i should change that to 4? hehe. Let me try. Is Double too big? Should I try Single?
===================================================================
Actually, I just changed decimal places to 4. But it doesn't help. What really matters is the QryMetricCombine. Is there another Format function? Like FormatDecimal?
========================================================
Then I tried FormatNumber out to 4 digits and it just returns 0.0000. Hmm.. Maybe in the source table MetricValueTbl, I should change Format: Percent to something else? If so, I still want it to display % correct in the QryMetricCombine and in the textboxes on the form...

 

[spikepl]

I hadn't realize that FormatPercent returns a string.

Formatting operations are a step from data to something consumable to the human eye, i.e. TEXT. When you do it explicitly using the various offspring of the FORMAT function, you wind up with strings

I still want it to display % correct in the QryMetricCombine ...

What I do not quite understand, is why do you wish to search/query using a particular format. This is what seemingly created your problems. Data manipulation or searches are best done on data as-is. FORMAT settings in properties of table fields do not change the field's numerical data, but only how it is displayed. But query output is not intended for human consumption, unless shown through a datasheet or another view of a form or report.

The DISPLAY of the data is a different story, which you can handle in the user interface, where people SEE the data, by means of the required format set as property in the respective textboxes.

 

[stopher]

Can you post a copy of your database with a small amount of data.
Chris

 

[flyinghippo99]

Formatting operations are a step from data to something consumable to the human eye, i.e. TEXT. When you do it explicitly using the various offspring of the FORMAT function, you wind up with strings

What I do not quite understand, is why do you wish to search/query using a particular format. This is what seemingly created your problems. Data manipulation or searches are best done on data as-is. FORMAT settings in properties of table fields do not change the field's numerical data, but only how it is displayed. But query output is not intended for human consumption, unless shown through a datasheet or another view of a form or report.

The DISPLAY of the data is a different story, which you can handle in the user interface, where people SEE the data, by means of the required format set as property in the respective textboxes.

spikepl,

Thanks for the suggestions. You wrote," But query output is not intended for human consumption, unless shown through a datasheet or another view of a form or report."

Therein lies my problem. Entire story is a bit complicated and we can revisit that soon. But the short version is that QryMetricXXXXXXCombine is used as a datasheet "view" of sorts. The underlying data is stored in MetricValueTbl at the correct format. But for viewing,when the user clicks a button one of these QryMetricXXXXXXCombine is called upon and thus the need for the nice formatting.

So, I'm a bit stuck. So, you are suggesting doing the formatting in textboxes? Which I agree and understand. However, the control source for the textboxes is one of these QryMetricXXXXXXCombine. hehe.

One other thing, you wrote,"When you do it explicitly using the various offspring of the FORMAT function, you wind up with strings" That's interesting. But for some reason when I do

for QryMetric110000Combine:

SELECT MetricDate, Format(StandardCol,'Standard') AS [Funded Accounts - Total]
FROM MetricValueTbl
WHERE (KeyNum=110000);

Everything turns out fine on the textbox. That is -.001 is picked up. I know it's not percent. But if Format returns a string then why would the DLookUp work. Maybe I'm a bit confused on the Format & DLookUp.

So, it seems the only problem is with percent data. Perhaps, I should change the PercentCol in MetricValue to just normal decimal numbers. And just use the Format(PercentCol,'Standard') and then do all the formatting in the textboxes?
Let me try that idea out!!!

Thanks!

flyinghippo99

 

[flyinghippo99]

Can you post a copy of your database with a small amount of data.
Chris

Chris,

OK. That's probably a good idea. I might need to scale it down a bit to post it on here since it's rather big. What's the size limitation for posting db? So I know how much to scale down to just fix the essential issues.

thanks!

flyinghippo99

P.S. Also, btw, how do I post the db? I don't see an upload file button anywhere.

 

[stopher]

Uploading is explained here.

Chris

 

[flyinghippo99]

hi all,

Last night, I realized that changing MetricValue table might not help since the DLookUp is looking at the QryMetricXXXXXXCombine queries. And those are the formatting issues.

so, what's the best way around this?

thnx,

flyinghippo99

 

[spikepl]

Maybe now you want to reconsider your entirely selvinflicted formatting pains?
"QryMetricXXXXXXCombine is used as a datasheet "view" of sorts" is the problem - why do you display a query result directly, instead of going through a datasheet view of a Form, that would allow you formating the data as required and piles of other stuff?

 

[flyinghippo99]

Maybe now you want to reconsider your entirely selvinflicted formatting pains?
"QryMetricXXXXXXCombine is used as a datasheet "view" of sorts" is the problem - why do you display a query result directly, instead of going through a datasheet view of a Form, that would allow you formating the data as required and piles of other stuff?

spikepl,

Interesting suggestion. What I meant to say is that i run/call QryMetricXXXXXXCombine as is. It just happen that when it runs it displays the data as a datasheet view. It's nothing I do. Access just returns a datasheet view. Are you implying there's something i can do to make it return not as a datasheet view? I thought all queries from Access returns by default the results as datasheet view(?).

The second implied question is more penetrating. In the form, where I have a bunch of textboxes with DLookUps that looks up the values in QryMetricXXXXXXCombine. That's an area that can potentially be saved or made more efficient. But before I can do that i need to explain some implicit in the data. I'll do that later. I'm in the midst something now.

thnx

 

[Rabbie]

Queries always display in datasheet format. If you want a different format then use a form based on the query

 

[spikepl]

Tables and queries are objects that should not be seen, shown or manipulated by a user directly. For that pupose we have Forms and Reports.

Your entire predicament stems from your wish to display query results by simply showing the query as is, which forces you through the formating hoops. Don't.

If you want to show output from a query, make a form or a subform based on that query, and set its Default View to Datasheet. Each control of this form, bound to a field of the query, can be formatted according to needs, and data in tables and queries can be left as is, without any fiddling with their formats.

 

[flyinghippo99]

spikepl et al,

Your points are duly noted. However, I'm in a bit of pickle due to some formatting requirements. So, instead of talking in generalities, I've created a testDB with just the queries, tables, and form in question to isolate the issue here at hand. I've also uploaded and attached here per the suggestion.

In the attached testDB, you'll find MetricValueTbl(the master table. noticed how it's organized). Then the QryMetricXXXXXXCombine(s) queries which is used to select the individual metrics in MetricValueTbl. Notice also an Attr table which holds various attributes information including the FORMAT type for each metrics.

Also, the FrmDataValidationAdvisorMetrics1, which has all the textboxes and the DLookUps.

Of particular interest are Metrics 120050 and 120055 which have the % data that are having the formatting issues.

IF there are better design approaches then I'm all ears as well. If it's suggestions to fix the percent data formatting then all is well also. But I feel this design is a bit clumsy due to the formatting requirements. Personally, I feel like there's gotta be a fancy way to get to the MetricValueTbl without this contortions..

thanks!

flyinghippo99

 

[spikepl]

How about sorting your pickle out first? Your seem to be asking for mending the socks, but are prepared to chuck them out afterwards. This does not sound like much fun to me, so I am more interested in preventing the mending effort.

What exactly is this formatting requirement that requires interfering with the data? This mystery has so far not been revealed.

 

[flyinghippo99]

How about sorting your pickle out first? Your seem to be asking for mending the socks, but are prepared to chuck them out afterwards. This does not sound like much fun to me, so I am more interested in preventing the mending effort.

What exactly is this formatting requirement that requires interfering with the data? This mystery has so far not been revealed.

spikepl,

I'm sorry if I sounded like I was going to chuck the sock after the mending. That's NOT the case. My question was a bit more open-ended. The reason I had all these formatting issues because I went about it the long and complicated way. But perhaps, the root of the problem is that my data schema is wrong(??)/

The formatting requirements is to mix na and nr with other data types. So, for example, they have $150.45, na, $45.56, nr etc. As we all know that's not possible in most standard database. So, my solution around this is assign -.001 for na and -.002 for nr. This seems to work fine, but when it came to % I had problems thus the start of this thread.

Also, the fact that I had to do DLookUp for each cell based on QryMetricXXXXXXCombine seems a bit heinous. Thus throwing into question of my entire design of the database. If we can fix the % data then I'll be happy to go forward with just that.

that's all I'm saying.

Thanks!

flyinghippo

 

[stopher]

I took a look at your database. All you need to do is:

- Remove the FormatPercent function completely form the 20055 query to just leave the value

- Change the test value in the Form to -0.00001 (see my previous post)

I tested it and it works.

But I think you have gone about this in the wrong way. You need to let the queries do all the work. I suspect you can do it all with just a couple of queries. Unfortunately I don't have time just now to look at it.

Chris

 

[flyinghippo99]

I took a look at your database. All you need to do is:

- Remove the FormatPercent function completely form the 20055 query to just leave the value

- Change the test value in the Form to -0.00001 (see my previous post)

I tested it and it works.

But I think you have gone about this in the wrong way. You need to let the queries do all the work. I suspect you can do it all with just a couple of queries. Unfortunately I don't have time just now to look at it.

Chris

Chris,

Yes, that works. Thnx!

But the pickle here is that QryMetricXXXXXXCombine queries are used for dual purposes. One for the form to lookup values via DLookUp. The SECOND purpose is for "viewing the data". Which is invoking QryMetricXXXXXXCombine. And when I revert by to normal decimal values(not formatted string with percent%), it does NOT display correctly as percentages.

So, it seems like Uncertainty principle at work here. I can either use it in validation forms or for viewing the data standalone but not both. ack!

any thoughts?

thnx,

flyinghippo99

 

[stopher]

If you format using a function then you are changing the structure of your data. However, this isn't the only way to change the way data is displayed.

- Open your query in the design view.
- Right click on the numeric field you want to format e.g. PercentCol
- Select the format and the decimal places from the pop-up e.g. Percent; 3

Note that changing the format this way does not change the actual data (unlike the format functions). So other procedures will still use the data as intended.

hth
Chris