Thanks This looks good, only problem is does not return the first record either. I do have a way of getting past that and will run a couple of benchmarks to check the speed of this against the left join Regards Peet YASP

Hi All We have a table with a persons Employee number (Resource tag) , Date Worked and the Shift Worked on that date. Like So resource tag date worked shift type ------------ ------------------------------------------------------ -------------------------------------------------- 125197584 2004-07-14 00:00:00.000 Night 125197584 2004-07-15 00:00:00.000 Night 125197584 2004-07-16 00:00:00.000 Night 125197584 2004-07-17 00:00:00.000 Night 125197584 2004-07-18 00:00:00.000 Morning 125197584 2004-07-19 00:00:00.000 Morning 125197584 2004-07-20 00:00:00.000 Morning 125197584 2004-07-21 00:00:00.000 Morning 125197584 2004-07-22 00:00:00.000 Morning 125197584 2004-07-23 00:00:00.000 Morning 125197584 2004-07-24 00:00:00.000 Morning 125197584 2004-07-25 00:00:00.000 Night 125197584 2004-07-26 00:00:00.000 Night 125197584 2004-07-27 00:00:00.000 Night 125197584 2004-07-28 00:00:00.000 Night 125197584 2004-07-29 00:00:00.000 Night 125197584 2004-07-30 00:00:00.000 Night 125197584 2004-07-31 00:00:00.000 Night 125197584 2004-08-01 00:00:00.000 Morning 125197584 2004-08-02 00:00:00.000 Morning 125197584 2004-08-03 00:00:00.000 Morning 125197584 2004-08-04 00:00:00.000 Morning 125197584 2004-08-05 00:00:00.000 Afternoon 125197584 2004-08-06 00:00:00.000 Morning 125197584 2004-08-07 00:00:00.000 Morning 125197584 2004-08-08 00:00:00.000 Morning 125197584 2004-08-09 00:00:00.000 Morning 125197584 2004-08-10 00:00:00.000 Morning 125197584 2004-08-11 00:00:00.000

Hi There Please try this (Not sure if this is what you are looking for: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1] GO CREATE TABLE [dbo].[Table1] ( [a] [int] NULL , [b] [int] NULL , [c] AS (isnull(case when (isnull([a],0) > isnull([b],0)) then (isnull([b],0)) else (isnull([a],0)) end,0)) ) ON [PRIMARY] GO insert into [table1] values (1,3) insert into [table1] values (22,3) insert into [table1] values (1,1) select [a],[b],[c] from [table1] Selecting [c] seems to always return the smallest of [a] or [b] Regards Peet Schultz YASP

1. Check your spelling. You are creating table tmplog and inserting into tmlog. 2) What version of SQL are you using? With the table name correct this works fine on SQL server 2000 sp3 Regards YASP

Hi There Looks like you will have to revert to Dynamic sql. Build up the statement as required and then excecute. This is normally a bit slower than straight sql but should be able to do the trick. Regards Peet Schultz USE [PUBS] DECLARE @LAST_NAME NVARCHAR(100) DECLARE @SQL NVARCHAR(1000) SET @LAST_NAME = '(''White'',''Green'')' SET @SQL = 'SELECT * FROM AUTHORS WHERE [AU_LNAME] IN' + @LAST_NAME EXEC (@SQL)

Hi There What does your code look like, if I use the following the grid immediately shows all the data (I think it may be from using a DataTable or not?) Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim oDA As SqlClient.SqlDataAdapter Dim oCon As SqlClient.SqlConnection = New SqlClient.SqlConnection Dim oTBL As DataTable = New DataTable oCon.ConnectionString = "Initial Catalog = Northwind;Data Source = (local);uid = sa;pwd=;" Try oCon.Open() Catch ex As Exception MessageBox.Show(ex.ToString) End Try oDA = New SqlClient.SqlDataAdapter("Select * from Customers", "Initial Catalog = Northwind;Data Source = (local);uid = sa;pwd=;") oDA.Fill(oTBL) Me.DataGrid1.DataSource = oTBL End Sub Regards Peet Schultz

If you use a GUID (Globally Unique Identifier) as the ID, you should have something that can be ported to any platform as well as solve your Identity problems. I have previously used a VB6 function that produced GUID's, and am sure you should find one for .Net. Regards Peet Schultz Centurion SA YASP

In this example I am using the datadapter, as you can bind the whole resultset to the grid in one command, with the datareader you have to loop through the resultset. Dim oConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection() Dim oCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand() Dim oDataTableResources As Data.DataTable = New Data.DataTable() Dim oDataTableSites As Data.DataTable = New Data.DataTable() Dim oDataAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter() Dim sSiteName As String Dim sResourceName As String Const iDayLength As Integer = 86400 sSQL = "Select * from Category" gsConnectString = " Initial Catalog=" & UCase(Trim(Me.txtDB.Text)) & _ ";Data Source=" & UCase(Trim(Me.txtSVR.Text)) & _ ";User ID=" & UCase(Trim(Me.txtUID.Text)) & _ ";Password=" & Trim(Me.txtPWD.Text) & ";" oConnection.ConnectionString = gsConnectString Try oConnection.Open() Catch o As System.Exception MsgBox(o.Message, MsgBoxStyle.Critical, gsMessageBoxHeader) Me.Cursor = Cursors.Default Exit Sub End Try Try oCommand.Connection = oConnection oCommand.CommandText = sSQL oDataAdapter.SelectCommand = oCommand 'fill the data adapter oDataAdapter.Fill(oDataTableResources) 'bind to the datagrid dbgrdDataGrid.DataSource = oDataTableResources dbgrdDataGrid.ParentRowsVisible = False Catch o As System.Exception MsgBox(o.Message) Me.Cursor = Cursors.Default End Try I do not have any code of getting a specific cell from the grid. I am sure you will be able to find samples on the web. YASP

Hi There If you are going to use the resultset for display purpose only, it is better to use the datareader to retrieve the data as in the following piece of code. The code shows how to read the sql results into a combo box, but you would do the same for a text box (given that you only return one row from the db) I think it would also be easier to read the parameters from the input text boxes as you build up the sql string, ie: sSQl = "select * from publishers where name = " + txtName.text + " and date ........" etc Private Sub LoadInitData() Dim sConnect As String Dim sCategory As String Dim sType As String Dim sPrinter As String Dim odrCategory As OleDb.OleDbDataReader Dim odrType As OleDb.OleDbDataReader Dim odrPrinter As OleDb.OleDbDataReader Dim oConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection() Dim oCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand() Dim sDBPath As String sDBPath = Application.StartupPath sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & "\Inks.MDB" sCategory = "SELECT CATEGORY.CATEGORY FROM CATEGORY ORDER BY CATEGORY.CATEGORY" Try oConnection.ConnectionString = sConnect oConnection.Open() 'get categories oCommand.CommandText = sCategory oCommand.Connection = oConnection odrCategory = oCommand.ExecuteReader While odrCategory.Read Me.cmbCategory.Items.Add(odrCategory.GetString(0)) End While If cmbCategory.Items.Count > 0 Then cmbCategory.SelectedIndex = 0 End If odrCategory.Close() oConnection.Close() Catch o As System.Exception MsgBox(o.Message) Exit Sub End Try End Sub YASP