Return records entered today

[Rebeca]

How do I write the query to return any records entered today where the
date field is in general format?

I've tried now() which of course returns null. I know that I need
between 12:00:00 AM and 11:59:59 PM as part of the criteria, I just
can't figure out the date part.

Thanks!!!

 

[John Spencer]

The where clause in a query would look like the following to get data for today

WHERE SomeDateField >= Date() and SomeDateField < DateAdd("D",1,Date())

In the query design view, that would be something like:
Field: SomeDateField
Criteria: >= Date() and < DateAdd("D",1,Date())

or an alternative that might be slower would be:
Field: DateValue([SomeDateField])
Criteria: Date()

If you need to show the actual date and time then keep someDatefield visible
and don't show DateValue([SomeDateField]) in the results.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

Rebeca

Now() returns the date/time value for right NOW.

Date() returns today's date.

What's being stored in the field that you are trying to compare to?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rebeca]

Rebeca

Now() returns the date/time value for right NOW.

Date() returns today's date.

What's being stored in the field that you are trying to compare to?

Regards

Jeff Boyce
Microsoft Office/Access MVP








- Show quoted text -

Thank you for taking the time to answer my question. I have two
fields, a datestamp and a batch date (which is the date of the batch,
may or not be today). I decided shortly after I posted the question
that the time is not necessary for what we are doing so I just changed
the format of the field to short date. Users tend to not enter the
correct date when entering their batch information, so I have to have
a datestamp in there for reporting purposes in case we need to locate
an error.

Thanks again!

 

[Jeff Boyce]

This may be simply a matter of semantics, but when you say you changed the
"format" of the field, in Access, "format" governs how a value is displayed,
not what value is actually stored.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Rebeca

Now() returns the date/time value for right NOW.

Date() returns today's date.

What's being stored in the field that you are trying to compare to?

Regards

Jeff Boyce
Microsoft Office/Access MVP








- Show quoted text -

Thank you for taking the time to answer my question. I have two
fields, a datestamp and a batch date (which is the date of the batch,
may or not be today). I decided shortly after I posted the question
that the time is not necessary for what we are doing so I just changed
the format of the field to short date. Users tend to not enter the
correct date when entering their batch information, so I have to have
a datestamp in there for reporting purposes in case we need to locate
an error.

Thanks again!

 

[John W. Vinson]

I decided shortly after I posted the question
that the time is not necessary for what we are doing so I just changed
the format of the field to short date.

Be careful! A Date/Time field always contains a time component (if it's zero,
it corresponds to midnight). Changing the format does NOT get rid of the time
- it merely conceals it from view, and it can cause huge problems if you have
what *looks* like #8/28/09# in a field but the field actually contains
#8/28/09 00:29:16#.

You can by all means do this (by populating the field with pure date data) but
NOT by changing the Format property of the field.

 

[Rebeca]

Be careful! A Date/Time field always contains a time component (if it's zero,
it corresponds to midnight). Changing the format does NOT get rid of the time
- it merely conceals it from view, and it can cause huge problems if you have
what *looks* like #8/28/09# in a field but the field actually contains
#8/28/09 00:29:16#.

You can by all means do this (by populating the field with pure date data) but
NOT by changing the Format property of the field.

Good to know

Thanks!

 

[Ron2006]

IF you load the time stamp with Now()


THEN

You can find all records entered TODAY by having the criteria

 

[Rebeca]

IF   you load the time stamp with    Now()

THEN

You can find all records entered TODAY  by having the criteria

           >Date()

Sorry, life got in the way and I dropped the conversation for a few
days. Here is what I need to accomplish:

I have a query with the following relevant (to my question) fields.

Date Entered in Log (the timestamp field)
Windows Login

The purpose of the query is for users to double-check their entries.

To activate this query, the users select a button on a form entitled
View Batches.
The query then will prompt them to "Enter the Date the Batches Were
Logged" and "Enter Username"

When I enter for example 08/19/09 and a username, there are a bunch of
records that should be returned, but I am not getting anything. What
do I need to do to the criteria to have users answer the question,
then compare that against the timestamp to have the records returned?

Hope this makes sense.

 

[Rebeca]

IF   you load the time stamp with    Now()

THEN

You can find all records entered TODAY  by having the criteria

           >Date()

Thanks everyone for your suggestions. Upon further review, I need to
modify my query from what I stated above.

Here is what I need to accomplish:

I have a query with the following relevant (to my question) fields.

Date Entered in Log (the timestamp field)
Windows Login


The purpose of the query is for users to double-check their entries.
To activate this query, the users select a button on a form entitled
View Batches. The query then will prompt them to "Enter the Date the
Batches Were
Logged" and "Enter Username"

When I enter for example 08/19/09 and a username, there are a bunch
of
records that should be returned, but I am not getting anything. What
do I need to put in the criteria to have users answer the question,
then compare that against the timestamp to have the records returned?

Hope this makes sense.
Thanks!

 

[John Spencer]

Change the criteria to

WHERE [date entered in Log] Between [Enter Date] and
DateAdd("d",1,[Enter Date])

Or if you might have records that were entered at exactly midnight.

WHERE [date entered in Log] >= [Enter Date]
and [date entered in Log] < DateAdd("d",1,[Enter Date])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Rebeca]

Change the criteria to

WHERE [date entered in Log] Between [Enter Date] and
DateAdd("d",1,[Enter Date])

Or if you might have records that were entered at exactly midnight.

WHERE [date entered in Log] >= [Enter Date]
and [date entered in Log] < DateAdd("d",1,[Enter Date])

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

Thanks everyone for your suggestions. Upon further review, I need to
modify my query from what I stated above.

Here is what I need to accomplish:

I have a query with the following relevant (to my question) fields.

Date Entered in Log (the timestamp field)
Windows Login

The purpose of the query is for users to double-check their entries.
To activate this query, the users select a button on a form entitled
View Batches. The query then will prompt them to "Enter the Date the
Batches Were
Logged" and "Enter Username"

When I enter for example 08/19/09 and a username, there are a bunch
of
records that should be returned, but I am not getting anything. What
do I need to put in the criteria to have users answer the question,
then compare that against the timestamp to have the records returned?

Hope this makes sense.
Thanks!- Hide quoted text -

- Show quoted text -

Perfect! Thank you so much. I don't think I would have ever figured
that out

 

[Rebeca]

Change the criteria to

WHERE [date entered in Log] Between [Enter Date] and
DateAdd("d",1,[Enter Date])

Or if you might have records that were entered at exactly midnight.

WHERE [date entered in Log] >= [Enter Date]
and [date entered in Log] < DateAdd("d",1,[Enter Date])

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

- Show quoted text -

Perfect! Thank you so much. I don't think I would have ever figured
that out - Hide quoted text -

- Show quoted text -

It turns out that I spoke too soon. It does report entries, just not
everyones. I didn't think this would have anything to do with it, but
apparently it does.
I also have a table joined (1-1) on Batch ID that lists the
corresponding entries for Cash/Check, Electronic, or Credit for the
Payments field. If those fields are included in my query, some users
data is not being displayed. If they are removed, everyone's is
displayed.

Any ideas?

 

[John Spencer]

CHange the link on the table you have added to show All matches in the other
table and only matches in the added table.

You can change the link by double clicking on the line joining the tables and
selecting the appropriate option (either 2 or 3) from the dialog box.

If that fails, then post the SQL of your query, so someone can show you the
needed change.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Rebeca]

CHange the link on the table you have added to show All matches in the other
table and only matches in the added table.

You can change the link by double clicking on the line joining the tablesand
selecting the appropriate option (either 2 or 3) from the dialog box.

If that fails, then post the SQL of your query, so someone can show you the
needed change.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County





   Snip



- Show quoted text -

Thanks for the reply. I worked around it. I actually created a
separate query that contained everything I needed and used that one
for my report. I had played with the joins this morning and wasn't
getting anywere. It works fine now. Thanks again for all of your
help! I learn something new everyday