Filter on 2 Boolean feilds

[insanity]

Hi experts!

I'm a bit stumped on this one. I am dynamically building a SQL string to load a forms recordset. I've set up 4 checkboxes which filter the data on two fields (set Yes/No).

Each field has three options, True/False, False/True, or All Records (Either True/True or False/False) - I've set False/False to All Records as the data to either be extract or not extracted!

I have the following code to set the strings, this is one for Extracted, then another for Uploaded (not pasted)

    If Me.chkoptExtracted = True Then
        If Me.chkoptNotExtracted = True Then
            strExtracted = "*" ' both true, select all (show all)
        Else
            strExtracted = True ' ext = true, notext = false
        End If
    Else
        If Me.chkoptNotExtracted = True Then
            strExtracted = False 'ext false, notext true
        Else
            strExtracted = "*" ' ext = false, notext = false (show all)
        End If
    End If

I have the following code to build the SQL string.

     strSQL = "SELECT * FROM tblValidUpload" _
        & "WHERE ((Uploaded = " & strUploaded & ") AND Extracted = " & strExtracted & ") ORDER BY CostCentre;"

It works fine when there is a True/False but when I try to select True/True or False/False it doesn't like "*".

Can anyone please point me in the right direction to overcome this or an alternative method?

Thanks.

Peter

 

[Mile-O]

I am dynamically building a SQL string to load a forms recordset.

     strSQL = "SELECT * FROM tblValidUpload" _
        & "WHERE ((Uploaded = " & strUploaded & ") AND Extracted = " & strExtracted & ") ORDER BY CostCentre;"

Psst! That's NOT a dynamic query. A Dynamic Query is one where you select a different amount of fields each time

i.e

SELECT Forename, Surname, Address1
FROM tblPeople
WHERE Forename = "John";

SELECT Forename, Surname, Address1
FROM tblPeople
WHERE Surname = "John";

Notice how the criteria on the fields changed?

That said, you'd be best to build this query of yours in the Query grid and just have it pre-saved. Having it pre-saved prevents database bloating and you manually having to compact it.

And your problem? If it's a Boolean field then you can't use the wildcard symbol (*) as that's used for evaluating string values.

 

[insanity]

My apologies, I always thought a dynamic query isone built from code. Learn something new everyday!

A query on your solution (no pun intended), does that mean I'd need to make 6 pre-built queries to load depending on the different solutions to the checkboxes?

Also the recordset needs to be updatable as this form where they check that the item has been uploaded or not (it automatically populates the Extracted when they run a crystal report via DAO).

How then do you filter for all records on a Yes/No from code? Do I need to have the string determine if it's selecting all records then exclude the WHERE if all records or include if it's either TRUE or FALSE?

Also the database is an MDE. Would this still cause bloating??

The other thing I thought of is two option groups on each field, then have a select case that determines the SQL string.

I think you created more question than answers, but thanks for your help so far!

Peter

 

[Mile-O]

I'd create some public functions to start with...still thinking on this one..

i.e.

Public Function MyCheck(ByVal boo As Boolean) AS Boolean
    MyCheck = boo
End Function

That way, at least, you can refer to your form from a normal query:

i.e. as a criteria

MyCheck([Forms]![MyForm]![MyCheckbox])

 

[insanity]

Now you've confused me!

I sorta do that now on another form which loads a query, but in the criteria field I just enter <=[forms]![frmJournalView]![cboPrevPeriod] and it filters on that combo box. Why do I need to create that public function??

Also why do I want the query to refer to the form? I want the information in the SQL to populate the form's recordsource. I set Me.Recordsource = strSQL. Just realsied I didn't explain myself very well to begin with .. sorry.

Sorry for asking so many questions. What I know of MS Access i've sorta made up as I go along. Would love to know if i'm doing things wrong (well not very well, as I usually get the outcome I want, if sometimes clumsily!)


Peter

 

[Mile-O]

In fact, forget all I've said, you've written the code:

Does this change work?

strSQL = "SELECT * FROM tblValidUpload " & _
        "WHERE ((Uploaded = " & strUploaded & ") AND Extracted " & IIf(strExtracted = "*", " < 1 ", " = " & strExtracted)  & ") " & _
        "ORDER BY CostCentre;"

 

[insanity]

Yippie!! Yes that did work ... THANK YOU!!!

I also discovered another way as well. You can filter boolean on all .. by the following:

WHERE Extracted Like "*"

So when I set the string I can either:

strExtracted = "= TRUE" 
or 
strExtracted = "LIKE " & Chr$(34) & "*" & Chr$(34)

Extending on from your suggestion I could also set the above string as "= -1", "= 0" or "< 1".

Thank you so much for your help!!!!

Onwards I go until the next problem!

Peter

 

[Mile-O]

strExtracted = "LIKE ""*"" &


To put a " in a string, you double up