Dates being recorded into table as 12:00?!? (1 Viewer)

[SteveClarkson]

Hello all,

I am trying to record a whole load of dates into a table (tblOccurences).

My code calling a function to whack the records into the table

CreateOccurence DecreaseDate(Text0) 'Main Occurence
CreateOccurence (DateAdd("d", 1, DecreaseDate(Text0))) '1 Day Reminder
CreateOccurence (DateAdd("m", 1, DecreaseDate(Text0))) '1 Month Reminder
CreateOccurence (DateAdd("m", 2, DecreaseDate(Text0))) '2 Month Reminder

The DecreaseDate code

Public Function DecreaseDate(ByVal dteStart As Date) As Date

'Takes a date (dteStart) and checks if it is a Saturday or Sunday - if so, it decreases the date returned to the previous Friday.

    Dim dteTemp As Date

    dteTemp = dteStart

    Select Case Weekday(dteTemp)
        Case Is = 1
            DecreaseDate = DateAdd("d", -2, dteTemp)
        Case Is = 7
            DecreaseDate = DateAdd("d", -1, dteTemp)
        Case Else
            DecreaseDate = dteTemp
    End Select

End Function

The CreateOccurence Code

Public Function CreateOccurence(Criteria As Date)
Dim strSQL As String, strCriteria As Date

strCriteria = Criteria
strSQL = "INSERT INTO tblOccurence ( TaskID, DueDate ) SELECT tblTask.TaskID, Format(" & strCriteria & ",""dd/mm/yyyy"") AS DueDate FROM tblTask"

DoCmd.RunSQL strSQL

End Function

Say I put today's date in the Text0 box (rubbish names I know, still testing!), I would expect it to write 4 records to the tblOccurences table, with dates:
06/08/2007
07/08/2007
06/09/2007
06/10/2007

Instead, I get:
12:00:00 AM
12:00:00 AM
12:00:00 AM
12:00:00 AM

There is no date in there, I have checked formats etc, no date is recorded.

Help!!!


Thank you!

 

[Rabbie]

Are you actually calling the code because 12:00:00 Am is stored as zero in a date/time field. have you checked that the funcyions are actually being called.

I would suggest you set a breakpoint and see if the code is doing what you expect.

 

[SteveClarkson]

I have had a look, and it is definately calling both functions, but still only putting 12:00:00 AM into the table. :-(

 

[Rabbie]

You need to set breakpoints and check that the SQL is being set up with the desired dates in it

 

[SteveClarkson]

Ah! Found it! It was (as expected) me being thick, and not checking my SQL.

All was fine except the inclusion of a couple of quote marks around the date in the CreateOccurence code.

The line should read:

strSQL = "INSERT INTO tblOccurence ( TaskID, DueDate ) SELECT tblTask.TaskID, Format([COLOR="red"]'[/COLOR]" & strCriteria & "[COLOR="Red"]'[/COLOR],""dd/mm/yyyy"") AS DueDate FROM tblTask"

Changes highlighted in red!

Thank you for your help Rabbie.