query by month displays Error 3464 Data type mismatch in criteria expression

[seb]

test_month.zip

If DCount("*", "qcos_month") <> 0 Then

Hello. When I enter 2016_10 or 2017_01 or so on the above statement ms access 2013 displays "Error 3464 Data type mismatch in criteria expression".
I have been googling hours and hours and I have no idea how to fix it.
One interesting thing is if I query by day the query runs correct.


I enclose a copy of the database to show you my probem. In my working Computer Access 2013 spanish version is installed



Please, any idea is welcome.

[Micron]

DCount ( expression, domain, [criteria] )
where expression is a field from a table or query. Domain is the table or query itself. You cannot specify a wild card for a field name.

[CJ_London]

you can use the * for expression. But looking at your attachment, you don't have that query.

In addition your dates are datetime fields which are numeric and you are trying to compare with a text value which is why you are getting the error.

It is not clear what you are trying to do, so I can't suggest a solution, but you wont be able to select a single day (presumable in the format dd/mm/yyyy) and a month formatted as yyyy_mm using the same criteria.

Google 'access date types' to find out how dates fields are constructed and the formats that can be applied so it looks like a date and also 'access date functions' to find out what sort of functions you can use

[seb]

Ajax. Many thanks.
The db attachment includes the query named "qcos_dy". In the query the field Dates which I am trying to has the format dd/mm/aaaa. As you says fields dd2 and dd3 are datetime and I understand I cannot be able to select a single day.
I am really confused due that a query design by single day runs if I pick a single day in the calendar.
Any idea is welcome

[CJ_London]

You need to understand how date datatypes 'work' and what sort of standard date functions are available. Google as I suggested. I do not understand what your query is intended to do - I can guess but there are many possibilities so cannot help your understanding unless you have the basics.

[Micron]

The db attachment includes the query named "qcos_dy".

What Ajax meant is that you are missing the table or query referenced in your post and code - qcos_month
And if you're really inputting dates with an underscore, I don't see how that would work either.

[seb]

Dear Micron and Ajax
I really apologize for my mistakes you montioned. I was trying to simplify names in sql and vba.
Now I thing the db I enclose shows the querys.
I also enclose one .jpg image where qry_cos_dd runs the query, but image cos_mm displays error 3464.

Now see that Qry "qcos_dd" has the following sql code and runs the qry

SELECT tblcos.Dates, tblcos.dd2, tblcos.parc_id, tblcos.task_id, tblcos.dd3
FROM tblcos
WHERE (((tblcos.Dates)=[Formularios]![frm_main]![txtcos_dd]));

And the vba code exports query "qcos_dd" to Excel.

Private Sub cmb_cos_dd_Click()If DCount("*", "qcos_dd") <> 0 Then
DoCmd.OutputTo acOutputQuery, "qcos_dd", acFormatXLSX, "D:\KKAR\Cons_qcos_dd\qcos_dd.xlsx", False
MsgBox "Query already exported to Excel"


Else
MsgBox "This day has no records"
DoCmd.Close acQuery, "qcos_dd"
End If
End Sub

On the other hand,
Qry "qcos_mm" has the following sql code:

SELECT tblcos.Dates, tblcos.dd2, tblcos.parc_id, tblcos.task_id, tblcos.dd3
FROM tblcos
WHERE (((tblcos.Dates)=[Formularios]![frm_main]![txtcos_mm]));

And the vba code displays Error 3464

Private Sub cmb_cos_mm_Click()
If DCount("*", "qcos_mm") <> 0 Then
DoCmd.OutputTo acOutputQuery, "qcos_mm", acFormatXLSX, "D:\KKAR\Cons_qcos_mm\qcos_mm.xlsx", False
MsgBox "Query already exported to Excel"
Else
MsgBox "This day has no records"
DoCmd.Close acQuery, "qcos_mm"
End If
End Sub

Pleasse. I need to fix such problem, nevertheless, I am really confused why sql and vba codes run query by single day, but not the qry by month.
Any help is welcome

[CJ_London]

@seb

Your question has been answered several times. You are comparing two different types of data - text and dates. You have not shown you understand what this means. 2017_02 may mean a month to you, but access expects a number since the date datatype is a number. Convert your dates field in the query so it is also text

[seb]

Ajax.

Thank you for your patience.
Finally I found how to fix my big mistake.

Kindest Regards