Great Search Functionality... Shame I seem to be missing something (1 Viewer)

hoochiepoos · Dec 12, 2018

Hi,

I am new to access and have been thrown in at the deep end.
I am making a search form using a form with subform. The subform is based on a query.

The form has a number of combo boxes which allow the user to select or type in a value. Stringing these together means that you can refine you search easily.

The following code is working for me but................. for some reason I just cannot work out how to return records that have blank fields. Some of the fields are optional and don't have to be filled. (of course I could force a default value but this seems clumsy)

Code:

Function SearchCriteria()

Dim EnquiryNo, strSparesStatus, strDescription As String
Dim strCriteria As String



'This field is an integer
If IsNull(Me.cboEnquiryNo) Then
    EnquiryNo = "[EnquiryNo] like '*'"
Else
    EnquiryNo = "[EnquiryNo] = " & Me.cboEnquiryNo
End If



'The next two fields are strings    
If IsNull(Me.cboSparesStatus) Then
    strSparesStatus = "[SparesStatus] like '*'"
Else
    strSparesStatus = "[SparesStatus] = '" & Me.cboSparesStatus & "'"
End If

If IsNull(Me.cboPartDes) Then
    strDescription = "[Description] like '*'"
Else
    strDescription = "[Description] = '" & Me.cboPartDes & "'"
End If
  



'Concatenating the data   

 strCriteria = EnquiryNo & "And" & strSparesStatus & "And" &  trDescription



'Feeding the string to my query   
  task = "select * from qrySparesDetailSearch where " & strCriteria


'Feeding the task to my subform

    Me.frmSubSparesSearch.Form.RecordSource = task
'Updating my subform

    Me.frmSubSparesSearch.Form.Requery

End Function

I would appreciate any pointers and input.
Thanks,
Hooch

pbaldy · Dec 12, 2018

I would simply exclude those fields from the criteria rather than using "Like *". Should you want to stay with this, try

EnquiryNo = "[EnquiryNo] like '*' OR [EnquiryNo] Is Null"

plog · Dec 12, 2018

Code:

 strCriteria = EnquiryNo & "And" & strSparesStatus & "And" &  trDescription

Since you are adding ORs to the mix you will need to add appropriate parenthesis as well:

Code:

 strCriteria = "(" & EnquiryNo & ") And (" & strSparesStatus & ") And (" &  trDescription & ")"

Minty · Dec 12, 2018

Just as a great learning pointer - have a read of what is considered the de facto search form sample here at Allen Browne's excellent site - http://allenbrowne.com/ser-62.html

pbaldy · Dec 12, 2018

Good point! I'd still just eliminate those fields, which is my excuse.

hoochiepoos · Dec 13, 2018

Wow guys thank you . I will look at it today and see how it goes.

I know I open myself to noob bashing but I have the blank fields in my table because until an enquiry turns into an order there is no value in the order reference field. Or nine times out of ten the customer orders a part number with no description or vice versa. Most of these need to be searched for but…… you have given me something to think about.

Thank you for the pointers.

hoochiepoos · Dec 13, 2018

Great. One step closer to world domination!.

Thanks all.

Hooch

hoochiepoos · Jan 3, 2019

Here is my code behind my search form with subform.
Sorry I haven't noted properly. Works nicely for my little database but we will see if anyone can break it or improve on it:

Code:

Function SearchCriteria()
' Set string variables

Dim EnquiryNo, strSparesStatus, strDescription, strEnquiryFrom As String
Dim strCriteria As String

' combo box with integer
If IsNull(Me.cboEnquiryNo) Then
    EnquiryNo = "[EnquiryNo] like '*' or [EnquiryNo] is null"
Else
    EnquiryNo = "[EnquiryNo] = " & Me.cboEnquiryNo
End If



'combo box with text    
If IsNull(Me.cboSparesStatus) Then
    strSparesStatus = "[SparesStatus] like '*' or [SparesStatus] is null"
Else
    strSparesStatus = "[SparesStatus] = '" & Me.cboSparesStatus & "'"
End If

If IsNull(Me.cboPartDes) Then
    strDescription = "[Description] like '*'  or [Description] is null"
Else
    strDescription = "[Description] = '" & Me.cboPartDes & "'"
End If


'text box with date from
If IsNull(Me.txtEnquiryDateFrom) Then
    strEnquiryFrom = "[EnquiryDate] like '*' or [EnquiryDate] is null"
Else
       strEnquiryFrom = "[EnquiryDate] >= #" & Me.txtEnquiryDateFrom & "#"
End If
   
 ' concatenate a string
strCriteria = "(" & EnquiryNo & ") And (" & strSparesStatus & ") And (" & strDescription & ") And (" & strEnquiryFrom & ")"


'querying with string of variables

    task = "select * from qrySparesDetailSearch where " & strCriteria


'Subform

    Me.frmSubSparesSearch.Form.RecordSource = task
    Me.frmSubSparesSearch.Form.Requery

End Function

The_Doc_Man · Jan 3, 2019

I know I open myself to noob bashing

Most of the persons on this site are mature enough to not engage in bashing in a technical forum, though in the non-techie threads you might find some more ... shall we say "loose" standards of comment.

pbaldy · Jan 3, 2019

I would only include fields that had values, and use a single variable, like:

Code:

' combo box with integer
If Not IsNull(Me.cboEnquiryNo) Then
    strSQL = strSQL  & "[EnquiryNo] = " & Me.cboEnquiryNo & " AND "
End If

note the trailing AND, which you trim off at the end.

pbaldy · Jan 3, 2019

pbaldy said:
note the trailing AND, which you trim off at the end.

I should have included that bit, after all the optional fields have been added:

Code:

    If Len(strSQL) > 0 Then
        strSQL= Left(strSQL, Len(strSQL) - 5)
    End If

hoochiepoos · Jan 4, 2019

Thank you for your help.
I will have to re-write my code and get my head around it.
I had a look at allenbrowne.com and they suggest the same approach but I had put work into my search and didn't want to have to re-think. As they say if a jobs worth doing it is worth doing Twice!

I hit problems with the FROM date search as well. It seems to give some weird results that I cannot see a pattern. I guess it has something to do with date format but I can't be sure.

Thanks again

pbaldy · Jan 4, 2019

I don't think Like can be used reliably with number or date fields (which are stored as numbers).

plog · Jan 4, 2019

I hit problems with the FROM date search as well. It seems to give some weird results that I cannot see a pattern. I guess it has something to do with date format but I can't be sure.

Ultimately you are building an SQL string. So instead of firing it up and looking at the output to indirectly discern if its working, look directly at the string you are creating:

Debug.Print strSQL

That will shoot out whatever is in strSQL so you can verify it with your own eyes. Better yet, you can copy it and paste it into a new query object and see if that query runs and debug it from there.

pbaldy · Jan 4, 2019

A visual of what plog is suggesting:

http://www.baldyweb.com/ImmediateWindow.htm

isladogs · Jan 4, 2019

hoochiepoos said:
I hit problems with the FROM date search as well. It seems to give some weird results that I cannot see a pattern. I guess it has something to do with date format but I can't be sure.

Is your date format dd/mm/yyyy? If so, you will get errors for dates up to 12th of each month.
To fix, you need to format as mm/dd/yyyy for use in SQL statements

hoochiepoos · Jan 10, 2019

I thought I would post the code I compiled for my search . Thanks to all who helped.
I can now see the SQL string to debug. Wonderful.

Code:

Function SearchCriteria()

Dim strSQL As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yy\#"

If Not IsNull(Me.cboEnquiryNo) Then
    strSQL = strSQL & "[EnquiryNo] = " & Me.cboEnquiryNo & " AND "
End If

If Not IsNull(Me.cboSparesStatus) Then
    strSQL = strSQL & "([SparesStatus] = """ & Me.cboSparesStatus & """) AND "
End If

If Not IsNull(Me.txtPartDes) Then
    strSQL = strSQL & "([Description] Like ""*" & Me.txtPartDes & "*"") AND "
End If

If Not IsNull(Me.txtEnquiryDateFrom) Then
    strSQL = strSQL & "([EnquiryDate] >= " & Format(Me.txtEnquiryDateFrom, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEnquiryDateTo) Then
    strSQL = strSQL & "([EnquiryDate] < " & Format(Me.txtEnquiryDateTo, conJetDate) & ") AND "
End If



lngLen = Len(strSQL) - 5
If lngLen <= 0 Then
    MsgBox "No Criteria", vbInformation, "Nothing To Do."
Else
    strSQL = Left$(strSQL, lngLen)
         
    task = "select * from qrySparesDetailSearch where " & strSQL
    Debug.Print task
    Me.frmSubSparesSearch.Form.RecordSource = task
    Me.frmSubSparesSearch.Form.Requery
    
End If
    
End Function

Great Search Functionality... Shame I seem to be missing something (1 Viewer)

hoochiepoos

New member

pbaldy

Wino Moderator

plog

Banishment Pending

Minty

AWF VIP

pbaldy

Wino Moderator

hoochiepoos

New member

hoochiepoos

New member

hoochiepoos

New member

The_Doc_Man

Immoderate Moderator

pbaldy

Wino Moderator

pbaldy

Wino Moderator

hoochiepoos

New member

pbaldy

Wino Moderator

plog

Banishment Pending

pbaldy

Wino Moderator

isladogs

MVP / VIP

hoochiepoos

New member

Similar threads

Users who are viewing this thread