Null Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am running the following code to produce a report with a where clause
based upon a users selection in a set of combo boxes.

How do I handle 'Null' values. What do I need to do if the user does not
enter anything in these combo boxes. Right now their default is blank.

Any help would be much appreciated.

Thanks
AC

strWhere = "[Country] = '" & Me!cmb_Country & "' and [Region] = '" &
Me!cmb_Region & "' and [ClientSector] = '" & Me!cmb_Sector & "'"

Select Case Me!ReportToPrint
Case 1
DoCmd.OpenReport "rpt_New_Bus_Wins_by_Sector", PrintMode, ,
strWhere
Case 2
DoCmd.OpenReport "rpt_Profitability_Analysis", PrintMode, ,
strWhere
Case 3
DoCmd.OpenReport "rpt_RePrice", PrintMode, , strWhere
 
Change your existing strWhere expression to:

If Not IsNull(Me!cmb_Country) Then
strWhere = "[Country] = '" & Me!cmb_Country & "'"
End If
If Len(strWhere) > 1 Then strWhere = strWhere & " And "
If Not IsNull(Me!cmb_Country) Then
strWhere = strWhere & "[Region] = '" & Me!cmb_Region & "'"
End If
If Len(strWhere) > 1 Then strWhere = strWhere & " And "
If Not IsNull(Me!cmb_Country) Then
strWhere = strWhere & "[ClientSector] = '" & Me!cmb_Sector & "'"
End If

HTH,
Nikos
 
Back
Top