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.
Percentages
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
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?
What is the data type of the Profit field?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
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.
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.
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
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?
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?
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
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
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
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.
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
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
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
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.
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
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.
Thanks, Hans, for your time and input.
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
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;
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;
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
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.