Search results

Bob - Date plus Time is a numeric calculation, not a concatenation. Since date/time data type is internally a double precision number with the integer portion representing the date and the decimal portion representing the time, we do need the + sign if we want to keep the date/time data type as...

DateAndNewTime: DateValue([DateAndTimeField]) + #22:33# ^

You can add the word DISTINCT in the Row Source property of the combo box i.e. Select DISTINCT [DateFieldName] from [TableName] ^

In your db, the CourseReportCurrent query returns 6 records and the CourseReportOverdue query returns 2. If what you want is combine them in one query showing 8 records, you can join them in a Union Query as Paul suggested in his first post. Select [name], "Current" as [Type], [studentNum]...

Two expressions for converting the text date and time to date and time:- DateSerial(Left([TextDate],4),Mid([TextDate],5,2),Right([TextDate],2)) TimeSerial(Left([TextTime],2),Mid([TextTime],3,2),Right([TextTime],2)) If your version of Access doesn't have the TimeSerial() function, you can...

See Jon K's basic criteria format and his Note (1) in this thread:- http://www.access-programmers.co.uk/forums/showthread.php?t=103312 ^

If you don't use criteria for Total Price, you can simply multiply the Loyalty by RoomPrice in a new column in the query grid and choose Expression in the Total: row. Total Price: CCur([Loyalty (no of nights)]*[RoomPrice]) However, if you may later need to use Criteria for this Total Price...

The 0 in the second argument returns a text field. ^

One warning The functionality of Null has been fully incorporated into Totals Queries. If you need to do Totals Queries, it is not advisable to change the Null values (which is Access' default). ^

You can use the Eval() function in the Where Clause of the query:- ... WHERE Eval(CDbl([DateDue]) & forms!frmInvoices!txtOp & CDbl(Date())) The CDbl() function converts [DateDue] and Date() to two numbers for evaluation. So Access doesn't have to evaluate something like 14/4/2007 > 14/5/2007...

Using two columns and two criteria rows in the query grid (as in the above link) for one field makes it very difficult to add criteria for other fields. See Jon K's basic criteria format and his discussion in Note 1 in this thread:-...

You have left out the single-quotes in the DateValue() ^

Search for percentile in these forums. There were quite a few discussions on this topic. ^

Thanks, Jon. That worked.

Jon - I removed the two = signs in your sample and tried the ending time of the first record in the text box 4/24/2007 11:30:00 AM but, contrary to what was expected, it clashed with the first record. But when I tried the ending time of the second record 4/25/2007 11:45:00 AM in the text box...

Take a look at Jon K's post and sample (post #10) in this thread - http://www.access-programmers.co.uk/forums/showthread.php?t=125130 In the sample, Jon used two simple queries to resolve an issue which is quite similar to the one you are facing. ^

I think you need to re-read Jon's statement:- DatePart("h",([Date Recvd]+[Time Recvd])-([Date Sent]+[Time Sent])) - returns an integer between 0 and 23, representing the number of completed hours. It works correctly when elapsed time is less than 24 hours. Jon was talking about DatePart, not...

DateDiff("h") doesn't work correctly all the time. For example, DateDiff("h") of 1/10/2007 3:59:59 AM and 1/10/2007 4:00:00 AM returns 1 hour though actually only one second has elapsed. ^

Try this: Format(TimeValue([Subtotal])-[Lunch], "Short Time") ^

Thanks for the simple expression for summing elapsed times in date/time fields. I expanded it to include the SECONDS: =Int(Sum([ElapsedTime])*24) & ":" & Format(Sum([ElapsedTime]),"nn:ss") and it was accurate to the final second! Amazing! ^