Need Help with Multiple Search in a Form...

[MaTREKZ]

Hello,

I have a form that I'm currently using as a search screen, and I'm having trouble passing a range to one field in a query.

I want to call a date range, let's say between date1 and date2 and pass that value to my [Date] field in the query, however I cannot figure out how to do this... I already have two date boxes in the form.

Below is an example of another value I'm sending to the query through VB. It is then queried through a button in the form...

' Check for LIKE Client Name
    If Me.Text2 > "" Then
        varWhere = varWhere & "[Client Name] LIKE """ & Me.Text2 & "*"" AND "
    End If

[beacon]

Since it sounds like you've got two date fields in your query, and you've got two textboxes on your form to accept the dates, you've actually got the majority of the work done. Now it's just a matter of telling the query that it needs to look for the dates on the textboxes on the form.

I'm going to make a couple of assumptions about your query/form setup, so you'll obviously have to swap the names out with the actual names you are using.

On your query, let's say your date field in the query is called MyDate, and on your form, which we'll call YourForm for lack of a better description, you're two text boxes are called txtStartDate and txtEndDate. I don't know if [Date] in your post was the name of an actual field, but if it is you need to rename it. Access uses Date as a keyword, so using it for anything else will cause a bunch of problems down the road, if they haven't already.

Ok, go to your query...in the criteria for MyDate, enter the following code:

Between [Forms]![YourForm]![txtStartDate] And [Forms]![YourForm]![txtEndDate]

The preceding code tells your query that when it runs it needs to go to YourForm and grab the values in txtStartDate and txtEndDate.

Ok, now let's go to your form. It sounds like you've already got a button to call/open the query, so that should be good to go. The only other thing I would suggest is that you may want to add similar conditional testing to the date controls on your form to ensure that the user enters a date prior to running the query so it doesn't return unexpected values.

Hope this helps,
beacon

[MaTREKZ]

Thank you for the response. This would work, however it's a bit more complex. I have other ranges than I would like to call within that query and I can't because then I get a "Complex error". Is there a way to do this through the code?