Easy one

[ProtegeV2]

Hi! I am creating a report based on a query - here is the SQL so far: Note: [Docket #] = [PRNumber] (same number)

SELECT [Project Expenses Table].[Docket #], [Project Expenses Table].[Invoice ID], [Project Expenses Table].[Invoice Date], [Project Expenses Table].[Invoice Amount], [Project Expenses Table].[Description of Invoice], tblPPOScript.MemoTo, tblPPOScript.FromNames, tblPPOScript.AirDate, tblPPOScript.OutageDatePlace, tblPPOScript.OutageNecessary, tblPPOScript.PRNumber
FROM [Project Expenses Table] INNER JOIN tblPPOScript ON [Project Expenses Table].[Docket #] = tblPPOScript.PRNumber
WHERE ((([Project Expenses Table].[Invoice ID])>"0"));

I think the easiest way to explain my problem is to give you an example. Docket #3298 has two invoices entered so far in the Project Expenses Table. A separate table, tblPPOScript.PRNumber, has eight matching dockets - the remaining six PRNumbers have not been invoiced yet. How do I select only the two invoiced dockets, but include the corresponding information in the SQL from tblPPOScript? I tried inner join but it still gives me all records. I hope that makes sense. Thanks in advance.

 

[lespaul]

This WHERE clause:

((([Project Expenses Table].[Invoice ID])>"0"));

will return all records where the string in [Invoice ID] is not '0'. Is [Invoice ID] a string or a number? if it's a string, then you probably need to replace the where clause with:

((([Project Expenses Table].[Invoice ID])>""));

If it's a number, then you need to get rid of the "" around the 0.




Leslie

 

[ProtegeV2]

Thanks for your reply Leslie. I found out my problem was with my report sorting and grouping and not the query afterall.

Have a star for your time!

 

[lespaul]

well good, cause I couldn't see anything wrong in the query and was taking a stab in the dark!!!

Glad you got it worked out!

thanks for the star!

les