Query expression

[Danny]

Hi guys,
I posted a formula question on the Excel discussion group
(http://www.microsoft.com/office/com...?lang=en&cr=US&dg=microsoft.public.excel.misc)
- "Calculate days in a given month between two dates" and received two
different formula solutions that worked in Excel.

I am now trying to apply this formula in an Access query/report (changing
the cell ref A2 to [admission date] and B2 to [discharge date]) but get the
message "the expression you entered has a function containing the wrong
number of arguments."

I'm a bit of a newbie to Access, can anyone help at all?
thanks,

 

[Danny]

I have a simple design query with the following fields

customerID
Admission Date
Discharge Date

I then want an expression in the same query to calculate the capacity (in
number of days) of a particular month for each customer. So for example, I
want an expression to work out how many days in January customers stay - if
admission is 14th Nov 08 and discharge is 26th Jan 09, the number of days in
January would be 26. If admission is 30th Jan 09 and discharge is 10th Feb
09, the capacity in number of days in January would be 2.

I tried to use the following expression that works in excel:
=Min([Discharge Date],Date(2009,1,31))-Max([Admission date],Date(2009,1,1))+1
but in Access it comes up with the error message "the expression you entered
has a function containing the wrong number of arguments."

Is there any way to get around this?
(I hope I've explained that a little better)
thanks,

Post the query Danny.

Bonnie
http://www.dataplus-svc.com

Hi guys,
I posted a formula question on the Excel discussion group
(http://www.microsoft.com/office/com...?lang=en&cr=US&dg=microsoft.public.excel.misc)
- "Calculate days in a given month between two dates" and received two
different formula solutions that worked in Excel.

I am now trying to apply this formula in an Access query/report (changing
the cell ref A2 to [admission date] and B2 to [discharge date]) but get the
message "the expression you entered has a function containing the wrong
number of arguments."

I'm a bit of a newbie to Access, can anyone help at all?
thanks,

 

[Lynn Trapp]

Danny,
The Min() and Max() functions take only one argument each. You have entered
2 in each one. Try this instead.

=Min([Discharge Date] - Max([Admission date]) + 1
--
Lynn Trapp

I have a simple design query with the following fields

customerID
Admission Date
Discharge Date

I then want an expression in the same query to calculate the capacity (in
number of days) of a particular month for each customer. So for example, I
want an expression to work out how many days in January customers stay - if
admission is 14th Nov 08 and discharge is 26th Jan 09, the number of days in
January would be 26. If admission is 30th Jan 09 and discharge is 10th Feb
09, the capacity in number of days in January would be 2.

I tried to use the following expression that works in excel:
=Min([Discharge Date],Date(2009,1,31))-Max([Admission date],Date(2009,1,1))+1
but in Access it comes up with the error message "the expression you entered
has a function containing the wrong number of arguments."

Is there any way to get around this?
(I hope I've explained that a little better)
thanks,

Post the query Danny.

Bonnie
http://www.dataplus-svc.com

Hi guys,
I posted a formula question on the Excel discussion group
(http://www.microsoft.com/office/com...?lang=en&cr=US&dg=microsoft.public.excel.misc)
- "Calculate days in a given month between two dates" and received two
different formula solutions that worked in Excel.

I am now trying to apply this formula in an Access query/report (changing
the cell ref A2 to [admission date] and B2 to [discharge date]) but get the
message "the expression you entered has a function containing the wrong
number of arguments."

I'm a bit of a newbie to Access, can anyone help at all?
thanks,

 

[Duane Hookom]

Are you only querying for a single month and single customer? Or do you want
all months for a single customer or all customers for a month or all
customers for all months?

I would start by creating a table of all dates [tblDates] with a single
field [TheDate] and enter dates from the min to way out into the future. Use
either code or Excel to assist with this.

Then create a query with your query and add this table. Set the criteria
under the [TheDate] field to
Between [Admission Date] And [Discharge Date]
This will return one record for each date for each customer. You can then
group by Month of TheDate and count the CustomerIDs.

--
Duane Hookom
Microsoft Access MVP

I have a simple design query with the following fields

customerID
Admission Date
Discharge Date

I then want an expression in the same query to calculate the capacity (in
number of days) of a particular month for each customer. So for example, I
want an expression to work out how many days in January customers stay - if
admission is 14th Nov 08 and discharge is 26th Jan 09, the number of days in
January would be 26. If admission is 30th Jan 09 and discharge is 10th Feb
09, the capacity in number of days in January would be 2.

I tried to use the following expression that works in excel:
=Min([Discharge Date],Date(2009,1,31))-Max([Admission date],Date(2009,1,1))+1
but in Access it comes up with the error message "the expression you entered
has a function containing the wrong number of arguments."

Is there any way to get around this?
(I hope I've explained that a little better)
thanks,

Post the query Danny.

Bonnie
http://www.dataplus-svc.com

Hi guys,
I posted a formula question on the Excel discussion group
(http://www.microsoft.com/office/com...?lang=en&cr=US&dg=microsoft.public.excel.misc)
- "Calculate days in a given month between two dates" and received two
different formula solutions that worked in Excel.

I am now trying to apply this formula in an Access query/report (changing
the cell ref A2 to [admission date] and B2 to [discharge date]) but get the
message "the expression you entered has a function containing the wrong
number of arguments."

I'm a bit of a newbie to Access, can anyone help at all?
thanks,