Combo-box Query Problems

[billyraum]

First of all I want to thank in advance anyone who helps me out, I appreciate it.

I just started working with Access a week ago and I have pretty much expended my potential, and I need help to get any farther. This is probably very basic. In fact I have read a lot of pther people having the same problem but so far I haven't been able to apply thier solutions to my database.

Here goes:

I have a form that has so far 2 combo boxes (this will grow), a parameter text box and beginning/end date boxes.

I want the query (run through a "search" command button), to of course, dispay the records that match all the criteria, ingnoring the cireria that is left blank.

I can get each one to work individually, but when all the criteria are together, thats when the results get dramatically messed up.

I could go through the different criterias I have tested but I'll just ask if anyone knows the right way to query multiple criteria.

If you need more information, as in SQL or whatever, just let me know.

Thank you very much,
Billy

 

[ThreeCrow]

Can you post a strip down of your database

 

[billyraum]

Sorry, but I'm not exactly sure what a "stripdown" is.

Let me know what information you need, thanks!

 

[ThreeCrow]

A strip down is a copy of your database less than 100K in size.
Post it on this thread so as I can see whats' going on with it.

 

[billyraum]

Okay, I deleted as much as I could but left the form (Start Up) the query (qrySearch) and the table (Archive). I zipped it as well.

Thanks,
Billy

 

[ThreeCrow]

billy

Here is the SQL for your Query. Make a copy of your database name it something other the actual name. Then copy and paste to query in your database.

SELECT Archive.ID, Archive.Type, Archive.Date, Archive.Client, Archive.Title, Archive.[#], Archive.Format, Archive.Length, Archive.Details, Archive.Location
FROM Archive
WHERE (((Archive.Type) Like [Forms]![Start Up]![ddlType]) AND ((Archive.Date) Between [Forms]![Start Up].[txtDateIn] And [Forms]![Start Up].[txtDateOut]) AND ((Archive.Client) Like IIf(IsNull([Forms]![Start Up]![ddlClient]),"*",[Forms]![Start Up]![ddlClient])) AND ((Archive.Title) Like IIf(IsNull([Forms]![Start Up]![txtSearch]),"*",[Forms]![Start Up]![txtSearch])) AND ((Archive.Details) Like IIf(IsNull([Forms]![Start Up]![txtSearch]),"*",[Forms]![Start Up]![txtSearch])));

Hope this Helped

 

[billyraum]

Yeah, I replaced that SQL and now I am getting no results at all. I double checked and triple checked that I had copied it correctly.

I did notice that there was an "AND" connection between the criteria of Title and Details. I'm looking for the criteria to search either Title OR Details. So I changed it to OR and still nothing worked.

I also noticed that there is a "Like IIf(IsNull..." criteria in front of Client (which uses a combobox on the Start Up form) but Type uses just a "Like " criteria (even when it has an identical combobox in Start Up). Is there a reason for this?

I changed the Type and Client criteria so they matched either way as well I still pull 0 records on my query.

I'm sorry, but is there something I am doing wrong?

-Billy

 

[ThreeCrow]

Yeah, I replaced that SQL and now I am getting no results at all. I double checked and triple checked that I had copied it correctly.

Hmmm, Worked fine for me.

What do you suppose happen?

Would have to see another strip down, send to me direct.

 

[billyraum]

I wasn't able to get a email address from your profile (I assume that is what you meant by "send to me direct") so I am posting the stripdown here.

I kept more forms and tables in this particular stripdown, just to give you a better idea about how it is setup at the moment.

Just to clarify what I am attempting to do with this database:
I would like a user to search for any type of client, date, or type, along with any "keywords" used in the "Enter footage desired:" text box. Of course, if any of these boxes are left empty, the query should return everything in that field.

I don't know why I am havign so many problems but once again I really appreciate your help.

-Billy

 

[RV]

Your query isn't working because it's referring to the combo box ID columns.
Looking at your table Archive, it seems that when entering Archive date you're not storing ID's.
It's better practice though enabling you to extract whatever data when you want to.
In fact, storing unique ID's is a part of what relational databases are all 'bout.
So adapt your applic, and retry.

A few DO NOTS

Do NOT use meaningless column names such as ID or #.
Do NOT use so called reserved words as column names (you're using date and type)
Referring to

http://www.access-programmers.co.uk/forums/showthread.php?t=90399

Do NOT use Lookup columns.

RV

 

[billyraum]

RV, I am creating unique ID #s for each Archive entry. It's the first field in the table.

I deleted the ID fields for Clients and Types of Footage, and changed the relationships accordingly so that the actual names were linked to the table.

This resulted in only numbers being displayed in the table's fields (which I anticipated). Unfortunatly I was not able to find/replace all the numbers to their respective names/acronyms, so I did a test.

I replaced a few numbers with the real words they are supposed to have and I still get exactly the same problem. My results are not accurate. I can search just for "kids" in the title and details text box, and yield only 5 results. Yet I can search for "kids" and the type as "B-Roll" and I get 10 results, all of which are correct.

Furthermore, if I search for just "B-roll" my query returns every record in the database.

I still keep haveing the same problem of seemingly random results in my database.

I also changed the names of the fields so that they do not conflict with the "reserved words" and that has no effect.

I know this had to be somethign simple that I am doing wrong since this database is extremely uncomplicated, but I just can't quite put my finger on it. I have spent so long testing different theories and none have worked.

One last thing: is there an alternative to Lookup columns because I need the database to adapt to new jobs and different clients, and I'm not gonna be the one inputing this data, so someone else, unfamiliar with the inner workings of access (kind of like me I guess ) will need to be able to easily enter new data for the fields they choose.

Thanks for both of your guy's help, and anyone else that has any advice in the future.

-Billy