return records from last week - DATE help 2

[Trope]

I have a field called dateCreated.

How would I return all rows that were created last week?

I have tried, but to no avail.

Thanks
Trope

 

[dhookom]

Try
Format([dateCreated],"yyyyww&quot = Format(Date()-7,"yyyyww&quot

Duane
MS Access MVP

 

[TonyJollans]

Hi Trope,

Assuming last week is actually any time over the preceding seven days rather than exactly seven days ago, you should probably use ..

[dateCreated] Between Date()-7 And Date()

(or should it be Date()-6?)

Enjoy,
Tony

 

[dhookom]

Good catch Tony. I usually think of last week as being the previous Sunday-Saturday but it could be the previous 7 days.

Duane
MS Access MVP

 

[Trope]

TERRIFIC - thanks for all the help!

Trope

 

[dhookom]

So, were you looking for the last 7 days or the most recent Sunday through Saturday? just curious...

Duane
MS Access MVP

 

[Trope]

Actually, I can't get a hold of the client today to see what he wants - check it out,

http://www.spinpacific.com

- and click on new artists this week. It's working now, but pulling all new signups from last 7 days.

I am assuming this is correct! ( i hope )

Thanks again!
Trope

 

[BobJacksonNCI]

If if turns out you need everything since the previous Sunday, (week-to-date) you could use:

>=(Date() - WeekDay(Date()) + 1 )

If you want everything for last week, it could be done in code, but I would recommend putting start date and end date on a form and using those in a "between" statement in your query. You could always preload the form using the default property and allow the user to type over the defaults...

HTH,
Bob
Thread181-473997 provides information regarding this site.

 

[dhookom]

If looks like the query is being performed from a web page where you would not expect users to enter beginning and ending dates.

Duane
MS Access MVP

 

[Trope]

Yeah, it's not requiring any user input - not here anyways, but elsewhere I can foresee that.

Now are these examples functional in Access, or via asp? Or both?

 

[dhookom]

It looks like you might have the best luck with Tony's solution. This considers the context of the web page.

Duane
MS Access MVP

 

[militarymindz]

Sup

I was just wondering how you could return rows for a specific year?

 

[militarymindz]

Sup

I was just wondering how you could return rows for a specific year?

 

[dhookom]

Your where clause in your sql would look like
WHERE Year([YourDateField]) = 2003
or
WHERE Year([YourDateField]) = [Enter Year]
or
WHERE Year([YourDateField]) = Forms!frmX!cboYear



Duane
MS Access MVP