calculating differences between dates and then totaling (1 Viewer)

demogorgan · Nov 29, 2017

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.

Code:

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 · Nov 29, 2017

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_ · Nov 29, 2017

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 · Nov 29, 2017

plog said:
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 · Nov 29, 2017

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:

Code:

SELECT SUM(FieldName) AS TotalDays FROM QueryName;

demogorgan · Nov 29, 2017

plog said:
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:

Code:

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_ · Nov 29, 2017

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

Pat Hartman · Nov 29, 2017

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 · Nov 29, 2017

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

Code:

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_ · Nov 29, 2017

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

demogorgan · Nov 29, 2017

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 · Nov 29, 2017

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 · Nov 29, 2017

plog said:
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 · Nov 29, 2017

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 · Nov 29, 2017

plog said:
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 · Nov 29, 2017

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 · Nov 29, 2017

plog said:
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 · Nov 29, 2017

Ok I think I managed to work out the parameter part

Code:

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

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

Demo

plog · Nov 29, 2017

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 · Dec 4, 2017

plog said:
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 !!

calculating differences between dates and then totaling (1 Viewer)

Registered User.

Banishment Pending

Longboard on the internet

Registered User.

Banishment Pending

Registered User.

Longboard on the internet

Attachments

Super Moderator

bit cruncher

Longboard on the internet

Registered User.

Attachments

Banishment Pending

Registered User.

Attachments

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Registered User.

Banishment Pending

Registered User.

Similar threads

Users who are viewing this thread