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.
--
KARL DEWEY
Build a little - Test a little
acctsrec said:
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.
--
KARL 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 #
:
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].
--
KARL 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],
[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
anothe window that has the SQL. Highlight all, copy, and paste in a post.
I or someone will will check your query.
--
KARL 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
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.
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
that don't have a claim against them and are older than 60 days.
:
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
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.
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?
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?
