Date and Time issues (1 Viewer)

[Chrisopia]

I am trying to create an automated "End of Month" report which prints when an end of day report is printed, but it's also the last day of the month.
It follows some simple conditions:
1) Find the last day of the month and stamp it in a table
2) If the last day is a Sunday, then stamp the Saturday instead
3) If the stamp date is in the past, the end of month report wasn't processed (i.e. for what ever reason the store wasn't open on the last day of the month - for example Christmas) - then produce report.

At the moment, I have a stamp in my main table "tblBranch": "30/09/2014"

Dim EndDay As Date
Dim EndMonth As Date
Dim dbs As Database
Dim rs As Recordset
Dim EndOfMonth As String
Dim NextMonth As Date
Set dbs = CurrentDb
bi = "5" 'retreived from a function to find Branch ID

EndOfMonth = "Select * FROM tblBranch Where ID = " & bi
    Set rs = dbs.OpenRecordset(EndOfMonth)
    EndDay = rs.Fields(22) ' field 22 = date '<============= This isn't retrieving the date properly!!!
    Debug.Print EndDay ' prints 00:00:00 instead of 30/09/2014
NextMonth = DateSerial(Year(EndDay), Month(EndDay) + 1, 0) finds the last day of the next month

If Format(NextMonth, "ddd") = "Sun" Then 'if its a Sunday, use Saturday as the last day
    NextMonth = DateSerial(Year(NextMonth), Month(NextMonth), Day(NextMonth) - 1)
End If

If EndDay <= Date Then
dbs.Execute "UPDATE tblBranch SET [End of Month] = " & NextMonth & " WHERE ID = " & bi 'update the next end of month date
MsgBox "You are " & Date - EndDay & "over!!"
Else
MsgBox "not yet!"
End If

 

[MarkK]

It's not clear to me how to help here. You haven't asked a question. You haven't shown the structure of your table. What does it mean that field 22 "isn't retrieving the date properly?" Is that an error? Wrong result?

 

[Chrisopia]

Apologies for not being clear.

rs.fields(22) points to EndofMonth date field in tblBranch, which is formatted as short date in the table properties

For some reason, when I try to retrieve the date from the field (30/09/14) I get 00:00:00 instead.
Subtracting 1 day from this throws an error and takes me back to 30/12/1899

 

[Chrisopia]

apologies again, it was such a simple answer.

I still get confused where '#' are needed when referring to dates.
Retrieving a date from the table didn't need them, but writing a date to a table did, so when it updated itself with the new date, it would just paste a number and reference it as a time. Even using formatting and such to strict it as a date, it still failed to see it was a date because there were no #

Very frustrating!

dbs.Execute "UPDATE tblBranch SET [End of Month] = #" & NextMonth & "# WHERE ID = " & bi

 

[MarkK]

So this is solved then?