Recent content by canrus01

I have a table column with dates like 02/05/06 02/06/06 ........... I need to calculate the next date[Due Date] in 6 months, which is simple [Patient's file].[Last Appointment Date]+180 as [Due Date] and I have to group the Due Date by the next month. Lets say the due date is 08/25/07, so it...

OK, I made it work. Here is the code SELECT [Accounts Payable].PaymentID, [Accounts Payable].CustomerID, [Accounts Payable].[Customer Name], IIf([Customer Name]="Petty Cash","N/A",([Bill Date]-Date()+27)) AS [Payment Deadline], IIf([Customer Name]="Petty Cash"," N/A", IIF([3% Discount]=No...

Thank you. I changed the statement a little and this fix not competely work. The reason is not all accounts have 3% discount option, not all IIF have "yes" selected. IIF([Bill Date]-Date()+24<=0, "Discount Expired", IIf([3% Discount]=Yes, [Bill Date]-Date()+24, [Bill Date]-Date()+34)) AS...

Thank you very much. Yes/No is True or False. I am new to SQL, only a week. How should I edit this statement, it doesn't work. IIf([3% Discount]=Yes,[Bill Date]-Date()+20, IIF([3% Discount]=No,[Bill Date]-Date()+30,IIF([Bill Date]-Date()+20<=0,"Discount Expired"))) AS [Discount Days...

I have Yes/No column for Discount option. If Yes is checked, than 3% applies. I am trying to write IIf statement, so if Yes is checked the query calculates how many days left to make a payment to get a discount based on 10 days period. If No is checked, the query calculates how many days left to...

Thanks. Now it works. SELECT [Accounts Receivable].InvoiceID, [Accounts Receivable].[Patient Last Name], [Accounts Receivable].InvoiceDate, Date()-[InvoiceDate]+1 AS [Days outstanding], IIf([Days outstanding] Between 30 And 60,"30 overdue",IIf([Days outstanding] Between 60 And 90,"60...

I didnt pay attention. Now, I made it work. But the query returned is not complete. Where I had 152-168 days overdue, the query returned nothing in STATUS column. All the other rows return correct message. Any ideas why?

Thanks a lot. I made the changes. The code looks like SELECT [Accounts Receivable].InvoiceID, [Accounts Receivable].[Patient Last Name], [Accounts Receivable].InvoiceDate, Date()-[InvoiceDate]+1 AS [Days outstanding], IIf([Days outstanding]-30 between 30 and 60,[Status]="30...

Thank you. Where exactly should I insert my WHERE statement? I am new to SQL and the books I use dont provide so much details nor examples. WHERE ((([Accounts Receivable].InvoiceDate)<Date()-60)) Also , can I write this IIf nested statement to categorize by STATUS outstanding accounts...

I am trying to classify/sort records into four categories like under 30 days, 30, 60, 90 days pst due. I wrote this function, but get a syntax errot. Here is my query code: SELECT [Accounts Receivable].InvoiceID, [Accounts Receivable].[Patient Last Name], [Accounts Receivable].InvoiceDate...

I quess it should be simple. But I couldn't find an answer or example in Access books and online. I have an access 2000 database with a Accounts Receivable table. I am building a query that returns accounts what are 60 days past due and calculating total ballance of ONLY past 60 days accounts. I...