Parameter Query with MonthName Abbreviations "Too Complex"

[melissa820]

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

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.

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?

[melissa820]

I'm assuming it has something to do with trying to pull the Month and MonthName from fields that have a NULL value in the [Visit Date], but I just can't wrap my head around a way to ignore those ones.

I should also point out that if I remove the Month section of it and just filter using ((Year(Invoice.[Invoice Date]))=[Enter Year])) it works fine.

It also works fine if I remove the MonthName function and do it with just Month... entering a number "1" instead of the abbreviation "Jan".

So what is it about MonthName that isn't working with this particular query (even though it works fine with my other one??)

[June7]

You have a custom function MonthName? Why?

Why not intrinsic Format function: Format([Visit Date], "mmm"). If [Visit Date] is null the function returns empty string.

Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention.

I NEVER use dynamic parameterized queries, especially not popup input prompts because impossible to validate input.

[melissa820]

Thanks for your reply!

I didn't create the MonthName function, it's part of Access. And I agree with you that the dynamic popup is not the way to go, but it does seem to be the simplest way to solve my problem without me having to learn VBA. It worked like a charm in the other query. I'm great with straight SQL but not so hot with Access and its interface just frustrate me... I find it very difficult to do any testing or debugging. I'm just using it to make something simple as a favor to a friend.

That all being said, your solution worked like a charm, so Thank You very much!!

[June7]

Well, that's a new one for me! However, the MonthName function errors if field is Null.

Instead of popup input, create a form where users can make their selection from comboboxes. This will allow validation control. Then the query parameter references the combobox for its input.

[melissa820]

That's definitely what I would do if (a) I was being paid more and (b) I didn't have to get this done by Monday.