Between Time and then date

[Douglas J. Steele]

The TimeValue function returns only the Time portion of the date/time field.
The TimeSerial functions return a time-only value.

The solution I'd posted originally was

WHERE CallTime BETWEEN DateSerial(2008, 3, 1) AND DateSerial(2008, 4, 1)
AND TimeValue([CallTime]) BETWEEN TimeSerial(8, 30, 0) AND TimeSerial(17,
30, 00)

 

[Graham R Seach]

Oh, OK. Thanks Doug. I didn't know if anyone had posted anything other than
"move to SQL Server".

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

The TimeValue function returns only the Time portion of the date/time
field. The TimeSerial functions return a time-only value.

The solution I'd posted originally was

WHERE CallTime BETWEEN DateSerial(2008, 3, 1) AND DateSerial(2008, 4, 1)
AND TimeValue([CallTime]) BETWEEN TimeSerial(8, 30, 0) AND TimeSerial(17,
30, 00)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

In this case, the OP wanted only the times between 0830 and 1730.
I've just never figured out how to reduce the datetime datatype to an
integer, for specific comparison purposes. I'm sure open to an education,
if you're willing.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[David W. Fenton]

Absolute rubbish.

I had an Access app running for 5 years with a Jet back end with
350K records in the main table, 500K in the main child table, and
600K in another child table. I wanted to upsize it to SQL Server,
since I thought it was pushing the limits of what Jet could support
with a dozen users. But performance was so good, and reliability so
high (we were running the FMS Agent every night to back up and
compact), with no corruptions over years of operation, that I just
couldn't make the case on a cost/benefit analysis.

Externals altered somewhat in the past year, and so we upsized to
SQL Server last fall. It went well, and the results have been good,
with a very small number of performance improvements that came from
re-engineering the db to work better in SQL Server.

So, it seems to me that there is no question that 100K records is
well within the limits of what Jet can handle quite well.

On the other hand, perhaps it takes competent developers to
accomplish that.