extracting a weeks worth of data in a query

[kcrad]

I'm trying to extract data from any given week in a query. I have data
categorized according to dd/mm/yyyy.

As a start, I'm simply trying to build an expression that will extract the
data from the current week.

DatePart("ww","Date")=DatePart("ww",Date()) - where "Date" is the name of
the coulmn in my query.

If I use this expression as criteria for a query column it should return
only data that was entered in current week, right? Something is wrong though
- I get a data mismatch error when I run the query. Can anyone see my error?
I'm sure it's something simple.

 

[KARL DEWEY]

Post your SQL.
If you do not know how then do this -- Open the query in design view, click
on meny VIEW - SQL View, hightlight all, copy, and paste in a post.

 

[Larry Daugherty]

First, "Date" is a reserved word in Access. You can often get away
with using it and that's too bad because sometimes you can't.

In the criteria line for that newly named Date field I would use
"Between"

HTH

 

[George Nicholson]

Rather than enclosing your field name in quotes (making Access think its a
string, leading to a Type Mismatch when it can't coerce that string to a
valid date which it must be for DatePart to work...), try enclosing the
field name in brackets: [Date].

As mentioned elsewhere, Date is a reserved word and can cause problems when
used as a field or variable name. the sooner you change it to something like
EntryDate the better.

HTH,

 

[kcrad]

Here is the SQL
I changed the "Date" to "EntryDate"

SELECT CarolRecodsQuery.EntryDate, CarolRecodsQuery.Project
FROM CarolRecodsQuery
GROUP BY CarolRecodsQuery.Project
HAVING ((DatePart("ww","EntryDate")=DatePart("ww",Date())));

 

[John W. Vinson]

SELECT CarolRecodsQuery.EntryDate, CarolRecodsQuery.Project
FROM CarolRecodsQuery
GROUP BY CarolRecodsQuery.Project
HAVING ((DatePart("ww","EntryDate")=DatePart("ww",Date())));

Change that to

DatePart("ww", [EntryDate])

What you're doing is trying to determine what week the nine-character text
string "EntryDate" represents. If you use square brackets rather than quotes
as the delimiter, Access will look in the *field* of that name.

John W. Vinson [MVP]

 

[kcrad]

Got it! I had tried that before but something else must have been wrong.

Thanks everyone!

SELECT CarolRecodsQuery.EntryDate, CarolRecodsQuery.Project
FROM CarolRecodsQuery
GROUP BY CarolRecodsQuery.Project
HAVING ((DatePart("ww","EntryDate")=DatePart("ww",Date())));

Change that to

DatePart("ww", [EntryDate])

What you're doing is trying to determine what week the nine-character text
string "EntryDate" represents. If you use square brackets rather than quotes
as the delimiter, Access will look in the *field* of that name.

John W. Vinson [MVP]