Re: Assign a table field value to a variable
- From: "Van T. Dinh" <VanThien.Dinh@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 31 Oct 2006 17:25:20 +1100
1. Have you included the "Microsoft DAO 3.6 Object Library" in the
References collection of your database?
In the IDE, use the Menu Tools / References ... and make sure you include
the above Library.
2. Should it be a Sub rather than a Function? While it is possible to leave
it as a function, a Sub is used to perform some action(s) (and in this case,
OpenReport is an action) and a Function usually returns a value (which your
code doesn't).
3. Do you want EndWeek = StartWeek + 7? This means Sunday to Sunday
inclusive? You may want to consider EndWeek = StartWeek + 6 (Sunday to
Saturday inclusive).
4. Check Access VB Help on the arguments of the OpenReport method. I don't
think you can use a Recordset as the value for the "WhereCondition"
argument. In fact, I am not sure why you created the Recordset in the first
place.
5. Your query involves checking for "overlapping periods". Check Google as
I think there have been a number of posts on how to check for overlapping
periods efficiently in Access Query.
--
HTH
Van T. Dinh
MVP (Access)
"George R" <GeorgeR@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1485E135-2968-4ECC-B0D9-F4C418024063@xxxxxxxxxxxxxxxx
Perhaps I could get some help to revise the following code:
Public Function Occupancy()
Dim db As database, rst As DAO.Recordset
Dim StartWeek As Date
Dim EndWeek As Date
StartWeek = CDate(InputBox("Select and enter a Sunday Date"))
EndWeek = StartWeek + 7
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblReservations " & _
"WHERE (ArrDate < StartWeek AND DepartDate > EndWeek) " & _
"OR (ArrDate Between StartWeek And EndWeek) " & _
"OR (DepartDate Between StartWeek And EndWeek) " & _
"ORDER BY CabinNum")
Dim MyRpt As String
MyRpt = "rptCabinOccupancy"
DoCmd.OpenReport MyRpt, acViewPreview, , rst
End Function
THis code stops at the first line. Thank you for your consideration.
"Klatuu" wrote:
You can't directly reference a table like that. You will have to use SQL,
a
Domain Aggragate function, or a recordset to establish a reference to the
table.
MyDepartDate = CDate(Tables!tblReservations.DepartDate)
Is not enough information in any case. I am sure there are more than one
record in your reservations table. Which one to you want? This could be
done with a Domain Aggragate function if you have a way to match to some
unique value in the the table. For demonstration purposes, I will
pretend
you have a reservation number on your form:
MyDepartDate = CDate(DLookup("[DepartDate]", "tblReservations",
[ReservationID] = " & Me.txtReservationID))
First, the syntax above assumes ReservationID is a numeric field.
Do you really need the CDate function? It is not already stored as a
date
in the table?
Line 4 needs to be fixed, also.
"George R" wrote:
I would appreciate any help on the followng:
The third line of this segment of code give me an error message "Object
Required." How can modify this to get the proper results?
Dim MyDepartDate As Date
Dim MyArrDate As Date
MyDepartDate = CDate(Tables!tblReservations.DepartDate)
MyArrDate = CDate(tblReservations.ArrDate)
Thank you for your consideration.
.
- References:
- RE: Assign a table field value to a variable
- From: George R
- RE: Assign a table field value to a variable
- Prev by Date: Re: Using VBA to add linked formula in Excel
- Next by Date: Re: Waiting for a function to finish.
- Previous by thread: RE: Assign a table field value to a variable
- Next by thread: Re: Waiting for a function to finish.
- Index(es):
Relevant Pages
|
