Advanced search form

[dcfi6052]

Some info:

I am currently making a DVD database that is quite large and rapidly coming more complex as I have new ideas to improve functionality. Right now I am trying to create an advanced search form for my database. Basically, I just want to make a form that can do the same as an access query but making it nicer looking and more user friendly (ie. without all the access punctuation with ", * etc.). My database is not an easy list of just the names of films, but also has fields like genre, actors, lengths, etc.

So far I have my form made with all necessary text boxes and drop down menu's for my search form. I followed some advise I found online and created a query and used the following code for each field:

Like "*" & [Forms]![Advanced Search]![MovieNameBox] & "*"

So when all the data fields are empty and you press the button to start the search all data is present. This code for the movie names is great! works just like I want because the user doesn't have to be familiar with the punctuation of access to search a movie, and it can be a partial title and still show.

My problem is when I search a movie by it's length/running time, or year. with the same code as above I can only search a specific time or year (ie. 1:46, or 2005). I would like to be able to search like a query (ie. >1:30 or <=2003) but without the user typing in the punctuation. My idea was to create a few small buttons beside the "movie length" text box that would toggle on and off for ">", "<", and "=" (possible to toggle more then one) and place it in the text box for the user but would also be invisible to them as well (running in the background). This way the user could search movies that are > 1:30 or <= 2008.

Using the code from above in the query for year and movie length, if I type in the year text box for example >2010 and click my search button (which runs the query) no results come up. I believe the problem is the like statement but have tried many different statements with no success.

I don't think its possible to do it in a query with the above code. I would have to actually code the buttons and am really lost on how to do so.

Does anybody have any input on how to go about doing this?

 

[vbaInet]

(ie. >1:30 or <=2003)

I'm trying to make sense of this to understand it better. How do you search for a greater than Time part or less than a Year?

 

[dcfi6052]

If I have a movie list that displays the movie year and the movie length for a hundred different movies and made an access query I could run that query by searching by year or length. So I could search for all movies that were made in year 2003 or search movies that are 1:56 long, or both. Good so far?

I could also search all movies that were made in 2003 or greater (>2003 in the query "criteria" cell). After running the query only movies that were made after 2003 are displayed. If it type in >=2005 it will show all movies made in 2005 and after 2005. Same goes for movie times, >=1:30 will show all movies with a running time of 1:30 and greater.


Make sense??

 

[vbaInet]

Well, you've explained what you can do, but can you elaborate on what you're trying to do?

 

[dcfi6052]

I want to have a user who isn't familiar with access punctuation be able to do an advanced search of the database using a designed form with textboxes and drop down menus without them using a query.

I know you can search in a query using >, <, and = I can have a button place a symbol in the box for the user, but I can't figure out how to have these symbols run properly.

I can simply input [Forms]![Advanced Search]![YearBox] into my query, then the user simply type in >2000 but no records show up for some reason when I run the query. the only way records will show is if I search a specific year, which isn't what I want.

It's difficult to explain exactly what I'm looking for without posting a pic of my search form and search query. But I'll try:

I have an advanced search form with multiple search criteria, one being year. so beside the textbox for year I have 3 small buttons, one >, one<, and one =.
I have set up some code so that the > and < symbols cannot be pressed at the same time and when you press a button it inserts the corresponding symbol into the year textbox at which point the user enters a year. So if they press the > button a > symbol is placed in the textbox and they proceed to enter a year. After pressing the "search button" to run the query, no data appears and I don't know why.

So how could I modify my
[Forms]![Advanced Search]![YearBox] line in my query to allow typing >,< or = in the text field of my search form and display my data correctly?

I have tried Like "*" & [Forms]![Advanced Search]![YearBox] & "*" But you can't use >, or < with a like statement. Any ideas?

 

[vbaInet]

If you send those inputs via parameters they will be read as text. You will have to construct the SQL purely in code.

 

[dcfi6052]

SQL you say?...I looked into it briefly and I think that is what I need to do. I'll have to fool around with it and see what I get.

Thanks once again!

 

[vbaInet]

Here's a tutorial from pbaldy on this topic:

http://baldyweb.com/BuildSQL.htm