Help! Problem i also donno how to describe..

[Xaverria]

I have 20 criteria (yes/no). All in the same query, but at all time not all of them are need to filter the table.

How should i compose my sql?

Furthermore, i have made it into a form if the user want to use this criteria, he will check the box and choose (yes/no). If he did not check the box, the criteria will not be added into the sql?.

How should i do it?


--------------------------------------------------------------------------

 

[llkhoutx]

Conditionally test each criteria, one by one, adding to your SQL string as required as the test indicates.

 

[Xaverria]

Does that mean my sql should be something like this:

SELECT * FROM (table) WHERE (u);

then using my VBA coding for my button i will need to add the condition to the (u).

 

[llkhoutx]

Yes it does.

 

[Xaverria]

In the end, my (u) look like this:

SELECT *
FROM (table)
WHERE ((((col1)=([Forms]![REPORT_ADMIN]![A1])) AND ((col2)=([Forms]![REPORT_ADMIN]![A2])) AND);

How do i remove the last "AND" at the back?

 

[llkhoutx]

SELECT * FROM (table) WHERE [col1]=Forms!REPORT_ADMIN!A1 AND [col2]=Forms!REPORT_ADMIN!A2;

Note that I have also removed the parentheses and the braces; they are not required; the parentheses are sometimes hard to balance.

In building the criteria with code I start with a blank string (sWhere), if it's not null I add an AND before the criteria. In adding each additional criteria, I also check for a null string, if not Null, I precede the additional criteria with an AND. That way I never worry about an AND on the end of the criteria. After testing the last criteria I add the sWhere to the sSQL ("SELECT * FROM (TABLE) WHERE ") for my sql string. If no criteria, peel off the WHERE; alternatively add it with the first (non null) criteria, if any.

I've posted this code on this site recently.

 

[yhgtbfk]

condition = null

if mytxt1 = "Yes" then
condition = condition & " AND col1 = 'Yes'"
end if

if mytxt2 = "Yes" then
condition = condition & " AND col2 = 'Yes'"
end if

etc....

select * from table where isnull(primarykeyfield) = FALSE " & condition

We know that every primary key field will not be null, so this wont eliminate any records.