 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / February 2007Elimination Formula
How would I write a formula to eliminate any invoices starting with the invoice number 02 that have a 0 claim value and are less than 60 days old? My query has the following columns: Inv Num Claim Amt No Days Open Also would I write this in the criteria line of the Inv Num column of the query? > How would I write a formula to eliminate any invoices starting with the > invoice number 02 that have a 0 claim value and are less than 60 days old? [quoted text clipped - 4 lines] > Also would I write this in the criteria line of the Inv Num column of the > query? Well, I'll assume [InvNum] is a string datatype, as it contains the preceding zero. But I have no idea what the datatype of [NoDaysOpen] is (a Number datatype? a Date Datatype?) I'll guess Number. So.. You wish to NOT INCLUDE [InvNum] that begin "02". On the [InvNum] criteria line, write: Not Like "02*" On the same row, on the [ClaimAmt] column, write: 0 On the same row, on the [NoDaysOpen] column, write: <60 The above will show all records that DO NOT begin with 02 and that are 0 due less than 60 days old. Is that what you want?  SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail >> formula to eliminate any invoices Do you mean to delete or just not display? Below is criteria to not display. Use the crireria as shown below -- Inv Num Not Like "02*" Claim Amt <>0 No Days Open <60 > How would I write a formula to eliminate any invoices starting with the > invoice number 02 that have a 0 claim value and are less than 60 days old? [quoted text clipped - 4 lines] > Also would I write this in the criteria line of the Inv Num column of the > query? That is correct I just don't want to display. I tried what you gave me but now I'm not getting anything when I run my query. All records are now eliminated. I put in the criteria line is that correct? Also, I only want to eliminate the "02" records only if the claim value is 0 and they are over 60 days old. > >> formula to eliminate any invoices > Do you mean to delete or just not display? Below is criteria to not display. [quoted text clipped - 17 lines] > > Also would I write this in the criteria line of the Inv Num column of the > > query? Try one criteria at a time. > That is correct I just don't want to display. I tried what you gave me but > now I'm not getting anything when I run my query. All records are now [quoted text clipped - 23 lines] > > > Also would I write this in the criteria line of the Inv Num column of the > > > query? The Not Like "02" is eliminating all invoices beginning in 02 whether they have a claim against them or not. I want to only eliminate the 02 invoices that don't have a claim against them and are older than 60 days. > Try one criteria at a time. > [quoted text clipped - 25 lines] > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > query? If you open the query in design view and place all criteria on the same line in the grid. When you put the criteria on the same line it says that all of the requirement must be met. It would read thus -- WHERE [Inv Num] Not Like "02*" AND [Claim Amt] <>0 AND [No Days Open]<60 > The Not Like "02" is eliminating all invoices beginning in 02 whether they > have a claim against them or not. I want to only eliminate the 02 invoices [quoted text clipped - 29 lines] > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > query? I tried the requirement below. I put it all in the criteria line on the Inv Num column of my query in design view. When I run the query I am getting a popup Enter Parameter Value - No Days Open. When I type in 60 I am getting no data from my query. It is also adding " before the where and after the not in Not Like. This is what it looks like after running: "WHERE [Invoice #] Not" Like "02*" And [claim amount]<>"0" And [No Days Open]<"60" What am I doing wrong? > If you open the query in design view and place all criteria on the same line > in the grid. [quoted text clipped - 35 lines] > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > query? Post your query SQL statement. Open the query in design view, click on menu VIEW - View SQL. This opens anothe window that has the SQL. Highlight all, copy, and paste in a post. I or someone will will check your query. SignatureKARL DEWEY Build a little - Test a little > I tried the requirement below. I put it all in the criteria line on the Inv > Num column of my query in design view. When I run the query I am getting a [quoted text clipped - 45 lines] > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > query? SELECT [Walmart Open Invoices].[Invoice #], [Walmart Open Invoices].[Inv Date], [Walmart Open Invoices].[Inv Amt], [Walmart Open Invoices].[Bal Due], [tbl primary].[date filed], [tbl primary].[claim amount], [tbl primary].[claim carrier], DateDiff("d",[Inv Date],Now()) AS [No Days Open] FROM [Walmart Open Invoices] LEFT JOIN [tbl primary] ON [Walmart Open Invoices].[Invoice #] = [tbl primary].[invoice number] WHERE ((([tbl primary].[claim amount])<>"0") AND (("WHERE [Invoice #] Not") Like "02*") AND (([No Days Open])<"60")); > Post your query SQL statement. > Open the query in design view, click on menu VIEW - View SQL. This opens [quoted text clipped - 51 lines] > > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > > query? Try this --- WHERE [tbl primary].[claim amount]<>0 AND [tbl primary].[Invoice #] Not Like "02*" AND DateDiff("d",[Inv Date],Now()) <60; Text goes in quotes, numbers do not. You must use the calculation and not the alias of [No Days Open]. SignatureKARL DEWEY Build a little - Test a little > SELECT [Walmart Open Invoices].[Invoice #], [Walmart Open Invoices].[Inv > Date], [Walmart Open Invoices].[Inv Amt], [Walmart Open Invoices].[Bal Due], [quoted text clipped - 60 lines] > > > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > > > query? I copied your string exactly into the Invoice # criteria cell in design view and now I am getting: Enter Parameter Value - tbl primary.Invoice # > Try this --- > WHERE [tbl primary].[claim amount]<>0 AND [tbl primary].[Invoice #] Not Like [quoted text clipped - 67 lines] > > > > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > > > > query? No it is to be pasted into the SQL view instead of what you had as you had errors. SignatureKARL DEWEY Build a little - Test a little > I copied your string exactly into the Invoice # criteria cell in design view > and now I am getting: Enter Parameter Value - tbl primary.Invoice # [quoted text clipped - 70 lines] > > > > > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > > > > > query? Delete everything that is there and paste this in? If so I am getting the following error: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. > No it is to be pasted into the SQL view instead of what you had as you had > errors. [quoted text clipped - 73 lines] > > > > > > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > > > > > > query? What I posted was the correction to PART of your SQL statement. Below is the complete SQL statement with corrections. This assumes that [tbl primary].[claim amount] is a number. SELECT [Walmart Open Invoices].[Invoice #], [Walmart Open Invoices].[Inv Date], [Walmart Open Invoices].[Inv Amt], [Walmart Open Invoices].[Bal Due], [tbl primary].[date filed], [tbl primary].[claim amount], [tbl primary].[claim carrier], DateDiff("d",[Inv Date],Now()) AS [No Days Open] FROM [Walmart Open Invoices] LEFT JOIN [tbl primary] ON [Walmart Open Invoices].[Invoice #] = [tbl primary].[invoice number] WHERE [tbl primary].[claim amount]<>0 AND [tbl primary].[Invoice #] Not Like "02*" AND DateDiff("d",[Inv Date],Now()) <60; Also, when you copy and paste there may be aded returns in it. You must edit them out. There are returns preceeding the word FROM and the word WHERE. There should not be any other returns in the SQL. SignatureKARL DEWEY Build a little - Test a little > Delete everything that is there and paste this in? If so I am getting the > following error: Invalid SQL statement; expected 'DELETE', 'INSERT', [quoted text clipped - 77 lines] > > > > > > > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > > > > > > > query? Okay I copied the complete statement below into SQL Now I am getting the following: Enter Parameter Value - Walmart: Open Invoices.Inv Date; Enter Parameter Value - tbl primary.claim carrier; Enter Parameter Value - tbl primary.Invoice #. The only thing that is text would be the Invoice Number and claim carrier. > What I posted was the correction to PART of your SQL statement. Below is the > complete SQL statement with corrections. [quoted text clipped - 95 lines] > > > > > > > > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > > > > > > > > query? >> Now I am getting the following: Enter Parameter Value - Walmart: ..... When Access says to 'Enter Parameter Value' it is not recognizing what you are using as table/field names as being valid. Double check your spelling of the table and field names to include spaces and puncuation. NOTE - It is best not to use spaces but underlines to reduce error potential. >> The only thing that is text would be the Invoice Number and claim carrier. What does this mean? Why are you mentioning text? SignatureKARL DEWEY Build a little - Test a little > Okay I copied the complete statement below into SQL Now I am getting the > following: Enter Parameter Value - Walmart: Open Invoices.Inv Date; Enter [quoted text clipped - 101 lines] > > > > > > > > > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > > > > > > > > > query? Okay, I have gotten rid of the error messages but it is not filtering at all. I am back to every record coming through. Should I resend SQL? REPLY TO NOTE: I do try to formalize my table names, column names, etc. However, in this case I am getting exports from our accounting system and sometimes you have no choice but take what you can get. > >> Now I am getting the following: Enter Parameter Value - Walmart: ..... > When Access says to 'Enter Parameter Value' it is not recognizing what you [quoted text clipped - 111 lines] > > > > > > > > > > > > > > > > Also would I write this in the criteria line of the Inv Num column of the > > > > > > > > > > > > > > > > query? >>Should I resend SQL? Yes... >>>REPLY TO NOTE: I do try to formalize my table names, column names, etc. However, in this case I am getting exports from our accounting system and sometimes you have no choice but take what you can get. What the heck are you talking about in the above? SignatureKARL DEWEY Build a little - Test a little SELECT [Walmart Open Invoices].[Invoice #], [Walmart Open Invoices].[Inv Date] AS Expr1, [Walmart Open Invoices].[Inv Amt], [Walmart Open Invoices].[Bal Due], [tbl primary].[date filed], [tbl primary].[claim amount], [tbl primary].[claim carrier] AS Expr2, DateDiff("d",[Inv Date],Now()) AS [No Days Open] FROM [Walmart Open Invoices] LEFT JOIN [tbl primary] ON [Walmart Open Invoices].[Invoice #] = [tbl primary].[invoice number]; PS: You wrote me a note re not using spaces but to use underline instead. I am just replying to your suggestion. > >>Should I resend SQL? > Yes... > > >>>REPLY TO NOTE: I do try to formalize my table names, column names, etc. However, in this case I am getting exports from our accounting system and sometimes you have no choice but take what you can get. > > What the heck are you talking about in the above? >However, in >this case I am getting exports from our accounting system and sometimes you >have no choice but take what you can get. You do NOT need to let the fieldnames or format of the accounting system exports dictate your table fieldnames. If they're bad fieldnames *just don't use them* - you can append from a table with one set of fieldnames into a table with a different set. I'd also avoid storing data - "Walmart Open Invoices" - in tablenames or fieldnames. John W. Vinson [MVP] Okay, I have completely redone the SQL and now nothing is filtering through. When I run my query I get no answers. SQL copied below. Any corrections? SELECT [Walmart Open Invoices].[Invoice #], [Walmart Open Invoices].[Inv Date], [Walmart Open Invoices].[Inv Amt], [Walmart Open Invoices].[Bal Due], [tbl primary].[date filed], [tbl primary].[claim amount], [tbl primary].[claim carrier], DateDiff("d",[Inv Date],Now()) AS [No Days Open] FROM [Walmart Open Invoices] LEFT JOIN [tbl primary] ON [Walmart Open Invoices].[Invoice #]=[tbl primary].[invoice number] WHERE [tbl primary].[claim amount]<>0 And [tbl primary].[invoice number] Not Like "02*" And DateDiff("d",[Inv Date],Now())<60; > >However, in > >this case I am getting exports from our accounting system and sometimes you [quoted text clipped - 9 lines] > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.