Problem with operators

[DanSmith]

Hello,

I'm gonna try to explain my problem.

In fact, I have a form where I have a list where the user can select the following signs :

<, >, >=, <=, = (greater than, less than etc...)

Also, the user has the possibility to set the value that will be go with the operator above.

I tried the following expression in the query :

=[Forms]![Open_Form]![Sign CL AMT] & [Forms]![Open_Form]![CL_Amt]

the sign CL Amt is > and the CL AMt is 500'000 and I want to have >500000 in the criteria of the query in order to have only the amount above 500'000.

But is does not work.

I get the message : "The expression is typed incorectly or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"
If some one has a solution would be very appreciated.

Thanks in advance.

Cheers

Dan

 

[neileg]

Step 1, remove the = sign. This might work.

Step 2, use Eval() as in Eval([Forms]![Open_Form]![Sign CL AMT] & [Forms]![Open_Form]![CL_Amt]) This has worked for me in situations where I was completely stumped. I'm still not sure why!

 

[DanSmith]

Hello Neil,

Thank you very much for your answer.

However I have tried with the Eval() but I still get the same error message.

It is strange because when I type <500000 in the criteria it works.

It seems that the sign is not recognise as such.

Cheers
Dan

 

[DanSmith]

Hello

Nobdy else has a solution to my problem ????

Thank you very much in advance

Cheers!

Dan

 

[neileg]

Haven't got time to tinker with this, but I think the answer is to build the SQL as a string in VBA. Using the approach you have seems to drop the criterion in as text.

 

[DanSmith]

Hi Neileg,

Thanks for the answer.....but now I have another problem....is how to build the SQL in VBA....good question....

I'm not an Access expert but I will try to get the info that way..

If you have an idea it would be very appreciated.

Thank you

Cheers!

Dan

 

[DanSmith]

Hi,

I have tried the following:

>[Forms]![Open_Form]![CL_Amt]) in the criteria and it works fine.

However, when I type the following, it returns all data equal to 0

IIf([Forms]![Open_Form]![Sign CL AMT]=">";>[Forms]![Open_Form]![CL_Amt])

Any idea ?

Thnak you very much

Dan

 

[neileg]

No, sorry.

I found the same as you. When you concatenate the operator and the value, it gets treated as a string, which has a zero value, hence it returns the data with zero in the field being tested.

Unless I'm being dense (quite possible!) you'll have to learn about building SQL in code.

Try DoCmd.RunSQL in VBA help as a start.