Access unable to report on items from "Last Week"

[tungkee831]

I have a report set to return a list of items that were opened last week. It has been working flawlessly until this morning 1/2/08. I ran the report which should have returned new opened items from 12/23 - 12/29, but doesn't return anything..

I suspect this is happening because of the change in the year, but I don't have any proof of that. It worked for the last several weeks before, but this morning it doesn't work.

My queries are seeing the same behavior. I have some queries set to search on new items opened the previous week. This morning, the same queries don't return anything.

Any of you guys out there seeing something similar?

Happy New Year otherwise!
-tungkee

 

[boblarson]

Sounds like you might have the year being automatically supplied based on YEAR and so that would make the date 12/23/2008 to 12/29/2008 instead of 2007. You'll have to look around to find it.

Or, you can post your db and we can take a look.

 

[tungkee831]

Sounds like you might have the year being automatically supplied based on YEAR and so that would make the date 12/23/2008 to 12/29/2008 instead of 2007. You'll have to look around to find it.

Or, you can post your db and we can take a look.

Thanks for the quick reply Bob. You may be right. My query uses the following to search on the previous week:

Year([Opened Date])* 53 + DatePart("ww", [Opened Date]) = Year(Date())* 53 + DatePart("ww", Date()) - 1

I got that directly from the Microsoft Access website here: http://office.microsoft.com/en-us/access/HA100666111033.aspx?pid=CH100645771033

I also tried running a quick search using my entire database. I have a table called "issues" and when I do a quick filter for "last week" on my "Opened Date" field, nothing is returned.

-tungkee

 

[tungkee831]

There is definitely something wrong with Access in this regard.... I opened my table and filtered the "Opened date" field using predefined filters Access offers. I select the field "Opened Date"-->Date Filters-->Last week from the drop down menu, and nothing is returned although there are definitely items whose opened date is from last week.

-tungkee

 

[boblarson]

There is definitely something wrong with Access in this regard.... I opened my table and filtered the "Opened date" field using predefined filters Access offers. I select the field "Opened Date"-->Date Filters-->Last week from the drop down menu, and nothing is returned although there are definitely items whose opened date is from last week.

-tungkee

Can you post the db, or at least enough to check it out?

 

[tungkee831]

Can you post the db, or at least enough to check it out?

Hi Bob,

I know posting my DB would be helpful, but I can't post any of it due to security reasons.

I posted the query criteria (see above) I used in some of my queries. Does that help diagnose the problem?

-tungkee

 

[boblarson]

I'm more visual so it helps me to actually see the problem in action because I'll usually find something important that the person describing the problem didn't mention and that I didn't think to ask.

So, unless you can maybe create a copy and strip out the data, and maybe make up a couple of bogus records, I think I'm probably at a loss to help further. I'm particularly interested in the datatype that the date is stored as and the format it is stored as because it is, most likely, not stored in the way that Access is looking for when you select the filter. But, that's just speculation.