Is null or other values

[Gespierde velopompe]

Hi All,

Small question. I use below code to pick up the beginning area and the end area for which I need results.

>=[Forms]![F_Homepage]![sector1] And <=[Forms]![F_Homepage]![sector2]

Now, in some instances, the input filed might also be empty, what do I need to add then? If I just add "Is null" to the criteria then I only get blank results.

Thanks,
Frederik.

 

[alansidman]

Wrap each portion of the criteria in the NZ function. Look here for a quick explanation of NZ

MS Access: Nz Function

 

[Gespierde velopompe]

Hi Alan,

thanks for the reply, something I learned again. But it is not what I was looking for.

In my code "sector1" and "sector2" are textboxes that can be filled in with a value. Meaning that if sector1 = 04 and sector2 = 07 then the querry should look up values between 04 and 07. But if the textbox is left blank then at the moment I don't get any values returned from my querry. In the contrary, if the textboxes are left blank, then the querry should return all values for "sector".

regards

 

[xenou]

If you have a minimum and maximum range of codes (such as 00-99 or even 00000-99999) then NZ can substitute these values for null textboxes.

>=NZ([Forms]![F_Homepage]![sector1],'00')
And
<=NZ([Forms]![F_Homepage]![sector2],'99')


Whether it's paranoia or actually a good idea I'd add exit code to the textboxes:

If Len(Me.Sector1.Value & "")=0 Then
    Me.Sector1.Value = Null
End if

I have found sometimes controls have zero-length strings in them rather than nulls, if a user deletes a value in the control. Or you could write the conditional test more stringently to handle both zero length strings and nulls.

 

[alansidman]

Try Adding an Or statement with Like [Forms]![F_Homepage]![sector1] & "*" or Like [Forms]![F_Homepage]![sector2] &"*"

 

[Kreszch68]

Just another thought. To avoid endless if not/or/and/else constructions in the expression of the criteria, just go a step further and dynamically built up the SQL statement you use for the form (or wherever you use it).

Add a button to the form to trigger creating the new SQL statement, here is an example for your case.

Private Sub cmd_CreateSQL_Click()
Dim sSQL As String
Dim sWhereClause As String
Dim iFrom As Integer
Dim iTo As Integer
'The SQL statement without any arguments/criteria
sSQL = "Select * from Table1"
'Built up the whereclause
With Me
    
        'Test if 1st criteria is present
        If Len(.Sector1.Value) > 0 Then iFrom = 1
        'test if 2nd criteria is present
        If Len(.Sector2.Value) > 0 Then iTo = 2
        
        Select Case iFrom - iTo
        
            Case 0 'No criteria
                    sWhereClause = vbNullString
            
            Case 1 'Only first criteria
                    sWhereClause = "Where Sector >='" & .Sector1.Value & "'"
            
            Case -1 'Both Criteria
                    sWhereClause = "Where Sector >='" & .Sector1.Value & "' And Sector <='" & .Sector2.Value & "'"
            
            Case -2 'Only second criteria
                    sWhereClause = "Where Sector <='" & .Sector2.Value & "'"
            
        End Select
        
End With
'Create the SQL string by adding the whereclause
sSQL = sSQL & " " & sWhereClause
'From here you can use the SQL as the recordsource for the form
Me.RecordSource = sSQL

End Sub

At first sight it may look like a little overkill, but you'll find it a lot easier to use.