Search Filter Question

[keithsimpson3973]

Hi,
I have an Access 2003 database I am running with vb 6. I have one report that I want to filter on the Start_Date and Stop_Date in a scheduling database. I would like to enter a stop and stop date, then have it look for anything that the start date or stop date range falls into the start and stop date I specify. I know how to do it if searching only the start_date or only the stop_date,but can't figure out how to make it look at both.

I know that if I put

Between [Start Date] and [Stop Date]

in the criteria field on the Start_Date in the querry it will prompt me for a Start Date and a Stop Date and will search the Start_Date field. But I want it to search the Stop_Date field in the database at the same time as well and return anything matching the dates I specify.

[keithsimpson3973]

Also, can you rotate a label on an Access report?

[MMcCarthy]

WHERE Start_Date >= [Start Date]
AND Stop_Date <= [Stop Date]
 

Try this ...

Mary

[keithsimpson3973]

Thanks Mary. Below is what I wound up with that works. Except now when the access report opens, even though I pass the arguement "vbMaximized", the access report still doesn't open maximized.

Dim LDT1 As Date
Dim LTD2 As Date
 
LDT1 = frmGroundOperationsSelectReport.DTPicker1.Value
LDT2 = frmGroundOperationsSelectReport.DTPicker2.Value
 
If CStr(LDT1) = "" Or CStr(LDT2) = "" Then
    Exit Sub
End If
 
Set appaccess = CreateObject("Access.Application")
 
dbstr = "C:\Scheduling\AAGTC_Scheduling.mdb"
 
appaccess.OpenCurrentDatabase dbstr
 
appaccess.DoCmd.OpenReport "DailyGroundScheduleReport", acViewPreview, vbMaximized, wherecondition:="[Date_In] Between" & _
 " " & "#" & LDT1 & "#" & " " & "And" & " " & "#" & LDT2 & "#" & " " & "Or" & _
 " " & "[Date_Out] Between" & " " & "#" & LDT1 & "#" & " " & "And" & " " & "#" & LDT2 & "#"
 
appaccess.Visible = True

[MMcCarthy]

This parameter is for passing a filter as follows:

DoCmd.OpenReport "ReportName", acViewPreview, Filter, LinkCriteria
 

You need to put the following command in the on open event of the report instead.

DoCmd.Maximize
 

Mary

[NeoPa]

Try this less involved version :

Dim LDT1 As Date
Dim LTD2 As Date
Dim strWhere As String
Dim strDate1 As String
Dim strDate2 As String
 
LDT1 = frmGroundOperationsSelectReport.DTPicker1.Value
LDT2 = frmGroundOperationsSelectReport.DTPicker2.Value
 
If CStr(LDT1) = "" Or CStr(LDT2) = "" Then Exit Sub
 
Set appaccess = CreateObject("Access.Application")
 
dbstr = "C:\Scheduling\AAGTC_Scheduling.mdb"
 
appaccess.OpenCurrentDatabase dbstr
 
strDate1 = Format(LDT1, "\#m/d/yyyy\#")
strDate2 = Format(LDT2, "\#m/d/yyyy\#")
strWhere = "(([Date_In] <= " & strDate2 & ") And (" & [Date_Out] >= " & strDate1 & "))"
 
appaccess.DoCmd.OpenReport "DailyGroundScheduleReport", acViewPreview, vbMaximized, strWhere
 
appaccess.Visible = True

In VBA (I'm assuming in VB too) you can't use named parameters unless all are named.
ALL date literals in SQL MUST be in d/m/y format (which isn't the default in all locations).
The logic is that one StartDate must be less than or equal to the other EndDate AS WELL AS the EndDate (of the first StartDate) must be greater than or equal to the other StartDate.