Query always returns dates on report between current week dates

D

david.isaacks

SELECT Contacts.FirstName, Contacts.LastName, Contacts.Supervisor,
Contacts.Group, Contacts.Channel, Contacts.Title, Contacts.WorkPhone,
Contacts.WorkExtension, Contacts.EOD, Contacts.Status,
Contacts.ContactTypeID, Contacts.ReferredBy, Contacts.Notes,
Contacts.[Assigned Monitor], Calls.[Monitor Date], Calls.[Monitor
Time], Calls.Completed, Calls.Notes, Calls.Monitor
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Calls.[Monitor Date])=[WeekOf]));

I have a report and I only want records from my query to return if they
are from the current week or the week that I run the report. A week
being Sunday - Saturday? I have be trying different Where statements,
but they seem to get me no where.

Any help?
David
 
J

Jeff Boyce

If you have a field that holds a Date/Time value (?[MonitorDate]?), your
query seems to be trying to compare that to something you're calling
"[WeekOf]". What's that?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

david.isaacks

[WeekOf] I put in my where statements under Monitor Date. So I was
trying to get only the records with dates returned where they equaled
the current week.

David

Jeff said:
If you have a field that holds a Date/Time value (?[MonitorDate]?), your
query seems to be trying to compare that to something you're calling
"[WeekOf]". What's that?

Regards

Jeff Boyce
Microsoft Office/Access MVP


SELECT Contacts.FirstName, Contacts.LastName, Contacts.Supervisor,
Contacts.Group, Contacts.Channel, Contacts.Title, Contacts.WorkPhone,
Contacts.WorkExtension, Contacts.EOD, Contacts.Status,
Contacts.ContactTypeID, Contacts.ReferredBy, Contacts.Notes,
Contacts.[Assigned Monitor], Calls.[Monitor Date], Calls.[Monitor
Time], Calls.Completed, Calls.Notes, Calls.Monitor
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Calls.[Monitor Date])=[WeekOf]));

I have a report and I only want records from my query to return if they
are from the current week or the week that I run the report. A week
being Sunday - Saturday? I have be trying different Where statements,
but they seem to get me no where.

Any help?
David
Click to expand...
Click to expand...
 
J

Jeff Boyce

I'm still missing something. It sounds like you are trying to compare
"weekof" with dates. Those don't sound like they're the same thing.

Regards

Jeff Boyce
Microsoft Office/Access MVP


[WeekOf] I put in my where statements under Monitor Date. So I was
trying to get only the records with dates returned where they equaled
the current week.

David

Jeff said:
If you have a field that holds a Date/Time value (?[MonitorDate]?), your
query seems to be trying to compare that to something you're calling
"[WeekOf]". What's that?

Regards

Jeff Boyce
Microsoft Office/Access MVP


SELECT Contacts.FirstName, Contacts.LastName, Contacts.Supervisor,
Contacts.Group, Contacts.Channel, Contacts.Title, Contacts.WorkPhone,
Contacts.WorkExtension, Contacts.EOD, Contacts.Status,
Contacts.ContactTypeID, Contacts.ReferredBy, Contacts.Notes,
Contacts.[Assigned Monitor], Calls.[Monitor Date], Calls.[Monitor
Time], Calls.Completed, Calls.Notes, Calls.Monitor
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Calls.[Monitor Date])=[WeekOf]));

I have a report and I only want records from my query to return if they
are from the current week or the week that I run the report. A week
being Sunday - Saturday? I have be trying different Where statements,
but they seem to get me no where.

Any help?
David
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

To get the current week you might use

WHERE [Calls].[Monitor Date] >= DateAdd("d",1 - Weekday(Date()),Date())
And Calls.[Monitor Date] < DateAdd("d",1 - Weekday(Date()),Date()) +7
 
D

david.isaacks

OK, maybe I only nee for current month?

John said:
To get the current week you might use

WHERE [Calls].[Monitor Date] >= DateAdd("d",1 - Weekday(Date()),Date())
And Calls.[Monitor Date] < DateAdd("d",1 - Weekday(Date()),Date()) +7

SELECT Contacts.FirstName, Contacts.LastName, Contacts.Supervisor,
Contacts.Group, Contacts.Channel, Contacts.Title, Contacts.WorkPhone,
Contacts.WorkExtension, Contacts.EOD, Contacts.Status,
Contacts.ContactTypeID, Contacts.ReferredBy, Contacts.Notes,
Contacts.[Assigned Monitor], Calls.[Monitor Date], Calls.[Monitor
Time], Calls.Completed, Calls.Notes, Calls.Monitor
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Calls.[Monitor Date])=[WeekOf]));

I have a report and I only want records from my query to return if they
are from the current week or the week that I run the report. A week
being Sunday - Saturday? I have be trying different Where statements,
but they seem to get me no where.

Any help?
David
Click to expand...
Click to expand...
 
Top