Percentages

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Percentages

Post by HansV »

:shrug:
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Percentages

Post by bknight »

PosCount: -Sum([Profit]>0)

Data type mismatch in criteria expression. (Error 3464)

The criteria expression in a Find method is attempting to compare a field with a value whose data type does not match the field's data type.

© 2007 Microsoft Corporation. All rights reserved.

Profit has no description, format, input mask, caption, smart tags. But displays a currency, so they are numbers. I don't understand the error message because it is looking at numbers GT 0. Doesn't make sense to me either, logically. Maybe someone else could make out the error and provide a work around. Since this is a external linked, I don't kow how to specify the data type for any field.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Percentages

Post by HansV »

If you open the linked worksheet in your database in design view, Access will display a warning. Click OK to open it anyway.
What is the data type of the Profit field?
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Percentages

Post by bknight »

In attempting to open the ss I get an error that is unrecoverable, something about the file is corrupted, which it isn't.
Then opening the Db after ss is open, then opening the linked ss it finally opens (I think this is what you meant in the first place.) I don't see any properties choice.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Percentages

Post by bknight »

OK opening the ss at the top of the Db window and ignoring the warning. The field Profit is? a test. So I changed it to currency but an error you can't change the properties of a linked table and opening attempting to run the query same data mismatch error.
I enter the data in the ss, would it be easier to import the data and then update periodically so the field can have a number property? What do you think?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Percentages

Post by HansV »

What did you mean by " The field Profit is? a test" ? That it is a text field? That would mean that the Profit column in Excel contains values that cannot be interpreted as currency.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Percentages

Post by bknight »

I mis-typed it is a text, but I don't believe there are nothing but numbers in the column. If you look at the ss I sent it has a formula in cell(3,15) If that was making the column "blow up" I can certainly move it, although I did already import the ss, but I could revert back to a link if you think it would work, that is moving the formula to another column.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Percentages

Post by HansV »

As I have shown, the query doesn't cause a problem if I use a table linked to your worksheet. I don't think importing it would make a difference. I suspect that the problem is somewhere in the data not included in your sample workbook.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Percentages

Post by bknight »

HansV wrote:
22 Mar 2022, 19:46
As I have shown, the query doesn't cause a problem if I use a table linked to your worksheet. I don't think importing it would make a difference. I suspect that the problem is somewhere in the data not included in your sample workbook.
Nope all numbers except the formula listed above. I don't doubt your word and I indicated the query opened fine until I shut down.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Percentages

Post by bknight »

The problem was the formula. In the subset of trades, I sent everyone, the formula was a number, however in the full ss it was a text. I moved it re-linked the tables and evrything works without issue.
Thanks, Hans, for your time and input.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Percentages

Post by bknight »

I have one more tweak that will probably need to be an expression. I attempted to add a sorting using either trade date or settle date sorted descending but not grouped that messes up the sums and averages.

SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS TotalCount, Sum(QuTrades.[-3958#96]) AS TotalSum, -Sum([-3958#96]>0) AS PosCount, -Sum([-3958#96]<0) AS NegCount, [PosCount]/[TotalCount] AS PosPerc, [NegCount]/[TotalCount] AS NegPerc, Avg(QuTrades.[-3958#96]) AS TotalAvg, -Sum([-3958#96]*([-3958#96]>0)) AS PosSum, IIf([PosCount]=0,Null,[PosSum]/[PosCount]) AS PosAvg, -Sum([-3958#96]*([-3958#96]<0)) AS NegSum, IIf([NegCount]=0,Null,[NegSum]/[NegCount]) AS NegAvg
FROM QuTrades
GROUP BY QuTrades.Symbol;

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Percentages

Post by bknight »

One can't use the group option, but one can use the Last function and sort that field descending and don't choose to display that date. Makes the record keeping look better.