Calculating number of days in a quering

[Jan T.]

Can I use Min and Max functions in a query?

I tried to build a query where I had the fields or columns under neath.
In the Column NUMOFDAYS, I tried to write an expression but it just gives
me an error saying; Wrong number of arguments or something like that.

Let's say I wrote a simple expression;
NUMOFDAYS: Min([SICKDATE, OKDATE) just for testing a function.
- it would give me an error. (I even added Group by and Expression).

What do I do wrong?

I want to find out how many days sick in January (see FROMDATE TODATE )

SICKDATE OKDATE FROMDATE TODATE NUMOFDAYS
12/28/2007 01/03/2008 01/01/2008 01/31/2008
3
01/05/2008 01/11/2008 01/01/2008 01/31/2008
6
01/26/2008 02/03/2008 01/01/2008 01/31/2008
5
02/05/2008 02/09/2008 01/01/2008 01/31/2008
0

I tried this formula in Excel and that work like a dream;
NUMOFDAYS
= MIN(TODATE, MAX(OKDATE, FROMDATE)) - MIN(TODATE, MAX(FROMDATE, SICKDATE))

Is it possible to use the same function as an expression in the field
NUMOFDAYS? (In the query builder)

Any suggestions are very much appreaciated.

Regards

Jan T.

 

[Michel Walsh]

A database works vertically, not horizontally. MIN and MAX operates over a
field (or an expression) across the records, vertically, for the given
groups. So, basically, it is MIN( fieldName ) or MAX( fieldName ). If you
want the minimun between two fields, horizontally, you can use:


iif( a < b, a, b )


for the minimum between a and b (both not null); for their maximum, use:

iif( a> b, a, b )



So, instead of Min(sickdate, okdate), use: iff(sickdate < okdate, sickdate,
okdate)



Hoping it may help,
Vanderghast, Access MVP

 

[Jan T.]

Thank you very much! That will do the trick.

Jan T

A database works vertically, not horizontally. MIN and MAX operates over a
field (or an expression) across the records, vertically, for the given
groups. So, basically, it is MIN( fieldName ) or MAX( fieldName ). If you
want the minimun between two fields, horizontally, you can use:


iif( a < b, a, b )


for the minimum between a and b (both not null); for their maximum, use:

iif( a> b, a, b )



So, instead of Min(sickdate, okdate), use: iff(sickdate < okdate,
sickdate, okdate)



Hoping it may help,
Vanderghast, Access MVP

Can I use Min and Max functions in a query?

I tried to build a query where I had the fields or columns under neath.
In the Column NUMOFDAYS, I tried to write an expression but it just gives
me an error saying; Wrong number of arguments or something like that.

Let's say I wrote a simple expression;
NUMOFDAYS: Min([SICKDATE, OKDATE) just for testing a function.
- it would give me an error. (I even added Group by and Expression).

What do I do wrong?

I want to find out how many days sick in January (see FROMDATE
TODATE )

SICKDATE OKDATE FROMDATE TODATE
NUMOFDAYS
12/28/2007 01/03/2008 01/01/2008 01/31/2008 3
01/05/2008 01/11/2008 01/01/2008 01/31/2008 6
01/26/2008 02/03/2008 01/01/2008 01/31/2008 5
02/05/2008 02/09/2008 01/01/2008 01/31/2008 0

I tried this formula in Excel and that work like a dream;
NUMOFDAYS
= MIN(TODATE, MAX(OKDATE, FROMDATE)) - MIN(TODATE, MAX(FROMDATE,
SICKDATE))

Is it possible to use the same function as an expression in the field
NUMOFDAYS? (In the query builder)

Any suggestions are very much appreaciated.

Regards

Jan T.

 

[Jan T.]

Well, that explains the error.

Tried this then:

IIF([TODATE]<[OKDATE],[TODATE],IIF([OKDATE]>[FROMDATE],[OKDATE],[FROMDATE]))
-
IIF([TODATE]<[SICKDATE],[TODATE],IIF([SICKDATE]>[FROMDATE],[SICKDATE],[FROMDATE]))

Keep formula in one line. Still getting an error though. Now it says the
expression is may be too complicated to be evaluated.
May be I am better of doing this in vba with ado and a recordset?

Jan

A database works vertically, not horizontally. MIN and MAX operates over a
field (or an expression) across the records, vertically, for the given
groups. So, basically, it is MIN( fieldName ) or MAX( fieldName ). If you
want the minimun between two fields, horizontally, you can use:


iif( a < b, a, b )


for the minimum between a and b (both not null); for their maximum, use:

iif( a> b, a, b )



So, instead of Min(sickdate, okdate), use: iff(sickdate < okdate,
sickdate, okdate)



Hoping it may help,
Vanderghast, Access MVP

Can I use Min and Max functions in a query?

I tried to build a query where I had the fields or columns under neath.
In the Column NUMOFDAYS, I tried to write an expression but it just gives
me an error saying; Wrong number of arguments or something like that.

Let's say I wrote a simple expression;
NUMOFDAYS: Min([SICKDATE, OKDATE) just for testing a function.
- it would give me an error. (I even added Group by and Expression).

What do I do wrong?

I want to find out how many days sick in January (see FROMDATE
TODATE )

SICKDATE OKDATE FROMDATE TODATE
NUMOFDAYS
12/28/2007 01/03/2008 01/01/2008 01/31/2008 3
01/05/2008 01/11/2008 01/01/2008 01/31/2008 6
01/26/2008 02/03/2008 01/01/2008 01/31/2008 5
02/05/2008 02/09/2008 01/01/2008 01/31/2008 0

I tried this formula in Excel and that work like a dream;
NUMOFDAYS
= MIN(TODATE, MAX(OKDATE, FROMDATE)) - MIN(TODATE, MAX(FROMDATE,
SICKDATE))

Is it possible to use the same function as an expression in the field
NUMOFDAYS? (In the query builder)

Any suggestions are very much appreaciated.

Regards

Jan T.

 

[Conan Kelly]

Jan T,

What version of Access are you using?

I used your formula exactly as posted and it worked for me, but it will
probably need a little tweaking. My results were 2 (instead of 3), 6, 5, &
0

HTH,

Conan

Well, that explains the error.

Tried this then:

IIF([TODATE]<[OKDATE],[TODATE],IIF([OKDATE]>[FROMDATE],[OKDATE],[FROMDATE]))
-
IIF([TODATE]<[SICKDATE],[TODATE],IIF([SICKDATE]>[FROMDATE],[SICKDATE],[FROMDATE]))

Keep formula in one line. Still getting an error though. Now it says the
expression is may be too complicated to be evaluated.
May be I am better of doing this in vba with ado and a recordset?

Jan

A database works vertically, not horizontally. MIN and MAX operates over a
field (or an expression) across the records, vertically, for the given
groups. So, basically, it is MIN( fieldName ) or MAX( fieldName ). If you
want the minimun between two fields, horizontally, you can use:


iif( a < b, a, b )


for the minimum between a and b (both not null); for their maximum, use:

iif( a> b, a, b )



So, instead of Min(sickdate, okdate), use: iff(sickdate < okdate,
sickdate, okdate)



Hoping it may help,
Vanderghast, Access MVP

Can I use Min and Max functions in a query?

I tried to build a query where I had the fields or columns under neath.
In the Column NUMOFDAYS, I tried to write an expression but it just
gives
me an error saying; Wrong number of arguments or something like that.

Let's say I wrote a simple expression;
NUMOFDAYS: Min([SICKDATE, OKDATE) just for testing a function.
- it would give me an error. (I even added Group by and Expression).

What do I do wrong?

I want to find out how many days sick in January (see FROMDATE TODATE )

SICKDATE OKDATE FROMDATE TODATE NUMOFDAYS
12/28/2007 01/03/2008 01/01/2008 01/31/2008 3
01/05/2008 01/11/2008 01/01/2008 01/31/2008 6
01/26/2008 02/03/2008 01/01/2008 01/31/2008 5
02/05/2008 02/09/2008 01/01/2008 01/31/2008 0

I tried this formula in Excel and that work like a dream;
NUMOFDAYS
= MIN(TODATE, MAX(OKDATE, FROMDATE)) - MIN(TODATE, MAX(FROMDATE,
SICKDATE))

Is it possible to use the same function as an expression in the field
NUMOFDAYS? (In the query builder)

Any suggestions are very much appreaciated.

Regards

Jan T.

 

[Jan T.]

I am using Access 2000.

Jan

Jan T,

What version of Access are you using?

I used your formula exactly as posted and it worked for me, but it will
probably need a little tweaking. My results were 2 (instead of 3), 6, 5,
& 0

HTH,

Conan

Well, that explains the error.

Tried this then:

IIF([TODATE]<[OKDATE],[TODATE],IIF([OKDATE]>[FROMDATE],[OKDATE],[FROMDATE]))
-
IIF([TODATE]<[SICKDATE],[TODATE],IIF([SICKDATE]>[FROMDATE],[SICKDATE],[FROMDATE]))

Keep formula in one line. Still getting an error though. Now it says the
expression is may be too complicated to be evaluated.
May be I am better of doing this in vba with ado and a recordset?

Jan

A database works vertically, not horizontally. MIN and MAX operates over
a field (or an expression) across the records, vertically, for the given
groups. So, basically, it is MIN( fieldName ) or MAX( fieldName ). If
you want the minimun between two fields, horizontally, you can use:


iif( a < b, a, b )


for the minimum between a and b (both not null); for their maximum, use:

iif( a> b, a, b )



So, instead of Min(sickdate, okdate), use: iff(sickdate < okdate,
sickdate, okdate)



Hoping it may help,
Vanderghast, Access MVP


Can I use Min and Max functions in a query?

I tried to build a query where I had the fields or columns under neath.
In the Column NUMOFDAYS, I tried to write an expression but it just
gives
me an error saying; Wrong number of arguments or something like that.

Let's say I wrote a simple expression;
NUMOFDAYS: Min([SICKDATE, OKDATE) just for testing a function.
- it would give me an error. (I even added Group by and Expression).

What do I do wrong?

I want to find out how many days sick in January (see FROMDATE TODATE )

SICKDATE OKDATE FROMDATE TODATE NUMOFDAYS
12/28/2007 01/03/2008 01/01/2008 01/31/2008 3
01/05/2008 01/11/2008 01/01/2008 01/31/2008 6
01/26/2008 02/03/2008 01/01/2008 01/31/2008 5
02/05/2008 02/09/2008 01/01/2008 01/31/2008 0

I tried this formula in Excel and that work like a dream;
NUMOFDAYS
= MIN(TODATE, MAX(OKDATE, FROMDATE)) - MIN(TODATE, MAX(FROMDATE,
SICKDATE))

Is it possible to use the same function as an expression in the field
NUMOFDAYS? (In the query builder)

Any suggestions are very much appreaciated.

Regards

Jan T.

 

[Jan T.]

Oh, I had written something wrong in my Formula..
Now it works fine! Thanx!

Jan

I am using Access 2000.

Jan

Jan T,

What version of Access are you using?

I used your formula exactly as posted and it worked for me, but it will
probably need a little tweaking. My results were 2 (instead of 3), 6, 5,
& 0

HTH,

Conan

Well, that explains the error.

Tried this then:

IIF([TODATE]<[OKDATE],[TODATE],IIF([OKDATE]>[FROMDATE],[OKDATE],[FROMDATE]))
-
IIF([TODATE]<[SICKDATE],[TODATE],IIF([SICKDATE]>[FROMDATE],[SICKDATE],[FROMDATE]))

Keep formula in one line. Still getting an error though. Now it says the
expression is may be too complicated to be evaluated.
May be I am better of doing this in vba with ado and a recordset?

Jan


"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> skrev i melding
A database works vertically, not horizontally. MIN and MAX operates over
a field (or an expression) across the records, vertically, for the given
groups. So, basically, it is MIN( fieldName ) or MAX( fieldName ). If
you want the minimun between two fields, horizontally, you can use:


iif( a < b, a, b )


for the minimum between a and b (both not null); for their maximum,
use:

iif( a> b, a, b )



So, instead of Min(sickdate, okdate), use: iff(sickdate < okdate,
sickdate, okdate)



Hoping it may help,
Vanderghast, Access MVP


Can I use Min and Max functions in a query?

I tried to build a query where I had the fields or columns under
neath.
In the Column NUMOFDAYS, I tried to write an expression but it just
gives
me an error saying; Wrong number of arguments or something like that.

Let's say I wrote a simple expression;
NUMOFDAYS: Min([SICKDATE, OKDATE) just for testing a function.
- it would give me an error. (I even added Group by and Expression).

What do I do wrong?

I want to find out how many days sick in January (see FROMDATE
TODATE )

SICKDATE OKDATE FROMDATE TODATE NUMOFDAYS
12/28/2007 01/03/2008 01/01/2008 01/31/2008 3
01/05/2008 01/11/2008 01/01/2008 01/31/2008 6
01/26/2008 02/03/2008 01/01/2008 01/31/2008 5
02/05/2008 02/09/2008 01/01/2008 01/31/2008 0

I tried this formula in Excel and that work like a dream;
NUMOFDAYS
= MIN(TODATE, MAX(OKDATE, FROMDATE)) - MIN(TODATE, MAX(FROMDATE,
SICKDATE))

Is it possible to use the same function as an expression in the field
NUMOFDAYS? (In the query builder)

Any suggestions are very much appreaciated.

Regards

Jan T.