Changing query criteria

[RevJeff]

I have a query, "adhocComplete" that has criteria ">=[Forms]![frmAdhoc].[Date] And <=[Forms]![frmAdhoc].[Date2]" for the field [30DayDue].

Is there a way of removing the criteria from the query by unchecking a checkbox [Check62] on the form [frmAdhoc] and re-adding the criteria if the checkbox is check?

I found the below code and think it is the way I am trying to go, but can't get it to work.

Dim qdf As DAO.QueryDef
Dim qdfOLD As String
Set qdf = CurrentDb.QueryDefs("Query1")

With qdf
    qdfOLD = .SQL
    .SQL = Replace(.SQL, "ProjectRef='P4'", "ProjectRef='P5'")

    ' Code to do stuff with SQL-string/query

    .SQL = qdfOLD ' Reset SQL to old setting
End With

Set qdf = Nothing

Thanks in advance

 

[spikepl]

The criteria must form,ultimately, a logic yes or no, and that can be accomplished by many different ways. You should look at the SQL view to see what things actually look like, because the design grid contravenes some conventions.

Anyway:

">=[Forms]![frmAdhoc].[Date] And <=[Forms]![frmAdhoc].[Date2]"

should b, in your case:

"(>=[Forms]![frmAdhoc].[Date] And <=[Forms]![frmAdhoc].[Date2] AND [Forms]![frmAdhoc].[Check62]=True) OR [Forms]![frmAdhoc].[Check62]=False

You should name your controls properly and not stick to the system defaults like check62 because that will make it harder when you revisit your code soon. And you will.

 

[RevJeff]

Thank you for your help! That worked perfectly! I try to name everything and not go with the system defaullts, but I have almost 150 check boxes on this form and got lazy.

 

[RevJeff]

Hi Spikepl,

It turns out that I have one more followup question. The code you posted works great if the checkbox is check with a date range and it works if the checkbox isn't checked, but I have a third option. I would also like to have the option to have the checkbox checked and have no date range, so it shows every record whether there is a date in the field or it is blank.

I've gone into the sql code and tried different things, but nothing seems to work.

Thank you again!

 

[spikepl]

You need to wrap each of your date references in the function Nz (look it up). That function gives a value specified as parameter 2, if parameter 1, the date reference, is Null.

That default value is of course a date, one so far away from your current dates so that it ensures that all dates are picked.

 

[RevJeff]

Ok, I got that working, it shows ever record with a date, but it doesn't show null values. Is there a way to show records that are null?

Thanks

 

[spikepl]

I misread your post. If you want 3 options you need to have an indicator for 3 options. In a form you can set tri-state for a checbox, which will give it 3 options, but that is not obvious for users and it might be better to have an option group. Based on the selected value null-records are in, and the remaining 2 options would be like for your checkbox now. You should be able to set that up, i.e. the last 2 options. When done, we can help you with the first one.

 

[RevJeff]

I went with the tri-state checkbox (for space reasons), which I never knew existed, so thank you for showing me that. I will be training the users once I have the db completed, so I will make sure they all know about it.

How do I check for that state of the check box? Now I have True, False and ???

Thank you

 

[James Deckert]

isnull(form_setup.checkbox)