Date format issue on INSERT VALUE sql statement
in [Help]
|
Prev: Pictures on command buttons
Next: Date Issue
From: Carl on 27 Jul 2010 07:04 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.
From: Bob Barrows on 27 Jul 2010 08:08 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 (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.
From: Carl on 27 Jul 2010 08:22 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...(a)yahoo.com> wrote: > 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 (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.- Hide quoted text - > > - Show quoted text -
From: Douglas J. Steele on 27 Jul 2010 08:23 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(a)newcastle.gov.uk> wrote in message news:7aaafa08-e396-4be7-b28c-41c1f6814546(a)c38g2000vba.googlegroups.com... > 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. > >
From: Bob Barrows on 27 Jul 2010 09:20
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 |