Query - criteria

[hafiff]

Hi everyone,

I a query setup and the criteria is pulled from a form, frmReport_1

on query, I entered: Like [Forms]![frmReport_1].[loc]

On the frmReport_1, I have a combo box with a value list of Not 3;"All";1;"Non-Provisional";2;"Provisional"

When I entered as "All", the report is blank - no reports. The rest options work fine. Does anyone know how to produce report without the number 3; thus, only number 1 and 2 will show.

Thanks,
HA

 

[apr pillai]

Change the Criteria in the Query to:

choose([COLOR=#333333][Forms]![frmReport_1].[loc][/COLOR],"IN(1)","IN(2)","IN(1,2)")

 

[apr pillai]

Sorry, that was a quick thought, but it will not work.

 

[apr pillai]

Here is a VBA based solution. The Code runs on the Combobox After_Update Event procedure. Modifies the SQL definition of the SELECT Query based on the selection in the Combobox.

Private Sub Combo4_AfterUpdate()
Dim strSQL As String, crit As String, cboval As Integer
Dim qryDef As QueryDef, db As Database, crit2 As String


Set db = CurrentDb
Set qryDef = db.QueryDefs("Query45")


strSQL = "SELECT Table1.* From Table1 WHERE (((Jobs.IDX)="

crit2 = "));"


cboval = Me!Combo4
If cboval = 1 Then
  crit = "1"
ElseIf cboval = 2 Then
  crit = "2"
Else
  crit = "1 OR (Jobs.IDX)=2"
End If


qryDef.SQL = strSQL & crit & crit2
db.QueryDefs.Refresh


End Sub