Query always returns dates on report between current week dates

  • Thread starter Thread starter david.isaacks
  • Start date Start date
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
 
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
 
[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...
 
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...
 
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
 
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...
 
Back
Top