If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Can I do this in Access? Please help
-- David "John W. Vinson" wrote: On Fri, 14 Nov 2008 08:48:13 -0800, caseysmydog wrote: 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 W. Vinson [MVP] |
#22
|
|||
|
|||
Can I do this in Access? Please help
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/AlbertKal.../msaccess.html |
#23
|
|||
|
|||
Can I do this in Access? Please help
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, -- David "John Spencer" wrote: 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/AlbertKal.../msaccess.html |
#24
|
|||
|
|||
Can I do this in Access? Please help
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 wrote: 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, |
#25
|
|||
|
|||
Can I do this in Access? Please help
It Worked...Thanks Will get back to you
-- David "John Spencer" wrote: 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 wrote: 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, |
#26
|
|||
|
|||
Can I do this in Access? Please help
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 "John Spencer" wrote: 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 wrote: 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, |
#27
|
|||
|
|||
Can I do this in Access? Please help
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 wrote: 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. |
#28
|
|||
|
|||
Can I do this in Access? Please help
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 "John Spencer" wrote: 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 wrote: 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. |
#29
|
|||
|
|||
Can I do this in Access? Please help
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 caseysmydog wrote: 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 |
Thread Tools | |
Display Modes | |
|
|