Solved

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

Posted on 2014-03-01
3
529 Views
Last Modified: 2014-04-05
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.
0
Comment
Question by:Sheils
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39898155
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.
0
 
LVL 16

Author Comment

by:Sheils
ID: 39898165
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#)
0
 
LVL 16

Author Comment

by:Sheils
ID: 39898199
OK I think that have seen the problem. I was using the before update event instead of after update by mistake.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question