Hi (again)

The solution below worked for the problem I posted last week (shown at
the bottom of this post) , however some of the dates are coming
though
as British dd/mm/yyyy and others are coming through as US (I need
british). For
example, the following is a list of dates I need inserted into the
timesheet table


11/07/2010
18/07/2010
25/07/2010
01/08/2010 (this is inserted into the table as 08/01/2010 even though
the variable EndDate stores it correctly)
08/08/2010
15/08/2010


I've tried the following on the solution you provided and it makes no
difference:

EndDate = FormatDateTime(DateAdd("d", 6, Me.WEEK_COMMENCING_DATE), 2)
For WeekCount = 1 To 26
DoCmd.RunSQL "INSERT INTO tbl_TIMESHEET(EMPLYE_ID,TMESHT_DATE) "
&
_
"VALUES(" & Me.EMPLYE_ID & ",#" & EndDate & "#)"
EndDate = FormatDateTime(DateAdd("d", 7, EndDate), 2)
Next

'---Tried this version as well


EndDate = Format(DateAdd("d", 6, Me.WEEK_COMMENCING_DATE), "dd/mm/
yyyy")
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), "dd/mm/yyyy")
Next


Again any help would be appreciated.


Cheers.




The commands for your command button would be something like:

Private Sub Sheets_Click()
Dim WeekCount As Byte
Dim EndDate As Date


EndDate = DateAdd("d", 6, Me.EMPLYE_StartDate)


For WeekCount = 1 To 26
DoCmd.RunSQL "INSERT INTO TIMESHEET(EMPLYE_ID,WeekEndingDate) " &
_
"VALUES(" & Me.EMPLYE_ID & ",#" & EndDate & "#)"
EndDate = DateAdd("d", 6, EndDate)
Next


'show the added sheets
Me.TIMESHEET_Subform.Requery


'field that tells you sheets are generated
Me.SHEETS_ADDED = True
Me.SHEETS_ADDED.SetFocus


'Sheets is the name of the command button
Me.Sheets.Visible = False


End Sub


In the event Current of the form you can check the value of
'Me.SHEETS_ADDED' and make the button (un)visible accordingly.


--
Groeten,


Peter



I have a couple of tables called tbl_EMPLYE (has EMPLYE_ID as
autonumber PK) and tbl_TIMESHEET (has TIMESHEET_ID as autonumber PK,
and EMPLYE_ID as FK) based on a one to many relationship. These use
a
form and subform where the form is based on tbl_EMPLYE and has a
combo
box based on employee. The subform is based on tbl_TIMESHEET and the
two are linked by the EMPLYE_ID column.

The frm_EMPLYE displays the Employee number and the Start Date they
started a placement (always a Monday date).


The frm_TIMESHEET displays a Week ending date (always a Sunday) along
with a few other fields in a tabulated layout.


What I need is a command button on the EMPLYE form that will create
26
records (timesheets) in the tbl_TIMESHEET table where each week
ending
date displays accordingly.


For example. If the Start Date on the main form (frm_EMPLYE) is 19-
JUL-2010 (a Monday date), I want 26 records in the subform to show
(showing Sunday dates):


25-JUL-2010
01-AUG-2010
08-AUG-2010


etc..


What I then need is for the command button to be disabled on the
frm_EMPLYE form for each employee that has had the 26 records added
(would this be used in conjunction with a flag or something????)


Any help would be greatly appreciated (or alternative solution)


Cheers.

The beginning of this thread is missing from my newsreader so I'm forced to
make some guesses. Am I correct in inferring that tbl_TIMESHEET is a linked
spreadsheet? If so, open it in Design mode and tell us what it says the
datatype of TMESHT_DATE is. I'm inferring from your use of # delimiters that
it is Date/Time, not Text, but it is best to be sure.

You should be aware that Jet requires literal dates to be expressed in
either US (mm/dd/yyyy) or iso (yyyy-mm-dd) format. It will never correctly
interpret date strings supplied in British format, unless you are supplying
dates like 01/0102010, etc.

So, when building your sql strings, you need to format the dates in one of
the two formats that Jet interprets correctly. I suggest you always use the
iso format, but it's really up to you. Either format will be correctly
interpreted.

Further information:
A date/time value is never stored with any format. It is stored as a Double
whose whole number portion represents the number of days since a seed date,
and whose decimal portion represents the time of day (.0 = midnight, .5 =
noon). It is the responsibility of the application displaying those date
values to apply format. Access is just such an application. If you don't
specify a format in the field's Format property, Access will display values
using the formats specified in the Windows regional settings. The same goes
for Excel (given that my assumption about tbl_TIMESHEET is correct)

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.

I've tried splitting the date into pieces using day month year and
pushing it back together with the format command around the variable -
but I'm getting nowhere.

Again, thanks for any help/advice/knowledge or long piece of rope you
can offer.

Cheers.

I have a couple of tables called tbl_EMPLYE (has EMPLYE_ID as
autonumber PK) and tbl_TIMESHEET (has TIMESHEET_ID as autonumber PK,
and EMPLYE_ID as FK) based on a one to many relationship. These use
a
form and subform where the form is based on tbl_EMPLYE and has a
combo
box based on employee. The subform is based on tbl_TIMESHEET and the
two are linked by the EMPLYE_ID column.

The frm_EMPLYE displays the Employee number and the Start Date they
started a placement (always a Monday date).


The frm_TIMESHEET displays a Week ending date (always a Sunday) along
with a few other fields in a tabulated layout.


What I need is a command button on the EMPLYE form that will create
26
records (timesheets) in the tbl_TIMESHEET table where each week
ending
date displays accordingly.


For example. If the Start Date on the main form (frm_EMPLYE) is 19-
JUL-2010 (a Monday date), I want 26 records in the subform to show
(showing Sunday dates):


25-JUL-2010
01-AUG-2010
08-AUG-2010


etc..


What I then need is for the command button to be disabled on the
frm_EMPLYE form for each employee that has had the 26 records added
(would this be used in conjunction with a flag or something????)


Any help would be greatly appreciated (or alternative solution)






On Jul 27, 1:08*pm, "Bob Barrows" <reb01... (AT) yahoo (DOT) com> wrote:

In addition to Bob's advice (which is absolutely correct), you might like to
read what Allen Browne has at http://www.allenbrowne.com/ser-36.html

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Carl" <carl.barrett (AT) newcastle (DOT) gov.uk> wrote

Carl wrote:
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

On Jul 27, 2:20*pm, "Bob Barrows" <reb01... (AT) NOyahoo (DOT) SPAMcom> wrote:


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