Query for data entered yesterday

[ardagh]

This question has probably been answered hundreds of times. I did search the FAQ's and forum and could not find anything.

How do I query for all entries created yeaterday. I.e. The date will not have to be entered. The query will automatically give me all yesterday's details based on querying the date field.

Any help would be appreciated.
Niall

 

[Crowley16]

where date = (format(now(), "medium date") - 1)

 

[ardagh]

Thanks for the reply but I don't understand how to use this information you provided.

Do I use it is the criteria field of the date entity in a query. Is it a SQL command??? Do I enter the line exactly as you provided etc.

Also what does 'medium date' mean.

I am relatively new to Access.

thank you for your help

 

[Crowley16]

(format(now(), "medium date") - 1) is what you would put in the criteria field of the date field in your query

 

[ardagh]

I have enter this exactly as above and I am getting a 'data mismatch in the criteria expression'.

I have changed the date field in the table from short date to Medium daate and the error still occurs.

I have Now() as the default value for the field.

What am I doing wrong

 

[Crowley16]

I think you are entering the wrong things in the wrong places in your query builder...

why not goto the sql view, and edit the condition directly...

 

[ardagh]

This is the SQL statement I have tried to enter.

SELECT timesheet.ID, timesheet.OperatorName, timesheet.Date, timesheet.Customer, timesheet.WorkOrderNo, timesheet.Operation, timesheet.Qty, timesheet.Hours, timesheet.Rejects, timesheet.Comments
FROM timesheet
where timesheet.Date = (format(now(), "medium date") - 1);

I am still getting the data mismatch in criteria expression error. I must have the date data type messed up.

I have tried to query this field putting in a date e.g. 07/07/2004 and I am getting no results but if I enter the date in this format 07/07/2004* I am getting the correct result.

This would suggest to me that the criteria field needs more than just the date (may need time).

I have been informed that the working day here is from 8am to 8am (three cycle shift) and this would have to constitute a day. The date would be the start 8am. Also on Monday the report would have to show results for Friday and over the weekend (Saturday and Sunday).

Any ideas how I could automate this.

 

[PHV]

Have you tried this in the criteria ?
Date() - 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[flyover789]

based on PHV's suggestion I found a solution (you have to format the field to be queried same as the criterion):

WHERE format(timesheet.Date,"short date") = Date()-1;

and BTW, always try to avoid using reserved words like 'Date' as fieldnames or controlnames in a DB!

HTH,
fly



[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com