Date Search in Date & Time Field

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

--
Message posted via http://www.accessmonster.com

hobbit2612 via AccessMonster.com wrote:
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])

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Rick,

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

Many thanks

Chris

Rick Brandt wrote:
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])


--
Message posted via http://www.accessmonster.com

In article 6e59a53393d8a@uwe, u27332@uwe says...
Rick,

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

Many thanks

Chris

Rick Brandt wrote:
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.

Michael Gramelspacher wrote:
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().

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

In article ,
says...
Michael Gramelspacher wrote:
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#)

Michael Gramelspacher wrote:
In article ,
says...
Michael Gramelspacher wrote:
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?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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.

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

Michael Gramelspacher wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

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

Rick Brandt wrote:
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])


--
Message posted via http://www.accessmonster.com