Overlapping Date Periods

[George1029]

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

[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!
:)

[NeoPa]

Alternatively, if you post the criteria you used I can check it over and make recommendations.

[George1029]

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

[NeoPa]

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

MyCriteria = "([ArrWk] = ' " & MyWeek & " ' OR [DepartWk] = ' " & MyWeek & " ' " & _
"OR ([ArrWk]<' " & MyWeek & " ' and [DepartWk] > ' " & MyWeek & " ')) " & _
"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 :-

MyCriteria = "([ArrWk] = '" & MyWeek & "' OR [DepartWk] = '" & MyWeek & "' " & _
"OR ([ArrWk]<'" & MyWeek & "' and [DepartWk] > '" & MyWeek & "')) " & _
"AND ([ArrYear] = '" & MyYear & "')"

[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:

 
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
 
 

[George1029]

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