PLEASE HELP! CRISIS!

  • Thread starter Claire Rohan via AccessMonster.com
  • Start date
C

Claire Rohan via AccessMonster.com

hi I have written a query that has worked fine for the last few weeks and
today has suddenly stopped working. I didnt do anything to the design of the
query today. the only thing I did today was to implement user level security
using the security wizard. when I try to run the query, logged in as an
administrator, it gives me this error: "data type mismatch in the criteria
expression"
I have checked all related data types and there should not be any mismatch,
even if I try to run the query without any criteria it gives that error!

it was working fine! now suddenly it wont! HELP!

the sql code is complex because the query is doing a lot of calculations:

SELECT DISTINCT Trade.ID, Trade.Client, Trade.Stock, Indices.Region, Trade.
Currency, IIf(Trade!Currency="USD",100,IIf(Trade!Currency="GBP",85,80)) AS
MinimumRebate, Trade.Position, Trade.OpenDate, euroTradePrices.EuroOpenPrice
AS OpenPrice, euroTradePrices.EuroClosePrice AS ClosePrice, Trade.OpenShares,
Trade.CloseDate, Trade.CloseShares, ProviderRates.Charge, ProviderRates.Rate,
Trade.OpenCommissionRate, 0.0003*[OpenPrice]*[OpenShares] AS [Controlled Risk
Premium], Trade.CloseCommissionRate, Trade!OpenCommissionRate+ProviderRates!
Rate AS ClientOpenRate, Trade!CloseCommissionRate+ProviderRates!Rate AS
ClientCloseRate, IIf(([OpenShares]*[OpenPrice]*[ClientOpenRate])>80,
[OpenShares]*[OpenPrice]*[ClientOpenRate],80) AS ClientOpenComm, IIf(
[CloseDate] Is Not Null,IIf(([ClosePrice]*[CloseShares]*[ClientCloseRate])>80,
[ClosePrice]*[CloseShares]*[ClientCloseRate],80),0) AS ClientCloseComm,
euroTradePrices.EuroInterest AS Interest, ClientContact.BaseFundAmount, IIf(
[ClientCloseComm] Is Not Null,[Interest]+[ClientCloseComm]+[ClientOpenComm]+
[Controlled Risk Premium],[Interest]+[ClientOpenComm]+[Controlled Risk
Premium]) AS [Total Cost], Trade.CurrentPrice, IIf([Position]=1,IIf(
[CloseDate] Is Not Null,([CurrClosePrice]/[CurrOpenPrice])-1,([CurrentPrice]/
[CurrOpenPrice])-1),IIf([CloseDate] Is Not Null,([CurrOpenPrice]/
[CurrClosePrice])-1,([CurrOpenPrice]/[CurrentPrice])-1)) AS [Individual
Return], IIf([Position]=1,IIf([CloseDate] Is Not Null,(([CurrClosePrice]*
[CloseShares]-[CurrOpenPrice]*[OpenShares])-[CurrTotalCost])/[BaseFundAmount],
(([CurrentPrice]*[OpenShares]-[CurrOpenPrice]*[OpenShares])-[CurrTotalCost])/
[BaseFundAmount]),IIf([CloseDate] Is Not Null,(([CurrOpenPrice]*[OpenShares]-
[CurrClosePrice]*[CloseShares])-[CurrTotalCost])/[BaseFundAmount],((
[CurrOpenPrice]*[OpenShares]-[CurrentPrice]*[OpenShares])-[CurrTotalCost])/
[BaseFundAmount])) AS [Portfolio Return], Leverage.IndLeverage, [IndLeverage]
/TotalLeverage!SumOfIndLeverage AS [Current Weighting], Trade.StopLossPrice,
IIf([CloseDate] Is Null,IIf([Position]=1,[StopLossPrice]/[CurrentPrice]-1,
[CurrentPrice]/[StopLossPrice]-1),Null) AS [Individual Risk to Market], IIf(
[CloseDate] Is Null,IIf([Position]=1,(((([StopLossPrice]-[CurrentPrice])*
[OpenShares])-(2*[ClientCurrOpenComm]))/[BaseFundAmount]),(((([CurrentPrice]-
[StopLossPrice])*[OpenShares])-(2*[ClientCurrOpenComm]))/[BaseFundAmount])),
Null) AS [Portfolio Risk to Market], Trade.OpenPrice AS CurrOpenPrice, IIf(
[CloseDate] Is Not Null,Trade!ClosePrice,Null) AS CurrClosePrice, IIf((
[CurrOpenPrice]*[OpenShares]*[ClientOpenRate])>[MinimumRebate],[CurrOpenPrice]
*[OpenShares]*[ClientOpenRate],[MinimumRebate]) AS ClientCurrOpenComm, IIf(
[CloseDate] Is Not Null,IIf(([CurrClosePrice]*[CloseShares]*[ClientCloseRate])
[MinimumRebate],[CurrClosePrice]*[CloseShares]*[ClientCloseRate],
Click to expand...
[MinimumRebate]),Null) AS ClientCurrCloseComm, IIf([ClientCloseComm]>0,Trade!
Interest+[ClientCurrCloseComm]+[ClientCurrOpenComm]+(0.0003*[CurrOpenPrice]*
[OpenShares]),Trade!Interest+[ClientCurrOpenComm]+(0.0003*[CurrOpenPrice]*
[OpenShares])) AS CurrTotalCost
FROM ProviderRates, CurrencyRateChange, (ClientContact INNER JOIN (((Trade
INNER JOIN Indices ON Trade.Index = Indices.Index) INNER JOIN Leverage ON
Trade.ID = Leverage.ID) INNER JOIN euroTradePrices ON Trade.ID =
euroTradePrices.ID) ON ClientContact.ClientReference = Trade.Client) INNER
JOIN TotalLeverage ON Trade.Client = TotalLeverage.Client
WHERE (((Trade.Client)=FindClient()) AND ((ProviderRates.Charge)=[Trade]!
[Currency]));
 
T

Tom Ellison

Dear Claire:

Not knowing the types of everything in your query, I'm not going to be able
to magically pinpoint the problem.

There are some clues to work form.

May I suggest this sequence:

1. Make sure this is a saved query, and that when you run it, you get that
error. Now make a copy of it and save that under a different query name
(Save As) say, "qTest"

2. The error messages says "in criteria expression" so remove all the
criteria expressions using Cut. That is, delete the bit at the end from
WHERE to the very end. Does the error disappear? If it does, then put them
back and remove just one of the criteria at a time to see which one is
causing the problem.

3. After you have this narrowed down (if the above works), examine the
elements of the criterion causing the problem. Can you see the problem? If
not, post full details about the columns, functions, or other elements of
the offending criterion.

I hope this helps, or that it may at least help us pinpoint the difficulty
and begin to work on it.

Tom Ellison


Claire Rohan via AccessMonster.com said:
hi I have written a query that has worked fine for the last few weeks and
today has suddenly stopped working. I didnt do anything to the design of
the
query today. the only thing I did today was to implement user level
security
using the security wizard. when I try to run the query, logged in as an
administrator, it gives me this error: "data type mismatch in the criteria
expression"
I have checked all related data types and there should not be any
mismatch,
even if I try to run the query without any criteria it gives that error!

it was working fine! now suddenly it wont! HELP!

the sql code is complex because the query is doing a lot of calculations:

SELECT DISTINCT Trade.ID, Trade.Client, Trade.Stock, Indices.Region,
Trade.
Currency, IIf(Trade!Currency="USD",100,IIf(Trade!Currency="GBP",85,80)) AS
MinimumRebate, Trade.Position, Trade.OpenDate,
euroTradePrices.EuroOpenPrice
AS OpenPrice, euroTradePrices.EuroClosePrice AS ClosePrice,
Trade.OpenShares,
Trade.CloseDate, Trade.CloseShares, ProviderRates.Charge,
ProviderRates.Rate,
Trade.OpenCommissionRate, 0.0003*[OpenPrice]*[OpenShares] AS [Controlled
Risk
Premium], Trade.CloseCommissionRate,
Trade!OpenCommissionRate+ProviderRates!
Rate AS ClientOpenRate, Trade!CloseCommissionRate+ProviderRates!Rate AS
ClientCloseRate, IIf(([OpenShares]*[OpenPrice]*[ClientOpenRate])>80,
[OpenShares]*[OpenPrice]*[ClientOpenRate],80) AS ClientOpenComm, IIf(
[CloseDate] Is Not
Null,IIf(([ClosePrice]*[CloseShares]*[ClientCloseRate])>80,
[ClosePrice]*[CloseShares]*[ClientCloseRate],80),0) AS ClientCloseComm,
euroTradePrices.EuroInterest AS Interest, ClientContact.BaseFundAmount,
IIf(
[ClientCloseComm] Is Not
Null,[Interest]+[ClientCloseComm]+[ClientOpenComm]+
[Controlled Risk Premium],[Interest]+[ClientOpenComm]+[Controlled Risk
Premium]) AS [Total Cost], Trade.CurrentPrice, IIf([Position]=1,IIf(
[CloseDate] Is Not
Null,([CurrClosePrice]/[CurrOpenPrice])-1,([CurrentPrice]/
[CurrOpenPrice])-1),IIf([CloseDate] Is Not Null,([CurrOpenPrice]/
[CurrClosePrice])-1,([CurrOpenPrice]/[CurrentPrice])-1)) AS [Individual
Return], IIf([Position]=1,IIf([CloseDate] Is Not Null,(([CurrClosePrice]*
[CloseShares]-[CurrOpenPrice]*[OpenShares])-[CurrTotalCost])/[BaseFundAmount],
(([CurrentPrice]*[OpenShares]-[CurrOpenPrice]*[OpenShares])-[CurrTotalCost])/
[BaseFundAmount]),IIf([CloseDate] Is Not
Null,(([CurrOpenPrice]*[OpenShares]-
[CurrClosePrice]*[CloseShares])-[CurrTotalCost])/[BaseFundAmount],((
[CurrOpenPrice]*[OpenShares]-[CurrentPrice]*[OpenShares])-[CurrTotalCost])/
[BaseFundAmount])) AS [Portfolio Return], Leverage.IndLeverage,
[IndLeverage]
/TotalLeverage!SumOfIndLeverage AS [Current Weighting],
Trade.StopLossPrice,
IIf([CloseDate] Is Null,IIf([Position]=1,[StopLossPrice]/[CurrentPrice]-1,
[CurrentPrice]/[StopLossPrice]-1),Null) AS [Individual Risk to Market],
IIf(
[CloseDate] Is Null,IIf([Position]=1,(((([StopLossPrice]-[CurrentPrice])*
[OpenShares])-(2*[ClientCurrOpenComm]))/[BaseFundAmount]),(((([CurrentPrice]-
[StopLossPrice])*[OpenShares])-(2*[ClientCurrOpenComm]))/[BaseFundAmount])),
Null) AS [Portfolio Risk to Market], Trade.OpenPrice AS CurrOpenPrice,
IIf(
[CloseDate] Is Not Null,Trade!ClosePrice,Null) AS CurrClosePrice, IIf((
[CurrOpenPrice]*[OpenShares]*[ClientOpenRate])>[MinimumRebate],[CurrOpenPrice]
*[OpenShares]*[ClientOpenRate],[MinimumRebate]) AS ClientCurrOpenComm,
IIf(
[CloseDate] Is Not
Null,IIf(([CurrClosePrice]*[CloseShares]*[ClientCloseRate])
[MinimumRebate],[CurrClosePrice]*[CloseShares]*[ClientCloseRate],
Click to expand...
[MinimumRebate]),Null) AS ClientCurrCloseComm,
IIf([ClientCloseComm]>0,Trade!
Interest+[ClientCurrCloseComm]+[ClientCurrOpenComm]+(0.0003*[CurrOpenPrice]*
[OpenShares]),Trade!Interest+[ClientCurrOpenComm]+(0.0003*[CurrOpenPrice]*
[OpenShares])) AS CurrTotalCost
FROM ProviderRates, CurrencyRateChange, (ClientContact INNER JOIN (((Trade
INNER JOIN Indices ON Trade.Index = Indices.Index) INNER JOIN Leverage ON
Trade.ID = Leverage.ID) INNER JOIN euroTradePrices ON Trade.ID =
euroTradePrices.ID) ON ClientContact.ClientReference = Trade.Client) INNER
JOIN TotalLeverage ON Trade.Client = TotalLeverage.Client
WHERE (((Trade.Client)=FindClient()) AND ((ProviderRates.Charge)=[Trade]!
[Currency]));
Click to expand...
 
B

BruceM

Did you use lookup fields in the table that underlies the query? That can
cause problems when user-level security is implemented (and with criteria in
general), as I understand.

Claire Rohan via AccessMonster.com said:
hi I have written a query that has worked fine for the last few weeks and
today has suddenly stopped working. I didnt do anything to the design of
the
query today. the only thing I did today was to implement user level
security
using the security wizard. when I try to run the query, logged in as an
administrator, it gives me this error: "data type mismatch in the criteria
expression"
I have checked all related data types and there should not be any
mismatch,
even if I try to run the query without any criteria it gives that error!

it was working fine! now suddenly it wont! HELP!

the sql code is complex because the query is doing a lot of calculations:

SELECT DISTINCT Trade.ID, Trade.Client, Trade.Stock, Indices.Region,
Trade.
Currency, IIf(Trade!Currency="USD",100,IIf(Trade!Currency="GBP",85,80)) AS
MinimumRebate, Trade.Position, Trade.OpenDate,
euroTradePrices.EuroOpenPrice
AS OpenPrice, euroTradePrices.EuroClosePrice AS ClosePrice,
Trade.OpenShares,
Trade.CloseDate, Trade.CloseShares, ProviderRates.Charge,
ProviderRates.Rate,
Trade.OpenCommissionRate, 0.0003*[OpenPrice]*[OpenShares] AS [Controlled
Risk
Premium], Trade.CloseCommissionRate,
Trade!OpenCommissionRate+ProviderRates!
Rate AS ClientOpenRate, Trade!CloseCommissionRate+ProviderRates!Rate AS
ClientCloseRate, IIf(([OpenShares]*[OpenPrice]*[ClientOpenRate])>80,
[OpenShares]*[OpenPrice]*[ClientOpenRate],80) AS ClientOpenComm, IIf(
[CloseDate] Is Not
Null,IIf(([ClosePrice]*[CloseShares]*[ClientCloseRate])>80,
[ClosePrice]*[CloseShares]*[ClientCloseRate],80),0) AS ClientCloseComm,
euroTradePrices.EuroInterest AS Interest, ClientContact.BaseFundAmount,
IIf(
[ClientCloseComm] Is Not
Null,[Interest]+[ClientCloseComm]+[ClientOpenComm]+
[Controlled Risk Premium],[Interest]+[ClientOpenComm]+[Controlled Risk
Premium]) AS [Total Cost], Trade.CurrentPrice, IIf([Position]=1,IIf(
[CloseDate] Is Not
Null,([CurrClosePrice]/[CurrOpenPrice])-1,([CurrentPrice]/
[CurrOpenPrice])-1),IIf([CloseDate] Is Not Null,([CurrOpenPrice]/
[CurrClosePrice])-1,([CurrOpenPrice]/[CurrentPrice])-1)) AS [Individual
Return], IIf([Position]=1,IIf([CloseDate] Is Not Null,(([CurrClosePrice]*
[CloseShares]-[CurrOpenPrice]*[OpenShares])-[CurrTotalCost])/[BaseFundAmount],
(([CurrentPrice]*[OpenShares]-[CurrOpenPrice]*[OpenShares])-[CurrTotalCost])/
[BaseFundAmount]),IIf([CloseDate] Is Not
Null,(([CurrOpenPrice]*[OpenShares]-
[CurrClosePrice]*[CloseShares])-[CurrTotalCost])/[BaseFundAmount],((
[CurrOpenPrice]*[OpenShares]-[CurrentPrice]*[OpenShares])-[CurrTotalCost])/
[BaseFundAmount])) AS [Portfolio Return], Leverage.IndLeverage,
[IndLeverage]
/TotalLeverage!SumOfIndLeverage AS [Current Weighting],
Trade.StopLossPrice,
IIf([CloseDate] Is Null,IIf([Position]=1,[StopLossPrice]/[CurrentPrice]-1,
[CurrentPrice]/[StopLossPrice]-1),Null) AS [Individual Risk to Market],
IIf(
[CloseDate] Is Null,IIf([Position]=1,(((([StopLossPrice]-[CurrentPrice])*
[OpenShares])-(2*[ClientCurrOpenComm]))/[BaseFundAmount]),(((([CurrentPrice]-
[StopLossPrice])*[OpenShares])-(2*[ClientCurrOpenComm]))/[BaseFundAmount])),
Null) AS [Portfolio Risk to Market], Trade.OpenPrice AS CurrOpenPrice,
IIf(
[CloseDate] Is Not Null,Trade!ClosePrice,Null) AS CurrClosePrice, IIf((
[CurrOpenPrice]*[OpenShares]*[ClientOpenRate])>[MinimumRebate],[CurrOpenPrice]
*[OpenShares]*[ClientOpenRate],[MinimumRebate]) AS ClientCurrOpenComm,
IIf(
[CloseDate] Is Not
Null,IIf(([CurrClosePrice]*[CloseShares]*[ClientCloseRate])
[MinimumRebate],[CurrClosePrice]*[CloseShares]*[ClientCloseRate],
Click to expand...
[MinimumRebate]),Null) AS ClientCurrCloseComm,
IIf([ClientCloseComm]>0,Trade!
Interest+[ClientCurrCloseComm]+[ClientCurrOpenComm]+(0.0003*[CurrOpenPrice]*
[OpenShares]),Trade!Interest+[ClientCurrOpenComm]+(0.0003*[CurrOpenPrice]*
[OpenShares])) AS CurrTotalCost
FROM ProviderRates, CurrencyRateChange, (ClientContact INNER JOIN (((Trade
INNER JOIN Indices ON Trade.Index = Indices.Index) INNER JOIN Leverage ON
Trade.ID = Leverage.ID) INNER JOIN euroTradePrices ON Trade.ID =
euroTradePrices.ID) ON ClientContact.ClientReference = Trade.Client) INNER
JOIN TotalLeverage ON Trade.Client = TotalLeverage.Client
WHERE (((Trade.Client)=FindClient()) AND ((ProviderRates.Charge)=[Trade]!
[Currency]));
Click to expand...
 
Top