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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|