Date Search in Date & Time Field

[hobbit2612 via AccessMonster.com]

Hi,

Wonder if someone can help me please.

I have a date and time field which shows the dates and times that files were
received onto a server at work. This is in the format of DD/MM/YYYY HH:MM and
comes in the form of a .csv file which I then import into the database.

The problem I have is that once loaded into the database, I need to search
within this field for files received between two user selected date
parameters. At the moment I am taking out the time element whilst in excel
but this is becoming a bit of a pain.

Can anyone tell me please how can I keep the 'raw' data as it is but also
enable a user to search between the two dates that they want to retrieve the
records for.

Thanks very much

 

[Rick Brandt]

Hi,

Wonder if someone can help me please.

I have a date and time field which shows the dates and times that
files were received onto a server at work. This is in the format of
DD/MM/YYYY HH:MM and comes in the form of a .csv file which I then
import into the database.

The problem I have is that once loaded into the database, I need to
search within this field for files received between two user selected
date parameters. At the moment I am taking out the time element
whilst in excel but this is becoming a bit of a pain.

Can anyone tell me please how can I keep the 'raw' data as it is but
also enable a user to search between the two dates that they want to
retrieve the records for.

Thanks very much

Store it in a date field and forget about formatting. It doesn't matter for
querying anyway. To find records between two dates with a simple self-prompting
parameter...

SELECT *
FROM TableName
WHERE DateField >= [Enter Start Date]
AND DateField < DateAdd("d", 1, [Enter End Date])

 

[hobbit2612 via AccessMonster.com]

Rick,

That's great! I'll give it a try.

Many thanks

Chris

Hi,

[quoted text clipped - 15 lines]

Thanks very much

Store it in a date field and forget about formatting. It doesn't matter for
querying anyway. To find records between two dates with a simple self-prompting
parameter...

SELECT *
FROM TableName
WHERE DateField >= [Enter Start Date]
AND DateField < DateAdd("d", 1, [Enter End Date])

 

[Michael Gramelspacher]

Rick,

That's great! I'll give it a try.

Many thanks

Chris

Hi,

[quoted text clipped - 15 lines]

Thanks very much

Store it in a date field and forget about formatting. It doesn't matter for
querying anyway. To find records between two dates with a simple self-prompting
parameter...

SELECT *
FROM TableName
WHERE DateField >= [Enter Start Date]
AND DateField < DateAdd("d", 1, [Enter End Date])

perhaps also:

SELECT *
FROM TableName
WHERE DateField BETWEEN
DATEADD("d", DATEDIFF("d",0, [Enter Start Date]),0)
AND DATEADD("d", DATEDIFF("d",0, [Enter End Date]),0)

I think this explicitly returns DateTime values for
the start date and the end date while omitting the time.

 

[Rick Brandt]

perhaps also:

SELECT *
FROM TableName
WHERE DateField BETWEEN
DATEADD("d", DATEDIFF("d",0, [Enter Start Date]),0)
AND DATEADD("d", DATEDIFF("d",0, [Enter End Date]),0)

I think this explicitly returns DateTime values for
the start date and the end date while omitting the time.

His problem stemmed from the fact that the field in the table contained a time
component, not because the value entered by the user contained a time. And
DateAdd("d",0) would not strip the time off anyway. What you are probably
thinking of is DateValue().

 

[Michael Gramelspacher]

perhaps also:

SELECT *
FROM TableName
WHERE DateField BETWEEN
DATEADD("d", DATEDIFF("d",0, [Enter Start Date]),0)
AND DATEADD("d", DATEDIFF("d",0, [Enter End Date]),0)

I think this explicitly returns DateTime values for
the start date and the end date while omitting the time.

His problem stemmed from the fact that the field in the table contained a time
component, not because the value entered by the user contained a time. And
DateAdd("d",0) would not strip the time off anyway. What you are probably
thinking of is DateValue().

I understand. How about this?

SELECT *
FROM TableName
WHERE DateField BETWEEN
DATEADD("d", DATEDIFF("d",0, [Enter Start Date]),0)
AND DATEADD("d", DATEDIFF("d",0, [Enter End Date]),
#1990-01-01 23:59:59#)

 

[Rick Brandt]

perhaps also:

SELECT *
FROM TableName
WHERE DateField BETWEEN
DATEADD("d", DATEDIFF("d",0, [Enter Start Date]),0)
AND DATEADD("d", DATEDIFF("d",0, [Enter End Date]),0)

I think this explicitly returns DateTime values for
the start date and the end date while omitting the time.

His problem stemmed from the fact that the field in the table
contained a time component, not because the value entered by the
user contained a time. And DateAdd("d",0) would not strip the time
off anyway. What you are probably thinking of is DateValue().

I understand. How about this?

SELECT *
FROM TableName
WHERE DateField BETWEEN
DATEADD("d", DATEDIFF("d",0, [Enter Start Date]),0)
AND DATEADD("d", DATEDIFF("d",0, [Enter End Date]),
#1990-01-01 23:59:59#)

I don't see where you are going with this. The sql I posted solves the problem
with a single (simple) expression on the right-hand side of the comparison.
What additional problem are you attempting to resolve?

 

[Michael Gramelspacher]

I don't see where you are going with this. The sql I posted solves the problem

with a single (simple) expression on the right-hand side of the comparison.
What additional problem are you attempting to resolve?

Rick, yes, you are right. Please disregard my previous posts.

 

[hobbit2612 via AccessMonster.com]

Michael,

Thanks for your reply.

Fine, there may have been a better way to overcome my problem (thanks Rick),
but I appreciate the time you spent to help.

Regards

Chris

 

[hobbit2612 via AccessMonster.com]

Hi Rick,

I tried the code that you gave me and unforunately it doesn't seem to work.

It comes up with the following error:

'The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in the parenthesis.

Any ideas Rick?

Regards

Chris

Hi,

[quoted text clipped - 15 lines]

Thanks very much

Store it in a date field and forget about formatting. It doesn't matter for
querying anyway. To find records between two dates with a simple self-prompting
parameter...

SELECT *
FROM TableName
WHERE DateField >= [Enter Start Date]
AND DateField < DateAdd("d", 1, [Enter End Date])