Search results

You need to wrap the query in quotes like this DoCmd.RunSQL ("insert into tblShift(Employee_ID,Shift_Wk_End) select Employee_ID,Forms!frmShift_Update_WK_End!NewDate as Shift_Wk_End from tblShift where Shift_Wk_End = Forms!frmShift_Update_WK_End!OldDate;")

insert into tblShift(Employee_ID,Shift_Wk_End) select Employee_ID,Forms!frmShift_Update_WK_End!NewDate as Shift_Wk_End from tblShift where Shift_Wk_End = Forms!frmShift_Update_WK_End!OldDate;

This should help you. http://support.microsoft.com/kb/209099

The only way to do it is to write some VBA code that goes through the table for each employee. The algorithm is roughly as follows. Select a distinct list of employees. For each employee filter out their absences and sort them on start date in a recordset and set up an iterative loop...

I think you need to define the target field list after the target table and then field match that to your select list. I dont think that aliasing Shift_Wk_End automatically matches that to the field name in the target table. See if this works then extend insert into tblShift(Shift_Wk_End)...

The issue is that dates input as text are potentially ambiguous, i.e. 1/7/2009 can be either Jan 7 or July 1. No such amibguity exists with the date picker or drop down lists.

The code given above is ASP (for web pages) but you can use ADO in access. You need to set the reference to Microsoft ActiveX Data Objects in the references section from a code module. Open a module, go to tools references and set the reference as shown in the attached image.

ADO GetRows Method Complete Recordset Object Reference The GetRows method copies multiple records from a Recordset object into a two-dimensional array. Syntax vararray=objRecordset.GetRows(rows,start,fields) Parameter Description...

Have a look at help for GetRows

You've identified difficulties associated with amiguities in text representation of dates. Two ways to get round this are: 1) Use three Combo boxes (drop down lists) , day month and year. 2) Use a calendar style date picker control. Any help with these two approaches needed I'm sure you can...

You should always wrap date literals in hash characters as you have discovered. select blah from blah where date>#22/1/2009# You can also use functions that retur a date type e.g. select blah from blah where date>now()-7

no problem drop me a line if you have any other issues. david.boyle@morrisonplc.com

Yes get rid of the single quotes. Also it's not a bad idea to get out of the habit of using spaces in your field names. Use the description property in the table to control how they display.