Date Query

[dsthome]

Hi Friday Brain again
I have a query that I run to give me a record of what what issued on a certain day I just used [DD/MM/YY] and then type the dat I require in when run, which has worked fine until I changed the dates that were entered to include the time. Now I run the query is comes up blank. What do I need to cheange in the query so can I get it to pick up everything that was done on that day?

Thanks

 

[pr2-eugin]

Hello dsthome, if the field in the table that you are Querying to hold Date and Time then this would be quiet tricky !!

As you know Date() is not equal to Now().. You should either revert back to the old style of storing only dates or use a Criteria that will be something like..

SELECT * FROM [COLOR=Blue]theTable[/COLOR] WHERE [COLOR=Blue]dateTimeField[/COLOR] BETWEEN  CDate([EnterTheDate] & " 00:00:01") AND CDate([EnterTheDate] & " 23:59:59")

Please Note: The above code is not tested but in theory should work..

 

[RainLover]

Hi Friday Brain again
I have a query that I run to give me a record of what what issued on a certain day I just used [DD/MM/YY] and then type the dat I require in when run, which has worked fine until I changed the dates that were entered to include the time. Now I run the query is comes up blank. What do I need to cheange in the query so can I get it to pick up everything that was done on that day?

Thanks

You need Between [StartDate] And [EndDate]

If you wanted for one day it would be Between say 01/01/2013 and 02/01/2013. (Non US Dates)

This is a Full day.

02/01/2013 = Midnight. The start of the day.

There is no need to use Minutes.

 

[oblivion86]

Hi,

I some questions about how to query between 2 dates.

I have a multi field search form which has [Startdate] and [Enddate] as one of the 2 fields.

I understand that I have to use >= [Forms]![searchform]![StartDate] and <= [Forms]![searchform]![EndDate] in the query.

This works fine if I have just one field in the search form.

I understand that if I have multiple fields and 1 of the fields might be blank when searching I have to write the query like this:
[forms]![searchform]![street] OR [forms]![searchform]![street] Is Null

How do i translate this concept into the query for between 2 dates?

Do I write it like this:
>= [Forms]![searchform]![StartDate] and <= [Forms]![searchform]![EndDate] OR [forms]![searchform]![StartDate] Is Null AND [forms]![searchfrom]![EndDate] is Null ?????

Appreciate your help greatly.

Thanks

 

[Brianwarnock]

Yes, but for clarity I would do some bracketing
Eg (a and b) or (c and d)

Brian

 

[RainLover]

I could be wrong but I have done some thinking,

If the previous statement worked then all that is required is to add 1 (+ 1) to the End Date. Nothing else..

Brian am I correct.

 

[RainLover]

Hi,

>= [Forms]![searchform]![StartDate] and <= [Forms]![searchform]![EndDate] + 1.

I think that this should do it. All that is happening is that we are going into the next day so we need one more day.

 

[Brianwarnock]

I could be wrong but I have done some thinking,

If the previous statement worked then all that is required is to add 1 (+ 1) to the End Date. Nothing else..

Brian am I correct.

If you are talking about the guy who added times to his data then you cannot just say startdate and enddate you must get rid of the times so that you effectively compare against
1/1/2013 00:00:00 for example, I use Datevalue on the data field and then everything is simple.

The reason I say this is something just may happen at midnight.

Brian

 

[RainLover]

The reason I say this is something just may happen at midnight.

But Midnight is simply the next day, It can't be both.

 

[Brianwarnock]

But that is my point people search on
Between startdate and enddate +1
so that they catch all of the data with a date of enddate plus any time, but they would also catch midnight which is the following day. It should be

Enddate +#23:59:59#

Searching on mydate: Datevalue(mydatetime)
Removes all problems

Brian

 

[RainLover]

But that is my point people search on
Between startdate and enddate +1
so that they catch all of the data with a date of enddate plus any time, but they would also catch midnight which is the following day. It should be

Enddate +#23:59:59#

Searching on mydate: Datevalue(mydatetime)
Removes all problems

Brian

Midnight is the next day. so 1 April + 1 includes 2 April but not Midnight as it becomes 3 April. Your CVal should prove this.

Signing off now. Bye. Back tomorrow.

 

[Brianwarnock]

Midnight is the next day. so 1 April + 1 includes 2 April but not Midnight as it becomes 3 April. Your CVal should prove this.

Signing off now. Bye. Back tomorrow.

We are talking about data with times thus an enddate of 1 April +1 would only select anything at midnight on the 1st ie 2/4/2013 00:00:00 , it would not include any other records for the 2 April.

Brian

 

[oblivion86]

hi guys,

Thanks for your help, the bracketing method worked!

I would like to also apply this to search for persons' age (e.g. Age from 14 - 25), just to verify the above stated query should also work right?

 

[RainLover]

Brian
Brian

The op appears happy.

Would you like to continue our debate or save it for another day.

but not now. Had a busy day and off to bed. Catch you tomorrow.

 

[Brianwarnock]

hi guys,

Thanks for your help, the bracketing method worked!

I would like to also apply this to search for persons' age (e.g. Age from 14 - 25), just to verify the above stated query should also work right?

If you know their age then sure between 14 and 25 will find those ages inclusively that is Between ... And... Is the equivalent of >= and <=

Brian