How to calculate no of days between date job is open and today's date

[lsingh165]

Hello,

What's the formula we can use to calculate days between no of days between date job is open and today's date in a query. I am using formulat below formula but the DATE_OPEN and Today (todays date) pops us as enter parameter value. I dont want those enter parameter windows pops us. The date job is open is in a linked table and linked table is getting the information from BPCS software. The date in Liinked is in format YYYYMMDD. Please help

Total Days: DateDiff("d",[Date_Open],[Today])



The SQL Details are below. AUNPRDF_AAA802 is a linked table and information gets updated on 12 am everyday

SELECT DateDiff("d",[Date_Open],[Today]) AS [Total Days], AUNPRDF_AAA802.AAAGE AS Age, AUNPRDF_AAA802.AAORD AS [Job Number], AUNPRDF_AAA802.AAWKSP AS Workshop, AUNPRDF_AAA802.AASWKT AS WorkType, AUNPRDF_AAA802.AANAME AS [Customer Name], AUNPRDF_AAA802.AANME AS [Service Admin], AUNPRDF_AAA802.AASDE1 AS Complaint, AUNPRDF_AAA802.AASDE2 AS Cause, AUNPRDF_AAA802.AASDE3 AS Correction, Sum(AUNPRDF_AAA802.AATOT) AS Total, CDate(Right([AAODTE],2) & "/" & Mid([AAODTE],5,2) & "/" & Left([AAODTE],4)) AS Date_Open, Date() AS Today
FROM AUNPRDF_AAA802
GROUP BY DateDiff("d",[Date_Open],[Today]), AUNPRDF_AAA802.AAAGE, AUNPRDF_AAA802.AAORD, AUNPRDF_AAA802.AAWKSP, AUNPRDF_AAA802.AASWKT, AUNPRDF_AAA802.AANAME, AUNPRDF_AAA802.AANME, AUNPRDF_AAA802.AASDE1, AUNPRDF_AAA802.AASDE2, AUNPRDF_AAA802.AASDE3, CDate(Right([AAODTE],2) & "/" & Mid([AAODTE],5,2) & "/" & Left([AAODTE],4)), Date(), AUNPRDF_AAA802.AARTYP
HAVING (((AUNPRDF_AAA802.AARTYP)="1"));

Thanks

[Bob Fitz]

I'm not sure this is right but you could try:
DateDiff("d",Format([Date_Open],"mm/dd/yyyy"),Format(Date(),"mm/dd/yyyy"))

[lsingh165]

Still enter value parameter for Date_Open and Today's Date comes up when I run the query, Please help mate. Thanks for reply

[lsingh165]

The linked table is pulling all the data out BPCS software and date is in a format YYYYMMDD without any back slash or dash in it. I applied formula in query to have date in format dd/mm/yyyy so that i can calculate the total days between Date_Open and today. I dont know why query is asking me for these parameters. Could you please help mate

[Bob Fitz]

Can you show us the SQL of the query as it is now.

[lsingh165]

SELECT DateDiff("d",[Date_Open],[Today]) AS [Total Days], AUNPRDF_AAA802.AAAGE AS Age, AUNPRDF_AAA802.AAWKSP AS Workshop, AUNPRDF_AAA802.AAORD AS [Job Number], AUNPRDF_AAA802.AASWKT AS WorkType, AUNPRDF_AAA802.AANAME AS [Customer Name], AUNPRDF_AAA802.AANME AS [Service Admin], AUNPRDF_AAA802.AASDE1 AS Complaint, AUNPRDF_AAA802.AASDE2 AS Cause, AUNPRDF_AAA802.AASDE3 AS Correction, Sum(AUNPRDF_AAA802.AATOT) AS Total, CDate(Right([AAODTE],2) & "/" & Mid([AAODTE],5,2) & "/" & Left([AAODTE],4)) AS Date_Open, Date() AS Today
FROM AUNPRDF_AAA802
GROUP BY DateDiff("d",[Date_Open],[Today]), AUNPRDF_AAA802.AAAGE, AUNPRDF_AAA802.AAWKSP, AUNPRDF_AAA802.AAORD, AUNPRDF_AAA802.AASWKT, AUNPRDF_AAA802.AANAME, AUNPRDF_AAA802.AANME, AUNPRDF_AAA802.AASDE1, AUNPRDF_AAA802.AASDE2, AUNPRDF_AAA802.AASDE3, CDate(Right([AAODTE],2) & "/" & Mid([AAODTE],5,2) & "/" & Left([AAODTE],4)), Date(), AUNPRDF_AAA802.AARTYP
HAVING (((AUNPRDF_AAA802.AAWKSP)="AP") AND ((AUNPRDF_AAA802.AARTYP)="1")) OR (((AUNPRDF_AAA802.AAWKSP)="KR") AND ((AUNPRDF_AAA802.AARTYP)="1") AND (("WP")<>False)) OR (((AUNPRDF_AAA802.AAWKSP)="WP") AND ((AUNPRDF_AAA802.AARTYP)="1") AND (("KR")<>False));

[Bob Fitz]

Try:
SELECT DateDiff("d",[Date_Open],Date()) AS [Total Days], AUNPRDF_AAA802.AAAGE AS Age, AUNPRDF_AAA802.AAWKSP AS Workshop, AUNPRDF_AAA802.AAORD AS [Job Number], AUNPRDF_AAA802.AASWKT AS WorkType, AUNPRDF_AAA802.AANAME AS [Customer Name], AUNPRDF_AAA802.AANME AS [Service Admin], AUNPRDF_AAA802.AASDE1 AS Complaint, AUNPRDF_AAA802.AASDE2 AS Cause, AUNPRDF_AAA802.AASDE3 AS Correction, Sum(AUNPRDF_AAA802.AATOT) AS Total, CDate(Right([AAODTE],2) & "/" & Mid([AAODTE],5,2) & "/" & Left([AAODTE],4)) AS Date_Open
FROM AUNPRDF_AAA802
GROUP BY DateDiff("d",[Date_Open],Date()), AUNPRDF_AAA802.AAAGE, AUNPRDF_AAA802.AAWKSP, AUNPRDF_AAA802.AAORD, AUNPRDF_AAA802.AASWKT, AUNPRDF_AAA802.AANAME, AUNPRDF_AAA802.AANME, AUNPRDF_AAA802.AASDE1, AUNPRDF_AAA802.AASDE2, AUNPRDF_AAA802.AASDE3, CDate(Right([AAODTE],2) & "/" & Mid([AAODTE],5,2) & "/" & Left([AAODTE],4)), Date(), AUNPRDF_AAA802.AARTYP
HAVING (((AUNPRDF_AAA802.AAWKSP)="AP") AND ((AUNPRDF_AAA802.AARTYP)="1")) OR (((AUNPRDF_AAA802.AAWKSP)="KR") AND ((AUNPRDF_AAA802.AARTYP)="1") AND (("WP")<>False)) OR (((AUNPRDF_AAA802.AAWKSP)="WP") AND ((AUNPRDF_AAA802.AARTYP)="1") AND (("KR")<>False));

[lsingh165]

Thanks Bob.......I used your query and Now enter parameter value Today (Today's date) doesn't comes up. but still query ask me to Date_Open as enter parameter value This is the screen shot. Is thr any way we can get rid of it. I don't want this window to pop up when i run query