Count between 2 dates

[icemac]

I have 2 dates. How do i count the number of days between the two dates?

 

[Brianwarnock]

DateDiff function?

 

[icemac]

DateDiff («interval», «date1», «date2», «firstweekday», «firstweek»)

i understand what you enter into date1 and what you enter into date 2 but what do you enter into the other ones?

 

[Brianwarnock]

DateDiff("d",date1,date2) will give you the count of days.
I pointed at the function rather than spelt it out as it is a function that you are likely to use in the future thwerefore reading the help would have been useful, just incase you are a newbie and don't know you have to look up the functions in VBA help, apologies if teaching grandmother to suck eggs.

brian

 

[icemac]

what if i want to do this:

DateDiff("d",[SEARCH FOR START DATE]![Start Date],[SEARCH FOR END DATE]![End Date])

this doesnt work

 

[Brianwarnock]

Iceman Pm'd me with the text below, as i haven't got time to visit the forum today, I was merely picking up my emails, I hope somebody else can decipher what he is tryng to do.

DateDiff("d",[SEARCH FOR START DATE]![Start Date],[SEARCH FOR END DATE]![End Date])

when SEARCH FOR START DATE = Like "*" & [Forms]![SearchFrm]![Search2] & "*"

and SEARCH FOR END DATE = Like "*" & [Forms]![SearchFrm]![Search2] & "*"

I need this to work so i can count the number of days between my entered start date and my entered end date.

Brian

 

[ColinEssex]

Brians 4th post will do the job.

The DateDiff() function will not of course exclude weekends or public holidays.

Col

 

[Brianwarnock]

Managed a few minutes before tea, . I don't understand the use of Like in the expressions or what the whole scenario is trying to do, assuming that the dates are being entered onto a form then

DateDiff("d", forms!formname!startdate,forms!formname!enddate) will give the difference in days. [] are needed round the names if they contain blanks.

Brian

 

[icemac]

the like expression simply searches through you table to find any values that you type into the "search box." So if i type in 01/11/2006 and there are any dates that match this then it will be displayed

 

[gemma-the-husky]

icemac

i think we are struggling to understand what you are trying to achieve

you can just do enddate-startdate to retrieve the number of days between any two dates.

the datediff function provides more flexibility.

but presumably you actually want to achieve more than this

 

[icemac]

This does not work it never returns any figures

 

[icemac]

icemac

can i send somone a copy of my database so that you can have a look at it. this way it might be easier for me to describe my problem to you.

 

[Brianwarnock]

Zip and attach to a post

 

[icemac]

the file is too large can you post your email or PM to me.

 

[stopher]

Iceman Pm'd me with the text below, as i haven't got time to visit the forum today, I was merely picking up my emails, I hope somebody else can decipher what he is tryng to do.

DateDiff("d",[SEARCH FOR START DATE]![Start Date],[SEARCH FOR END DATE]![End Date])

when SEARCH FOR START DATE = Like "*" & [Forms]![SearchFrm]![Search2] & "*"

and SEARCH FOR END DATE = Like "*" & [Forms]![SearchFrm]![Search2] & "*"

I need this to work so i can count the number of days between my entered start date and my entered end date.

Yup, I'm confused. What exactly are you trying to do icemac?

You have a form called SearchFrm.

On the form you have a field called Search2 (presumably where you enter a date ?) Did you really mean Search1 for start date ? in which case you have two fields to enter a date range.

I assume these fields are unbounded and purely for entering a date range?

Then what do you want to happen? You want to calculate how many days between these two dates? Then brians suggestion will do the trick:
DateDiff("d", forms!formname!startdate,forms!formname!enddate)

The bit that people normally come unstuck at is getting the syntax right for the above.

What's the puspose of this:
SEARCH FOR END DATE = Like "*" & [Forms]![SearchFrm]![Search2] & "*"

I just don't get it. Are you trying to maybe find the start and end dates in a list of records ?

Stopher

 

[Brianwarnock]

the like expression simply searches through you table to find any values that you type into the "search box." So if i type in 01/11/2006 and there are any dates that match this then it will be displayed

Why do you need the like to find a date match? It suggests that your date string is in the middle of a string, are we talking text data here?

As to your DB being too big to zip and attach we only need enough data to illustrate and test the situation and any amendment that might be made. It is imperative that you use the forum as an individual may not have either the time or knowledge to help when somebody else is able to do so.

Brian

 

[icemac]

Basically i want to count the number of days between two dates. Thats it. I have a field in my booking table called Count of days and when i type in the two dates "Starts Date" and "End Date", into the fields "Start Date" and "End Date" (also in the booking table), i want the number of days between these two fields to appear in this field. Does that make more sense?

 

[stopher]

Basically i want to count the number of days between two dates. Thats it. I have a field in my booking table called Count of days and when i type in the two dates "Starts Date" and "End Date", into the fields "Start Date" and "End Date" (also in the booking table), i want the number of days between these two fields to appear in this field. Does that make more sense?

Yes. However, what you are wanting to do is store a calculated field. This is an absolute no-no in relational database design. The point is you can always calculate the difference whenever you need it eg in a form, query or report.

For example, in a form that has your table as its underlying source, add a new field and enter the DateDiff expression mentioned earlier

DateDiff("d", [start date],[end date])

I've assumed the names of then fields in your table are as above with spaces (tip: best not to use spaces in field names). Note that you are using the fields from the table to calculate the diff. This is better that referencing the values entered in the form boxes imho although the result is much the same.

Now when you enter a start date and end date in the boxes on your form, the difference will be shown in your new box.

hth
Stopher

 

[icemac]

Im sorry but im a bit of a noob when it comes to database design and not exactly sure what you mean now lol. Can someone PM me their email so i can send them them database for them to have a look at?

 

[Brianwarnock]

As I pointed out earlier in the thread as to your DB being too big to zip and attach we only need enough data to illustrate and test the situation and any amendment that might be made, so strip it down and attach it. It is imperative that you use the forum as an individual may not have either the time or knowledge to help when somebody else is able to do so.

What Stopher is saying is that you never store in your database information that can be calculated from data already in the database, thus remove Count of days from your table, and in any query , form, or report that needs to show count of days calculate it using DateDiff.

brian