Prev: Pictures on command buttons
Next: Date Issue
From: Carl on 27 Jul 2010 11:13
On Jul 27, 2:20 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
> Carl wrote:
> > Hi,
>
> > My original post is as follows (as you can see timesheet is a table in
> > the database).  I am populating the timesheet table with week endings
> > (Sunday dates) based on the Monday from when an employee started.  The
> > insert statement works fine as I get all 26 weeks, however there are a
> > few incorrect dates like 11/07/2010 (11 July) coming in as 07/11/2010
> > (07 Nov) and the same with 01/08/2010 displaying as 08/01/2010.
>
> > How and where do I read the data in as ISO and where do I get it to
> > display as British in the subform displaying 26 weekending dates for
> > my time sheets.
>
> You don't read it as ISO, you format the string you are creating as ISO,
> using the Format function:
>
> EndDate = Format(DateAdd("d", 6, Me.WEEK_COMMENCING_DATE), "yyyy-mm-dd")
>
> The Format function returns a string that is formatted using the
> specification in the third argument. When you concatenate that into the
> string you are planning to execute via RunSQL, the presence of the hash
> delimiters (#) tell Jet that you are presenting a date literal and it
> interprets the string accordingly, converting it into a proper date/time
> value to be stored in the database.
>
> --
> HTH,
> Bob Barrows



Hi Bob,

Thanks very much for your response.

Here's the bit of code with your advice and it seems to work a treat.

Thanks again.

EndDate = Format(DateAdd("d", 6, Me.WEEK_COMMENCING_DATE), "yyyy/mm/
dd")

For WeekCount = 1 To 26
DoCmd.RunSQL "INSERT INTO tbl_TIMESHEET(EMPLYE_ID,TMESHT_DATE) " &
_
"VALUES(" & Me.EMPLYE_ID & ",#" & EndDate & "#)"
EndDate = Format(DateAdd("d", 7, EndDate), "yyyy/mm/dd")
Next
First  |  Prev  | 
Pages: 1 2
Prev: Pictures on command buttons
Next: Date Issue