Query for the current day's new records

[Maggie23]

Hi -
I had originally posted my question under reports but with the help I got for part of it I think I should have posted under queries.

I am trying to create a query that would show me the first and last ID# and Lname of new records added during the day. It was suggested to try ">Date ( ) -7". When I tried it I got this message: Data type mismatch in criteria expression. I am using the criteria cell of my date field in the query. The date field in the table I am querying is set to short date. So I'm not sure what I am doing wrong.

Also if I change the 7 to a 1 would it show just the records for the day? That's what I really need.

Thanks in advance,
Maggie

 

[Jon K]

For a date field, do not use the double quotes in the criteria. Double quotes are for text only.


To return all the records entered during the day, just put Date() in the criteria cell for the date field.

Date()-1 will return records entered yesterday (i.e. today's date minus 1 day).

 

[Mile-O]

Personally, I prefer to use the DateAdd function in criteria.

i.e.

>=DateAdd("d", -7, Date())

 

[Maggie23]

Thanks for your help - a little more please.

TheDate() criteria worked and listed all the records for today. Is it possible to list only the beginning ID and ending record ID for the day? I just want to show the range of IDs entered for the current date not every record ID. Is that possible?

Also, I tried the DateAdd function after I had entered 2 new records. All I got back was a blank line.

Maggie

 

[Mile-O]

TheDate() criteria worked and listed all the records for today. Is it possible to list only the beginning ID and ending record ID for the day? I just want to show the range of IDs entered for the current date not every record ID. Is that possible?

When you say beginning ID and ending ID, what makes the range?

Do you store a time for each entry into your database? If so you can use the Max and Min totals to return what you wish.

If not, clarify further please...


Also, in which way did you use the DateAdd() function?

 

[Jon K]

Assuming ID is a numeric field, you can return the Min and Max values of the IDs for the current day by means of a Totals Query (in query design, click on the Totals button on the tool bar to display the Total: row in the query grid).

I have attached a DB as an illustration. You can open the query in design view to see how the query is built.


I noted from your thread in the Reports forum that you wanted to show the range on a form. So I have also included a form in the DB to show these two values in two text boxes. I used the DLookup() function in their Control Source.


(The attached DB is in Access 97 format. To open it in Access 2000 or Access 2002, choose Convert and save as a new name when the DB is opened for the first time.)

 

[Maggie23]

Thanks all so much for your help. Jon the example was great. (I see a couple of others downloaded it also). I am fairly new to Access and this project is a case of volunteering to put together a simple little database for a library that has grown into something way past me - but I'm trying. Thanks again for your help.

Maggie