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!!!!

 

[GingerR]

You could forget about the txt box, and just use the cbo box in your query.

In your query, make a calculated field, something like this:

ChosenStatus: Forms!FormName!cboSponsoredStatus

In the CRITERIA, in the first row put

"Sponsored"

and under that in the second row, put

"Unsponsored"

in the criteria for the field that you care that is NULL or NOT NULL, on the first row of CRITERIA, put

Is Not Null

and under that, Is Null.

Try that out. Take out any reference to txtSponsoredStatus.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BakerUSMC]

GingerR,

Thanks for your help... Worked great and it was a simple solution. I haven't done that type of thing in a query before so it was a good learning experience for me.

Plus... I posted this same question in the VBA coding forum and got 2 responses that didn't work... I appreciated all the help though...

Thanks again!!!!

 

[BakerUSMC]

Another question:

If I have multiple parameters in the query, how do I ensure the calculated field will only refer to the field I need to know the Null values in?

I have 3 combo boxes on frmViewOrphans that the user needs to select a value from. Sometimes it works with the right criteria selected but when I put other criteria that I know should return no records, the query populates all the records....

I am not sure how to ensure that the values I selected from the 3 combo boxes are passed to the query so that I get the correct results...

Please help!!!

 

[GingerR]

Not quite clear on what you're asking. Maybe it's this: So say you have done the thing we talked about with one combo box, and it works right. Then you want to deal with a second combo box. Put in another calculated field, the same way, referencing the second combo box. Then in the criteria rows below, you'd have four rows that deal:

cbo1 cbo2
Is Null Is Null
Is Null Is Not Null
Is Not Null Is Null
Is Not Null Is Not Null

Might turn out complicated/irritating with many combo boxes. If I'd known you had more than one, we might have gone in a different direction. In any case, is that what you were talking about? If not, you'll have to post your query's sql statement, plus table structure, field names and data types. Plus some sample data; how it's not working, how it should work. Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html