Active/Inactive 1

[ryshkus]

Hello,

I am trying to figure out how to do something with our accounting DB. We bill a customer. They pay a certain amount less than what billed. I need to take 3 fields and if they equal the write off amount billed field then I want them to go to inactive status.

This is what I have and it prompts me to enter the amt billed....

IIf(IsNull([W/O_Amt]-[Amt_paid]-[Later_pay]=[ Amt_billed]),"Inactive")

 

[PHV]

Try to replace this:
[ Amt_billed]
By this:
[Amt_billed]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryshkus]

Still shows nothing. I have added the write off amount field, so everything should show when I run this query. I only want the fields to show that the 3 fields added together don't equal the last.

Stacey

 

[PHV]

IIf(Nz([W/O_Amt],0)+Nz([Amt_paid],0)+Nz([Later_pay],0)=[ Amt_billed]),"Inactive")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryshkus]

Does Nz refer to the database name? I replaced the Nz's with the database name, and then I get an error that points to the first ( after the first Nz.

Why the ,0?

Thank You for your help so far!

 

[ryshkus]

If I leave Nz, and take out the last ) after Amt_billed] I get a prompt to enter a value. No matter what I enter I get no results.

 

[PHV]

Nz is a function handling null value.
Can you please post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryshkus]

This is all I have. I don't think I have any SQL code...

IIf(Nz([W/O_Amt],0)+Nz([Amt_paid],0)+Nz([Later_pay],0)=[ Amt_billed],"Inactive")

 

[ryshkus]

SELECT [101504].Date_Ent, [101504].File_No, [101504].Caption, [101504].Resp_Atty, [101504].Invoice, [101504].Date, [101504].Amt_billed, [101504].Amt_paid, [101504].Amt_due, [101504].Later_pay, [101504].Reason, [101504].Res_Date, [101504].Res_Amt, [101504].[W/O Date], [101504].[W/O_Amt], [101504].Reconcile
FROM 101504
WHERE ((([101504].Reconcile)=IIf(Nz([W/O_Amt],0)+Nz([Amt_paid],0)+Nz([Later_pay],0)=[ Amt_billed],"Inactive")));

 

[PHV]

WHERE [101504].Reconcile=IIf(Nz([W/O_Amt],0)+Nz([Amt_paid],0)+Nz([Later_pay],0)=[Amt_billed],"Inactive","Active");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryshkus]

It still shows no results. I even added a few dummy entries to make sure the result would be something.

 

[ryshkus]

Is there an easier way that I should be doing this? Basically I need to be able to show that either the company paid or that we wrote the difference off.

 

[PHV]

For test purpose:
SELECT [101504].Date_Ent, [101504].File_No, [101504].Caption, [101504].Resp_Atty, [101504].Invoice, [101504].Date, [101504].Amt_billed, [101504].Amt_paid, [101504].Amt_due, [101504].Later_pay, [101504].Reason, [101504].Res_Date, [101504].Res_Amt, [101504].[W/O Date], [101504].[W/O_Amt], [101504].Reconcile, IIf(Nz([W/O_Amt],0)+Nz([Amt_paid],0)+Nz([Later_pay],0)=[Amt_billed],"Inactive","Active") As TestStatus
FROM 101504;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryshkus]

That worked.

 

[ryshkus]

It worked, but it shows me everything.

 

[PHV]

Are the Reconcile and the TestStaus columns like you expected ?
Which rows shouldn't be displayed ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryshkus]

I only wanted the report to show active status.

 

[PHV]

SELECT [101504].Date_Ent, [101504].File_No, [101504].Caption, [101504].Resp_Atty, [101504].Invoice, [101504].Date, [101504].Amt_billed, [101504].Amt_paid, [101504].Amt_due, [101504].Later_pay, [101504].Reason, [101504].Res_Date, [101504].Res_Amt, [101504].[W/O Date], [101504].[W/O_Amt], [101504].Reconcile, IIf(Nz([W/O_Amt],0)+Nz([Amt_paid],0)+Nz([Later_pay],0)=[Amt_billed],"Inactive","Active") As TestStatus
FROM 101504
WHERE Nz([W/O_Amt],0)+Nz([Amt_paid],0)+Nz([Later_pay],0)<>[Amt_billed];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryshkus]

You are absolutely amazing to me. You have no idea how much I appreciate your time and effort in this! Wow! I am speechless.

Thank You So Much!!!!

Stacey

 

[ryshkus]

Is there someway I can get this to print in a report form?