Can I do this in Access? Please help

[caseysmydog]

--
David

Query:

Field Deceased Date of Death Caregivers Name Address
Table Table1 Table1 Table1
Table1
Sort
Show Y Y Y
Y
Criteria =1
or:
Dateadd("m",1)date of death))

Data above is skewed but it gives the idea.
Many thanks

If you want to display a query here, please open it in design view; select
View... SQL from the menu; and post the SQL text here. It may look cryptic but
it's the *real* query, for which the grid is just a design tool, and the folks
who answer questions here can read it like today's newspaper.

As written - if I'm interpreting it correctly - this query will return an
error because the expression Dateadd("m",1)date of death)) makes no sense.
Even if it were corrected to Dateadd("m",1, [date of death]) it would return
only those records where the Date of Death was December 31, 1899 (one day
after the "anchor point" for Date/TIme fields), or was equal to one month
after the date of death - so you'll get no results.

Perhaps you could explain in words what results you want from the query.

 

[John Spencer]

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

That should return all records with a date of death in the previous month.

Change the -1 to -3 to get date of death three months prior, -5 for five
months, etc.

Transferring that data into a stored form letter is a bit more complex.
Alfred Kallal has one example for doing this at his site. If you are
just beginning this may be difficult to use.
Word Merge Code

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

 

[caseysmydog]

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

SELECT Table1.Deceased, Table1.[Date of Death], Table1.[Caregivers Name],
Table1.Address
FROM Table1
WHERE (((Table1.[Date of Death])="1")) OR (((Table1.[Date of
Death])=DateAdd("m",1,[DateOfDeath])))

Thank you for the above...I've tried both and they return an error:
Syntax In Subquery in this expression is incorrect.
Check the Subquery syntax & enclose Subquery in parenthesis.

I know I'm beginning to be a pain--I bought 2 books on Access today and will
study them. Any thoughts on these query's?
Thanks,

 

[John Spencer]

Are you running ONE query.

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

Open a new query
Do NOT ADD any tables, just close the dialog to select tables.
Switch to SQL view (View: SQL from the menu)
Paste the above into the SQL view of the query

Run the query. IF you get a syntax error then I am baffled, but post
back and let us know what the error is.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[caseysmydog]

It Worked...Thanks Will get back to you
--
David

Are you running ONE query.

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

Open a new query
Do NOT ADD any tables, just close the dialog to select tables.
Switch to SQL view (View: SQL from the menu)
Paste the above into the SQL view of the query

Run the query. IF you get a syntax error then I am baffled, but post
back and let us know what the error is.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

SELECT Table1.Deceased, Table1.[Date of Death], Table1.[Caregivers Name],
Table1.Address
FROM Table1
WHERE (((Table1.[Date of Death])="1")) OR (((Table1.[Date of
Death])=DateAdd("m",1,[DateOfDeath])))

Thank you for the above...I've tried both and they return an error:
Syntax In Subquery in this expression is incorrect.
Check the Subquery syntax & enclose Subquery in parenthesis.

I know I'm beginning to be a pain--I bought 2 books on Access today and will
study them. Any thoughts on these query's?
Thanks,

 

[caseysmydog]

The query is working fine...trying to understand the relationship of the
number 1s in the query.
If I need to change the 1 to a 3(for 3 months) which to change?
Thanks again.
--
David

Are you running ONE query.

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

Open a new query
Do NOT ADD any tables, just close the dialog to select tables.
Switch to SQL view (View: SQL from the menu)
Paste the above into the SQL view of the query

Run the query. IF you get a syntax error then I am baffled, but post
back and let us know what the error is.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-1,1) AND
DateSerial(Year(Date()),Month(Date()),1-1)

SELECT Table1.Deceased, Table1.[Date of Death], Table1.[Caregivers Name],
Table1.Address
FROM Table1
WHERE (((Table1.[Date of Death])="1")) OR (((Table1.[Date of
Death])=DateAdd("m",1,[DateOfDeath])))

Thank you for the above...I've tried both and they return an error:
Syntax In Subquery in this expression is incorrect.
Check the Subquery syntax & enclose Subquery in parenthesis.

I know I'm beginning to be a pain--I bought 2 books on Access today and will
study them. Any thoughts on these query's?
Thanks,

 

[John Spencer]

For three months, you would need

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-3,1) AND
DateSerial(Year(Date()),Month(Date())-2,1-1)

For 6 months
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-6,1) AND
DateSerial(Year(Date()),Month(Date())-5,1-1)

For 12 months
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-12,1) AND
DateSerial(Year(Date()),Month(Date())-11,1-1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[caseysmydog]

John, this working very well except for the 12 month query.
For instance, we had 4 people die in Dec., '07.
Yet it pulls only 2.
The other two are lower day dates in Dec. than the two pulled.
For all the other months, the formula has pulled successfully and the day
dates do not affect, can be higher or lower.
Can you share your thoughts?
Many thanks
--
David

For three months, you would need

SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-3,1) AND
DateSerial(Year(Date()),Month(Date())-2,1-1)

For 6 months
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-6,1) AND
DateSerial(Year(Date()),Month(Date())-5,1-1)

For 12 months
SELECT Table1.Deceased
, Table1.[Date of Death]
, Table1.[Caregivers Name]
, Table1.Address
FROM Table1
WHERE Table1.[Date of Death] Between
DateSerial(Year(Date()),Month(Date())-12,1) AND
DateSerial(Year(Date()),Month(Date())-11,1-1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The query is working fine...trying to understand the relationship of the
number 1s in the query.
If I need to change the 1 to a 3(for 3 months) which to change?
Thanks again.

 

[John Spencer]

The only think I can think of is to carefully check the date of death and make
sure the correct year has been entered. Make sure the date is 2007 and not
1907 or 2107 or ...

Also, make sure the month is December.

If you are storing Date of Death in a text field, Access may be attempting to
convert the string to dates for you. If so, it could be misunderstanding the
date string and converting 03/12/2007 to March 12, 2007 when you expect it to
see that as December 3, 2007.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County