Searching multiple fields problem

[snicker7]

Before I explain the problem, let me explain the goal here.

Ive designed a database for an apartment cleaning company. Currently there are three tables that apply directly to a single apartment.

The tables are this:

tblApartments:
Aptcode: consists of a two to four letter apartment complex code
AptNumber: the room number of the apartment, which is not strictly a numerical field
WhoWalked: person that last walked the apartment
DateWalked: Obviously the date the above person walked it
Notes: a memo field.

tblAptCodes:
Aptcode: directly related to the above field.
Aptname: the corresponding name to the two to four letter apartment complex code.
then address and phone fields for the complexes

tblDates:
datecleaned: dates the apartment was cleaned
whocleaned: who cleaned it on that date
notes: duh

Each apartment is linked to the apartment complex codes table, and then each apartment has dates in the dates table when it was cleaned. The dates table is linked by the AptID field in tblApartments.

I used Parker's simple search in the sample DBs section to do a base search of a combination of these three tables into a listbox. The list box contains these values:

tblAptCodes.AptCode, tblAptCodes.Name, tblApartments.AptNumber, tblApartments.WhoWalked, tblDates.DateCleaned, tblDates.WhoCleaned

The query is rather complicated, so i have multiple listings for each apartment, but the listings each have different dates. Now to the actual point.



Currently, with parkers simple search, I can only reference one column of data at a time. What I mean by this is if I have an apartment with code CC and room number 1234, If I type "cc 1234" into my query box, I get no returns.

I need a way for this simple search to be able to reference many fields with independent data, as separated by a space. So if i want to find apartments in the AMI complex cleaned by Harry, i type in "ami harry" and i get the records. This input must stay a single field. I hope that I have explained myself well enough.

 

[Dreamboat]

Are you using query by form?
http://support.microsoft.com/default.aspx?scid=kb;KO;209645

If so, then I think (I'm no Access geek! Didn't see anyone else answer, so I thought I'd take a shot) that you could do the query by form on the two fields... parse the data from a 3rd textbox into your two fields by using =left and/or mid functions? You can hide your two fields.

I'm not sure I understand why it has to be able to be entered only into one box, but...I mean...what are you going to do if they put cc1234 into the box with no spaces?

 

[snicker7]

thats true. Sigh. I guess I will have to go about the multiple fields search. I got scammed into writing a manual for the damned database anyway so I will just mention that. SEARCH BY INDIVIDUAL FIELDS.

 

[DALeffler]

I think I got something that might work...

How important is it that the user has to seperate search elements with a space? I'd use a comma and then qualify the text box with a direction like:

TextBox Search Syntax:

AptName, AptNumber, WhoWalked, WhoCleaned, etc...

You can leave an argument blank in the middle of the syntax, but you must include the argument's comma. If you leave one or more trailing arguments blank, don't use a comma following the last argument you specify.

Then in the AfterUpDate event for the unbound search text box (or command button OnClick event), you'll have to design a parsing procedure that parses the Search text box string into a class public array that has as many elements as search elements available in the text box syntax. Each array element is set to the search criteria in front/between the comma's in the text box. If there's not a criteria, leave the corresponding array element null.

Then for the query that's run, for each search element in the search syntax, you'll need in the query design grid a column like this:

FieldColumn1: IIf(IsNull(frmArrayElmnt(1)),True,IIf([MyTblorQry].[AptName]=frmArrayElmnt(1),True,False))
Show: unchecked
Criteria: True

FieldColumn2: IIf(IsNull(frmArrayElmnt(2)),True,IIf([MyTblorQry].[AptNumber]=frmArrayElmnt(2),True,False))
Show: unchecked
Criteria: True

FieldColumn3: etc...

It's not that parkers' search can only reference one column at a time. You can add as many columns as you want, but, if any column's criteria resolves to "" (as in when a text box is left blank), then the query returns no records unless "" is present in the underlying table/query field.

If you use field expressions with the nested Iif statements, ALL records in the underlying table/query should be returned if all search elements are "" (and you might want to trap for that before running the query).

Hope that's not too late or too complicated...

(Edit: Curses! Syntax errors...)