calculating differences between dates and then totaling

[demogorgan]

Hi,

So I have made a simple database & have it linking to forms & a few reports, again basic.

I have however a requirement where I have two dates, a start & finish date for each record in my table.
What I need to be able to do is calculate the difference between the two dates & then total the different numbers in a report / form, so how many records have a difference of 1 day, 2 days etc.

I have worked out how to calculate the difference in a query using the following.

DateDiff("d",[Creation Date],[Date_Booking_Confirmed])

But now I am stumped, I cannot seem to work the last part out.

Any help would be greatly appreciated

Thanks in advance
Demo

 

[plog]

then total the different numbers in a report / form

You could use the =SUM in a report header/footer. And you could use a DSum in either a form or report. You could also build a totals query and SUM the data there.

 

[Mark_]

INT([Date_Booking_Confirmed]) - INT([Creation Date]) = Difference

Dates are stored as a number with the "Date" as the integer portion and the "Time" as the decimal portion.

Today is not 29/11/17 it is something like 42448.

 

[demogorgan]

You could use the =SUM in a report header/footer. And you could use a DSum in either a form or report. You could also build a totals query and SUM the data there.

Hi,

Thanks for the response - I have been trying to work out how to acomplish this in a query but thus far I am drawing a blank, I have it calculating the differences but as I say I just cannot work out how to make it total the individual numbers.

Thanks again
Demo

 

[plog]

You didn't give us query nor field names ot work with so;

FieldName is the name of the field based on the DateDiff calculation you initially posted
QueryName is the name of the query where FieldName is

So a totals query would be this:

SELECT SUM(FieldName) AS TotalDays FROM QueryName;

 

[demogorgan]

You didn't give us query nor field names ot work with so;

FieldName is the name of the field based on the DateDiff calculation you initially posted
QueryName is the name of the query where FieldName is

So a totals query would be this:

SELECT SUM(FieldName) AS TotalDays FROM QueryName;

Hi,

I am having a particulary :banghead:day I cannot work this out from your code, I am very new to Access - when I input it into my query & change the fields I get a syntax error

In lieu of permenant names I am using "Difference" as my datediff field & "query1" as my query name.

Thank you again for your help

Demo

 

[Mark_]

Can you put up a picture of your current query format? Something that looks like the attached?

 

[Pat Hartman]

In a query:

Select fld1, fld2, DateDiff("d",[Creation Date],[Date_Booking_Confirmed]) As DaysDiff, fld4
From YourTable;

Then in your report, you would sum or sort on the DaysDiff field.


@Mark,
Is there really a reason to NOT use the proper date function?

 

[MarkK]

I think to count the rows that have the same difference, you need to use a GROUP BY clause, like...

SELECT Date2-Date1 As Difference, Count(*) As DiffCount
FROM Table
GROUP BY Date2-Date1

This has the effect of rolling all the differences that are the same into a single row, and then the Count() function counts them.
hth
Mark

 

[Mark_]

@Pat,
Habit, mostly. I tend to use DiffDate for month/years.

 

[demogorgan]

Hi,

I want to thank everyone for their help so far I am very grateful - I am just not getting it I am afraid...

I have attached a snip of my query so far as requested

Demo

 

[plog]

Perhaps you can demonstrate what you ultimately want with data? Give us 2 sets:

A. Starting sample data from your original table(s). Include table and field names and enough sample data to cover all cases.

B. Expected results of A. Show us the data you want returned in your ultimate query when you feed it the data in A.

Again, show us data. Don't re-explain your desires, show us what you want by using data.

 

[demogorgan]

Perhaps you can demonstrate what you ultimately want with data? Give us 2 sets:

A. Starting sample data from your original table(s). Include table and field names and enough sample data to cover all cases.

B. Expected results of A. Show us the data you want returned in your ultimate query when you feed it the data in A.

Again, show us data. Don't re-explain your desires, show us what you want by using data.

Hi,

I will try.
The sample data for A is in the attachment tblOrder_Details - the result I want is in the attachment orders.

Thank you

Demo

 

[plog]

That's in a form correct? In that case I would do a simple query using your date diff calculation and then on the form I would do a DCOUNT() to determine the count of each. For the 1 day area the DCOUNT() would look like this:


=DCOUNT("[FieldName]", "QueryName", "[FieldName]='1'")

 

[demogorgan]

That's in a form correct? In that case I would do a simple query using your date diff calculation and then on the form I would do a DCOUNT() to determine the count of each. For the 1 day area the DCOUNT() would look like this:


=DCOUNT("[FieldName]", "QueryName", "[FieldName]='1'")

Hi,

Yes, its in a form - when I reproduce your code it creates an #error

=DCOUNT("[Difference]", "Query1", "[Difference]='1'")

Have I not changed the code correctly? & was I suppsed to enter this into a text box as the control source?


Thanks

Demo

 

[plog]

Yes, it should be the control source of an input box. That looks correct. Try this and see if you get anything:

=DCOUNT("[Difference]", "Query1")

 

[demogorgan]

Yes, it should be the control source of an input box. That looks correct. Try this and see if you get anything:

=DCOUNT("[Difference]", "Query1")

Hi,

yes!! it returns the total returned by the query - 6 records

Demo

 

[demogorgan]

Ok I think I managed to work out the parameter part

=DCount("Difference","query1","Difference=" & 1)

This seems to be returning the correct values - how does it look?

Demo

 

[plog]

If its returning the correct results you've got it. I really thought the first set of code you had for it should work. As well that ends well.

 

[demogorgan]

If its returning the correct results you've got it. I really thought the first set of code you had for it should work. As well that ends well.

You are a star - thank you for all your help !!