Re: need help creating criteria for time

Sorry Allen. Got busy and had to shelf this for a couple days. It shows:
"7/17/2006 2:30pm"

"Allen Browne" wrote:

But did the calculated field show 7/17/2006 2:30pm?

Or did it actually show a different date, such as 2/1/2113 2:20pm?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"bluezcruizer" <bluezcruizer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C97FBF9E-37C2-47E1-AD58-40FF5C915ADC@xxxxxxxxxxxxxxxx
I ran the query with the parameter you gave. It returned all items for
7/17/06. I then removed the criteria and left everything else and ran it
again. This time, it returned all records.

In the form, what is displayed is in h:m AM/PM format. However, the
general
date format shows up if you click on the field.


"Allen Browne" wrote:

Just to be sure, enter the full 4-digit year, and the seconds when the
query
asks for the parameter:
7/17/2006 14:00:00

Now let's verify that these date/time fields have *only* date or time,
not
both in them. Remove the criteria, but leave the Expr1 calculated field
there. Find the record you expect to be returned. What date and time does
it
display?

"bluezcruizer" <bluezcruizer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:119D3C6F-9A5A-4D0D-AF36-52F2F4C3B08B@xxxxxxxxxxxxxxxx
Here's the SQL statement:
PARAMETERS [After what date and time?] DateTime;
SELECT CVDate([TheDate]+[TheTime]) AS Expr1, [Sales Log].[End User],
[Sales
Log].Reseller, [Sales Log].[IM PO#], [Sales Log].Amount, [Sales
Log].[Order
closed]
FROM [Sales Log]
WHERE (((CVDate([TheDate]+[TheTime]))>[After what date and time?]));


You are correct in your assumption; they are date/time fields:
We are assuming here that TheDate and TheTime are both
Date/Time
fields
in your table (not Text fields.)

I've double checked my regional settings, and the "short date" is in
m/d/yyyy format.

Still pulling all records where the date is matched, but the time
doesn't
appear to be tested.

What I'm putting in the box is "7/17/06 2:00pm"

There should be 1 record coming up for "7/17/06 2:30pm"

Thanks --
bluezcruizer


"Allen Browne" wrote:

That should work, so let's work on the assumption that it is not
understanding the data types correctly.

1.) The Expr1 looks right: Access adds an alias like that.
Let's typecast the expression. Try:
Expr1: CVDate([TheDate]+[TheTime])
We are assuming here that TheDate and TheTime are both Date/Time
fields
in
your table (not Text fields.)

2.) It is important to declare the parameter, so Access does a date
comparsion, not a text comparison. Copy the:
[After what date and time?]
from the criteria line (without the >), open the Parameters box, and
enter
it there. If you switch your query to SQL View (View menu, in query
design),
you should now see:
PARAMETERS [After what date and time?] DateTime;
SELECT ...

3.) If that still isn't right, change your Regional Settings in the
Windows
Control Panel so that Short Date shows the 4-digits of the year. This
will
ensure the data is not a century out, as you can see the 2006 instead
of
06.

If it still fails, post the whole SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"bluezcruizer" <bluezcruizer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:2FC925E1-8BD7-42F3-8AFA-EA21CAA33A02@xxxxxxxxxxxxxxxx
Sorry, Allen, I must be doing something wrong. Here's what I've
got:
Field slot 1...
Expr1: [TheDate]+[TheTime] (the "Expr1: " was added by Access)
Table (blank)
Sort (blank)
Show (checked)
Criteria: >[After what date and time?]

I've removed the field listings from the parameters box as it was
asking
the
question twice.

I ran the query as a test I used "7/17/06 2:00pm" in the dialog box
requesting the parameters of the search. Prior to this, I made sure
there
is
a record that would fit the bill -- it is set to 2:30pm on 7/17/06.
The
query returned zero results.

Thank you for your continued help Allen.


"Allen Browne" wrote:

In query design, type this expression into the *Field* row:
[TheDate] + [TheTime]

You can now put your criteria in the Criteria row under this field.

"bluezcruizer" <bluezcruizer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:89F9C3AE-9C89-405F-9C7D-C6AB402A3C5C@xxxxxxxxxxxxxxxx
I'm not completely sure I follow...we'll have to go with 2
different
fields
because of other requirements.

I've changed the field names to "TheDate" and "TheTime" as per
your
recommendations.

So in my query, I have the field names, the first two are
"TheDate"
and
"TheTime"

How do I list these in the parameters box that pulls up? Do I
leave
anything in the Criteria for those fields?

Thank you.
bluezcruizer

"Allen Browne" wrote:

Simplest solution would be to use just one field that contains
the
date
and
time. You could the type your criteria into the query as:
> [After what date and time?]
(Note: include the greater than sign.)
Then choose Parameters on the Query menu.
In the dialog that opens, enter:
[After what date and time?] Date/Time
All done.

If you want to continue with the 2 different fields, make sure
they
are
not
named Date and Time. Those are both reserved words in JET. We
will
use
TheDate and TheTime for this example.

Now in your query, enter this into the Field row:
[TheDate] + [TheTime]
and then continue to add the criteria and declare the parameter
in
the
same
way as desribed above for a combined date/time field.

to group, rather than allenbrowne at mvps dot org.

"bluezcruizer" <bluezcruizer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:2879FBF4-CEFE-4660-A74B-6FB0946BE0A3@xxxxxxxxxxxxxxxx
Hi.
I'm trying to create a query that will return records from a
specified
date
and after a specified time.

So far, I've got the table set with 2 fields; one for "date"
and
the
other
for "time". Both are auto inputted as the record is
generated.

The query I've got I have the date criteria as
[Date?]
to
return records for a specified date, and am having trouble
with
the
time
side
of the coin. I've currently got >[Time?] but
that
seems
to
return any records regardless of what time it was inputted.

So what I'm trying to do is return say any records inputted
after
2:30pm
on
7/17/06.



.

Relevant Pages

  • Re: need help creating criteria for time
    ... Allen Browne - Microsoft MVP. ... I then removed the criteria and left everything else and ran it ... Now let's verify that these date/time fields have *only* date or time, ... If you switch your query to SQL View (View menu, ...
    (microsoft.public.access.queries)
  • Re: need help creating criteria for time
    ... Both fields are Date/Time type. ... Allen Browne - Microsoft MVP. ... I then removed the criteria and left everything else and ran ... If you switch your query to SQL View (View menu, ...
    (microsoft.public.access.queries)
  • Re: part of multi search query non-functional
    ... solution would be to leave the criteria out of the query, ... Search form - Handle many optional criteria ... Allen Browne - Microsoft MVP. ... Locate the WHERE clause. ...
    (microsoft.public.access.queries)
  • Re: need help creating criteria for time
    ... You are correct in your assumption; they are date/time fields: ... If you switch your query to SQL View, ... post the whole SQL statement. ... You can now put your criteria in the Criteria row under this field. ...
    (microsoft.public.access.queries)
  • Re: need help creating criteria for time
    ... Just to be sure, enter the full 4-digit year, and the seconds when the query ... Now let's verify that these date/time fields have *only* date or time, ... Remove the criteria, but leave the Expr1 calculated field ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
Quantcast