Assign a table field value to a variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
Thank you Klatuu,
I had intended to use the variable as part of a criteria statement in a
"Where" clause wtih Do.Cmd to open a report. Multiple records will match the
criteria. The CDate function was something that I had tried and not removed.
I will try the recordset approach.
Thanks again.

Klatuu said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
There is no need for a recordset here. What you pass to the report as a
Where condition is just that, an SQL Where clause without the word Where.
Note that the report's record source must have fields in it that match the
field names used in the Where condition.

I Changed the initiation of the EndWeek date to it is Sunday to Saturday.
This seems more logical. If Sunday to Sunday was correct, change it back.

I also added checking for a valid Sunday date and allow a way for the user
to cancel.

Note the variable name changes. It is always best to use naming
conventions. It makes it obvious what data type you are working with and
avoids naming conflicts with reserved words.

Public Function Occupancy()
Dim dtmStartWeek As Date
Dim dtmEndWeek As Date
Dim strWhere As String

Do While True
dtmStartWeek = InputBox("Select and enter a Sunday Date")
If Len(Trim(NZ(dtmStartWeek,""))) = 0 Then
If MsgBox("Cancel Report", vbQuestion + vbYesNo) = vbNo Then
Exit Sub
End If
ElseIf Not IsDate(dtmStartWeek) Then
MsgBox dtmStartWeek & " Is No A Valid Date"
ElseIf WeekDay(dtmStartWeek) <> 7 Then
MsgBox dtmStartWeek & " Is No A Sunday"
Else
Exit Do
End If
Loop

dtmEndWeek = DateAdd(6, "d", dtmStartWeek)

strWhere = "[arrDate] Between #" & dtmStartWeek & "# And #" & _
dtmEndWeek & "# OR [DepartDate] Between "# & dtmStartWeek & _
"# And #" & dtmEndWeek & "# ORDER BY [CabinNum]"

DoCmd.OpenReport "rptCabinOccupancy", acViewPreview, , strWhere

End Function


George R said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top