Getting correct query results

[atadbitslow]

I need to know how to get a query to pull certain records.

Example:

Record 1 V
Record 2 V/W
Record 3 W/V
Record 4 FV

I need to pull records 1-3, but not 4. How do I accomplish this?? Thanks!

 

[RickSpr]

Use the Criteria line in the query grid. Under the column that contains the information that determines whether you want a row, enter a criteria expression (see the help file for help writing criteria expressions).

You can enter multiple criteria for different fields in the same Criteria line to include records that meet all the criteria on that line. You can also enter distinct sets of criteria expressions on successive Criteria lines. If a row meets all of the criteria on any line, it will be selected. Rick Sprague

 

[atadbitslow]

Thanks Rick, I know about where to put the criteria and all that stuff, but I don't know the statement I should use. I've tried using the Instr function which picks up the W/V, but it also picks up the FV.

The situation is the user puts in a code in a textbox on a form for who they want on the report. The query looks to the code the user has entered. So when the user puts V in the textbox, I want it to pick up records 1-3 (above). Using the instr function picks up the FV also. There are other choices the user can put in the textbox, such as A, C, ID, W, FV so I can't customize it completely for the FV.
I've looked at the wildcards available, but they don't seem to apply.

Anybody else?? Thanks!

 

[RickSpr]

So this field you're wanting the criteria on has multiple names in it? That's why you're having a problem. In good relational design, a given column should only have a single fact in it.

Your best approach would be to use another table to hold the names/initials, with a foreign key to this table. The other table would have one row per name, and have a one-to-many relationship from this table.

However, if you can't do that, you could write a code loop to search this field, break it into individual names (assuming they're always separated by "/" characters), and test the individual names. Rick Sprague

 

[atadbitslow]

I guess I am not being clear--

The name of the column/field is Displ_main. The possible entries in this field are A, C, FV,ID,V,W and sometimes a person has more than one discipline, so they may have W/V or V/W or C/ID in their displ_main field. I don't believe this is a problem in a relational database. I just want the user to be able to print a report of any particular discipline and have the report include W/V's and V/W's if the user enters V, BUT not have the FV's show up on the report. Is this any clearer?? Hope so!

 

[dneufarth]

try this

SELECT tblTemp.a
FROM tblTemp
WHERE (tblTemp.a="V&quot OR InStr(tblTemp.a,"V/&quot = true OR InStr(tblTemp.a,"/V&quot= true;



Dave