Date 1 - Date 2

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
 
M

Michel Walsh

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
 
R

Ray

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
 
M

Michel Walsh

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
 
R

Ray

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...
 
M

Michel Walsh

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...
 
Top