Need help - Combo box search to include records that equal and larger than

[thaonguyen]

Hi all,

I have a problem that I have been trying to google for ideas/ help but haven't been successful. I have a DB of books and a form to search for multiple fields including ranking (combo box). The way I have it now is to pick "3 stars", "4 stars", "5 stars". What I want to do is have it so that if I pick "4+", it will show all records that have 4 stars and 5 stars and "3+", it will show records with 3, 4, 5 stars.

Can anyone point me in the right direction? Thanks so much!

 

[Mihail]

Upload the DB (Access 2007 or older)

 

[thaonguyen]

Hi, I can't upload the DB because it's really big. I did attach a picture of the search form that I built. Hope it helps!

 

[Mihail]

Oh. You can for sure.
I need only 10 records in each table in order to test.

Backup (create a copy) of your DB
Remove the records
Do a Compact and Repair
If still is too big, ZIP it.
Upload.

 

[thaonguyen]

Hi Mihail,

There it is.

Also, I am wondering if you happen to know how to prevent copying and pasting records from tables? Or put a password to them. I have been trying to do that as well without success. Thanks so much!

 

[Mihail]

Upload the DB (Access 2007 or older)

Better is 2003.

I use 2007 but I am unable to open your DB

 

[thaonguyen]

Oops, sorry. I forgot. You did say it before. Here you go. Hope this works

 

[Mihail]

OK. I catch it.
One more question for now: Do you need an OR or an AND between search criteria ?

 

[thaonguyen]

Right now, on the Rating criteria it's just 1 star, 2 stars... I want to change it to 3+, 4+ so that when you choose "3 stars" for example, it includes the records that have 3 stars, 4 stars and 5 stars. For the other criteria, I'd like to keep it as it is, which is a combination of OR and AND (I develop this from the Multiple field search of a member of the forum). My idea was a bit more complicated.

 

[thaonguyen]

Oops, choose 3+ for example...

 

[Mihail]

In attachment you have a "template". (for the yellow search controls)
I'm sure that you can continue.

DO NOT use names with spaces like "Work title", "Language focus" and so on.

WorkTitle or LanguageFocus are easy to read and understand and not causes troubles.

Feel free to ask more questions.

Good luck !

 

[thaonguyen]

Yeh, I know that. I did this for my boyfriend, who is an English teacher and he prefers it this way. I did tell him. Looking into that now. Thanks a lot. You're a star!

 

[thaonguyen]

Hi Mihail, I think you might have misunderstood what I meant. I am happy with the other search criteria. What I hope to change is the "Rating" (Combo195). Right now, I have 5 options as "1 star", "2stars", "3stars", "4 stars" and "5 stars".

I want to change them to "1+", "2+", "3+", "4+" so that when I choose "3+" for example, the search results will include the records that have 3 stars, 4 stars and 5 stars. "4+" includes records with 4 stars and 5 stars, and so on.

I don't think a query can help with what I want. I know it has to be a function of some kind but I couldn't figure it out.

 

[Mihail]

Unfortunately, I can't debug your query. I never tried something like this. When you have so much logic to implement, a function is the best way.

So, I used YOUR query Query_Search (Query_Results is no more necessary) in order to achieve the desired result (see attachment).

I did this for my boyfriend, who is an English teacher and he prefers it this way.

I speak about the names that you use in tables, queries etc, not about the labels that you show in forms or reports.

 

[CazB]

Another option: Have you tried changing the criteria in your query to something like

>=Nz([forms]![Search]![Combo195],"0")

?

I know that doesn't make the combobox say 3+ but it means if they select 3 stars they'll get 3 stars or more...

 

[thaonguyen]

That's it!. I tried the >= before but I missed out on the last part >=Nz([forms]![Search]![Combo195],"0")

Thank you so much. It works like magic!

 

[thaonguyen]

Thank you both. I will play with the idea and see how it goes. It's much clearer now when I'm pointed at the right directions.

 

[thaonguyen]

Also, is there a way to prevent copy and paste records or put a password to it? I can do with the forms but not the tables.