Recent content by quatermass

Hi, As the SQL statement is a string for execution, just pasting in a working query won't work if there is a quote anywhere in it. SQL will take a single quote as being the end of what is to be executed and will attempt to run that and the remenants of the string. It will try to run select...

Hi TJ Try select convert(datetime, replace('May-18-09 14:00', '-', ' '), 109) The format 109 wants the format to be mon dd yyyy hh:mi:ss:mmmAM (or PM) Using the replace function creates that format. Hope it helps Kevin ************************************************************** Rock...

Hi, I generally use the following (VBA) code to generate a string to create a data-based folder cstr(year(now)) + rightpad(cstr(month(now))) + rightpad(cstr(day(now))) In order to generate the correct format (YYYYMMDD) ie 20080624, I use the following function function rightpad(val_in)...

Hi Bruce, I haven't done anything with the number of columns you have, I have only had to do 7/12 for days/months, but.... What I do is to create a table with a single column and populate that column with 1-7 or 1-12 as required. Next, I run a cross-join query against the original table and...

Hi, If you are trying to get a complete extract of the table you will need to make your first line set @as_select = 'bcp "DATABASENAME..ct_rl2cpcs"' where DATABASENAME is the database which holds the ct_r12cpcs table. Hope it helps...

Hi, I think the problem may be in the fact that the final "True" parameter indicates that the file will be created if it doesn't exist. Given that the file is for reading only, this doesn't make sense. Try removing the final "True" Hope it helps Kevin...

Olivia Apparantly, the port for 2005 Express is randomly assigned and can be viewed through the SQL Server Config Manager. Under Network Config and on the IP addresses tab, is TCP Dynamic Ports, this value is the one which you will need to connect the Content Store on. I think the port can...

Hi, You can either use the code to generate a new table, or use the sub-select statement as an update statement as follows: update table set DateAppended = subselect.min_DateAppended from table, (select PO, min(DateAppended) as min_DateAppended, Status from table...

Hi Tamrak, If I've got the idea: select t1.PO, t2.min_DateAppend, t1.Status, t1.ShippedDate, t1.ReceivedDate from table t1, (select PO, min(DateAppended) as min_DateAppended, Status from table group by PO, Status) t2 where t1.PO = t2.PO and t1.Status = t2.Status...

Hi Wayne, I am assuming that the report is in Impromptu. If this is the case, you can use the summary filter and prefilter options. I think this is right, I seem to recall doing a YTD/period to date Impromptu report like this. summary filter actuals = 0 This will have the same effect as...

Hi, There are a couple of ways to do this a) assuming that your target table has a datetime field for the import date, set the default value of this field to getdate() and the default value of the source field to be 'PICK' for example b) in the dts package on the source for the transform...

Hi, It has been a while since I have done this with DTS, so please forgive any coding errors, but the general formula is as follows Create a single column table with records of 1-7 (representing the days) Import the Excel data into a SQL table As the source for the transform, use both the...

Hi, You will need to modify the statement to have the variable name outside the quoted sql command string DoCmd.RunSQL "INSERT INTO tblTest2 (f1) VALUES (" & myVar & ")" You may have issues with data formatting. In which case, either change the variable data type, or put...

Laura, You can set the sql statement to pick up the latest date, by using maximum and group Example using Northwind table, Orders: SELECT EmployeeID, MAX(ShippedDate) AS Expr1 FROM Orders GROUP BY EmployeeID This will return the employee id with the latest shipped date for that...

Hi, You could try using the SkipInsert result. This is a basic example --start code dim x if DTSSource("col001") = "PRICE CHANGE RECS" then x = DTSTransformation_SkipInsert else DTSDestination("ITEM_NUMBER") = DTSSource("col001") x =...