extracting a weeks worth of data in a query

G

Guest

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.
 
G

Guest

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.
 
L

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
 
G

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,
 
G

Guest

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())));
 
J

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]
 
G

Guest

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

Thanks everyone!

John W. Vinson said:
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top