Enter Parameter Value

[access10000]

Query will not filter

I have searched other threads and cannot find one that helps solve my problem. So, I hope someone can help.

I have a query which runs ok until it is filtered. I have tried using criteria to filter the info I need but this returns an 'Enter Parameter Value' message. Running the query without using criteria works okay but using a filter in the report itself returns a 'Data Mismatch' message.

The report filters fine on fields until it gets to one that has been changed from text to number. It also includes an Nz formula. The formula in the is field is: CLng(Nz([order_no],0)). Other fields related to this also appear to be affected.

I need the CLng formula as the field is needed to calculate other fields using a zero instead of a null which would otherwise cause an error. I was given the formula on this forum yesterday, (thanks Bob Larson and AlanJ10), which was great in solving the other issue but is this is what is affecting filtering, (ie. a confusion between text and number?!?!) I have also tried converting the result of the calculation I want to filter back into text using CStr but this does not make any difference.

I have checked the field names and also renamed fields that were 'Expr' (as in default names for calculated fields), and cannot see any problems.


Thanks

 

[boblarson]

CLng(Nz([order_no],0)).

Maybe this would work:

MyNewFieldName:IIf(IsNumeric([order_no]),Nz([order_no],0),Clng(Nz([order_no],0)))

 

[access10000]

Hi, thanks for the reply. Unfortunately it still won't filter on the field this formula is in or the others.

This field is used in a calculation in another field (1), which in turn is used in another one (2). There are now also error messages again in (2).

Appreciate the help. If you have any other ideas I'll give them a try. Until then I'll keep tearing my hair out...Ahhhh!!!

I have tried including the table name with the field name in the parameters but it still doesn't work.


rgds

 

[boblarson]

If you can post your database then maybe I, or someone else, can track your problems down easier. I won't be able to get to it for at least 9 hours due to so much work at work. I'm just leaving for work now.

 

[access10000]

Parameter Issue resolved

Thankyou Bob for offering to help with this. However, I think the problem is now solved.

It seems the Text to Number fields were what causing the issues with the parameters. By creating a new query with the fields from the original query and setting the criteria in the new one, it works and runs 'off the back' of the original query.

If anyone else has a similar problem, give this a go.


rgds

 

[astrofan]

Re: Parameter Issue resolved

thanks for the post. I had this same problem and found your post and it corrected the problem.

Thanks,

James