Number of records filtered

[MinnKota]

I am currently filtering my form with the following two lines of code:

Me.Filter = "

![field] = '" & [tempVariable] & "'"
Me.FilterOn = True

It is working well, except when the tempVariable is not equal to the field in the table then the form comes up blank and the user cannot go backwards. Is there a way to see how many records will be returned by the filter? If it returns no records or if there are no records in the table equal to the temp form, then I will not apply the filter and give the user some sort of message box.

Thanks

 

[hneal98]

change the = to the word 'like', without the '.

 

[MinnKota]

I get the sam exact results as I was getting before I changed the '=' to 'LIKE'

 

[hneal98]

Why not do an if state to check if the field and tempvariable are equal. If they are, use it, if not, don't. That way, you don't have to count the records.

 

[hneal98]

Now that I think about it, i am confused. Isn't it working properly if it is comming up blank when the value of your variable does not exist in your data?

I think you are missing what is supposed to happen in a filter. Let me know if I am missing something.

 

[MinnKota]

Yes, I think that you are missing something.

The filter is working properly. Reread my post.

 

[hneal98]

Sorry, crazy day for me. Lets try this again. How are you letting the variable be populated in the first place. You should make it so your user only need to reenter data when the form comes up empty. They should have an option to turn the filter off or on.

 

[MinnKota]

They do have the option...

I am not quite sure what this has to do with my question though...I simply want to know if there is a way to determine how many records the filter will 'extract'.

 

[hneal98]

Well, my remarks had nothing to do with your question, but everthing to do with your problem.

Anyway, go to this thread. RoyVidar gives what you are looking for:

thread702-818368

 

[MinnKota]

thanks a million...I should have found that thread on my own.

once again, thanks for all your help.

 

[hneal98]

Don't thank me, thank RoyVidar by giving him a star.

 

[MinnKota]

I will do that. (I always forget to do that, sorry...)