A little help with this parameter expression! 1

[khwaja]

I have a numerical field called region with values ranging between 1 and 10.

I am opening a form based on a parameter query which gets its value from a form. I am using the following expression to pass the info from the form.

Like "*" & [Forms]![frmCriteriaDivPM]![txtRegion] & "*"

It works well except for the fact that when entered '1', it shows all records relating to regions '1' as well as '10'. I am sure this to do with the expression which treats the input as text. Is there any way, one can pass values in numerical form?

Cheers

AK

Note: Using Access 97 - still.

 

[johncb]

I assume that you want to return data for the region contained in [Forms]![frmCriteriaDivPM]![txtRegion] only.

Simply change the criteria to [Forms]![frmCriteriaDivPM]![txtRegion] and you will only see data for that region. i.e. remove LIKE, the "*" and the ampersands (&).

Using LIKE "*1*" means you want to get everything with a 1 in it such as 1, 10, 11 , 12 up to 19 then 21, 31 and so on.

By removing the LIKE you will only get data where there is an exact match with the contents of [Forms]![frmCriteriaDivPM]![txtRegion].

It works for [Forms]![frmCriteriaDivPM]![txtRegion] = 2 because there is not another Region with a 2 in it.

Hope this helps.

John

 

[khwaja]

Thanks John. The reason I had the * and amprasand there was to enable to user to bring up ALL regions if required. So if I take your solution, I will lose this functinality. Any other way to get around this?



Cheers

AK

Note: Using Access 97 - still.

 

[TonyJollans]

Hi khwaja,

Have to guess a bit but if you want EITHER records matching a single number (when it is input) OR all records (when nothing is input) then try something like ..

Like Iif(IsNull([Forms]![frmCriteriaDivPM]![txtRegion]),"*",[Forms]![frmCriteriaDivPM]![txtRegion])

Enjoy,
Tony

 

[khwaja]

John, I can't thank you enough for this. It works great. That is precisely I wanted to achieve. Very insightful use of IIF.



Cheers

AK

Note: Using Access 97 - still.