Subtracting number from Date Format in qry 2

[james33]

Hallo all,
I would appreciate some help with a form I am using which is based on a query (qryUnpaidAccountJobs) designed to extract from the table
tblHirejobs those records,

1. Whose status is "Unpaid "
2. Whose payment type is "Account"
3. Whose "ageddate" is greater than the number which the user is asked to input before the Form opens (i.e. an Expression [Please enter Number of Days Overdue]) and is calculated by (AgeingInput: Date()-ageddate])
and on the Criteria line is ">[Please Enter Number of Days Overdue])

The 'Trouble' is that even though I have some jobs that fit the criteria (i.e. their 'ageddate' is 145 days) if I enter any number greater than 1 They do not show up, and if I enter 0, they all show up.

I feel it must be something to do with type of Format,
But cannot for the life of me figure it out.
The fields I would be checking would be yyyy/mm/dd
and the System short date is set the same as I use a Text based organiser that needs it that way for sorting.
Any help would be appreciated.
Thank you.
James33

 

[tlbroadbent]

Please go to the SQL View of your query, copy the query and post it in the forum so we can analyze it

Thanks, Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[grobertson]

Here's a similar question. What is the formula for finding the total time. Example: Total Time= Time1 + Time 2 I want to do a query that will take the sum of time from Field 1 and Field 2, and display the result in Field 3 of my query.

Thank you.
grobertson1@msn.com

 

[tlbroadbent]

grobertson,

Please post your question as a new thread.

Thanks, Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[james33]

Hello again,
Here is the SQL of the qry and it's funny but I'm feeling a little embarassed showing it it's a bit like that dream where you imagine you're running down the street and then you realise you're naked!
Oh well no-one learn't anything by not risking anything so here goes....

SELECT HireJobs.*, Customers.*, MachinesH.*, MachineType.*, +Date()-[DateBack] AS AgeingInput
FROM MachineType INNER JOIN (MachinesH INNER JOIN (Customers INNER JOIN HireJobs ON Customers.CustomerID = HireJobs.CustomerID) ON MachinesH.MachineSerNo = HireJobs.MachineSerNo) ON MachineType.MachineTypeID = MachinesH.MachineTypeID
WHERE (((+Date()-[DateBack])>[Input Days]) AND ((HireJobs.JobStatus)="In") AND ((HireJobs.TransType)="Account") AND ((HireJobs.Paid)="No"))
ORDER BY Customers.Last_Name;

hmm is that an extra ( before the WHERE condition?
OK, go ahead, but be a little indulgent i'm only a "technical user"
regards
Jim

 

[tlbroadbent]

You are almost there. A little tweak should do it. You need to use the DateDiff function to find the difference between dates. You can't just subtract one date from another. Here is the modified query.

SELECT HireJobs.*, Customers.*, MachinesH.*, MachineType.*, Datediff("d", [DateBack], Date()) AS AgeingInput
FROM MachineType INNER JOIN (MachinesH INNER JOIN (Customers INNER JOIN HireJobs ON Customers.CustomerID = HireJobs.CustomerID) ON MachinesH.MachineSerNo = HireJobs.MachineSerNo) ON MachineType.MachineTypeID = MachinesH.MachineTypeID
WHERE Datediff("d", [DateBack], Date())>[Input Days] AND HireJobs.JobStatus="In" AND HireJobs.TransType="Account" AND HireJobs.Paid="No" ORDER BY Customers.Last_Name; Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[james33]

Thanks very much for the SQL but the problem remains, is there a possibility that as Datediff returns a Variant type that I cannot Insert just a number into the msgbox, or that I need to reformat the Datediff.
Because now with 3 records that have a Datediff of 7,165, and 7 respectively.

When the msgbox comes up saying [Input Days]
if I enter 7 it returns 0 records
if I enter a number less than 7 it returns 2 records both of 7,
whereas if I enter 1 it shows all 3

What I want to filter is the records which have not been paid for 30 days or 60 days or 90 days and then send a reminder using a report.
Thanks Terry for your help so far
Jim

 

[james33]

Addendum,
When I enter 2 in msgbox it still shows the 2 records with Datediff of 7 days.
Only when I enter 1 do they all show up

Regards
Jim

 

[tlbroadbent]

Datediff return an integer! Please post the entire Select statement again so I can analyze why the selection criteria isn't working. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[james33]

Here it is,
and my apologies
"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." (I read Variant in the VBHelp on Datediff and assumed too much I guess)

SELECT HireJobs.*, Customers.*, MachinesH.*, MachineType.*, Datediff("d", [DateBack], Date()) AS AgeingInput
FROM MachineType INNER JOIN (MachinesH INNER JOIN (Customers INNER JOIN HireJobs ON Customers.CustomerID = HireJobs.CustomerID) ON MachinesH.MachineSerNo = HireJobs.MachineSerNo) ON MachineType.MachineTypeID = MachinesH.MachineTypeID
WHERE Datediff("d", [DateBack], Date())>[Input Days] AND HireJobs.JobStatus="In" AND HireJobs.TransType="Account" AND HireJobs.Paid="No"
ORDER BY Customers.Last_Name;

I did copy and paste it in though..

Regards
Jim

 

[tlbroadbent]

My apologies. VBA help says the following regarding Datediff.

"Returns a Variant (Integer) containing the specified part of a given date."

Regardless, the value returned can be treated as an integer. I can't see why the query would fail but your analysis seems sound given the result. How about trying a different approach? I actually like this query better. I think it is easier to understand.

SELECT [Input Days] As AgeingInput, HireJobs.*, Customers.*, MachinesH.*, MachineType.*
FROM MachineType INNER JOIN (MachinesH INNER JOIN (Customers INNER JOIN HireJobs ON Customers.CustomerID = HireJobs.CustomerID) ON MachinesH.MachineSerNo = HireJobs.MachineSerNo) ON MachineType.MachineTypeID = MachinesH.MachineTypeID
WHERE [DateBack]<Dateadd(&quot;d&quot;, 0-Val([Input Days]), Date()) AND HireJobs.JobStatus=&quot;In&quot; AND HireJobs.TransType=&quot;Account&quot; AND HireJobs.Paid=&quot;No&quot;
ORDER BY Customers.Last_Name; Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[james33]

Thanks Terry,
My brain has just gone a bit Dead for a while, inputting that last SQL has aged eveything to 2 days so I will study what I may have overlooked telling you, I'll also study the arguments for DateAdd and I'll come back tomorrow and post further, thanks a lot for the help so far
&quot;The mark of a great man is his willingness to train a successor&quot;

Regards
Jim

 

[james33]

Whoopee!
Thanks for your help Terry and I managed with the original SQL + the Datediff function you showed me + the Val function from the last one you posted and after a lot of fruitless tooing and froing with those darn brackets. They are so soporific, better than sheep!
Here is the stuff that worked in the end and I guess the Now() helped too.

SELECT HireJobs.*, Customers.*, MachinesH.*, MachineType.*, DateDiff("d",(HireJobs.DateBack),Now()) AS AgeingInput
FROM MachineType INNER JOIN (MachinesH INNER JOIN (Customers INNER JOIN HireJobs ON Customers.CustomerID = HireJobs.CustomerID) ON MachinesH.MachineSerNo = HireJobs.MachineSerNo) ON MachineType.MachineTypeID = MachinesH.MachineTypeID
WHERE (((DateDiff("d",([HireJobs].[DateBack]),Now()))>Val([How Many Days Overdue])) AND ((HireJobs.JobStatus)="In") AND ((HireJobs.TransType)="Account") AND ((HireJobs.Paid)="No"))
ORDER BY Customers.Last_Name;

Thanks again I really appreciate the help.
Regards
Jim