If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Date Search in Date & Time Field
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 |
#3
|
|||
|
|||
Date Search in Date & Time Field
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 |
#4
|
|||
|
|||
Date Search in Date & Time Field
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. |
#5
|
|||
|
|||
Date Search in Date & Time Field
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 |
#6
|
|||
|
|||
Date Search in Date & Time Field
|
#8
|
|||
|
|||
Date Search in Date & Time Field
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. |
#9
|
|||
|
|||
Date Search in Date & Time Field
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 |
#10
|
|||
|
|||
Date Search in Date & Time Field
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 |
Thread Tools | |
Display Modes | |
|
|