Date Search in Date & Time Field

  • Thread starter Thread starter hobbit2612 via AccessMonster.com
  • Start date Start date
H

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
 
hobbit2612 said:
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
Click to expand...

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,

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

Many thanks

Chris

Rick said:
Hi,
Click to expand...
[quoted text clipped - 15 lines]
Thanks very much
Click to expand...

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])
Click to expand...
 
Rick,

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

Many thanks

Chris

Rick said:
Hi,
Click to expand...
[quoted text clipped - 15 lines]
Thanks very much
Click to expand...

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])
Click to expand...
Click to expand...
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 said:
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.
Click to expand...

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 said:
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.
Click to expand...

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().
Click to expand...
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 said:
Michael said:
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.
Click to expand...

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().
Click to expand...
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#)
Click to expand...

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?
 
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?
Click to expand...

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
 
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 said:
Hi,
Click to expand...
[quoted text clipped - 15 lines]
Thanks very much
Click to expand...

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])
Click to expand...
 
Back
Top