Query criteria from combo box of a form

[kkbharat]

Hi all,



I have a table in which I have a column of date data type. I want to have a query whereby the criteria for this date field is provided dynamically from a form combo box. The criteria could be

1) Is Not Null And <=Date()
2) Is Not Null
or
3) All
or
Any other combination.

Can someone suggest how do I go about it. I tried to search the web but could be find solution which I could understand.

Thanks,

[ranman256]

use 2 date fields, txtSTartDate , txtEndDate
(they could be the same date)
then the query is:

select * from table where [datefld] between forms!myForm!txtSTartDate and forms!myForm!txtEndDate

[kkbharat]

Thanks for your response.

But is there any way to pass the criteria like "Is Not Null And <=Date()" to the query from form combo box because in some cases I will have no date in the record.

Thanks once again.

Bharat

[kkbharat]

Hi all,

I tried to assign code to the after update combo box event and I could see in the immediate windows the value I wanted like -> like "*" or Is Not Null <= Date() is getting assigned to the combo box but they are not working in the query.

Somebody please help

Thanks.

[ssanfu]

Well, you really haven't given enough info to give a good answer.

What are you using the query for?
Is it the record source for a form? A report?
Is the query a saved query?
Could it be a query in code?


If the query is for a form record source, you could use code to set a filter.

If a saved query, you could use code to modify the saved query SQL. This would change the SQL of the query - until you changed it again.
Example:
Say you have a saved query ("qry1") with SQL of

SELECT MyDate FROM MyTable

. You execute the query and it returns all records.
Now you have a form where some how you have a way to pick different options to filter the date field and you select "Is Not NULL".
You run code so that now the SQL for the query is changed to:

SELECT MyDate FROM MyTable WHERE MyDate Is Not NULL

Every time you execute the query "qry1", the criteria is "Is Not NULL" until you run the code to change the criteria to something different.

Here is an example of modifying the query SQL:

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef

   Set db = CurrentDb()
   Set qdf = db.QueryDefs(strQueryName)

   qdf.SQL = "SELECT [Table].Field1, [Table].Field2 " & vbCrLf & _
             "FROM [Table] " & vbCrLf & _
             "WHERE ([Table].Field1 = " & chr(34) & strParameter & chr(34) & ");"

   Set qdf = Nothing
   Set  db = Nothing
End Sub

[Micron]

Proposed solution looks good. Assuming one wanted to use combo values that are more explanatory to the user, but not suitable for sql, you could get the index number of the chosen result. Then in a Select Case block that uses the index number, construct the WHERE part and concatenate it to the SELECT part - or don't if there is to be no criteria.

[kkbharat]

Hello Sir,

Thank you very much for your response.

You see I am a novice and I haven't yet got good understanding of the coding part of Access. When I searched the web for solution for my situation and came across solutions suggesting passing values to the saved query like [Forms]![FormName]![comboBox] in criteria and I was hoping this would work for me. So I tried this and wanted this to work at After Update event of the combobox and running the requery form macro but I get error saying the field name could not be found. Here definitely I am doing something wrong but am unable to put my finger on.

For now the coding looks complicated for me and will need time to get around it. However here is what I am trying to do:

I am trying to design a followup system of the customers who have visited us, some of whom will have visited us multiple times so I have assigned VisitID in my followup table. Then I have designed a query with fields like CustomersName, MobileNo, ActionDate and ActionNeeded and have attached a subquery which find the last VisitID of the customer so that I will have only one record of the customers last visit. Now the query result will have two conditions:

1) The ActionDate will have dates equal to today, greater than today or less than today,
2) Some of the dates for some of the customers will be blank indicating no action is needed.

I am trying to give the users the facility to select from a combo box on a form to select one of the options:

1) Select the records for dates up to today.
2) Select the records for all the dates except blank dates.
or
3) Select all the records.

This form is a continuous form and I have placed the combo box in the header section of this form which has AfterUpdate macro to it in which I have requery statement and in control name section I have given the form name. Since this is not working I know this is the wrong way but the requery and form name in control name section has worked for me in another form. So I am very confused and don't know what to do.

I sincerely hope I am making some sense here. I would be highly obliged if someone can suggest me something I can try immediately.

Thanks once again,

Bharat

Well, you really haven't given enough info to give a good answer.

What are you using the query for?
Is it the record source for a form? A report?
Is the query a saved query?
Could it be a query in code?


If the query is for a form record source, you could use code to set a filter.

If a saved query, you could use code to modify the saved query SQL. This would change the SQL of the query - until you changed it again.
Example:
Say you have a saved query ("qry1") with SQL of

SELECT MyDate FROM MyTable

. You execute the query and it returns all records.
Now you have a form where some how you have a way to pick different options to filter the date field and you select "Is Not NULL".
You run code so that now the SQL for the query is changed to:

SELECT MyDate FROM MyTable WHERE MyDate Is Not NULL

Every time you execute the query "qry1", the criteria is "Is Not NULL" until you run the code to change the criteria to something different.

Here is an example of modifying the query SQL:

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef

   Set db = CurrentDb()
   Set qdf = db.QueryDefs(strQueryName)

   qdf.SQL = "SELECT [Table].Field1, [Table].Field2 " & vbCrLf & _
             "FROM [Table] " & vbCrLf & _
             "WHERE ([Table].Field1 = " & chr(34) & strParameter & chr(34) & ");"

   Set qdf = Nothing
   Set  db = Nothing
End Sub

[ssanfu]

Would you post the code you are trying to use?

Since you have a continuous form. could try setting a form filter. Would need the form name, the combo box name and the field name for the date.
Is the row source for the combo box a table/query or a value list?

[kkbharat]

Hello Sir,

I am sorry for the late reply. Please note that there is no code but as you mentioned in your reply, I tried form filter but it did not work. I suppose the syntax I used for the filter, which is as follows, was wrong.

=[ActionDate]= Is Not Null And <=Date()

I am very confused about the usage of double and single quotes, ampersand etc in an expression and I think this is where I must have gone wrong. I think I will need to study access well before I can do something useful with it.

Thanks any way. Have a nice time.

Bharat

Would you post the code you are trying to use?

Since you have a continuous form. could try setting a form filter. Would need the form name, the combo box name and the field name for the date.
Is the row source for the combo box a table/query or a value list?

[ssanfu]

You understand what this means

=[ActionDate]= Is Not Null And <=Date()

but Access does not.

You MUST explicitly tell Access what fields you are testing/filtering. If you used the form filter button, the filter string would look something like this:

(([ActionDate] Is Not Null)) AND ([ActionDate]<=Date())

The first time you try to set the filter, you would select "Date Filters", then "Does Not Equal". Enter NULL and click OK.
Click on the filter again, select "Date Filters", then "Before". Enter Date(), then click OK.

To remove the filter, Click on the Filter icon, then click on "Clear Filter from ActionDate".

[kkbharat]

Hi Sir,

So what you are saying is that the syntax I am using is not right. I tried your suggestion "(([ActionDate] Is Not Null)) AND ([ActionDate]<=Date())" in the form filter and it works there but I wanted this to be a dynamic options provided from a combo box placed in the header section of the form.

I think I need to learn about syntax so could you direct me to the resources where I can learn about syntax and their usage at various places in access.

Thank you very much,

Bharat

You understand what this means

=[ActionDate]= Is Not Null And <=Date()

but Access does not.

You MUST explicitly tell Access what fields you are testing/filtering. If you used the form filter button, the filter string would look something like this:

(([ActionDate] Is Not Null)) AND ([ActionDate]<=Date())

The first time you try to set the filter, you would select "Date Filters", then "Does Not Equal". Enter NULL and click OK.
Click on the filter again, select "Date Filters", then "Before". Enter Date(), then click OK.

To remove the filter, Click on the Filter icon, then click on "Clear Filter from ActionDate".

[ssanfu]

So what you are saying is that the syntax I am using is not right. I tried your suggestion "(([ActionDate] Is Not Null)) AND ([ActionDate]<=Date())" in the form filter and it works there but I wanted this to be a dynamic options provided from a combo box placed in the header section of the form.

Since you want to be able to pick a date from a combo box in the header, using the form filter from the menu is not the best method.

Add an unbound combo box to the header and configure the combo box row source. Lets say you named the combo box "cboDateFilter".
In the after update event add code:

Option Compare Database
Option Explicit

Private Sub cboDateFilter_AfterUpdate()
    Dim sFilter As String

    sFilter = "([ActionDate] Is Not Null) AND ([ActionDate]<= #" & Me.cboDateFilter & "#)"

    Me.Filter = sFilter
    Me.FilterOn = True
    
    Me.cboDateFilter = vbNullString
    
End Sub

To show all records (remove the filter), add a button with code like:

Private Sub cmdRemoveFilter_Click()
    Me.cboDateFilter = vbNullString
    Me.Filter = vbNullString
    Me.FilterOn = False
End Sub