Search Records Between two dates HELP!!!! (1 Viewer)

[Casanova411]

I am currently trying to complete a Search Form for a table of mine and i baffled by what Access does for me. I want to Search for dates between Start Date and End Date when there is input and List all dates when blank. This to me is the correct code
Like "*" & (Between [Forms]![SearchRecords]![SearchADate1] And [Forms]![SearchRecords]![SearchADate2]) & "*" , which goes in the Criteria Row in the Query.

When i close the query Access replaces my code with this

Like "*" & (([EquipmentAssignments].[Assigned]) Between [Forms]![SearchRecords]![SearchADate1] And [Forms]![SearchRecords]![SearchADate2]) & "*"

, which returns the wrong data regardless of output. No matter how many times i erase the code Access replaces mine with it wont budge. Can you guys tell me what is going wrong? and provide a solution.

Thanks ahead!

 

[G37Sam]

Weird, can you post your database or a sample of it where this error is happening?

 

[bob fitz]

I think the WHERE clause of your query's SQL statement needs to be something like:

WHERE ((([B][COLOR=red]Table1[/COLOR][/B].visitdate)<=[Forms]![SearchRecords]![SearchADate1]) AND (([B][COLOR=#ff0000]Table1[/COLOR][/B].nextapp)>=[Forms]![SearchRecords]![SearchADate2])) OR (([Forms]![SearchRecords]![SearchADate1] Is Null) AND ([Forms]![SearchRecords]![SearchADate2] Is Null));

Substitute Table1 with the name of your table.

 

[Brianwarnock]

Bob is correct in his approach, but I would add that you can replace the >=. <=construct with Between .. And.. If you prefer. This is one of the situations where it is easier to code in SQL than the design grid.

Brian

 

[bob fitz]

Bob is correct in his approach, but I would add that you can replace the >=. <=construct with Between .. And.. If you prefer. This is one of the situations where it is easier to code in SQL than the design grid.

Do you see any advantages using Between...And..

 

[vbaInet]

I've seen this quite a few times and the construct fails when you leave one of the text boxes empty. This is what I would do:

Between Nz([Forms]![SearchRecords]![SearchADate1], [COLOR=Red]DMin()[/COLOR]) And Nz([Forms]![SearchRecords]![SearchADate2], [COLOR=Red]DMax()[/COLOR])

... where DMin() is the minimum date in your table and DMax() is the maximum date. You can add extra conditions to search for only a specific date if one of the textboxes is empty.

If you don't like the DMin() and DMax(), you can put in your own fictitious dates, like 1/1/1900 for min and 31/12/2050.

 

[bob fitz]

vbaInet
I don't understand where the "Between" code that you posted should be in the query's SQL statement.

 

[vbaInet]

Hi Bob,

It will go in the WHERE clause and the DMin() and DMax() functions need to completed.

 

[bob fitz]

Hi vbaInet,
I think I must be having a "senior" moment but I'm afraid I just don't get it.
Below is the full SQL statement I used as a quick test before I posted the WHERE clause earlier. Would you be good enough to change it so that it includes the "Between" syntax that you posted. I really would be most appreciative.

 

[vbaInet]

Sure... but you forgot to include the SQL.

 

[bob fitz]

Sure... but you forgot to include the SQL.

I'm so sorry. I seem to be having a bad day. Now I feel really stupid.

SELECT Table1.patientID, Table1.visitdate, Table1.nextapp
FROM Table1
WHERE (((Table1.visitdate)<=[Forms]![SearchRecords]![SearchADate1]) AND ((Table1.nextapp)>=[Forms]![SearchRecords]![SearchADate2])) OR (([Forms]![SearchRecords]![SearchADate1] Is Null) AND ([Forms]![SearchRecords]![SearchADate2] Is Null));

 

[vbaInet]

We all have our moments, and besides it's the weekend

One of the two:

SELECT patientID, visitdate, nextapp
FROM Table1
WHERE visitdate Between Nz([Forms]![SearchRecords]![SearchADate1], DMin("visitdate", "Table1")) And Nz([Forms]![SearchRecords]![SearchADate2], DMax("visitdate", "Table1"))


SELECT patientID, visitdate, nextapp
FROM Table1
WHERE visitdate Between Nz([Forms]![SearchRecords]![SearchADate1], #1/1/1900#) And Nz([Forms]![SearchRecords]![SearchADate2], #31/12/2050#)

 

[bob fitz]

Thank you. I shall take a look at them both tomorrow. Hopefully a better day.

 

[Casanova411]

This is a complex search query. It has four fields in which each criteria could be entered. So the code you put is correct but has to be include some other fields to. Here is the sequel view it includes what access puts into my code.


SELECT EquipmentAssignments.[Perm ID], EquipmentAssignments.[Equipment ID], EquipmentAssignments.Assigned, EquipmentAssignments.Returned
FROM EquipmentAssignments
WHERE (((EquipmentAssignments.[Perm ID]) Like "*" & [Forms]![SearchRecords]![SearchStudent] & "*") AND ((EquipmentAssignments.[Equipment ID]) Like "*" & [Forms]![SearchRecords]![SearchID] & "*") AND ((EquipmentAssignments.Assigned) Like "*" & (([EquipmentAssignments].[Assigned]) Between [Forms]![SearchRecords]![SearchADate1] And [Forms]![SearchRecords]![SearchADate2]) & "*") AND ((EquipmentAssignments.Returned) Like "*" & ((([EquipmentAssignments].[Returned]) Between [Forms]![SearchRecords]![SearchRDate1] And [Forms]![SearchRecords]![SearchRDate2])) & "*"));

 

[Casanova411]

Here is my database

SearchRecords and SearchCriteria are the two objects i am working on. The form is where the input is placed, whereas SearchRecords is the query which is replacing my code with additional that throws off my search. :banghead:

 

[boblarson]

The main problem is that you have the word LIKE in there for the date fields and that is NOT what you want to use. Just in the Grid, you currently have this:

but what you should have is this:

which yields the SQL of:

SELECT EquipmentAssignments.[Perm ID], EquipmentAssignments.[Equipment ID], EquipmentAssignments.Assigned, EquipmentAssignments.Returned
FROM EquipmentAssignments
WHERE (((EquipmentAssignments.[Perm ID]) Like "*" & [Forms]![SearchRecords]![SearchStudent] & "*") AND ((EquipmentAssignments.[Equipment ID]) Like "*" & [Forms]![SearchRecords]![SearchID] & "*") AND ((EquipmentAssignments.Assigned) Between [Forms]![SearchRecords]![SearchADate1] And [Forms]![SearchRecords]![SearchADate2]) AND ((EquipmentAssignments.Returned) Between [Forms]![SearchRecords]![SearchRDate1] And [Forms]![SearchRecords]![SearchRDate2]));

 

[Casanova411]

The problem with your solution is that when you run the query with blank boxes nothing is returned.

 

[vbaInet]

And you didn't even pay attention to what we wrote in posts #6 and #12.

Strip off all criteria, get the date part working first before you start adding other criteria one by one.

 

[boblarson]

The problem with your solution is that when you run the query with blank boxes nothing is returned.

No, the problem with the criteria is that the way it is currently written ALL of the criteria must match exactly. So, there is a lot of "fixing" that needs to be done.

Personally, I would not put the criteria in the query. I would apply the criteria as a filter on a form by using something like this:

Dim strWhere As String
 
If Len(Me.SearchDate1 & vbNullString) > 0 And Len(Me.SearchDate2 & vbNullString) > 0 Then
   strWhere = "([Assigned] Between #" & Me.SearchDate1 & "# And #" & Me.SearchDate2 & "# OR [Returned] Between #" & Me.SearchDate1 & "# And #" & Me.SearchDate2 & "#) AND "
End If
 
If Len(Me.SearchDate1 & vbNullString) > 0 And Len(Me.SearchDate2 & vbNullString) = 0 Then 
   strWhere = "([Assigned] >=#" & Me.SearchDate1 & "# OR [Returned] >=#" & Me.SearchDate1 & "#) AND "
End If
 
If Len(Me.SearchDate1 & vbNullString) = 0 And Len(Me.SearchDate2 & vbNullString) > 0 Then 
   strWhere = "([Assigned] <=#" & Me.SearchDate1 & "# OR [Returned] <=#" & Me.SearchDate1 & "#) AND "
End If
 
If Len(Me.SearchStudent & vbNullString) > 0 Then
   strWhere = strWhere & "[Perm ID] Like *" & Me.SearchStudent & "* AND "
End If
 
If Len(Me.SearchID & vbNullString) > 0 Then
   strWhere = strWhere & "[Equipment ID] Like '*" & Me.SearchID & "*'"
End If
 
If Right(strFilter, 5) = " AND " Then
   strWhere = Left(strWhere, Len(strWhere) - 5)
End If
 
DoCmd.OpenForm "FormNameHere", , strWhere

 

[Brianwarnock]

No, the problem with the criteria is that the way it is currently written ALL of the criteria must match exactly.

Erm! didn't you write that criteria Bob?

Casanova, Bob has now given you a solution, but as mentioned earlier by Vba you need both date controls on the Form to be Null.

If you prefer a design grid solution you might like to read this

link

Brian