My Eyes Are Crossing

I have a query that is selecting no records, and I can't figure out why. It
involves two tables, "activities" and "activitiesABM_Temp". I want the query
to take the temp table and find related records in the activities table,
based on a loan officer number AND the last day of the previous month. This
query is run each month, so the date restrictor needs to always look for the
last day of the previous month. I know there's something wrong with the date
restrictor, because when I remove it the query works. But I can't for the
life of me figure out why. This is part of a large database project that I've
just been working on for too long.

SELECT activities.deposits
FROM activitiesABM_TEMP LEFT JOIN activities ON
activitiesABM_TEMP.assistantManager = activities.officerNumber
WHERE
(((activities.officerNumber)=([activitiesABM_TEMP].[assistantManager])) AND
((activities.month)=DateSerial(Year(Date()),Month( Date()),0)));

Ideas and suggestions are greatly appreciated!
Many thanks,

--
GwenH, CIW, CWP, Master MOS
Some of my best leading men have been dogs and horses ~ Elizabeth Taylor

What does activities.month contain: an actual date, or something else? (BTW,
Month is a bad choice for a field name: it's a reserved word, and using
reserved words for your own purposes can lead to problems. If you cannot, or
will not, change the field name, at least enclose it in square brackets)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Gwen H" wrote in message
...
I have a query that is selecting no records, and I can't figure out why. It
involves two tables, "activities" and "activitiesABM_Temp". I want the
query
to take the temp table and find related records in the activities table,
based on a loan officer number AND the last day of the previous month.
This
query is run each month, so the date restrictor needs to always look for
the
last day of the previous month. I know there's something wrong with the
date
restrictor, because when I remove it the query works. But I can't for the
life of me figure out why. This is part of a large database project that
I've
just been working on for too long.

SELECT activities.deposits
FROM activitiesABM_TEMP LEFT JOIN activities ON
activitiesABM_TEMP.assistantManager = activities.officerNumber
WHERE
(((activities.officerNumber)=([activitiesABM_TEMP].[assistantManager]))
AND
((activities.month)=DateSerial(Year(Date()),Month( Date()),0)));

Ideas and suggestions are greatly appreciated!
Many thanks,

--
GwenH, CIW, CWP, Master MOS
Some of my best leading men have been dogs and horses ~ Elizabeth Taylor

Activities.month contains a date - always the last day of a given month.
--
GwenH, CIW, CWP, Master MOS
Some of my best leading men have been dogs and horses ~ Elizabeth Taylor


"Douglas J. Steele" wrote:

What does activities.month contain: an actual date, or something else? (BTW,
Month is a bad choice for a field name: it's a reserved word, and using
reserved words for your own purposes can lead to problems. If you cannot, or
will not, change the field name, at least enclose it in square brackets)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Gwen H" wrote in message
...
I have a query that is selecting no records, and I can't figure out why. It
involves two tables, "activities" and "activitiesABM_Temp". I want the
query
to take the temp table and find related records in the activities table,
based on a loan officer number AND the last day of the previous month.
This
query is run each month, so the date restrictor needs to always look for
the
last day of the previous month. I know there's something wrong with the
date
restrictor, because when I remove it the query works. But I can't for the
life of me figure out why. This is part of a large database project that
I've
just been working on for too long.

SELECT activities.deposits
FROM activitiesABM_TEMP LEFT JOIN activities ON
activitiesABM_TEMP.assistantManager = activities.officerNumber
WHERE
(((activities.officerNumber)=([activitiesABM_TEMP].[assistantManager]))
AND
((activities.month)=DateSerial(Year(Date()),Month( Date()),0)));

Ideas and suggestions are greatly appreciated!
Many thanks,

--
GwenH, CIW, CWP, Master MOS
Some of my best leading men have been dogs and horses ~ Elizabeth Taylor

AFAICS (and I may be wrong):

1. The first criterion is not needed as this is already covered by the On
Clause.

2. In this Query, the INNER JOIN should give you the same result except the
INNER JOIN will be more efficient.

3. Have you checked the values of the Field [month] and make sure that the
values have zero time component? Your SQL won't work correctly if the
values of [month] in Table [activities] have non-zero time component.

If_all_ time component values are zero, try:

========
SELECT activities.deposits
FROM activities INNER JOIN activitiesABM_TEMP
ON activities.officerNumber = activitiesABM_TEMP.assistantManager
WHERE (activities.[month] = DateSerial(Year(Date()), Month(Date()), 0));
========

--
HTH
Van T. Dinh
MVP (Access)



"Gwen H" wrote in message
...
Activities.month contains a date - always the last day of a given month.
--
GwenH, CIW, CWP, Master MOS
Some of my best leading men have been dogs and horses ~ Elizabeth Taylor