Trouble getting textbox search and datefield search to work together 1

[BRoy]

I am attempting to create a form that allows a user to open a flexible report by offering several search criteria options in the form of checkboxes with corresponding textboxes. I am using 4 checkbox/textbox options and 1 checkbox/datefield option (i'm using a between date expression). I am getting an error when any of the textbox options are selected and the datefield option is not selected - the syntax error states there is an error in date in my query expression:

"Syntax error in date in query expression '(Client like '*honda*' AND Assignee like '**' AND Inventor like '**' AND Title like '**' AND date_created Between ## AND ##'."

I got the code to start my cmd buttom from Tek-Tips, so I thought I could possibly get some help for what I am doing wrong. Am I missing a quote or apostrophe somewhere with my "strWhere=" expression? Here is my code:

Dim strWhere As String 'String variable to store where clause

If (Me.chkClient = True And Len(Me.txtClient & "") > 0) Or (Me.chkAssignee = True And Len(Me.txtAssignee & "") > 0) Or (Me.chkInventor = True And Len(Me.txtInventor & "") > 0) Or (Me.chkTitle = True And Len(Me.txtTitle & "") > 0) Or (Me.chkCreatedDate = True And (Len(Me.txtCreatedDate1 & "") > 0 And Len(Me.txtCreatedDate2 & "") > 0)) Then
        'search criteria is valid: checkbox selected and corresponding textbox value is present for either of the search options on the form.
        strWhere = strWhere & " AND Client like '" & "*" & Me.txtClient & "*" & "' AND Assignee like '" & "*" & Me.txtAssignee & "*" & "'AND Inventor like '" & "*" & Me.txtInventor & "*" & "' AND Title like '" & "*" & Me.txtTitle & "*" & "'" & " AND date_created Between #" & Me.txtCreatedDate1 & "# AND #" & Me.txtCreatedDate2 & "#"
    End If
        
    If Len(strWhere & "") = 0 Then
    ' checkbox not selected and/or corresponding textbox is not valid . Return message box informing of "Invalid Search Criteria".
        MsgBox "Please confirm search criteria.", vbInformation, "Invalid Search Criteria"
    Else
        ' remove first "AND" from where condition and pass across to report
        DoCmd.OpenReport "Report2", acViewPreview, WhereCondition:=Mid(strWhere, 6)
    End If

 

[PHV]

Replace this:
& " AND date_created Between #" & Me.txtCreatedDate1 & "# AND #" & Me.txtCreatedDate2 & "#"
with this:
& IIf(Me.chkCreatedDate = True And Len(Me.txtCreatedDate1 & "") > 0 And Len(Me.txtCreatedDate2 & "") > 0," AND date_created Between #" & Me.txtCreatedDate1 & "# AND #" & Me.txtCreatedDate2 & "#", "")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BRoy]

Thank you PH, your suggested solution worked like a charm! Can you let me know what exactly the IIF statement is accomplishing? I am a relative new comer to VBA, so please excuse me for my lack of knowledge.

Thank's again for your help...I greatly appreciate it!

BRoy

 

[PHV]

what exactly the IIF statement is accomplishing?
Place the cursor inside the IIf word in your code and then press the F1 key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

IIF is NOT a VBA function. It is an MS Access function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Sorry Skip, but I disagree as IIf is a member of the VBA.Interaction class.

 

[SkipVought]

Huh! So it is, and I never knew that!

Thanks!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}