Specify Date to Calculate

A

antmorano

Good Morning Everyone:

I just realized that my queries are not calculating from the date I
input, but instead today's date. I had a parameter set up that
specified that date that I wanted put it. I noticed this b/c I was
comparing data for 12/2006 and I had people in there for 4/2007...
here is the SQL view... any suggestions on what might be wrong. FYI-
I have two fields that calculate the age of a retiree and spouse.

SELECT [All Plans].[Chain Sequence Number], [All Plans].[Retiree Last
Name], [All Plans].[Retiree First Name], [All Plans].[Retiree SSN],
[All Plans].Plan, [All Plans].Fund, [All Plans].[Retiree DOB], [All
Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date], [All
Plans].[Retirement Date], [All Plans].Address, [All Plans].City, [All
Plans].State, [All Plans].Zip, [All Plans].[Control Group], [All
Plans].[Spouse First Name], [All Plans].[Spouse Last Name], [All
Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].[Spouse
Gender], [All Plans].[Spouse Eligibility Date], [All Plans].[Retiree
DOD], [All Plans].[Spouse DOD], [All Plans].[Date of Entry], [All
Plans].[Status of Coverage- Retiree], [All Plans].[Status of Coverage-
Spouse], IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],
[TODAY])-IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1))) AS [Retiree Age], IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",
[Spouse DOB],[TODAY])-IIf(Format([Spouse
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))) AS [Spouse Age]
FROM [All Plans]
GROUP BY [All Plans].[Chain Sequence Number], [All Plans].[Retiree
Last Name], [All Plans].[Retiree First Name], [All Plans].[Retiree
SSN], [All Plans].Plan, [All Plans].Fund, [All Plans].[Retiree DOB],
[All Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date],
[All Plans].[Retirement Date], [All Plans].Address, [All Plans].City,
[All Plans].State, [All Plans].Zip, [All Plans].[Control Group], [All
Plans].[Spouse First Name], [All Plans].[Spouse Last Name], [All
Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].[Spouse
Gender], [All Plans].[Spouse Eligibility Date], [All Plans].[Retiree
DOD], [All Plans].[Spouse DOD], [All Plans].[Date of Entry], [All
Plans].[Status of Coverage- Retiree], [All Plans].[Status of Coverage-
Spouse], IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],
[TODAY])-IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1))), IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],
[TODAY])-IIf(Format([Spouse DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1)))
HAVING ((([All Plans].Plan)="VA" Or ([All Plans].Plan)="VB" Or ([All
Plans].Plan)="VAL" Or ([All Plans].Plan)="VBL") AND (([All
Plans].Fund)="H&W") AND (([All Plans].[Retiree Eligibility
Date])<Date()) AND (([All Plans].[Control Group])<>"GRAND UNION,
NYVI") AND (([All Plans].[Status of Coverage- Retiree]) Is Null) AND
((IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],[TODAY])-
IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65))
OR ((([All Plans].Plan)="VA" Or ([All Plans].Plan)="VB" Or ([All
Plans].Plan)="VAL" Or ([All Plans].Plan)="VBL") AND (([All
Plans].Fund)="H&W") AND (([All Plans].[Control Group])<>"GRAND UNION,
NYVI") AND (([All Plans].[Spouse Eligibility Date])<Date()) AND (([All
Plans].[Status of Coverage- Spouse]) Is Null) AND ((IIf([Spouse DOD]
Is Null,Abs(DateDiff("yyyy",[Spouse DOB],[TODAY])-IIf(Format([Spouse
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65));
 
J

Jerry Whittle

Hi,

In the SQL provided, I don't see a parameter. I do see "<Date()" a number of
places which would give today's date.

Another possibility is the Format function on dates. It returns a string
which can cause problems when attempting numerical character comparisions.
However, it looks like your use of "mmdd" should work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
Good Morning Everyone:

I just realized that my queries are not calculating from the date I
input, but instead today's date. I had a parameter set up that
specified that date that I wanted put it. I noticed this b/c I was
comparing data for 12/2006 and I had people in there for 4/2007...
here is the SQL view... any suggestions on what might be wrong. FYI-
I have two fields that calculate the age of a retiree and spouse.

SELECT [All Plans].[Chain Sequence Number], [All Plans].[Retiree Last
Name], [All Plans].[Retiree First Name], [All Plans].[Retiree SSN],
[All Plans].Plan, [All Plans].Fund, [All Plans].[Retiree DOB], [All
Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date], [All
Plans].[Retirement Date], [All Plans].Address, [All Plans].City, [All
Plans].State, [All Plans].Zip, [All Plans].[Control Group], [All
Plans].[Spouse First Name], [All Plans].[Spouse Last Name], [All
Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].[Spouse
Gender], [All Plans].[Spouse Eligibility Date], [All Plans].[Retiree
DOD], [All Plans].[Spouse DOD], [All Plans].[Date of Entry], [All
Plans].[Status of Coverage- Retiree], [All Plans].[Status of Coverage-
Spouse], IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],
[TODAY])-IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1))) AS [Retiree Age], IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",
[Spouse DOB],[TODAY])-IIf(Format([Spouse
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))) AS [Spouse Age]
FROM [All Plans]
GROUP BY [All Plans].[Chain Sequence Number], [All Plans].[Retiree
Last Name], [All Plans].[Retiree First Name], [All Plans].[Retiree
SSN], [All Plans].Plan, [All Plans].Fund, [All Plans].[Retiree DOB],
[All Plans].[Retiree Gender], [All Plans].[Retiree Eligibility Date],
[All Plans].[Retirement Date], [All Plans].Address, [All Plans].City,
[All Plans].State, [All Plans].Zip, [All Plans].[Control Group], [All
Plans].[Spouse First Name], [All Plans].[Spouse Last Name], [All
Plans].[Spouse SSN], [All Plans].[Spouse DOB], [All Plans].[Spouse
Gender], [All Plans].[Spouse Eligibility Date], [All Plans].[Retiree
DOD], [All Plans].[Spouse DOD], [All Plans].[Date of Entry], [All
Plans].[Status of Coverage- Retiree], [All Plans].[Status of Coverage-
Spouse], IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],
[TODAY])-IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1))), IIf([Spouse DOD] Is Null,Abs(DateDiff("yyyy",[Spouse DOB],
[TODAY])-IIf(Format([Spouse DOB],"mmdd")<=Format([TODAY],"mmdd"),
0,1)))
HAVING ((([All Plans].Plan)="VA" Or ([All Plans].Plan)="VB" Or ([All
Plans].Plan)="VAL" Or ([All Plans].Plan)="VBL") AND (([All
Plans].Fund)="H&W") AND (([All Plans].[Retiree Eligibility
Date])<Date()) AND (([All Plans].[Control Group])<>"GRAND UNION,
NYVI") AND (([All Plans].[Status of Coverage- Retiree]) Is Null) AND
((IIf([Retiree DOD] Is Null,Abs(DateDiff("yyyy",[Retiree DOB],[TODAY])-
IIf(Format([Retiree DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65))
OR ((([All Plans].Plan)="VA" Or ([All Plans].Plan)="VB" Or ([All
Plans].Plan)="VAL" Or ([All Plans].Plan)="VBL") AND (([All
Plans].Fund)="H&W") AND (([All Plans].[Control Group])<>"GRAND UNION,
NYVI") AND (([All Plans].[Spouse Eligibility Date])<Date()) AND (([All
Plans].[Status of Coverage- Spouse]) Is Null) AND ((IIf([Spouse DOD]
Is Null,Abs(DateDiff("yyyy",[Spouse DOB],[TODAY])-IIf(Format([Spouse
DOB],"mmdd")<=Format([TODAY],"mmdd"),0,1))))<65));
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Does Not Pick Up Data 5
Criteria Expression Problem 3
How to create queries that will calculate age from date of birth 0
#error in calculate age 4
Calculate Chronological Age ex: 10 yrs, 4 mo, 2 days 2
Calculating age in a form 7
#Name? error in Formula 5
Use calculate age to write other fields automatically 24

Top