Error 2107 The value you entered doesn't meet the validation rule defined for the field or control

I am using the following code in a search form to create an sql to use as the recordsource for the form. It works fine except for when a Date To is selected.

Public Sub SearchForm()
	'Main body of the query
    strSQL = "SELECT Distinct tblDocs.* FROM tblDocs INNER JOIN tblDocumentContents ON tblDocs.fldDocID=tblDocumentContents.fldDocID"
    
	'Create Where conditions based on the selected search controls
    strWhere = " WHERE tblDocs.fldDocID In (Select fldDocID From tblDocs "
    strWhere = strWhere & "WHERE fldDocNameID Like " & Nz(Me.cboSearchDocuments, " '*' ") & ") "
    
    If Not IsNull(Me.cboSearchPeople) Then
    
        strWhere = strWhere & "AND tblDocs.fldDocID "
        strWhere = strWhere & "In (Select fldDocID From tblDocumentContents WHERE fldEntityID Like " & Me.cboSearchPeople & ")"
  
    End If
    
   If Not IsNull(Me.cboSearchPlaces) Then
      
    strWhere = strWhere & " AND tblDocs.fldDocID "
    strWhere = strWhere & "In (Select fldDocID From tblDocumentContents WHERE fldPlaceID Like " & Me.cboSearchPlaces & ")"
   
   End If
   
   If Not IsNull(Me.cboSearchTopics) Then
      
    strWhere = strWhere & " AND tblDocs.fldDocID "
    strWhere = strWhere & "In (Select fldDocID From tblDocumentContents WHERE fldTopicID Like " & Me.cboSearchTopics & ")"
   
   End If
   
   If Not IsNull(Me.txtDateFrom) Then
   
   strWhere = strWhere & " AND tblDocs.fldDocID "
    strWhere = strWhere & "In (Select fldDocID From tblDocs WHERE fldDate >= #" & Me.txtDateFrom & "#)"
   
   End If
   
   If Not IsNull(Me.txtDateTo) Then
   
   strWhere = strWhere & " AND tblDocs.fldDocID "
    strWhere = strWhere & "In (Select fldDocID From tblDocs WHERE fldDate <= #" & Me.txtDateTo & "#)"
   
   End If
   
   'Complete the SQL
   strSQL = strSQL & " " & strWhere
    
	'Assign strSQL to the form recordsource.
    Me.RecordSource = strSQL
    
	'Refresh form if there is no records 
    If Me.RecordsetClone.RecordCount = 0 Then
    
    MsgBox "This search produce 0 records"

    Call cmdRefresh_Click
    
    End If
End Sub

Open in new window

It seems that the problem is with the codes between line 37 and 42. But the codes in these lines are almost identical to the codes in line 30-35 which works fine.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 120

Rey Obrero (Capricorn1)Commented: 2014-03-01

to see the SQL statement that was created,

'Complete the SQL
strSQL = strSQL & " " & strWhere

'Add this line

Debug.print strSQL

look at what was printed in the Immediate window and you will see that the SQL statement is not correct, when you have both the "txtDateFrom" and "txtDateTo" (are not null) filled out.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 16

SheilsAuthor Commented: 2014-03-01

I have already tried this and even pasted the sql in a query. The query run no problem but still can't open the form. The debug print result is as follows:

SELECT Distinct tblDocs.* FROM
tblDocs INNER JOIN tblDocumentContents ON tblDocs.fldDocID=tblDocumentContents.fldDocID WHERE
tblDocs.fldDocID In (Select fldDocID From tblDocs WHERE fldDocNameID Like '*' ) AND
tblDocs.fldDocID In (Select fldDocID From tblDocs WHERE fldDate <= #02/03/2014#)

OK I think that have seen the problem. I was using the before update event instead of after update by mistake.

Error 2107 The value you entered doesn't meet the validation rule defined for the field or control

Who is Participating?