A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Find Bad Debtors: DateDiff from now is more than 90 days



 
 
Thread Tools Display Modes
  #1  
Old April 1st, 2009, 12:05 PM posted to microsoft.public.access
Confused87
external usenet poster
 
Posts: 42
Default Find Bad Debtors: DateDiff from now is more than 90 days

I am creating a fundraising database and one of the essential duties it needs
to fulfil is to display when a debt is bad.

In my Events Form I have “Funds Due 1” and “Funds Received 1”

I need the following calculation to appear (in the query, though I don’t
mind where it is done as it will end up in a form) but only if “Funds
Received 1” is empty.

=(DateDiff("d",[Funds Due 1],Now())).

I know I need an IF statement somewhere but I am confused as I am not sure
how this will fit together and also whether it happens in the table, query or
form.

I have tried this:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due 1],Now())))


but it asks me to Enter A Parameter Value for Funds Received 1 and then
displays the same data as the above calculation :/

The next step is to create a button or message in a form for every Bad
Debt(90 days), which I imagine would be

=IIf([whatever the field is]"90","Bad Debt")

  #2  
Old April 1st, 2009, 12:33 PM posted to microsoft.public.access
Maurice
external usenet poster
 
Posts: 1,585
Default Find Bad Debtors: DateDiff from now is more than 90 days

If it's asking you for a parameter that means that either the name is spelled
incorrectly or the field cannot be found. The iif(isnull( example you provide
did work for me so place that in the query and make sure you have a field on
your form to which you can reference the field. Based on that field you can
create an unbound field and place =IIf([whatever the field is]"90","Bad
Debt") in there where the [whatever the field is] should be replaced by the
field you dragged onto the form to calculate the difference in dates.

hth
--
Maurice Ausum


"Confused87" wrote:

I am creating a fundraising database and one of the essential duties it needs
to fulfil is to display when a debt is bad.

In my Events Form I have “Funds Due 1” and “Funds Received 1”

I need the following calculation to appear (in the query, though I don’t
mind where it is done as it will end up in a form) but only if “Funds
Received 1” is empty.

=(DateDiff("d",[Funds Due 1],Now())).

I know I need an IF statement somewhere but I am confused as I am not sure
how this will fit together and also whether it happens in the table, query or
form.

I have tried this:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due 1],Now())))


but it asks me to Enter A Parameter Value for Funds Received 1 and then
displays the same data as the above calculation :/

The next step is to create a button or message in a form for every Bad
Debt(90 days), which I imagine would be

=IIf([whatever the field is]"90","Bad Debt")

  #3  
Old April 1st, 2009, 01:19 PM posted to microsoft.public.access
Confused87
external usenet poster
 
Posts: 42
Default Find Bad Debtors: DateDiff from now is more than 90 days

Thanks for the speedy respone!

Turns out I can't spell the word Receieved the same way twice; that's solved
the first problem, thank you.

However... the query is still ignoring the first part of the calculation and
only giving the me the DateDiff information. I have tried changing the format
of the "Receieved Funds 1" field in the Table to Text, Number and Date/Time,
however it still isn't pickin g it up

"Maurice" wrote:

If it's asking you for a parameter that means that either the name is spelled
incorrectly or the field cannot be found. The iif(isnull( example you provide
did work for me so place that in the query and make sure you have a field on
your form to which you can reference the field. Based on that field you can
create an unbound field and place =IIf([whatever the field is]"90","Bad
Debt") in there where the [whatever the field is] should be replaced by the
field you dragged onto the form to calculate the difference in dates.

hth
--
Maurice Ausum


"Confused87" wrote:

I am creating a fundraising database and one of the essential duties it needs
to fulfil is to display when a debt is bad.

In my Events Form I have “Funds Due 1” and “Funds Received 1”

I need the following calculation to appear (in the query, though I don’t
mind where it is done as it will end up in a form) but only if “Funds
Received 1” is empty.

=(DateDiff("d",[Funds Due 1],Now())).

I know I need an IF statement somewhere but I am confused as I am not sure
how this will fit together and also whether it happens in the table, query or
form.

I have tried this:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due 1],Now())))


but it asks me to Enter A Parameter Value for Funds Received 1 and then
displays the same data as the above calculation :/

The next step is to create a button or message in a form for every Bad
Debt(90 days), which I imagine would be

=IIf([whatever the field is]"90","Bad Debt")

  #4  
Old April 1st, 2009, 02:30 PM posted to microsoft.public.access
Maurice
external usenet poster
 
Posts: 1,585
Default Find Bad Debtors: DateDiff from now is more than 90 days

Before i'm getting confused too :-) i need a little more explanation from
which you are telling us here. You wrote that the first part works correctly,
which part are we talking about here.

I assume you have put this expression in the query:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due 1],Now())))

right? And the last part isn't working, the part from DateDiff on...

need to know we are talking about the same..
--
Maurice Ausum


"Confused87" wrote:

Thanks for the speedy respone!

Turns out I can't spell the word Receieved the same way twice; that's solved
the first problem, thank you.

However... the query is still ignoring the first part of the calculation and
only giving the me the DateDiff information. I have tried changing the format
of the "Receieved Funds 1" field in the Table to Text, Number and Date/Time,
however it still isn't pickin g it up

"Maurice" wrote:

If it's asking you for a parameter that means that either the name is spelled
incorrectly or the field cannot be found. The iif(isnull( example you provide
did work for me so place that in the query and make sure you have a field on
your form to which you can reference the field. Based on that field you can
create an unbound field and place =IIf([whatever the field is]"90","Bad
Debt") in there where the [whatever the field is] should be replaced by the
field you dragged onto the form to calculate the difference in dates.

hth
--
Maurice Ausum


"Confused87" wrote:

I am creating a fundraising database and one of the essential duties it needs
to fulfil is to display when a debt is bad.

In my Events Form I have “Funds Due 1” and “Funds Received 1”

I need the following calculation to appear (in the query, though I don’t
mind where it is done as it will end up in a form) but only if “Funds
Received 1” is empty.

=(DateDiff("d",[Funds Due 1],Now())).

I know I need an IF statement somewhere but I am confused as I am not sure
how this will fit together and also whether it happens in the table, query or
form.

I have tried this:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due 1],Now())))


but it asks me to Enter A Parameter Value for Funds Received 1 and then
displays the same data as the above calculation :/

The next step is to create a button or message in a form for every Bad
Debt(90 days), which I imagine would be

=IIf([whatever the field is]"90","Bad Debt")

  #5  
Old April 7th, 2009, 10:39 AM posted to microsoft.public.access
Confused87
external usenet poster
 
Posts: 42
Default Find Bad Debtors: DateDiff from now is more than 90 days

Sorry about the delay, I only volunteer for the charity a few days a week and
have no access to Accesss at home...

Confusion is rife at present I'm afraid :/
My Field reads thus:

Expr3: IIf(IsNull([Funds Recieved 1])," ",(DateDiff("d",[Funds Due 1],Now())))

What is happening is the opposite of what I want:

Funds Due 1 Funds Recieved 1 Expr3
10/03/2007 10/03/2007 759
01/03/2008
10/02/2009 10/04/2009 56

The first and third column shouldn't say anything(or 0 or the amount of days
between the two dates). What is is doing is letting me know how many days
have elapsed since the due date and now, but only for Funds I have already
Recieved.

I need to know: how may days have passed for debts over 90 days from now.
It would be nice, but not essentail to know:the number of days passed
regardless of the 90 day limit, the difference between the Funds Due 1 and
date Funds Recieved 1.

So, with the above example, it would be good to have the difference between
the days in the 1st and 3rd column, however the important one is the 2nd
which should be telling me the number of elapsed days from today as it is
over 90.

Make sense :S

I really appreciate your assistance

Camilla


"Maurice" wrote:

Before i'm getting confused too :-) i need a little more explanation from
which you are telling us here. You wrote that the first part works correctly,
which part are we talking about here.

I assume you have put this expression in the query:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due 1],Now())))

right? And the last part isn't working, the part from DateDiff on...

need to know we are talking about the same..
--
Maurice Ausum


"Confused87" wrote:

Thanks for the speedy respone!

Turns out I can't spell the word Receieved the same way twice; that's solved
the first problem, thank you.

However... the query is still ignoring the first part of the calculation and
only giving the me the DateDiff information. I have tried changing the format
of the "Receieved Funds 1" field in the Table to Text, Number and Date/Time,
however it still isn't pickin g it up

"Maurice" wrote:

If it's asking you for a parameter that means that either the name is spelled
incorrectly or the field cannot be found. The iif(isnull( example you provide
did work for me so place that in the query and make sure you have a field on
your form to which you can reference the field. Based on that field you can
create an unbound field and place =IIf([whatever the field is]"90","Bad
Debt") in there where the [whatever the field is] should be replaced by the
field you dragged onto the form to calculate the difference in dates.

hth
--
Maurice Ausum


"Confused87" wrote:

I am creating a fundraising database and one of the essential duties it needs
to fulfil is to display when a debt is bad.

In my Events Form I have “Funds Due 1” and “Funds Received 1”

I need the following calculation to appear (in the query, though I don’t
mind where it is done as it will end up in a form) but only if “Funds
Received 1” is empty.

=(DateDiff("d",[Funds Due 1],Now())).

I know I need an IF statement somewhere but I am confused as I am not sure
how this will fit together and also whether it happens in the table, query or
form.

I have tried this:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due 1],Now())))


but it asks me to Enter A Parameter Value for Funds Received 1 and then
displays the same data as the above calculation :/

The next step is to create a button or message in a form for every Bad
Debt(90 days), which I imagine would be

=IIf([whatever the field is]"90","Bad Debt")

  #6  
Old April 7th, 2009, 10:49 AM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Find Bad Debtors: DateDiff from now is more than 90 days

Expr3: IIf(IsNull([Funds Recieved 1]),DateDiff("d",[Funds Due 1],Now())), "
")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Confused87" wrote in message
...
Sorry about the delay, I only volunteer for the charity a few days a week
and
have no access to Accesss at home...

Confusion is rife at present I'm afraid :/
My Field reads thus:

Expr3: IIf(IsNull([Funds Recieved 1])," ",(DateDiff("d",[Funds Due
1],Now())))

What is happening is the opposite of what I want:

Funds Due 1 Funds Recieved 1 Expr3
10/03/2007 10/03/2007 759
01/03/2008
10/02/2009 10/04/2009 56

The first and third column shouldn't say anything(or 0 or the amount of
days
between the two dates). What is is doing is letting me know how many days
have elapsed since the due date and now, but only for Funds I have already
Recieved.

I need to know: how may days have passed for debts over 90 days from now.
It would be nice, but not essentail to know:the number of days passed
regardless of the 90 day limit, the difference between the Funds Due 1 and
date Funds Recieved 1.

So, with the above example, it would be good to have the difference
between
the days in the 1st and 3rd column, however the important one is the 2nd
which should be telling me the number of elapsed days from today as it is
over 90.

Make sense :S

I really appreciate your assistance

Camilla


"Maurice" wrote:

Before i'm getting confused too :-) i need a little more explanation from
which you are telling us here. You wrote that the first part works
correctly,
which part are we talking about here.

I assume you have put this expression in the query:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due 1],Now())))

right? And the last part isn't working, the part from DateDiff on...

need to know we are talking about the same..
--
Maurice Ausum


"Confused87" wrote:

Thanks for the speedy respone!

Turns out I can't spell the word Receieved the same way twice; that's
solved
the first problem, thank you.

However... the query is still ignoring the first part of the
calculation and
only giving the me the DateDiff information. I have tried changing the
format
of the "Receieved Funds 1" field in the Table to Text, Number and
Date/Time,
however it still isn't pickin g it up

"Maurice" wrote:

If it's asking you for a parameter that means that either the name is
spelled
incorrectly or the field cannot be found. The iif(isnull( example you
provide
did work for me so place that in the query and make sure you have a
field on
your form to which you can reference the field. Based on that field
you can
create an unbound field and place =IIf([whatever the field
is]"90","Bad
Debt") in there where the [whatever the field is] should be replaced
by the
field you dragged onto the form to calculate the difference in dates.

hth
--
Maurice Ausum


"Confused87" wrote:

I am creating a fundraising database and one of the essential
duties it needs
to fulfil is to display when a debt is bad.

In my Events Form I have "Funds Due 1" and "Funds Received 1"

I need the following calculation to appear (in the query, though I
don't
mind where it is done as it will end up in a form) but only if
"Funds
Received 1" is empty.

=(DateDiff("d",[Funds Due 1],Now())).

I know I need an IF statement somewhere but I am confused as I am
not sure
how this will fit together and also whether it happens in the
table, query or
form.

I have tried this:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due
1],Now())))


but it asks me to Enter A Parameter Value for Funds Received 1 and
then
displays the same data as the above calculation :/

The next step is to create a button or message in a form for every
Bad
Debt(90 days), which I imagine would be

=IIf([whatever the field is]"90","Bad Debt")



  #7  
Old April 7th, 2009, 11:20 AM posted to microsoft.public.access
Confused87
external usenet poster
 
Posts: 42
Default Find Bad Debtors: DateDiff from now is more than 90 days

YOU STAR!

Thanks


"Douglas J. Steele" wrote:

Expr3: IIf(IsNull([Funds Recieved 1]),DateDiff("d",[Funds Due 1],Now())), "
")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Confused87" wrote in message
...
Sorry about the delay, I only volunteer for the charity a few days a week
and
have no access to Accesss at home...

Confusion is rife at present I'm afraid :/
My Field reads thus:

Expr3: IIf(IsNull([Funds Recieved 1])," ",(DateDiff("d",[Funds Due
1],Now())))

What is happening is the opposite of what I want:

Funds Due 1 Funds Recieved 1 Expr3
10/03/2007 10/03/2007 759
01/03/2008
10/02/2009 10/04/2009 56

The first and third column shouldn't say anything(or 0 or the amount of
days
between the two dates). What is is doing is letting me know how many days
have elapsed since the due date and now, but only for Funds I have already
Recieved.

I need to know: how may days have passed for debts over 90 days from now.
It would be nice, but not essentail to know:the number of days passed
regardless of the 90 day limit, the difference between the Funds Due 1 and
date Funds Recieved 1.

So, with the above example, it would be good to have the difference
between
the days in the 1st and 3rd column, however the important one is the 2nd
which should be telling me the number of elapsed days from today as it is
over 90.

Make sense :S

I really appreciate your assistance

Camilla


"Maurice" wrote:

Before i'm getting confused too :-) i need a little more explanation from
which you are telling us here. You wrote that the first part works
correctly,
which part are we talking about here.

I assume you have put this expression in the query:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due 1],Now())))

right? And the last part isn't working, the part from DateDiff on...

need to know we are talking about the same..
--
Maurice Ausum


"Confused87" wrote:

Thanks for the speedy respone!

Turns out I can't spell the word Receieved the same way twice; that's
solved
the first problem, thank you.

However... the query is still ignoring the first part of the
calculation and
only giving the me the DateDiff information. I have tried changing the
format
of the "Receieved Funds 1" field in the Table to Text, Number and
Date/Time,
however it still isn't pickin g it up

"Maurice" wrote:

If it's asking you for a parameter that means that either the name is
spelled
incorrectly or the field cannot be found. The iif(isnull( example you
provide
did work for me so place that in the query and make sure you have a
field on
your form to which you can reference the field. Based on that field
you can
create an unbound field and place =IIf([whatever the field
is]"90","Bad
Debt") in there where the [whatever the field is] should be replaced
by the
field you dragged onto the form to calculate the difference in dates.

hth
--
Maurice Ausum


"Confused87" wrote:

I am creating a fundraising database and one of the essential
duties it needs
to fulfil is to display when a debt is bad.

In my Events Form I have "Funds Due 1" and "Funds Received 1"

I need the following calculation to appear (in the query, though I
don't
mind where it is done as it will end up in a form) but only if
"Funds
Received 1" is empty.

=(DateDiff("d",[Funds Due 1],Now())).

I know I need an IF statement somewhere but I am confused as I am
not sure
how this will fit together and also whether it happens in the
table, query or
form.

I have tried this:

IIf(IsNull([Funds Received 1])," ",(DateDiff("d",[Funds Due
1],Now())))


but it asks me to Enter A Parameter Value for Funds Received 1 and
then
displays the same data as the above calculation :/

The next step is to create a button or message in a form for every
Bad
Debt(90 days), which I imagine would be

=IIf([whatever the field is]"90","Bad Debt")




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.