I am trying to create a parameter query where the user can type in the month (three letter abbreviation, for example "Jan") and year (4 digits) and receive a listing of all records where the date matches. I've tried this below... if I run the query I get "Data type mismatch" error, and if I run it inside my report I get the error message "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
Code:
SELECT Customer.Company, Customer.[First Name], Customer.[Last Name], Tank.[Visit Date]
FROM Customer LEFT JOIN Tank ON Customer.CustomerID = Tank.CustomerID
WHERE ((Tank.[Visit Date] IS NOT NULL) AND (MonthName(Month(Tank.[Visit Date]),True)=[Enter Month]) AND ((Year(Tank.[Visit Date]))=[Enter Year]));
I am using another similar query with the same type of paramaters and this one below works fine.
Code:
SELECT Customer.Company, Customer.[First Name], Customer.[Last Name], Invoice.[Invoice Number], Invoice.[Invoice Date]
FROM qryServiceCostPerInvoice INNER JOIN (qryPaymentsPerInvoice INNER JOIN (Customer INNER JOIN Invoice ON Customer.[CustomerID] = Invoice.[CustomerID]) ON qryPaymentsPerInvoice.[InvoiceID] = Invoice.[InvoiceID]) ON qryServiceCostPerInvoice.[InvoiceID] = Invoice.[InvoiceID]
WHERE (((MonthName(Month(Invoice.[Invoice Date]),True))=[Enter Month]) And ((Year(Invoice.[Invoice Date]))=[Enter Year]));
What's wrong with my first query?