By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,364 Members | 1,604 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,364 IT Pros & Developers. It's quick & easy.

Setting up search query that ignores null values

P: 33
I'm creating a form where you can input either some or all of a person's information in order to sort through the matching records in the table TblPatientDemographics. I'm searching based on ID number (PtID), last name (PtLastName), first name (PtFirstName), DOB (PtDOB), and SSN (PtSocSec). I want to design a query that searches for matching results for all of these (meaning I need to use AND in my WHERE condition-- not OR).... But I want the query to ignore any null values that may be in these fields. For instance, if you just entered "Jones" for the last name, it would return all records with that last name, and ingore the fact that the other fields are blank).

I'm not sure how to set up my query to where it ignores null values in these fields. Can someone help me? Thanks!
Nov 9 '10 #1

✓ answered by ADezii

Jennifer, you should have clearly stated you intention of populating a List Box from the dynamic SQL Statement, in the very beginning. That being said, it is simply a matter of a few more lines of Code (Lines 32 to 45) no matter what approach you use to generate the SQL Statement. Assuming the List Box is named List1, the completed Code would be:
Expand|Select|Wrap|Line Numbers
  1. Dim strCriteria As String
  2.  
  3. If Not IsNull(Me![txtLastName]) Then
  4.   strCriteria = strCriteria & "[PtLastName] = " & "'" & Me![txtLastName] & "'" & " AND "
  5. End If
  6.  
  7. If Not IsNull(Me![txtFirstName]) Then
  8.   strCriteria = strCriteria & "[PtFirstName] = " & "'" & Me![txtFirstName] & "'" & " AND "
  9. End If
  10.  
  11. If Not IsNull(Me![txtDOB]) Then
  12.   strCriteria = strCriteria & "[PtDOB] = " & "#" & Me![txtDOB] & "#" & " AND "
  13. End If
  14.  
  15. If Not IsNull(Me![txtSocSec]) Then
  16.   strCriteria = strCriteria & "[PtSocSec] = " & "'" & Me![txtSocSec] & "'" & " AND "
  17. End If
  18.  
  19. If Not IsNull(Me![txtPtID]) Then
  20.   strCriteria = strCriteria & "[PtID] = " & Me![txtPtID] & " AND "
  21. End If
  22.  
  23. If Len(strCriteria) = 0 Then Exit Sub       'No Criteria
  24.  
  25. 'Strip ' AND '
  26. If DCount("*", "TblPatientDemographics", Left$(strCriteria, Len(strCriteria) - 5)) = 0 Then
  27.   MsgBox "No Records meet the specified Criteria", vbCritical, "No Records"
  28.     Exit Sub
  29. End If
  30.  
  31. '**************************************** Newly Added Code ****************************************
  32. Dim strSQL As String
  33. Dim lst As ListBox
  34.  
  35. strSQL = "SELECT * FROM TblPatientDemographics WHERE " & Left$(strCriteria, Len(strCriteria) - 5)
  36.  
  37. Set lst = Me![List1]
  38.  
  39. With lst
  40.   .ColumnCount = 5
  41.   .BoundColumn = 1
  42.   'Create your own Column Width Values
  43.   .ColumnWidths = ".5 in;1.5 in;1.5 in;1 in;2.25 in"
  44.     .RowSource = strSQL
  45. End With
  46. '**************************************************************************************************

Share this Question
Share on Google+
6 Replies


gnawoncents
100+
P: 214
I used the code below to generate a where string for one of my databases. It sounds like what you're looking for.

Expand|Select|Wrap|Line Numbers
  1. Dim strCBname As String
  2. Dim ctlCB As Control
  3. Dim strSQLwhere as String
  4.  
  5. strSQLwhere = ""
  6. 'Loop through the controls, find those with selections and add to WHERE string
  7. For Each ctlCB In Me.Controls
  8.     If (ctlCB.ControlType = acComboBox) And IsNull(ctlCB) = False Then
  9.        strCBname = ctlCB.Name
  10.        strSQLwhere = strSQLwhere & " TBLSurveyResponses." & strCBname & " Like [Forms]![FRMsurveyReports]." & strCBname & " AND"
  11.     End If
  12. Next ctlCB
  13.  
  14. strSQLwhere = Left(strSQLwhere, Len(strSQLwhere) - 4) ' remove the trailing " AND" 
Note that my combo box names mirror the table field names so I can automate the process. If yours don't or can't, you'll need to tweak the code some.
Nov 9 '10 #2

ADezii
Expert 5K+
P: 8,669
Assumptions (just another approach, based on Text Boxes, that will also alert User if No Records Match specified Criteria):
  1. [PtID] is Numeric
  2. [PtSocsec] is a String
  3. Control names on Form mimic actual Field Names
    Expand|Select|Wrap|Line Numbers
    1. Dim strCriteria As String
    2.  
    3. If Not IsNull(Me![txtLastName]) Then
    4.   strCriteria = strCriteria & "[PtLastName] = " & "'" & Me![txtLastName] & "'" & " AND "
    5. End If
    6.  
    7. If Not IsNull(Me![txtFirstName]) Then
    8.   strCriteria = strCriteria & "[PtFirstName] = " & "'" & Me![txtFirstName] & "'" & " AND "
    9. End If
    10.  
    11. If Not IsNull(Me![txtDOB]) Then
    12.   strCriteria = strCriteria & "[PtDOB] = " & "#" & Me![txtDOB] & "#" & " AND "
    13. End If
    14.  
    15. If Not IsNull(Me![txtSocSec]) Then
    16.   strCriteria = strCriteria & "[PtSocSec] = " & "'" & Me![txtSocSec] & "'" & " AND "
    17. End If
    18.  
    19. If Not IsNull(Me![txtPtID]) Then
    20.   strCriteria = strCriteria & "[PtID] = " & Me![txtPtID] & " AND "
    21. End If
    22.  
    23. If Len(strCriteria) = 0 Then Exit Sub       'No Criteria
    24.  
    25. 'Strip ' AND '
    26. If DCount("*", "TblPatientDemographics", Left$(strCriteria, Len(strCriteria) - 5)) = 0 Then
    27.   MsgBox "No Records meet the specified Criteria", vbCritical, "No Records"
    28.     Exit Sub
    29. End If
    30.  
    31. Me.RecordSource = "SELECT * FROM TblPatientDemographics WHERE " & Left$(strCriteria, Len(strCriteria) - 5)
Nov 9 '10 #3

P: 33
I get what the code aims to do here.... Now let me ask a dumb question (And please forgive me, as I've been working in Access about 5 weeks).

The reason I was trying to structure this in SQL was that I wanted the records shown to show up in a listbox.... That way I could have the rowsource pulling values from this query.
I get that this code creates the SQL string, but how would I add it to my project so that it would populate that listbox as well? I'm thinking I need to add it to the OnClick event of a button, but then how is the generated SQL used to make these values show up in the listbox?
Nov 10 '10 #4

ADezii
Expert 5K+
P: 8,669
Jennifer, you should have clearly stated you intention of populating a List Box from the dynamic SQL Statement, in the very beginning. That being said, it is simply a matter of a few more lines of Code (Lines 32 to 45) no matter what approach you use to generate the SQL Statement. Assuming the List Box is named List1, the completed Code would be:
Expand|Select|Wrap|Line Numbers
  1. Dim strCriteria As String
  2.  
  3. If Not IsNull(Me![txtLastName]) Then
  4.   strCriteria = strCriteria & "[PtLastName] = " & "'" & Me![txtLastName] & "'" & " AND "
  5. End If
  6.  
  7. If Not IsNull(Me![txtFirstName]) Then
  8.   strCriteria = strCriteria & "[PtFirstName] = " & "'" & Me![txtFirstName] & "'" & " AND "
  9. End If
  10.  
  11. If Not IsNull(Me![txtDOB]) Then
  12.   strCriteria = strCriteria & "[PtDOB] = " & "#" & Me![txtDOB] & "#" & " AND "
  13. End If
  14.  
  15. If Not IsNull(Me![txtSocSec]) Then
  16.   strCriteria = strCriteria & "[PtSocSec] = " & "'" & Me![txtSocSec] & "'" & " AND "
  17. End If
  18.  
  19. If Not IsNull(Me![txtPtID]) Then
  20.   strCriteria = strCriteria & "[PtID] = " & Me![txtPtID] & " AND "
  21. End If
  22.  
  23. If Len(strCriteria) = 0 Then Exit Sub       'No Criteria
  24.  
  25. 'Strip ' AND '
  26. If DCount("*", "TblPatientDemographics", Left$(strCriteria, Len(strCriteria) - 5)) = 0 Then
  27.   MsgBox "No Records meet the specified Criteria", vbCritical, "No Records"
  28.     Exit Sub
  29. End If
  30.  
  31. '**************************************** Newly Added Code ****************************************
  32. Dim strSQL As String
  33. Dim lst As ListBox
  34.  
  35. strSQL = "SELECT * FROM TblPatientDemographics WHERE " & Left$(strCriteria, Len(strCriteria) - 5)
  36.  
  37. Set lst = Me![List1]
  38.  
  39. With lst
  40.   .ColumnCount = 5
  41.   .BoundColumn = 1
  42.   'Create your own Column Width Values
  43.   .ColumnWidths = ".5 in;1.5 in;1.5 in;1 in;2.25 in"
  44.     .RowSource = strSQL
  45. End With
  46. '**************************************************************************************************
Nov 10 '10 #5

P: 33
I guess my original aim was to just create a query, so I didn't realize it was necessary--- my apologies there, ADezzii, and I appreciate your patience with this. Your solution worked... thank you!!! I wasn't aware that you could use listboxes in that way. I guess you learn something new everyday!
Nov 10 '10 #6

ADezii
Expert 5K+
P: 8,669
That is my life's goal, to try to learn at least 1 new thing every day! (LOL).
Nov 10 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.