Multiple criteria on DCount including date field

[PaulD2019]

Try changing semicolons to commas ...

Thanks Eugene, that has got it working in the text field, it is showing 531 instead of 565 still though. I am going to do a cleanup of the projects that have been quoted tomorrow, I will see if that sorts the count out after the old jobs have been removed.

The table has got quite a bit of personal data included which if it is removed probably won't show the error if removed, fingers crossed cleaning out the old data will sort the issue.

Thanks all for your help

 

[Eugene-LS]

Thanks all for your help

Below is an example:

 

[Pat Hartman]

The third argument of the function DCount() "WhereCondition" is a string!

Yes - Except that you are not providing a string as a date. Date() in a query is NOT a string. It is a date/time data type which means that it is actually a double precision number. And the reference to the QuotedDate is also not a string assumming that QuotedDate is defined as a date/time data type which means that internally, it is a double precision number.

The query engine will be comparing two date/time data type fields. There is NO string involved. If however you had a literal in the domain function, that would be a different situation.

=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Date()" ---- no string at all.
=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < #" & Format(Me.SomeDate,"\mm/dd/yyyy\") & "#") --- this is a string
However
=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Forms!someform!SomeDate" --- no string at all provided that the SomeDate control is either bound to a date/time field OR is an unbound control bhat has a date type format specified so that the query engine knows it is dealing with a date.

The problem was with the use of "quoted" as the column name argument since it is not the name of a column.

=DCount("StatusName";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Date()" ----would have worked although it is inefficient compared to
=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Date()"

 

[June7]

The date formatting includes # delimiters so it should work.
Semicolons instead of comma is syntax I see in many non-U.S. Access dbs.

 

[Pat Hartman]

@PaulD2019 while likes are appreciated, this is not a social media site where people are trolling for "likes". We are answering technical questions. "Liking" every single response is not useful. People who find this thread in a search have no way of determining which thing you "liked" worked or if you were just being polite and thanking people for bothering to offer a solution. If a specific solution solves your problem, mention that specifically so that the actual solution is clear.

 

[Pat Hartman]

The date formatting includes # delimiters so it should work.

Not really relevant since Date() is ALWAYS a date/time format and never needs to be formatted in this context into a US format string value.

 

[Eugene-LS]

=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Date()"

Harsh practice:

 

[Pat Hartman]

When you put the Date() OUTSIDE the quotes, you force Access to pass it in as a string.

=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Date() - 30"

Is NOT the same as:

=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate <" & Date() -30

The second variation passes a string into the concatenation and therefore, it must be enclosed in #'s so the query engine sees it as a date and the formatting would be required if your default date format is not US standard. In the first case, the expression is referencing the Date() function so it retains its definition as a date/time data type and it is NOT a string. Subtle but very important difference.

 

[PaulD2019]

@PaulD2019 while likes are appreciated, this is not a social media site where people are trolling for "likes". We are answering technical questions. "Liking" every single response is not useful. People who find this thread in a search have no way of determining which thing you "liked" worked or if you were just being polite and thanking people for bothering to offer a solution. If a specific solution solves your problem, mention that specifically so that the actual solution is clear.

Apologies @Pat Hartman , Noted

Thanks for your help, neither of the below work in the listed format

=DCount("StatusName";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Date()" ----would have worked although it is inefficient compared to
=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Date()"

even after changing the semicolons to commas, after changing to commas both said they needed a closing bracket, I assumed they need to be added at the end, I added the bracket at the end & got the below results

=DCount("StatusName","tblJobRefBook","JobStatus = 'Quoted' AND QuotedDate < Date()")
This displays 571 instead of 565 as it should or 531 as what has been posted above, I can't see where the 571 came from either.

=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Date()"
This displays #Error with the field flashing

Thanks again all for your help

 

[Eugene-LS]

=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate < Date() - 30") ... -would have worked

Yes, you are absolutely right!

 

[Pat Hartman]

=DCount("StatusName","tblJobRefBook","JobStatus = 'Quoted' AND QuotedDate < Date()")

You persist in using a field name despite my explanation of WHY you should not in this context.

=DCount("*","tblJobRefBook","JobStatus = 'Quoted' AND QuotedDate < Date() - 30")

Somehow the -30 part got lost in the translation so that needs to go back.

Please reread my entire response. If you are not getting the correct count there is something about the data that is different from what you are expecting. THEREFORE, you need to learn how to divide and conquer. The criteria has two conditions so create a query, not a domain function because you need to actually SEE the individual rows, and include ONLY the date part and save it as a querydef. Then Create a second query that uses the first querydef and in that one select ONLY JobStatus. Then make it a count query and add a count expression. That will show you all the JobStatus values for the date range and a count for each separate status. If you are still no closer to understanding the problem, then you need to swap and do the first select for the specific status. then sort that ascending by date and then descending by date. This will pop to the top any empty or odd dates/

 

[cheekybuddha]

The third argument of the function DCount() "WhereCondition" is a string!

It is a string that needs to represent a valid WHERE clause. In SQL the WHERE clause can use the Date() function directly.