Query returning inaccurate results...

[Guest]

Hallo,

I have a query that's not really returning the right results from a
calculated field. Here's the SQL:

SELECT tbl_empTraining.IDNumber, tbl_empTraining.FirstName,
tbl_empTraining.LastName, Employee.Address, Employee.City, Employee.State,
Employee.Zip, Employee.[Phone Number], tbl_empTraining.CPR,
DateAdd("yyyy",1,[CPR]) AS [CPR Expiry]
FROM tbl_empTraining INNER JOIN Employee ON tbl_empTraining.IDNumber =
Employee.[Employee Number]
WHERE (((DateAdd("yyyy",1,[CPR]))<=[Earlier than Expire Date for CPR
MM/DD/YY]));

I am getting dates greater than the criteria date I enter. Any help would be
greatly appreciated!

Fuddled,

John.

 

[Chris2]

Hallo,

I have a query that's not really returning the right results from a
calculated field. Here's the SQL:

I am getting dates greater than the criteria date I enter. Any help would be
greatly appreciated!

Fuddled,

John.

SELECT tbl_empTraining.IDNumber
,tbl_empTraining.FirstName
,tbl_empTraining.LastName
,Employee.Address
,Employee.City
,Employee.State
,Employee.Zip
,Employee.[Phone Number]
,tbl_empTraining.CPR
,DateAdd("yyyy",1,[CPR]) AS [CPR Expiry]
FROM tbl_empTraining
INNER JOIN
Employee
ON tbl_empTraining.IDNumber = Employee.[Employee Number]
WHERE (((DateAdd("yyyy",1,[CPR])) <= [Earlier than Expire Date for
CPR MM/DD/YY]));

Try:

PARAMETERS [Earlier than Expire Date for CPR MM/DD/YY] DATETIME;
SELECT tbl_empTraining.IDNumber
,tbl_empTraining.FirstName
,tbl_empTraining.LastName
,Employee.Address
,Employee.City
,Employee.State
,Employee.Zip
,Employee.[Phone Number]
,tbl_empTraining.CPR
,DateAdd("yyyy",1,[CPR]) AS [CPR Expiry]
FROM tbl_empTraining
INNER JOIN
Employee
ON tbl_empTraining.IDNumber = Employee.[Employee Number]
WHERE (((DateAdd("yyyy",1,[CPR])) <= [Earlier than Expire Date for
CPR MM/DD/YY]));

 

[Guest]

Hey Chris2,

Thanks for your post; it still didn't work - it's still returning dates
greater than the date I specify. I have chosen to let it go; it still returns
the dates less than the date I specify, so it'll work for now. I hope it
doesn't come back to bite me later.

Thanks again,

John.

Hallo,

I have a query that's not really returning the right results from a
calculated field. Here's the SQL:

I am getting dates greater than the criteria date I enter. Any help would be
greatly appreciated!

Fuddled,

John.

SELECT tbl_empTraining.IDNumber
,tbl_empTraining.FirstName
,tbl_empTraining.LastName
,Employee.Address
,Employee.City
,Employee.State
,Employee.Zip
,Employee.[Phone Number]
,tbl_empTraining.CPR
,DateAdd("yyyy",1,[CPR]) AS [CPR Expiry]
FROM tbl_empTraining
INNER JOIN
Employee
ON tbl_empTraining.IDNumber = Employee.[Employee Number]
WHERE (((DateAdd("yyyy",1,[CPR])) <= [Earlier than Expire Date for
CPR MM/DD/YY]));

Try:

PARAMETERS [Earlier than Expire Date for CPR MM/DD/YY] DATETIME;
SELECT tbl_empTraining.IDNumber
,tbl_empTraining.FirstName
,tbl_empTraining.LastName
,Employee.Address
,Employee.City
,Employee.State
,Employee.Zip
,Employee.[Phone Number]
,tbl_empTraining.CPR
,DateAdd("yyyy",1,[CPR]) AS [CPR Expiry]
FROM tbl_empTraining
INNER JOIN
Employee
ON tbl_empTraining.IDNumber = Employee.[Employee Number]
WHERE (((DateAdd("yyyy",1,[CPR])) <= [Earlier than Expire Date for
CPR MM/DD/YY]));