Passing "Is Not Null" as a parameter in a query

[Packy427]

Hi all,

Hopefully someone will be able to help me with this. I've been struggling with it for a bit.

I have a form which users can toggle whether they want to only see entries that has data in a certain field. Previously I have set up a separate query with a hardcoded 'Is Not Null' in the criteria, and set the form to call the different queries based on the status of a toggle button. This time there is 22 queries that need to be modified so I'm hoping there's a better way.

I have a hidden textbox on my form that has value "Is Not Null" or Null based on the position of the toggle button.

In my query for the field criteria I have [Forms]![MainForm].[txtCriteria] where txtCriteria is the textbox previously mentioned.

When I run the query no data is shown.

Any help or direction would be greatly appreciated.

Regards,
Pat

 

[vbaInet]

The solution would depend on how many other criteria are you applying on the query?

 

[Packy427]

The only other Criteria is for a date field. It's criteria is Not Null And Between two dates, where the two dates are referenced from the same form

 

[vbaInet]

In that case you'll need to build your criteria in code. Please search the forum for examples.

 

[Packy427]

Tried searching before and came up emptyhanded, will give it another go. Thanks!

 

[vbaInet]

I'll give you a head start:

    Dim strWhere As String
    
    ' build WHERE clause for use in the Filter property
    If Len(Me.txtCriteria & vbNullString) [COLOR="Red"]<>[/COLOR] 0 Then
        strWhere = "[FieldName] = '" & Me.txtCriteria & "' AND "
    End If
    
    If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
        strWhere = strWhere & "[FieldName] BETWEEN " & Format(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & " AND " _
                                                     & Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & " AND "
    End If
    
    ' strip off last AND
    strWhere = Left(strWhere, Len(strWhere) - 5)    ' 5 represents Len(" AND ")
    
    ' apply the filter
    If Len(strWhere) <> 0 Then
        Me.Filter = strWhere
        Me.FilterOn = True
    End If

 

[Gasman]

vbaInet,

PMFJI, but I read a lot of posts to gain more knowledge, and am puzzled by the first part of the code?

Shouldn't that first part of the code be checking for > 0 to start off the strwhere variable.?

If it was 0, wouldn't that mean an empty field, and hence not be required?

TIA

 

[vbaInet]

Good catch Gasman! That was all aircode so there's bound to be typos which the poster can alter.

 

[vbaInet]

vbaInet,

PMFJI, but I read a lot of posts to gain more knowledge, and am puzzled by the first part of the code?

Shouldn't that first part of the code be checking for > 0 to start off the strwhere variable.?

If it was 0, wouldn't that mean an empty field, and hence not be required?

TIA

I didn't even ask what PMFJI meant? And I know Swansea quite well... Oystermouth road, mumbles, wind street, the marina

 

[Gasman]

Wind St.... say no more.

PMFJI... Pardon Me For Jumping In.

You actually helped me a lot a few years back when I was creating a database for my Merchant Navy Shipping Company's Gazette.

Many thanks for that.

 

[vbaInet]

I'm sure you have many good memories from there

No problem! That's what we're here for.