nearest date

G

Guest

Users enter records, I have to determine the nearest due date, for each user.
This code returns the 1 nearest date, but I need the nearest date for all
users. So, when I add the user it tells me "You tried to execute a query
that does not inclide the specific expression "TR_USER' as part of an
aggregate function"..

The first code below works but it returns only the 1 nearest date: The code
below is with the TR_USER added.

SELECT Min(IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0)))) AS DueDate
FROM tblTrackingData;


SELECT tblTrackingData.TR_USER, Min(IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0)))) AS DueDate
FROM tblTrackingData;



Suggestions
 
M

Michel Walsh

If I move only by the syntax requirement, without trying to understand all
the ... noise ... add :

GROUP BY tblTrackingData.TR_USER

to get:

SELECT tblTrackingData.TR_USER, Min(IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0)))) AS DueDate

FROM tblTrackingData

GROUP BY tblTrackingData.TR_USER



Hoping it may help,
Vanderghast, Access MVP



Dan @BCBS said:
Users enter records, I have to determine the nearest due date, for each
user.
This code returns the 1 nearest date, but I need the nearest date for all
users. So, when I add the user it tells me "You tried to execute a query
that does not inclide the specific expression "TR_USER' as part of an
aggregate function"..

The first code below works but it returns only the 1 nearest date: The
code
below is with the TR_USER added.

SELECT Min(IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0)))) AS DueDate
FROM tblTrackingData;


SELECT tblTrackingData.TR_USER, Min(IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0)))) AS DueDate
FROM tblTrackingData;



Suggestions
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

Date Format 5
Two PCs Different Results 11
YES/NO not working 5
Date calculation 9
Time calc. 3
Dialog in MsgBox 4
error trying to sort data 4
Outlook help regarding outlook vba 0

Top