Query produces unpredicted result

J

Jack Sheet

Tables
=====
T_Clients contains three fields:
ID_Clients = autonumber, primary key
YEM = integer in range 1 through 12 (representing a month)
YED = integer in the range 1 through 31 (representing a day in a month)

T_Tasks contains three fields:
ID_Tasks = autonumber, primary key
ID_Clients = number (related to T_Clients.ID_Clients)
PeriodTo = Date

Queries
=====

Q_RecExists:
SELECT T_Tasks.ID_Tasks, T_Tasks.ID_Clients, T_Tasks.PeriodTo
FROM T_Clients INNER JOIN T_Tasks ON T_Clients.ID_Clients=T_Tasks.ID_Clients
WHERE
((T_Tasks.PeriodTo)>DateSerial(Year([RollDate])-1,Month([RollDate]),Day([RollDate])));

Q_RecNotExistsSLOW:
SELECT T_Clients.ID_Clients
FROM T_Clients
WHERE [T_Clients].[ID_Clients] not in ( SELECT [Q_RecExists].[ID_Clients]
FROM Q_RecExists);

Q_RollForward:
INSERT INTO T_Tasks ( ID_Clients, PeriodTo )
SELECT Q_RecNotExistsSLOW.ID_Clients,
DateSerial(IIf(DateSerial(Year([RollDate]),T_Clients.YEM,T_Clients.YED)>[RollDate],
Year([RollDate])-1,
Year([RollDate])),
T_Clients.YEM,
T_Clients.YED)
FROM Q_RecNotExistsSLOW INNER JOIN T_Clients ON
Q_RecNotExistsSlow.ID_Clients=T_Clients.ID_Clients;

Starting Data
=========
T_Clients:
ID_Clients YEM YED
1 4 30
2 5 31
3 6 30

T_Tasks
ID_Tasks ID_Clients PeriodTo
(irrelevant) 1 30/04/2004
2 31/05/2005
3 30/06/2006

PROBLEM
========
The IIF() expression in Q_Rollforward is producing unexpected test results.
If I run Q_RollForward I am prompted to enter a value for RollDate
If I enter 2006-06-30 then it adds 2 records to T_Tasks
ID_Clients PeriodTo
1 30/04/2005
2 31/05/2005
The records being added are for the correct ID_Clients, but the PeriodTo
dates are incorrect in each case. They should be 30/04/2006 and 31/05/2006
respectively

It wouild appear that in each case it is evaluationg the IIF condition as
true when it should be false, and so deducting 1 from the year in each case
when it should not. But to me the IIF statement seems to be correctly
constructed.

Take for example the first record ID_Clients = 1
(DateSerial(Year([RollDate]),T_Clients.YEM,T_Clients.YED) =
DateSerial(2006,4,30).
This value is NOT greater than 2006-06-30
So IIF should test FALSE
So the Year to be inserted should be Year([RollDate]) = Year(#2006-06-30#) =
2006, NOT 2005

Can anyone else spot my error, please?
 
J

Jack Sheet

Problem seems to be solved (for now).
If I store the value of RollDate in a single-field, single-record table, and
use DLookup to access its value as necessary instead of liberally scattering
[parameter] prompts in several queries, then the IIF statement seems to
evaluate correctly. Quite why it didn't work otherwise remains a mystery to
me, but one that I am happy to leave unresolved.

Jack Sheet said:
Tables
=====
T_Clients contains three fields:
ID_Clients = autonumber, primary key
YEM = integer in range 1 through 12 (representing a month)
YED = integer in the range 1 through 31 (representing a day in a month)

T_Tasks contains three fields:
ID_Tasks = autonumber, primary key
ID_Clients = number (related to T_Clients.ID_Clients)
PeriodTo = Date

Queries
=====

Q_RecExists:
SELECT T_Tasks.ID_Tasks, T_Tasks.ID_Clients, T_Tasks.PeriodTo
FROM T_Clients INNER JOIN T_Tasks ON
T_Clients.ID_Clients=T_Tasks.ID_Clients
WHERE
((T_Tasks.PeriodTo)>DateSerial(Year([RollDate])-1,Month([RollDate]),Day([RollDate])));

Q_RecNotExistsSLOW:
SELECT T_Clients.ID_Clients
FROM T_Clients
WHERE [T_Clients].[ID_Clients] not in ( SELECT [Q_RecExists].[ID_Clients]
FROM Q_RecExists);

Q_RollForward:
INSERT INTO T_Tasks ( ID_Clients, PeriodTo )
SELECT Q_RecNotExistsSLOW.ID_Clients,

DateSerial(IIf(DateSerial(Year([RollDate]),T_Clients.YEM,T_Clients.YED)>[RollDate],
Year([RollDate])-1,
Year([RollDate])),
T_Clients.YEM,
T_Clients.YED)
FROM Q_RecNotExistsSLOW INNER JOIN T_Clients ON
Q_RecNotExistsSlow.ID_Clients=T_Clients.ID_Clients;

Starting Data
=========
T_Clients:
ID_Clients YEM YED
1 4 30
2 5 31
3 6 30

T_Tasks
ID_Tasks ID_Clients PeriodTo
(irrelevant) 1 30/04/2004
2 31/05/2005
3 30/06/2006

PROBLEM
========
The IIF() expression in Q_Rollforward is producing unexpected test
results.
If I run Q_RollForward I am prompted to enter a value for RollDate
If I enter 2006-06-30 then it adds 2 records to T_Tasks
ID_Clients PeriodTo
1 30/04/2005
2 31/05/2005
The records being added are for the correct ID_Clients, but the PeriodTo
dates are incorrect in each case. They should be 30/04/2006 and
31/05/2006 respectively

It wouild appear that in each case it is evaluationg the IIF condition as
true when it should be false, and so deducting 1 from the year in each
case when it should not. But to me the IIF statement seems to be
correctly constructed.

Take for example the first record ID_Clients = 1
(DateSerial(Year([RollDate]),T_Clients.YEM,T_Clients.YED) =
DateSerial(2006,4,30).
This value is NOT greater than 2006-06-30
So IIF should test FALSE
So the Year to be inserted should be Year([RollDate]) = Year(#2006-06-30#)
= 2006, NOT 2005

Can anyone else spot my error, please?
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

Append query syntax requested 3
Create table query syntax 3
Select query generating new field? 4
Another update query 2
I need to help with Excel (beginner) -- year row and month column 2
serial number - dynamic 3
IF Function with Dates 9
Query to display the difference of period for an employee. 1

Top