General Time Conversion

[Guest]

Okay Here is a good one, I bet it is an easy one though.

I have two fields for Time Entry [StartTime] and [EndTime]

I used general time entry so I see 11/03/06 02:30, now I want to create a
query that will display only entering a date 11/03/06, all entries for that
date.

I Believe that I am going to have to create another field in the query and
enter a conversion formula but I don't know the formula.

Now I am going to have to create two Queries and merge them together because
I want to show everything that had a [StartDate] or a [EndDate] for the same
day. In other words I want to know what came in or left out. The question
here is it possible to have one criteria date entry, instead of having to
enter the date twice?

 

[Rick Brandt]

Okay Here is a good one, I bet it is an easy one though.

I have two fields for Time Entry [StartTime] and [EndTime]

I used general time entry so I see 11/03/06 02:30, now I want to
create a query that will display only entering a date 11/03/06, all
entries for that date.

I Believe that I am going to have to create another field in the
query and enter a conversion formula but I don't know the formula.

Now I am going to have to create two Queries and merge them together
because I want to show everything that had a [StartDate] or a
[EndDate] for the same day. In other words I want to know what came
in or left out. The question here is it possible to have one
criteria date entry, instead of having to enter the date twice?

WHERE (StartTime >= [Enter Date]
AND StartTime < DateAdd("d", 1, [Enter Date])
OR (EndTime >= [Enter Date]
AND EndTime < DateAdd("d", 1, [Enter Date])

As long as the parameter marker [Enter Date] is exactly the same in al four
cases above you will only be prompted for the value once.

It's never a good idea to apply criteria to an expression unless that is the ony
way to do it as you lose the ability for an index to be used by the query
engine. Using an expression AS your criteria is fine though as long as the
expression does not reference any fields since that only needs to be evaluated
one time.

 

[Guest]

Okay can we start with where exactly would I list the equation you gave me.
I am really new at queries.

Okay Here is a good one, I bet it is an easy one though.

I have two fields for Time Entry [StartTime] and [EndTime]

I used general time entry so I see 11/03/06 02:30, now I want to
create a query that will display only entering a date 11/03/06, all
entries for that date.

I Believe that I am going to have to create another field in the
query and enter a conversion formula but I don't know the formula.

Now I am going to have to create two Queries and merge them together
because I want to show everything that had a [StartDate] or a
[EndDate] for the same day. In other words I want to know what came
in or left out. The question here is it possible to have one
criteria date entry, instead of having to enter the date twice?

WHERE (StartTime >= [Enter Date]
AND StartTime < DateAdd("d", 1, [Enter Date])
OR (EndTime >= [Enter Date]
AND EndTime < DateAdd("d", 1, [Enter Date])

As long as the parameter marker [Enter Date] is exactly the same in al four
cases above you will only be prompted for the value once.

It's never a good idea to apply criteria to an expression unless that is the ony
way to do it as you lose the ability for an index to be used by the query
engine. Using an expression AS your criteria is fine though as long as the
expression does not reference any fields since that only needs to be evaluated
one time.

 

[fredg]

Okay Here is a good one, I bet it is an easy one though.

I have two fields for Time Entry [StartTime] and [EndTime]

I used general time entry so I see 11/03/06 02:30, now I want to create a
query that will display only entering a date 11/03/06, all entries for that
date.

I Believe that I am going to have to create another field in the query and
enter a conversion formula but I don't know the formula.

Now I am going to have to create two Queries and merge them together because
I want to show everything that had a [StartDate] or a [EndDate] for the same
day. In other words I want to know what came in or left out. The question
here is it possible to have one criteria date entry, instead of having to
enter the date twice?

Here is one way.
Add a new column to the query grid.
WhatDate:Format([DateField],"mm/dd/yyyy")
As criteria on this new column, write:
[Enter the date mm/dd/yyyy]

 

[Rick Brandt]

Okay can we start with where exactly would I list the equation you
gave me. I am really new at queries.

It is difficult in these groups to describe how to do things in the graphical
query designer so we tend to use the SQL for the query. Apply any simple
criteria to your query and then switch to SQL view. Once there you should see
(near or at the end) a WHERE clause. You would replace that with what I posted.
Then you can switch back to the normal deisgn view to see what that looks like.

 

[Guest]

Holy Moly it works perfectly, That is why I love it when you answer my
questions... You are one of my favorites.....

Thanks Jen

 

[Rick Brandt]

Holy Moly it works perfectly, That is why I love it when you answer my
questions... You are one of my favorites.....

Thanks Jen

Well thanks for that. You just made my day.