By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,401 Members | 928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,401 IT Pros & Developers. It's quick & easy.

Search Filter Question

P: 63
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

Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 6 '06 #1
Share this Question
Share on Google+
5 Replies


P: 63
Also, can you rotate a label on an Access report?
Dec 6 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. WHERE Start_Date >= [Start Date]
  2. AND Stop_Date <= [Stop Date]
  3.  
Try this ...

Mary
Dec 6 '06 #3

P: 63
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.

Expand|Select|Wrap|Line Numbers
  1. Dim LDT1 As Date
  2. Dim LTD2 As Date
  3.  
  4. LDT1 = frmGroundOperationsSelectReport.DTPicker1.Value
  5. LDT2 = frmGroundOperationsSelectReport.DTPicker2.Value
  6.  
  7. If CStr(LDT1) = "" Or CStr(LDT2) = "" Then
  8.     Exit Sub
  9. End If
  10.  
  11. Set appaccess = CreateObject("Access.Application")
  12.  
  13. dbstr = "C:\Scheduling\AAGTC_Scheduling.mdb"
  14.  
  15. appaccess.OpenCurrentDatabase dbstr
  16.  
  17. appaccess.DoCmd.OpenReport "DailyGroundScheduleReport", acViewPreview, vbMaximized, wherecondition:="[Date_In] Between" & _
  18.  " " & "#" & LDT1 & "#" & " " & "And" & " " & "#" & LDT2 & "#" & " " & "Or" & _
  19.  " " & "[Date_Out] Between" & " " & "#" & LDT1 & "#" & " " & "And" & " " & "#" & LDT2 & "#"
  20.  
  21. appaccess.Visible = True
Dec 6 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
This parameter is for passing a filter as follows:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "ReportName", acViewPreview, Filter, LinkCriteria
  2.  
You need to put the following command in the on open event of the report instead.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Maximize
  2.  
Mary
Dec 6 '06 #5

NeoPa
Expert Mod 15k+
P: 31,661
Try this less involved version :
Expand|Select|Wrap|Line Numbers
  1. Dim LDT1 As Date
  2. Dim LTD2 As Date
  3. Dim strWhere As String
  4. Dim strDate1 As String
  5. Dim strDate2 As String
  6.  
  7. LDT1 = frmGroundOperationsSelectReport.DTPicker1.Value
  8. LDT2 = frmGroundOperationsSelectReport.DTPicker2.Value
  9.  
  10. If CStr(LDT1) = "" Or CStr(LDT2) = "" Then Exit Sub
  11.  
  12. Set appaccess = CreateObject("Access.Application")
  13.  
  14. dbstr = "C:\Scheduling\AAGTC_Scheduling.mdb"
  15.  
  16. appaccess.OpenCurrentDatabase dbstr
  17.  
  18. strDate1 = Format(LDT1, "\#m/d/yyyy\#")
  19. strDate2 = Format(LDT2, "\#m/d/yyyy\#")
  20. strWhere = "(([Date_In] <= " & strDate2 & ") And (" & [Date_Out] >= " & strDate1 & "))"
  21.  
  22. appaccess.DoCmd.OpenReport "DailyGroundScheduleReport", acViewPreview, vbMaximized, strWhere
  23.  
  24. 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.
Dec 7 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.