need help creating criteria for time

[Guest]

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.

Please help -- thanks.

bluezcruizer

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

Please help -- thanks.

bluezcruizer

 

[Guest]

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

Please help -- thanks.

bluezcruizer

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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.

Please help -- thanks.

bluezcruizer

 

[Guest]

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.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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.

Please help -- thanks.

bluezcruizer

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

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

:

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.

message
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.

 

[Guest]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

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

:

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.

message
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.

 

[Allen Browne]

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?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.


:

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.

message
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

:

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.

message
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.

 

[Guest]

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.

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?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.


:

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.

message
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

:

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.

message
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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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?

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


:

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.

Reply to group, rather than allenbrowne at mvps dot org.

message
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.


:

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.

message
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

:

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.

message
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.

 

[Guest]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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?

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


:

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.

Reply to group, rather than allenbrowne at mvps dot org.

message
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.


:

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.

message
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

:

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.

message
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.

 

[Allen Browne]

I don't know what else to suggest.

Reviewing the thread:
- Both fields are Date/Time type.
- You changed their names so the names are not ambiguous.
- You declared the parameter, so its type will be correct.
- You included CVDate() around the calculation, so the calculated field type
will be correct.
- You verified that the sum of date and time is correct.
- You disabled Name AutoCorrect, so there is no chance of misundersanding
the names.
- You did a compact/repair, so any indexes are rebuilt.

I can't see how JET can still get it wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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?

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.


:

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?

message
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


:

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.

Reply to group, rather than allenbrowne at mvps dot org.

message
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.


:

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.

message
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

:

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.

in
message
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.