Differences the date by time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Example : Assume a have many transactions on between 11/27/2004 (10.00PM) -
11/28/200 (6.00AM).
How do i create a query on this?I have try to create a query criteria with
input the date n time but is not working.My input was 11/27/2004 and the
start time 10.00PM to 6.00AM.
Could anybody help me on this.Thx a lot.
 
Jerry C.k schreef op 27-11-2004 :
Example : Assume a have many transactions on between 11/27/2004 (10.00PM) -
11/28/200 (6.00AM).
How do i create a query on this?I have try to create a query criteria with
input the date n time but is not working.My input was 11/27/2004 and the
start time 10.00PM to 6.00AM.
Could anybody help me on this.Thx a lot.
Click to expand...

You mean BETWEEN <start date and time> AND <end date and time>
 
On Sat, 27 Nov 2004 05:45:03 -0800, Jerry C.k <Jerry
Example : Assume a have many transactions on between 11/27/2004 (10.00PM) -
11/28/200 (6.00AM).
How do i create a query on this?I have try to create a query criteria with
input the date n time but is not working.My input was 11/27/2004 and the
start time 10.00PM to 6.00AM.
Could anybody help me on this.Thx a lot.
Click to expand...

An Access Date/Time field is stored internally as a double float
number, a count of days and fractions of a day since an arbitrary
start point (#12/30/1899 00:00:00#). The date is the integer part of
the number, and the time is the fractional part. If you want to
generate this time range given only a date, I'd suggest using a
criterion like
= DateAdd("h", 22, CDate([Enter date:])) AND < DateAdd("h", 30, CDate([Enter date:]))
Click to expand...

The entered date will have 0 as a fractional portion - midnight; the
first criterion will add 22 hours to that time, to get 10 pm; the
second criterion will return 6am on the following day.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thx..
But the problem is my daily transaction time was started at 6.00AM to 6.00AM
next day.So how could i different the day instead of 00:00:00 to 23:59:59
change to 06:00:00 to 05:59:59.


John Vinson said:
On Sat, 27 Nov 2004 05:45:03 -0800, Jerry C.k <Jerry
Example : Assume a have many transactions on between 11/27/2004 (10.00PM) -
11/28/200 (6.00AM).
How do i create a query on this?I have try to create a query criteria with
input the date n time but is not working.My input was 11/27/2004 and the
start time 10.00PM to 6.00AM.
Could anybody help me on this.Thx a lot.
Click to expand...

An Access Date/Time field is stored internally as a double float
number, a count of days and fractions of a day since an arbitrary
start point (#12/30/1899 00:00:00#). The date is the integer part of
the number, and the time is the fractional part. If you want to
generate this time range given only a date, I'd suggest using a
criterion like
= DateAdd("h", 22, CDate([Enter date:])) AND < DateAdd("h", 30, CDate([Enter date:]))
Click to expand...

The entered date will have 0 as a fractional portion - midnight; the
first criterion will add 22 hours to that time, to get 10 pm; the
second criterion will return 6am on the following day.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Thx..
But the problem is my daily transaction time was started at 6.00AM to 6.00AM
next day.So how could i different the day instead of 00:00:00 to 23:59:59
change to 06:00:00 to 05:59:59.
Click to expand...

Ok... in your original post you said

That's the question I answered. My telepathy wasn't working; you were
THINKING that the transaction time starts at 6.00am but you didn't say
so.

Just use a criterion of

BETWEEN DateAdd("h", 6, CDate([Enter date:])) AND DateAdd("h", 6,
CDate([Enter date:]) + 1)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
ok..i have try to insert the criteria but is seem does not get what i
wan..when i enter the date assume 11/23/2004 it does not show the transaction
on 11/23/2004 6.00AM to 12.00PM but just show the whole transaction day on
11/24/2004

Example that i wan...assume 6.00AM to 6.00AM

TransactionDate Time
11/23/2004 10.00AM
11/23/2004 6.00PM
11/23/2004 11.00PM
11/24/2004 1.00AM
11/24/2004 5.00AM
11/24/2004 6.00AM
unitl 6.00AM....sorry for disturb you but i know u could help me on this
matter..thx a lot..
John Vinson said:
Thx..
But the problem is my daily transaction time was started at 6.00AM to 6.00AM
next day.So how could i different the day instead of 00:00:00 to 23:59:59
change to 06:00:00 to 05:59:59.
Click to expand...

Ok... in your original post you said

That's the question I answered. My telepathy wasn't working; you were
THINKING that the transaction time starts at 6.00am but you didn't say
so.

Just use a criterion of

BETWEEN DateAdd("h", 6, CDate([Enter date:])) AND DateAdd("h", 6,
CDate([Enter date:]) + 1)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
ok..i have try to insert the criteria but is seem does not get what i
wan..when i enter the date assume 11/23/2004 it does not show the transaction
on 11/23/2004 6.00AM to 12.00PM but just show the whole transaction day on
11/24/2004

Example that i wan...assume 6.00AM to 6.00AM

TransactionDate Time
11/23/2004 10.00AM
11/23/2004 6.00PM
11/23/2004 11.00PM
11/24/2004 1.00AM
11/24/2004 5.00AM
11/24/2004 6.00AM
unitl 6.00AM....sorry for disturb you but i know u could help me on this
matter..thx a lot..
Click to expand...

I'm sorry, I am obviously misunderstanding your question.

What do you want to input as criteria for the query? Where do you want
to input it? What records do you want to find? What records do you NOT
want to find?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Assume
I have 3 field in my query

Counter TransactionDate Time
C1 23/11/2004 8.00AM
C1 23/11/2004 10.00PM
C2 23/11/2004 11.00PM
C1 24/11/2004 1.00AM
C1 24/11/2004 5.00AM
C2 24/11/2004 7.00AM
C1 24/11/2004 8.00AM
C1 24/11/2004 10.00PM
CI 25/11/2004 1.00AM

I would like to retreive the counter by using the date and time....example
enter the begin date (23/11/2004 and 10.00PM) to end date(24/11/2004 and
6.00AM) The data will be in between the starting datetime on 23/11/2004 to
the end datetime.
The data will be capture like below
Counter TransactionDate Time
C1 23/11/2004 10.00PM
C2 23/11/2004 11.00PM
C1 24/11/2004 1.00AM
C1 24/11/2004 5.00AM

Thx...
 
Jerry C.k stelde dit idée voor :
Assume
I have 3 field in my query

Counter TransactionDate Time
C1 23/11/2004 8.00AM
C1 23/11/2004 10.00PM
C2 23/11/2004 11.00PM
C1 24/11/2004 1.00AM
C1 24/11/2004 5.00AM
C2 24/11/2004 7.00AM
C1 24/11/2004 8.00AM
C1 24/11/2004 10.00PM
CI 25/11/2004 1.00AM

I would like to retreive the counter by using the date and time....example
enter the begin date (23/11/2004 and 10.00PM) to end date(24/11/2004 and
6.00AM) The data will be in between the starting datetime on 23/11/2004 to
the end datetime.
The data will be capture like below
Counter TransactionDate Time
C1 23/11/2004 10.00PM
C2 23/11/2004 11.00PM
C1 24/11/2004 1.00AM
C1 24/11/2004 5.00AM

Thx...
Click to expand...

The first problem is that your date and time fields are separated from
each other.
The second problem is that you have to compare the date and time
_together_ in order to determine if a record is between the entered
values.

The solution :
Add a field to your query :
Expr1: CDate([transactionDate] & " " & [time])
In that column, add the following criteria:
Between CDate([BeginDate] & " " & [BeginTime]) And CDate([EndDate] & "
" & [EndTime])
Remove all other criteria
Set up parameters for BeginDate, BeginTime, EndDate and EndTime (all
date/time)

Now when you run the query, and enter your citeria 23/11/2004,
24/11/2004, 10.00PM and 6.00AM, the correct data shows up...
 
Thx u very much...

Gijs Beukenoot said:
Jerry C.k stelde dit idée voor :
Assume
I have 3 field in my query

Counter TransactionDate Time
C1 23/11/2004 8.00AM
C1 23/11/2004 10.00PM
C2 23/11/2004 11.00PM
C1 24/11/2004 1.00AM
C1 24/11/2004 5.00AM
C2 24/11/2004 7.00AM
C1 24/11/2004 8.00AM
C1 24/11/2004 10.00PM
CI 25/11/2004 1.00AM

I would like to retreive the counter by using the date and time....example
enter the begin date (23/11/2004 and 10.00PM) to end date(24/11/2004 and
6.00AM) The data will be in between the starting datetime on 23/11/2004 to
the end datetime.
The data will be capture like below
Counter TransactionDate Time
C1 23/11/2004 10.00PM
C2 23/11/2004 11.00PM
C1 24/11/2004 1.00AM
C1 24/11/2004 5.00AM

Thx...
Click to expand...

The first problem is that your date and time fields are separated from
each other.
The second problem is that you have to compare the date and time
_together_ in order to determine if a record is between the entered
values.

The solution :
Add a field to your query :
Expr1: CDate([transactionDate] & " " & [time])
In that column, add the following criteria:
Between CDate([BeginDate] & " " & [BeginTime]) And CDate([EndDate] & "
" & [EndTime])
Remove all other criteria
Set up parameters for BeginDate, BeginTime, EndDate and EndTime (all
date/time)

Now when you run the query, and enter your citeria 23/11/2004,
24/11/2004, 10.00PM and 6.00AM, the correct data shows up...

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise.
Click to expand...
 
Back
Top