Date 1 - Date 2

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I would like to carry out a calculation: Date 1 - Date 2 = or > 30.
Unfortunately, the Date 2 is not a date field but a If statement that may
contain a short date or text. Can someone advise me how to turn Date 2 to
date field to allow the format to work.

Thanks,

Ray
 
CDate() will convert anything that "looks like" (or is) a date into a
Date variable. However, it will throw an error if you ask it to
convert something that can't be read as a date. If you are doing this
in VBA, you can easily check the prospective convertee with IsDate()
first. Otherwise, you can do something like:

IIF(IsDate(SomeFunction),CDate(SomeFunction),#DefaultDate#)


I would like to carry out a calculation: Date 1 - Date 2 = or > 30.
Unfortunately, the Date 2 is not a date field but a If statement that may
contain a short date or text. Can someone advise me how to turn Date 2 to
date field to allow the format to work.

Thanks,

Ray
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Hi,


CDate(Date1) - CDate(Date2)


should cover all the cases (well, still left the traditional imprecision
"yyyy-mm-dd" or "dd-mm-yyyy" or "mm-dd-yyyy", but if you use English name
for month, and four digits for year, that should be without problem, on an
English PC).

Hoping it may help,
Vanderghast, Access MVP
 
Michel,

Thanks for your formula and now I am working below formula and just wonder
how to make a parameter query for inputting the number of days. Any idea?

CDate(Date1) - CDate(Date2)>=14 <- I want to create a parameter query to
inputting this number since this is a variable.

Thanks,

Ray
 
Hi,



It is simply a matter to give a "name" for that parameter, you would be
prompted for it:


( CDate(date1) - CDate(date2)) >= [number of days ]


( the use of [ ] is required if the name is invalid, like here, with spaces
in it).



Hoping it may help
Vanderghast, Access MVP
 
Michel,

Thanks for your advice. The results with [] and without parameter query
were quite different.

( CDate(date1) - CDate(date2)) >= [number of days ] Entered 7 in bracket
Result = 3 rows (only 8, 8, 9 days)
( CDate(date1) - CDate(date2)) >= 7 No
parameter query Result = 13 rows (from 8-40 days)

Did I mis-understand your formula?

Regards,

Ray

Michel Walsh said:
Hi,



It is simply a matter to give a "name" for that parameter, you would be
prompted for it:


( CDate(date1) - CDate(date2)) >= [number of days ]


( the use of [ ] is required if the name is invalid, like here, with spaces
in it).



Hoping it may help
Vanderghast, Access MVP


Ray said:
Michel,

Thanks for your formula and now I am working below formula and just wonder
how to make a parameter query for inputting the number of days. Any idea?

CDate(Date1) - CDate(Date2)>=14 <- I want to create a parameter query to
inputting this number since this is a variable.

Thanks,

Ray
Click to expand...
on
an that
may
Click to expand...
Date
2
Click to expand...
Click to expand...
 
Hi,


You should not enter [7] since this would represent a field name 7 (
the name of the field is seven, like a number nine in a Star Treck (Voyager)
serie). You should answer 7 when prompted with
number of day


since in [number of day ] , the field/parameter name is now number of
day.


Vanderghast, Access MVP


Ray said:
Michel,

Thanks for your advice. The results with [] and without parameter query
were quite different.

( CDate(date1) - CDate(date2)) >= [number of days ] Entered 7 in bracket
Result = 3 rows (only 8, 8, 9 days)
( CDate(date1) - CDate(date2)) >= 7 No
parameter query Result = 13 rows (from 8-40 days)

Did I mis-understand your formula?

Regards,

Ray

Michel Walsh said:
Hi,



It is simply a matter to give a "name" for that parameter, you would be
prompted for it:


( CDate(date1) - CDate(date2)) >= [number of days ]


( the use of [ ] is required if the name is invalid, like here, with spaces
in it).



Hoping it may help
Vanderghast, Access MVP


Ray said:
Michel,

Thanks for your formula and now I am working below formula and just wonder
how to make a parameter query for inputting the number of days. Any idea?

CDate(Date1) - CDate(Date2)>=14 <- I want to create a parameter
Click to expand...
Click to expand...
query
to English
name on Date
Click to expand...
Click to expand...
 
Back
Top