Form based Parameter query, with criteria for two fields or none

[nocoffeenoworkee]

What i'm trying to do is enter into a text box on a form a number which will then be the criteria for two different fields in a query. But it can be either both of the fields or just one of them that has it. The criteria isnt necessary, so if nothing is entered in the box then theres no criteria for those fields.

Here is what I entered in field in the QBE, but doesnt work:

Expr 1: IIf(Not IsNull([Forms]![Predictive_Form2]![Text14]),True,[MET_ATCK] Or [VEH_ATCK]=[Forms]![Predictive_Form2]![Text14]) OR 
IIf(IsNull([Forms]![Predictive_Form2]![Text14]),True,[MET_ATCK] Or [VEH_ATCK]=" " OR "*")

It just returning all records with a -1 in Expr 1 column.

If someone could help me modify it or go a different route i'd appreciate it.

[orange]

Try describing the "business involved" in simple terms (plain English) just as you would say to an 8 year old.
We can't help you until we understand what you are trying to do.

This doesn't start with a text box on a form.
Tell us about
[MET_ATCK] and [VEH_ATCK] and the related table---What does it represent?

[nocoffeenoworkee]

[MET_ATCK] and [VEH_ATCK] actually represent the same thing. They're just the Method of attack or M.O for a crime. Its just that they can be entered into my database in two different fields in an either/or fashion. So I need to refer to them both to get all instances where that M.O is used.

The tables are [MAIN] for [MET_ATCK] and [VEHICLE] for [VEH_ATCK]

The text should come from [Forms]![Predictive_Form2]![Text14]. Check boxes would work too.

Hope that explains it.

[orange]

Not really/. Think about this situation -- for some reason you can't build this database (you're assigned other duties; you're being sent to Italy for 6 months...whatever). So, you have a few days to organize things so that a contracted developer would build the database -based on your analysis and design.
What would you be handing over to that developer?
What are the facts he/she will use to build this database?
Who will test the evolving design?
Who will accept the product(completed database and related instructions) and determine that it is complete?

Databases don't just happen. You need to identify the things involved, how they relate to each other, what validation is needed....

Typically one fact one field. It would not be common to have
[MET_ATCK] and [VEH_ATCK] actually represent the same thing

but you know your situation better than the readers.

This data model from Barry Williams site may offer some insight.



Good luck with your project.

[nocoffeenoworkee]

Sorry I didnt mean to say that I'm building the database. Its already built. I'm just querying it using a form. I know its weird that two fields are the same thing but thats how it is and thats whats causing this dilemma. I'm trying to say if either of them have the given M.O then return the record. The two tables involved are related by a ID#.

[Bulzie]

As Orange I believe is saying, normally that would be 1 table, where each record has a Type field to say if it was MET or VEH. But if you have to keep the structure that way, maybe do a UNION query on those 2 tables, then use that in another query to check for the criteria which now only looks at a single field.

Select MET_ATCK.Field1, MET_ATCK.Field2, MET_ATCK.Field3 From MET_ATCK
UNION ALL
Select VEH_ATCK.Field1, VEH_ATCK.Field2, VEH_ATCK.Field3 From VEH_ATCK

Then use above query in a 2nd query to use your criteria?

[nocoffeenoworkee]

Thanks Bulzie. That was the right idea.

In summary,

I made a Union query:

SELECT MAIN.ID_NUMBER, MAIN.MET_ATCK FROM MAIN


UNION SELECT VEHICLE.ID_NUMBER, VEHICLE.VEH_ATCK FROM VEHICLE;

Then put that union table in the form query, relating tables by the ID.