Irregular Queries Results

[Guest]

I am querying a Invoice and Invoice Detail linked table by requesting the
YEAR value extracted from the date field in the invoice table and variety
value from the invoice detail table. Both tables contain values from several
years back.
I receive correct return values from this query using values up to 2005.
However when i request values for 2006 i receive no feedback.
If I querry the same tables for only one of the two values i receive all the
requested information. Could someone shed some light on this for me?
ArthurS

 

[Guest]

Post the SQL of your query.

 

[Guest]

SQL statement is as follows:

SELECT Invoices.Invoice_no, Invoices.Invoice_date, Invoices.Customer,
InvoiceDetails.Item_name_printed, InvoiceDetails.Total_amount_shipped,
InvoiceDetails.Weight_seeds, Invoices.Currency, InvoiceDetails.Unit_price,
InvoiceDetails.Total_price, IIf([Weight_seeds]=4,[Total_amount_shipped],0) AS
Expr1, IIf([Weight_seeds]=6,[Total_amount_shipped],0) AS Expr2,
IIf([Currency]=1,[Total_price],0) AS Expr3, IIf([Currency]=4,[Total_price],0)
AS Expr4, IIf([Currency]=2 Or [Currency]=3,[Total_price],0) AS Expr5
FROM Invoices INNER JOIN InvoiceDetails ON Invoices.Invoice_no =
InvoiceDetails.Invoice_no
WHERE (((InvoiceDetails.Weight_seeds)<>1) AND
((InvoiceDetails.Item)=[Forms]![All_in_One_Querry]![Combo_variety]) AND
((Year([Invoice_date]))=[Forms]![All_in_One_Querry]![Combo_year]) AND
((Invoices.Commercial_sample)=No) AND ((Invoices.Invoice_deleted)=No))
ORDER BY Invoices.Invoice_no;

 

[Guest]

I do not see a problem but just to check, include
[Forms]![All_in_One_Querry]![Combo_year] as an output field date criteria to
input prompt.

SELECT Invoices.Invoice_no, .... [Currency]=3,[Total_price],0) AS Expr5,
[Forms]![All_in_One_Querry]![Combo_year] AS MyYear

.....AND ((Year([Invoice_date]))=[Enter year]) AND
((Invoices.Commercial_sample)=No) ....

SQL statement is as follows:

SELECT Invoices.Invoice_no, Invoices.Invoice_date, Invoices.Customer,
InvoiceDetails.Item_name_printed, InvoiceDetails.Total_amount_shipped,
InvoiceDetails.Weight_seeds, Invoices.Currency, InvoiceDetails.Unit_price,
InvoiceDetails.Total_price, IIf([Weight_seeds]=4,[Total_amount_shipped],0) AS
Expr1, IIf([Weight_seeds]=6,[Total_amount_shipped],0) AS Expr2,
IIf([Currency]=1,[Total_price],0) AS Expr3, IIf([Currency]=4,[Total_price],0)
AS Expr4, IIf([Currency]=2 Or [Currency]=3,[Total_price],0) AS Expr5
FROM Invoices INNER JOIN InvoiceDetails ON Invoices.Invoice_no =
InvoiceDetails.Invoice_no
WHERE (((InvoiceDetails.Weight_seeds)<>1) AND
((InvoiceDetails.Item)=[Forms]![All_in_One_Querry]![Combo_variety]) AND
((Year([Invoice_date]))=[Forms]![All_in_One_Querry]![Combo_year]) AND
((Invoices.Commercial_sample)=No) AND ((Invoices.Invoice_deleted)=No))
ORDER BY Invoices.Invoice_no;

I am querying a Invoice and Invoice Detail linked table by requesting the
YEAR value extracted from the date field in the invoice table and variety
value from the invoice detail table. Both tables contain values from several
years back.
I receive correct return values from this query using values up to 2005.
However when i request values for 2006 i receive no feedback.
If I querry the same tables for only one of the two values i receive all the
requested information. Could someone shed some light on this for me?
ArthurS