Form Search Question

[Alexvh]

Hi,

Having searched the forums for an answer to my question, I found the link http://www.datapigtechnologies.com/f...earchform.html which helped out a lot.

The problem I'm having at the moment is that I have a field called 'Description', which obviously contains a lot more then just 1 word. The end result of the tutorial provides me with a button that opens up a table in which the search result is displayed. However, it doesn't perform a free text search (something which was easily done using a search macro, but it seems macro's aren't the best way to perform searches). So when searching, you would only get a result if you type in exactly what's in the field. Is there any way to modify this example to get it to run a free text search?

Additionally, is it possible to get the result displayed in a form, or at least *something* that allows you to click on the result to get the complete record?

Thanks in advance!

 

[John Big Booty]

Is this Cool Search Tool the sort of thing you are after

 

[Yecats131]

I hate to tag onto your original post but I followed the video that you referenced earlier and I seem to be stuck. I've setup my text boxes so that they default to null values and written the following statement for my Run Query Button:

Private Sub btnRunQuery_Click()

DoCmd.OpenQuery "SearchQuery", acViewNormal

End Sub

I have several search boxes, but the only one that seems to "work" is my Submital date range. I've attached a screenshot of my form.

I put a quote around the word work because when I have a date range entered, it only pulls up 4 records - There is about 100 or so that meet my range. WHen i do not have a date in, but I fill in any of the other text boxes I get the following error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

Here is my SQL text for my query:

SELECT Requisitions.[Requisition #], Requisitions.[Requisition Type], Requisitions.[New Hire DeptID], Requisitions.Dated, Requisitions.[Job Title], Requisitions.Status, Requisitions.[Status Date], Requisitions.[Type of Hire], Requisitions.Manager, Requisitions.Candidate, Requisitions.[Replacing Employee], Requisitions.Director, Requisitions.Project, Requisitions.[Work Location], Requisitions.PayRange1, Requisitions.PayRange2, Requisitions.[Job Title]
FROM Requisitions
WHERE (((Requisitions.[Requisition #]) Like [forms].[SearchRequisitions].[SearchReqNum] & "*") AND ((Requisitions.[New Hire DeptID]) Like [forms].[SearchRequisitions].[SearchDeptID] & "*") AND ((Requisitions.Dated) Between [forms].[SearchRequisitions].[SearchSubmitStartDate] And [forms].[SearchRequisitions].[SearchSubmitEndDate]) AND ((Requisitions.[Type of Hire]) Like [forms].[SearchRequisitions].[SearchHireType] & "*") AND ((Requisitions.Manager) Like [forms].[SearchRequisitions].[SearchManager] & "*") AND ((Requisitions.Candidate) Like [forms].[SearchRequisitions].[SearchCandidate] & "*") AND ((Requisitions.[Replacing Employee]) Like [forms].[SearchRequisitions].[SearchReplacedEE] & "*") AND ((Requisitions.Director) Like [forms].[SearchRequisitions].[SearchDirector] & "*") AND ((Requisitions.Project) Like [forms].[SearchRequisitions].[SearchProject] & "*") AND ((Requisitions.[Work Location]) Like [forms].[SearchRequisitions].[SearchWorkLocation] & "*") AND ((Requisitions.[`Job Title]) Like [forms].[SearchRequisitions].[SearchPosition] & "*"));


Any ideas?

Thanks!

 

[DCrake]

Here is an acticle on how to pass parameters from forms to queries without making them form dependant.

 

[Yecats131]

I've hit a road block in doing it the way that the document says. I've written the following code:

Option Compare Database

Public StartDate As Date
Public EndDate As Date

Public Function SearchStartDate() As Date
SearchStartDate = StartDate
End Function

Public Function SearchEndDate() As Date
SearchEndDate = EndDate
End Function

Private Sub btnEndEffectiveDate_AfterUpdate()
EndDate = Me.ActiveControl
End Sub
Private Sub btnStartEffectiveDate_AfterUpdate()
StartDate = Me.ActiveControl

End Sub

WHen I run my query I get an error saying "Undefined Function" - Any ideas?

 

[DCrake]

What you need in your code is the following

StartDate = CDate(Me.ActiveControl)

Then in your Query enter

Between SearchStartDate() And SearchEndDate() in you condition row underneath your date column.