Date

[Elamranii]

Hi,
If I have the following table, How do I get the total of:
5/31/05 Minus 05/04/05 and 05/03/05 Minus 05/03/05

Period MEMBID ADMITDT DISCHDT
Current 00927771301 05/31/05
Current 00927771301 05/03/05 05/04/05
Current 00927771301 05/01/05 05/03/05

Thanks in advance
Ismail

 

[dhookom]

Your question is about as vague as your subject line.

Does this question pertain to a report or form or query or what?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[earthandfire]

Simply use ordinary arithmetic.

eg
Dim x as long
x = (CDate("5/31/05") - CDate("05/04/05")) + (CDate("05/03/05") - CDate("05/03/05"))


Dates are simply numbers behind the scenes (0 = 31/12/1899)

Hope this helps.

 

[ProgramError]

Now you've totally confused him!

Program Error
Programmers do it one finger at a time!

 

[dhookom]

Dates are basically numbers. To get the number of days between two dates, you just subtract the earlier date from the later date.

Dates can be very confusing since a format of m/d/yy is expected in SQL statements.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[earthandfire]

Program Error, how???

 

[RoyVidar]

Must be cause you're a day off, earthandfire;-)

[tt]? format$(0,"dd/mm/yyyy")[/tt]

Roy-Vidar

 

[dhookom]

earthandfire,
Your earlier reply used two different date formats: mm/dd/yy and dd/mm/yyyy.

I'm not sure why converting dates to text by wrapping them in quotes and then using CDate() is necessary.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[earthandfire]

Roy, well spotted (I was just seeing if anyone would notice).

dhookom:
(1) Date formats - for the calculations I used the OP's dates and formats. For my incorrect day 0 - thank you Roy, I inadvertently used UK format - because that's what I'm used to.
(2) It was just easier for the example code - and I thought that it would demonstrate the arithmetic more clearly. (Which is why I queried Program Error's reply to me.) Presumably the OP will use date fields in which case the conversion is obviously not needed.

 

[Elamranii]

Sorry to reply late. I solved the problem using excel.
Thank you all for you help.
Ismail