Irregular Queries Results

A

ArthurS

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
 
A

ArthurS

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;
 
K

KARL DEWEY

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) ....

ArthurS said:
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;


ArthurS said:
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
Click to expand...
Click to expand...
 
Top