Passing Is Null or Is Not Null from VBA to Query Criteria

[BakerUSMC]

Hello to all,

I have been searching these threads all afternoon, but can't find anything that comes close to what I need. I feel its a simple solution but just can't figure it out.

Here's the situation:

I have a form (frmViewOrphans) that has a combo box on it (cboSponsoredStatus). When an item is selected in cboSponsoredStatus, the value is placed in an unbound textbox (txtSponsoredStatus). Once a status is selected then an 'Open' button is clicked to open another form (frmOrphans) that is based on a query (qryView). qryView is looking at txtSponsoredStatus for its value to filter the records.

I need in VBA coding to pass a Is Null or Is Not Null value to the query criteria. Here is the code that I have so far:

    If Me.cboSponsoredStatus = "Sponsored" Then
    
    Me.txtSponsoredStatus = [b]Is Not Null[/b]    
    Else
    
    If Me.cboSponsoredStatus = "Unsponsored" Then
    
    Me.txtSponsoredStatus.Value = [b]Is Null[/b]

    
    End If
    End If

But using Is Not Null or Is Null does not work. What do I have to use to obtain the result I am looking for? Using Is Not Null or Is Null in the acutal query works but it doesn't work in VBA...?????

Thanks in advance!!!!

 

[AlanJordan]

How about running an update query?

In it's simplest form you would run:

UPDATE Table1 SET Table1.Field1 = Null;

Of course, you'd probably like to put some criteria into it.

You could build your update query in access, and then run it using the Docmd.RunSQL command or the query.execute command:

Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb
 Set qdf = db.QueryDefs "qry_ap_AppendOvertimeForAWeek_Old")
        qdf("datPPE") = datPPE  'Set the value of the parameter 1
        qdf("WeekNumber") = intWeekNumber '2
        qdf("PayPeriod") = intPayPeriod '3
        qdf("intWeekNumber") = intWeekNumber '4
        qdf("intPayPeriod") = intPayPeriod
        qdf.Execute '("qry_ap_AppendOvertimeForAWeek_Old")

I hope this helps. Alan

 

[MajP]

This will not work. Your are basically passing program flow. This would be similar to trying to set the name of a procedure equal to a variable. Yes in the query developer it looks like a string, but the query developer converts that criteria into a sql statement.

How about using the DoCmdOpenForm
If Me.cboSponsoredStatus = "Sponsored" Then
DoCmd.OpenForm "frmOphans", , , "strYourFieldName is Null"
else
If Me.cboSponsoredStatus = "Unsponsored" Then
DoCmd.OpenForm "frmOrphans", , , "strYourFieldName is not Null"
end if