Compile Error!

[baggyboy26]

Hello,

I've written the following module and when i call it from a button i get a compile error, it expects an =, and i'm not sure why! Any ideas?

Public Function WhichWeek(WeekDate As Date, FirstDate As Date, LastDate As Date)

Dim db As Database
Dim rs As Recordset
Dim dteDate As Date
Dim strSQL As String
Dim tName As String
Dim n As Integer

Dim WeekNumber As Integer
WeekNumber = DateDiff("ww", FirstDate, LastDate, 2)

Set db = CurrentDb

On Error Resume Next
tName = "tblMondays"

db.Execute "DROP TABLE " & tName & ";"
strSQL = "CREATE TABLE " & tName & " (DateID AUTOINCREMENT, Monday DATETIME, Tuesday DATETIME, Wednesday DATETIME, Thursday DATETIME, Friday DATETIME);"
db.Execute strSQL
Set rs = db.OpenRecordset(tName)

dteDate = WeekDate - Weekday(WeekDate) + 2
For n = 1 To WeekNumber
With rs
.AddNew
!Monday = dteDate
!Tuesday = dteDate + 1
!Wednesday = dteDate + 2
!Thursday = dteDate + 3
!Friday = dteDate + 4
.Update
dteDate = dteDate + 7
End With
Next n

rs.Close
db.Close
Set db = Nothing
End Function

This is the code behind the call:

Private Sub WhichWeekCall_Click()

WhichWeek (Autumn1Start, Autumn1Start, Autumn1End)

End Sub

I know that i have Autumn1Start twice in this example but have tried other variables and get the same error.

BaggyBoy

Sometimes the answer stares you right in the face!!!

 

[HarleyQuinn]

Use either:

Call WhichWeek (Autumn1Start, Autumn1Start, Autumn1End)

'or

WhichWeek Autumn1Start, Autumn1Start, Autumn1End

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[baggyboy26]

Genius! You've saved me hours of ponderment! I really must do a course sometime but alas as a humble teacher I have no time.

BaggyBoy

Sometimes the answer stares you right in the face!!!