UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multi Search Form - Missing Something, Access 2016    
 
   
Brinehart
post Sep 9 2016, 02:10 AM
Post#1



Posts: 7
Joined: 9-September 16



I have a multi Field Search Form. With a list box displaying the results. You select the fields you want to search by and when you hit the search button it runs the RequerySearchListbox Function I am having problems with the checkbox fields when they equal true,false, or not checked and how to get that code to work properly. I have racked my brain trying to get it to work I feel I am close, but something is causing it to not work. I did notice I am getting a "and" where I shouldn't in my SQL statement.


CODE
Private Sub RequerySearchListbox()

    Dim WhereStr As String
    Dim SQLStr As String

    WhereStr = ""
    SQLStr = ""
  

    ' WHERE CONDITIONS
    If JobNo <> "" And Not IsNull(JobNo) Then
        WhereStr = "JobNo LIKE '*" & JobNo & "*'"
      
    End If
    
    If JobName <> "" And Not IsNull(JobName) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
            WhereStr = WhereStr & "JobName LIKE '*" & JobName & "*'"
    End If
    
    If CName <> "" And Not IsNull(CName) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
            WhereStr = WhereStr & "CName LIKE '*" & CName & "*'"
    End If
    
    If CFN <> "" And Not IsNull(CFN) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
            WhereStr = WhereStr & "CFN LIKE '*" & CFN & "*'"
    End If
    
    If Active <> "" And Not IsNull(Active) Then
    
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
            WhereStr = WhereStr & "Active = True"
        Else
            WhereStr = WhereStr & " And " & "Active = False"
        
    End If

    If CertPayroll <> "" And Not IsNull(CertPayroll) Then
    
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
            WhereStr = WhereStr & "CertPayroll = True"
        
        Else
            WhereStr = WhereStr & " And " & "CertPayroll = False"

    End If



    SQLStr = "SELECT JobID, JobNo, JobName, CName, CFN, Active, CertPayroll FROM JobWCoordandCompanyQ "
    If WhereStr <> "" Then SQLStr = SQLStr & "WHERE " & WhereStr & " ORDER BY " & Forms!SearchF!SortBy
    

    SearchListBox.RowSource = SQLStr
    SQLBox = SQLStr

End Sub

Attached File(s)
Attached File  Search.zip ( 91.8K )Number of downloads: 12
 
Go to the top of the page
 
chrismbaylis
post Sep 9 2016, 05:10 AM
Post#2



Posts: 75
Joined: 25-March 10



Hi There,

It seems as though the 'CertPayroll' validation is adding in the extra 'AND':

CODE
    If CertPayroll <> "" And Not IsNull(CertPayroll) Then
    
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
            WhereStr = WhereStr & "CertPayroll = True"
        
        Else
            WhereStr = WhereStr & " And " & "CertPayroll = False"

    End If


I tested this by only entering '601' in the project name and stepping through the code - if you add a watch to the WhereStr, you'll see the 'AND' pop up at this point.

Hope that helps,

Chris
Go to the top of the page
 
moke123
post Sep 9 2016, 05:21 AM
Post#3



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



why the triple state checkbox?
take a look at DatAdrenelines post here
specifically
QUOTE
A yes/no datatype field will not accept a Null, so Ken is correct in stating that a Yes/No is the wrong datatype to use if you have 3 options ... I often will use a Byte field and store the appriate option value that was selected.
espite the illusion that a Yes/No will accept a Null, A NULL will be converted to FALSE/NO when saved. MS Access will only show a Null (Greyed) on a NEW record, if a default value has not been defined, but once the record is committed (saved) the Null will be converted as indicated.


you could also shorten some code
CODE
If CertPayroll <> "" And Not IsNull(CertPayroll) Then

by testing for both conditions at once
CODE
If Nz(CertPayroll,"") <> ""  Then
Go to the top of the page
 
Brinehart
post Sep 10 2016, 08:59 PM
Post#4



Posts: 7
Joined: 9-September 16



I have switched the yes/no fields to Combo Boxes where the user would select Yes (1), No (2), Both (3) I am not sure how to code this this piece of code I have been using for years which I had someone help me with and I never fully understood what it was all doing. If someone wouldn't mind spelling out in english what each part of the code is doing I might be able to figure out how to fix it.

I also have imported my data from another database and I have notice any new records I add to the JobT table does not show up in my search. I can hand key in a job number and go back and search for it and it would not come up. That is another problem that doesnt make any sense to me.

Any help would be greatly appreciated
Go to the top of the page
 
Brinehart
post Sep 10 2016, 09:02 PM
Post#5



Posts: 7
Joined: 9-September 16



This is the updated database so far, but I have rem out the active and CertPayroll fields until I can figure how to code them.Attached File  Search_Revised.zip ( 121.28K )Number of downloads: 13
Go to the top of the page
 
moke123
post Sep 11 2016, 06:11 AM
Post#6



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



I simplified your code a little, post back if theres anything you dont understand.

Attached File  Updtd_Search_Revised.zip ( 99.78K )Number of downloads: 29


QUOTE
I have rem out the active and CertPayroll fields until I can figure how to code them

I added them back in. Basically, if they were "Yes "or "no" I add them to the wherestr and if "Both" i omit them from the wherestr.
Go to the top of the page
 
Brinehart
post Sep 12 2016, 12:07 AM
Post#7



Posts: 7
Joined: 9-September 16



moke123 thank you for taking the time to help me I appreciate all your help, test all scenario such as yes / yes , yes/no , no/no etc they all seem to work except one when I make Active = both and certified Payroll = both it crashes and says runtime error '5' invalid procedure call., I have know Idea what would cause that do you?
Go to the top of the page
 
moke123
post Sep 12 2016, 05:28 AM
Post#8



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



if those are both set to both, the wherestr returns nothing so you cant remove the last 4 characters of nothing.

try changing

CODE
        WhereStr = Left(WhereStr, Len(WhereStr) - 4)  ' Remove the extra " And " at end of string


to

CODE
     If Nz(WhereStr, "") <> "" Then
        WhereStr = Left(WhereStr, Len(WhereStr) - 4)  ' Remove the extra " And " at end of string
    End If
Go to the top of the page
 
Brinehart
post Sep 12 2016, 09:11 AM
Post#9



Posts: 7
Joined: 9-September 16



that worked thank you so much I have lots to learn.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 02:08 PM