Days Between Dates 4 beginners please? 2

[DesertTrip]

I am new at Access so I need any suggestions typed out for a beginner.

In my design query, I have a column for BeginingDate and a column for DateDone. I need to count the days between these two entries.

I researched everywhere and found this:

Syntax: NumDays = days365(StartDate, EndDate)

Arguments
StartDate: Enter as serial date numbers or date strings.
EndDate: Enter as serial date numbers or date strings.

My Questions-

Am I correct in interpreting this as [NumDays = days365(BeginingDate, DateDone)] and if so, WHERE do I paste it? Is it to go in a new column as an expression? Under or in an existing column? Do I put it in the Report field/text box?

I tried this as an expression and it didn't work.

 

[dalchri]

In design view, what you would want to do is paste the expression:
[tt]
NumDays: days365(StartDate, EndDate)
[/tt]
into a new column in the "field" row. "NumDays" will act as the title for the new column when you run the query.

 

[DesertTrip]

Hi and thanks Dalchri!!!

I wrote it, susbtituting the names of my columns, as such: NumDays: days365(830BDate, Date) Then I entered it in the field row of a new column as you suggested.

But, it immediately errored saying: "The Expression You Entered Contains Invalid Syntax. You may have entered an operand without an operator".

When I press OKAY, it shows the BDate of the command as highlighted. Maybe it doesn't like the title of my column???

I then wrote it out as such:
NumDays: days365("830BDate", "Date&quot

This time, I made it all the way until I hit View. Then an error stated: "Undefined Function 'days365' in expression"

Any idea where I am going wrong?

 

[dalchri]

I was wondering where the function days365 came from. I've never heard of it before and it looks like access doesn't have it either. Also, any field names that start with a number such as 830BDate need to be enclosed with square brackets.

I'd try revising the formula for the new column as follows:
[tt]
NumDays: DateDiff('d',[830BDate],Date)
[/tt]
if you get negative numbers, just reverse the date order:
[tt]
NumDays: DateDiff('d',Date,[830BDate])
[/tt]
The DateDiff function that I used will return the difference in days between two dates.

 

[DesertTrip]

YESSSSSSSSSSSSSSSSS!!!!!!!!!!!! IT WORKS!!!!

Thank you! Thank you and THANK YOU!!!

You wouldn't happen to be able to recommend any USER FRIENDLY books, websites, or cheat guide referrences that teach SQL or that offer the more common commands, would ya? I can't find most of what I need in my Access Bible.

 

[mikevh]

I've found the following site very helpful for SQL:

http://www.dcs.napier.ac.uk/~andrew/sql/

 

[dalchri]

Unfortunately, I've learned most of my stuff by looking at other people's examples.;-) One quick recommendation that I can remember from the visual query designer is as follows...

When you have to create a calculated field, like your NumDays field, try right clicking on the "field" row. Select Build... from the context menu that appears. On the left hand side of the screen that appears is a folder called functions. Double click on this folder. Click on the build-in functions folder that appears. In the two lists to the right you will get a darn near complete, categorized list of all functions available in Access queries.

I've found this list to be a good starting point and reference for finding new and appropriate functions. Sometimes I can tell by the name its what I'm after, other times I have to dig into the help docs to verify what the functions actually do. Either way, its been a good resource for me.

Hope it helps and thanks for the feedback! Does wonders for morale!

 

[DesertTrip]

Thanks, Mikevh! I just checked it out and bookmarked it as it looks like something I can definitely use.

 

[DesertTrip]

And thanks again to you, Dalchri. I will go play with that and see what I can come up with for a select problem I am having.

 

[WildHare]

You know, DesertTrip, that if you want just the number of days between two other dates, you can just subtract the most recent (largest) one from the oldest (smallest) one:

DaysElapsed: EndDate-StartDate

If EndDate and StartDate are both DATE datatypes, this would appear to be the simplest solution to your problem.

Bottom Line: Date Arithmetic is easier if you don't try to tie yourself in knots first, and remember that a DATE is just a serial number.

Jim
How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm