text criteria in table gives data type mismatch

KeithYo

New member
Local time
Yesterday, 22:11
Joined
Jan 18, 2012
Messages
5
I am hoping the right number and placement of quotation marks will make this work. I have this in a text field >100000. I want to use that text field as criteria in a query to look up a currency field. I get a "Type mismatch in expression" dialogue. Here's the sql:
SELECT A5.*, InvestorClient.ClientName, A5.BigTableActives.Zip, A5.LP
FROM A5, InvestorClient
WHERE (((InvestorClient.ClientName)="Bob") AND ((A5.BigTableActives.Zip)=[InvestorClient]![Zip]) AND
((A5.LP)=[InvestorClient]![ListPr]));

It's the last line giving me fits. Thanks for any help!
 
Look in access help for Type Conversion Functions, in your case

CCur() or CLng()

JR
 
Thanks JanR, and I have tried both. Then tried many combinations of those with quotes here and there. If I had one price in the ListPr field, then then CCur() works great. But it's the text string that I need for the criteria to evaluate the currency field in the other table. So this works when I add criteria manually.

SELECT A5.*, InvestorClient.ClientName, A5.BigTableActives.Zip, A5.LP
FROM A5, InvestorClient
WHERE (((InvestorClient.ClientName)="Bob") AND ((A5.BigTableActives.Zip)=[InvestorClient]![Zip]) AND
((A5.LP)>100000));

I suppose for the sake of keeping the ListPr field pure, I could add another field for the operators...I get the feeling I am not thinking about this correctly.
 
Create a Query with the criteria you need.

Once it works, copy the SQL and use it where you need.
 
Thanks Rain, but the idea here is to have query criteria in a table to set up and manage hundreds of different searches.
 
If the field ListPr in the table InvestorClient is the "textNumber" then I would think that this should work:

((A5.LP)> CCur([InvestorClient]![ListPr])));

JR
 
ListPr is a text field that contains an expression, not just a number. I want it to contain for example "<200000", or for another example "Between 100000 And 200000". All of these expressions work in the sql by putting them in directly, but getting the expression to pass from the table where it's stored to the sql causes the data type error.
 
Ok JanR, I am looking at your answer again, and I think I see how that can work...I have other search fields with similar challenges using different operators, but essentially I need to keep the operator out of that text field that needs to be coerced to a currency type. Thanks very much!
 

Users who are viewing this thread

Back
Top Bottom