Querying Issues

[scarlo]

Multiple Query Issues

Ok, I have a DB of client contacts. I'm drawing the values for a query from a form using [Forms]![Sort]![Contact] in criteria and using the parameter field to give values to each. The query works fine for state, contact and industry- all in conjunction with each other. Where I am having problems is:

1) When I input less then all 3 working parameter values in the referenced form the query will not run. I think i need an SQL string of some sort- doing an if, then relationship, but I don't know how to do this.

2) I don't just have 3 values I want to reference, I have 9 total. On the form one of the problems is that a field is chosen by the end-user using check boxes (allowing only one check between three values, with no default: current client, potential client, not applicable are the choices). However, in the table these are referenced by the values of 1, 2 or 3. Another of my fields is check boxes, but referenced by yes, no. How do I correspond the field values in text to numerical values in the table? (and the yes/no prbly the same I will assume)

3) I have "sectors" as a field option in my Sort form (again the drop down menu to select). There is one field for the choice, however, in my data table I have 4 fields corresponding all back to that one field in the form through the query. This doesn't seem to work at all. The sector choices, in my original Input form, are 8, chosen and recorded into the table using 4 consecutive drop downs. They are the same 8, so sometimes a sector like Agriculture could end up in the Sector 1, or Sector 2, etc field in the table. Some can also be left blank. So in summary of this dilemma: 4 drop downs in input form to 4 data table fields, all being queried through one field on the Sort form, which is tied to my query through a command button, having specific criteria reference text in the form.

Thanks in advance for any help!!!

 

[ThreeCrow]

We'll take this a step a time

For you first 3 parameter the ones' that are working

In the criteria field of the query under "Contact"; "State"; "Industry"

Like IIf(IsNull([Forms]![YourFormName].[Contact]),"*",[Forms]![YourFormName].[Contact])

Like IIf(IsNull([Forms]![YourFormName].[State]),"*",[Form]![YourFormName].[State])

Like IIf(IsNull([Forms]![YourFormName].[Industry]),"*",[Forms]![YourFormName].[Industry])

Using this method you can select all or just one.

Example: If for "Contact" you select "nothing" from the combo box, and for "State" you select "FL" from the combo box, and for "Industry" you select "Dog Catcher".

Then run the query: All Contacts in the State of Florida that are Dog Catchers will be listed.

Give it a try

Let me hear from you as to how this worked out, then we'll go on.
You might have the others figured out.
What numbers are you using for Yes/No .

 

[scarlo]

Ok, here's what I put in my contact and state sections of table:
Like IIf(IsNull([Forms]![Sort].[Contact]),"*",[Forms]![Sort].[Contact])
Like IIf(IsNull([Forms]![Sort].[State]),"*",[Forms]![Sort].[State])

The query produces a table when entries are chosen for both contact and state, but if either are missing the table is empty..

Now I don't have an SQL or anything else running- I thought I could get away without it. Is this above all I'm supposed to need?

I believe I have numbers 1 and 2 running for the check boxes- if it's difficult I can always just make them dropdowns and put yes/no in the table instead.

 

[ThreeCrow]

What are the names of your comboboxes
are they Named "Cortact" and "State"
What is the Name of your From is it "Sort"

Do the other criteria fields have anything in them.

 

[scarlo]

The other criteria besides these two are totally empty. Contact is one combobox, State is another one- from the form.

I found this:
= [Forms]![Sort]![Contact] Or Trim([Forms]![Sort]![Contact] & "") = ""
which seems to work on the first try...

 

[scarlo]

I still have one issue remaining before I can complete this DB:
4 table fields, supported by 4 query fields, with four criteria parameters drawn from a single selection on the form. How do I tie those four fields into one parameter function without having them crash?

 

[ThreeCrow]

For your check boxes

=[Forms]![Sort].[NameOfCheckBox]

the other four should be in this query

 

[scarlo]

Ok, the only problem is that when I
= [Forms]![Sort]![Contact] Or Trim([Forms]![Sort]![Contact] & "") = ""
for 8 parameters, the "query is too complex" error pops up. And that doesn't include the four sector fields that will draw from the single form field (still haven't figured that out, even by itself).