IIF ([date_combo_box] Is Null,“xâ€, Between date 1 And date 2)

[maria]

In a query, in the criteria I write the following IIF statement:

IIf([Forms]![frm_search]![date_1] Is Null,
[tbl_projects].[date],
[tbl_ projects].[date] Between [Forms]![frm_search]![date_1] And
[Forms]![frm_search]![date_2])

In a form named “frm_search†there are 2 combo boxes, named "date_1" and
"date_2".

In the case that I leave the combo boxes null, the query runs without a
problem. But when I write some date in the combo boxes the query don’t show
any records. I revise the dates and it should show some record filtered by
the dates.


Appreciate any help. Thanks

Maria

 

[excel/access-chump]

Maria,
You need to write the query in VB to base it on the combo box selection.

Here is a wroking example:

Dim db1 As dao.Database
Dim RevID As String
Dim strSQL2 As String
Set db1 = DBEngine(0)(0)
RevID = Me!User_ID.Value
<The Me!User_ID.Value is the name of the combo box object>

strSQL2 = "SELECT x.HPKeyword, x.ReviewerId, x.addrkey, " _
& "x.ChartsScheduled, x.ChartsRetrieved, x.ChartsNotRetrieved, " _
& "FROM dbo_vw_ScheduledChartByRvwrAppt AS x " _
& "WHERE x.ReviewerId = '" & RevID & "' ;"

< The query now looks for the record where ReviewerId matches RevID >

Debug.Print strSQL2
db1.Execute strSQL2, dbFailOnError