Recent content by mik18

NewTime = Format(DateDiff("s",StartDate,EndDate),"hh:nn:ss") The format part may work though I have never tried it. If not, still use the datediff function to get the time interval in seconds then build that into hours minutes and seconds. mike

You need to establish the connection before you open the recordset. Set cn = CurrentProject.Connection mike

OK. This was a question I posted in a crystal forum asking relativily the same question. You will have to modify this slightly for your use but it gets the idea. mik18 (MIS) Jun 24, 2004 Is there any way to take a date in the form of a week and year (ex. Week 25, 2004) and find the start and...

The DatePart Function allows you to specify the start of the week. In your case Monday. You want to use this function on the FillDate. DatePart("ww", Filldate, 2) this will return the week number the FillDate occured in. If you group on this field and count the number of scripts you have what...

In my exmple i only had 7 columns and I wanted to keep them all. When do you filter your results based on user? Before or after you run the crosstab.

Use the absolute value function Abs(). mike

You can do this a couple of different ways. The first that comes to mind is to use IIF statements. SELECT IIF([field 1]="yes",1,0)As One, IIF([field 2]="yes",1,0)As Two, IIF([field 3]="yes",1,0) As Three, One+Two+Three As Total FROM tablename; mike

Is the query appending the correct record? If so, assign the values to variables and pass the variables to the email portion of the code. Mike

I had to do something like this jsut recently. You need to take a couple of steps back from the crosstab query to make this work. I'm sure the column headings in the crosstab you want displayed even if there is null values. To do this you need a query that selects the column values you want...

Have them update to Outlook 2003.

Use the DateAdd Function DateAdd("m", -6, Date()) Check out the help in access to see all the options for the first parameter. good luck mike

The WHERE Clause would need a unique identifier to know which record to pull the TimeIn field from. If it is in the previous row then you could return the entire dataset, use the rst.Find command to move to the current record, then use the rst.MovePrevious command. Hope this helps. mike

You can use a recordset to retreive the TimeIn of the previous record. Dim qry as String Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim v_TimeIn As ??? Set cnn = CurrentProject.Connection qry = "SELECT TimeIn FROM TableName WHERE criteria match = ??" rst.open qry,cnn...

Function DateAdd(Interval As String, Number As Double, Date) Yesterday = DateAdd("d", -1, Date()) Tomorrow = DateAdd("d", 1, Date()) mike

Try setting up a linked table(s) to the remote database and using a make table query. After you initially make the table you can just use an append query that captures only unique records to update the local table(s). mike