ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Error 2107 The value you entered doesn't meet the validation rule defined for the field or control
Posted on 2014-03-01
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.
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.
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
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.
[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
Active 3 days ago
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.
'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.
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=tblDocume
tblDocs.fldDocID In (Select fldDocID From tblDocs WHERE fldDocNameID Like '*' ) AND
tblDocs.fldDocID In (Select fldDocID From tblDocs WHERE fldDate <= #02/03/2014#)
SELECT Distinct tblDocs.* FROM
tblDocs INNER JOIN tblDocumentContents ON tblDocs.fldDocID=tblDocume
tblDocs.fldDocID In (Select fldDocID From tblDocs WHERE fldDocNameID Like '*' ) AND
tblDocs.fldDocID In (Select fldDocID From tblDocs WHERE fldDate <= #02/03/2014#)
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening. This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 18 hours left to enroll
626 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.