Calculating Dates in Access

[chayes1964]

I used this simple code to calculate the difference between the current date and another date for several records in an Access table:

Option Explicit

Function GetElapsedDays(interval)
Dim days As Long
days = Int(CSng(interval))
GetElapsedDays = days & " Days"
End Function

This works fine. However, my problem comes while working at the query level. I get datatype mismatch error when attempting to place limits on the results (i.e., when trying to retrieve all records where the elapsed number of days is greater than 20 days). Any suggestions on the proper formatting of criteria for the results of this date query?

 

[dekcool]

try this one
declare days as single
Dim days As Single

or

days = Clng(CSng(interval))
DEK

 

[Darrylles]

Hi,

If you get 'mismatch' errors, then you must be comparing 2 'fields' or 2 variables (or a mix) that aren't of the same type.

E.g. String "2" is = to long '2' - data mismatch.

The 'limit' field is not 'long' data type perhaps?

Sorry if if I insult your level of knowledge - (v little info).

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[RickSpr]

You've put a column in your query whose Field row contains GetElapsedDays(), right? Well, GetElapsedDays returns a string, since you cancatenated the word "Days" to it. If your Criteria row has >20 in it, you're trying to compare a string to a number--won't work.

You could perhaps use > "20 Days", but that will fail if you ever have 100+ days of difference. String comparison isn't reliable for this purpose.

I would modify your function to return just the number. (And by the way, I would declare explicit datatypes if I were you.) Then your > 20 would work. Of course, you'll have to concatenate "Days" on the form or report where you want to display it. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein