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

Overlapping Date Periods

P: 5
I have an Access table listing cabin numbers, arrival dates, and depart dates. I need to select all cabins (and their dates) having either an arrival date or a depart date within a specified week, or having both dates outside the specified week. I need to open a report using these criteria.
Placing those criteria in a query design grid produces a message that they are too complicated. I tried a recordset approach but I am not adept in that area.

I would appreciate any help on this
Oct 31 '06 #1
Share this Question
Share on Google+
6 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi,

So you need to convert your arrivel and depart dates to weeks

Use the function datepart("ww", [ArrivalDate]) to obtain the week

the same for your depart date

put those functions in a separate column in your query and in the criteria part put the respectiv criteria about!

Nice luck!
:)
Oct 31 '06 #2

NeoPa
Expert Mod 15k+
P: 31,489
Alternatively, if you post the criteria you used I can check it over and make recommendations.
Oct 31 '06 #3

P: 5
Alternatively, if you post the criteria you used I can check it over and make recommendations.
Here is the code based on the PEB recommendation. It is a function because it is called from the switchboard. When it runs, all the variables are correct, but no data results where some should.
Public Function Occupancy()
Dim EnterDate As Date
Dim MyWeek As Integer
Dim MyYear As Integer
Dim MyCriteria As String

EnterDate = InputBox("Please enter a date.")
MyWeek = DatePart("ww", EnterDate)
MyYear = DatePart("yyyy", EnterDate)
MyCriteria = "([ArrWk] = ' " & MyWeek & " ' OR [DepartWk] = ' " & MyWeek & " ' " & _
"OR ([ArrWk]<' " & MyWeek & " ' and [DepartWk] > ' " & MyWeek & " ')) " & _
"AND ([ArrYear] = ' " & MyYear & " ')"

Dim MyRpt As String
MyRpt = "rptCabinOccupancy"

DoCmd.OpenReport MyRpt, acViewPreview, "qryWeekSum_01", MyCriteria

End Function

Thank you for your assitance
Oct 31 '06 #4

NeoPa
Expert Mod 15k+
P: 31,489
I'm thinking your criteria are still the problem here. I'll just go through the code of this, rather than the logic.
Your Code
Expand|Select|Wrap|Line Numbers
  1. MyCriteria = "([ArrWk] = ' " & MyWeek & " ' OR [DepartWk] = ' " & MyWeek & " ' " & _
  2. "OR ([ArrWk]<' " & MyWeek & " ' and [DepartWk] > ' " & MyWeek & " ')) " & _
  3. "AND ([ArrYear] = ' " & MyYear & " ')"
This has spaces inside the eventually resolved strings.
([ArrWk] = 'SPACEvalueSPACE'... which should never work. White space (spaces; tabs; new lines; etc) are fine in the SQL code except when they appear in strings.
Try :-
Expand|Select|Wrap|Line Numbers
  1. MyCriteria = "([ArrWk] = '" & MyWeek & "' OR [DepartWk] = '" & MyWeek & "' " & _
  2. "OR ([ArrWk]<'" & MyWeek & "' and [DepartWk] > '" & MyWeek & "')) " & _
  3. "AND ([ArrYear] = '" & MyYear & "')"
Oct 31 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Firstly, the user entering a date is entered as a string and has to be converted to a date.

Next MyWeek is an integer and doesn't require single quotes as they are only needed for strings.

Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function Occupancy()
  3. Dim EnterDate As Date
  4. Dim MyWeek As Integer
  5. Dim MyYear As Integer
  6. Dim MyCriteria As String
  7.  
  8. EnterDate = CDate(InputBox("Please enter a date:"))
  9. MyWeek = DatePart("ww", EnterDate)
  10. MyYear = DatePart("yyyy", EnterDate)
  11. MyCriteria = "([ArrWk]=" & MyWeek & " OR [DepartWk]=" & MyWeek & ")" & _
  12. " OR ([ArrWk]<" & MyWeek & " and [DepartWk]>" & MyWeek & ")" & _
  13. " AND [ArrYear]=" & MyYear & ")"
  14.  
  15. Dim MyRpt As String
  16. MyRpt = "rptCabinOccupancy"
  17.  
  18. DoCmd.OpenReport MyRpt, acViewPreview, "qryWeekSum_01", MyCriteria
  19.  
  20. End Function
  21.  
  22.  
Nov 1 '06 #6

P: 5
Many thanks to all for the assistance. Here is how I finally solved it
[code]
Public Function Occupancy()
On Error GoTo Err_Occupancy
Dim EnterDate As Date
Dim MyWeek As Integer
Dim MyYear As Integer
Dim MyCriteria As String
Dim MyOne As String, MyTwo As String, MyThree As String, MyFour As String, MyFive As String

EnterDate = InputBox("Please enter a date.")
MyWeek = DatePart("ww", EnterDate)
MyYear = DatePart("yyyy", EnterDate)
MyOne = "[ArrWk] = " & MyWeek
MyTwo = "[DepartWk] = " & MyWeek
MyThree = "[ArrWk] < " & MyWeek
MyFour = "[DepartWk] > " & MyWeek
MyFive = "[ArrYear] = " & MyYear

MyCriteria = MyFive & "AND(" & MyOne & "OR" & MyTwo & "OR(" & MyThree & "And" & MyFour & "))"

Dim MyRpt As String
Dim MyFilter As String

MyRpt = "rptCabinOccupancy"
MyFilter = "qryWeekSum_01"
DoCmd.OpenReport MyRpt, acViewPreview, MyFilter, MyCriteria

Exit_Occupancy:
Exit Function

Err_Occupancy:
MsgBox Err.Description
Resume Exit_Occupancy

End Function

[End of code]
That's the end of the code. This is the only way I could figure out the syntax.
Thanks again


[quote=mmccarthy]Firstly, the user entering a date is entered as a string and has to be converted to a date.

Next MyWeek is an integer and doesn't require single quotes as they are only needed for strings.

Try this:

[code]

Public Function Occupancy()
Dim EnterDate As Date
Dim MyWeek As Integer
Dim MyYear As Integer
Dim MyCriteria As String

EnterDate = CDate(InputBox("Please enter a date:"))
MyWeek = DatePart("ww", EnterDate)
MyYear = DatePart("yyyy", EnterDate)
MyCriteria = "([ArrWk]=" & MyWeek & " OR [DepartWk]=" & MyWeek & ")" & _
" OR ([ArrWk]<" & MyWeek & " and [DepartWk]>" & MyWeek & ")" & _
" AND [ArrYear]=" & MyYear & ")"

Dim MyRpt As String
MyRpt = "rptCabinOccupancy"

DoCmd.OpenReport MyRpt, acViewPreview, "qryWeekSum_01", MyCriteria

End Function
Nov 1 '06 #7

Post your reply

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