Multiple criteria on DCount including date field (1 Viewer)

[PaulD2019]

Hi all, I am working on a form to show different statistics on the database I am working on, the quotations raised on the database are valid for 30 days, after 30 days we send a quote reminder via email (already set up) & another 30 days are added before the quotation is cancelled on the database.

I would like a field that counts how many quotes there are that need to have reminders sent

Table name - tblJobRefBook
Date field on table - QuotedDate
Status field on table - JobStatus
Status for quoted jobs - "Quoted"

I tried the below but it gave an error so I guess something is missing

=DCount("Quoted", "tblJobRefBook", "[JobStatus] & [QuoteDate]>=(Date()-30)")

I have other fields to count which work fine, it is just this one I am having trouble with.

As always, any help would be appreciated.

Thanks in advance

 

[June7]

Omitted parameter for JobStatus, need AND operator. The criteria must be same syntax as in query WHERE clause (without the WHERE keyword).

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

& is for concatenation, not logical operation.

 

[PaulD2019]

Thanks for your help @June7

I tried the below in the field on the form but I am still getting an error

=DCount("Quoted","tblJobRefBook","[JobStatus] = 'Quoted' AND QuotedDate >= Date()-30)")

Have I added what you posted wrong?

 

[June7]

Based on information provided, that should work. Note I used "*" instead of specifying field name.

What is error message?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[PaulD2019]

@June7

The field on the form just says #Error

 

[June7]

Well, no idea why.

 

[PaulD2019]

Thanks for trying to help June

 

[Josef P.]

Test it with a query:

select count(Quoted) from tblJobRefBook where [JobStatus] = 'Quoted' AND QuotedDate >= Date()-30)
                ^?                                                                              ^
vs.
select count(*) from tblJobRefBook where [JobStatus] = 'Quoted' AND QuotedDate >= Date()-30

 

[June7]

Ooops, too many ending parens in your attempt. Remove the one after 30.

=DCount("Quoted","tblJobRefBook","[JobStatus] = 'Quoted' AND QuotedDate >= Date()-30")

 

[PaulD2019]

Hi June

In a query that says

Extra ) in query expression [JobStatus] = "Quoted" AND QuoteDate >= Date()-30)

I removed the ) at the end & the query worked but it showed the amount of projects that were under 30 days - 39 projects in total

Changing the > to a < corrected the issue but the figure displayed is wrong, we have been busy at work so chasing quotes has been left, in total there are 604 projects with the status Quoted so the total should say 565 but with the query it says 531, I can't see why though.

 

[Eugene-LS]

I tried the below but it gave an error so I guess something is missing

If your regional date format is different from MM/DD/YYYY - try this:
=DCount("Quoted","tblJobRefBook","[JobStatus] = 'Quoted' AND QuotedDate >=" & Format(Date()-30, "\#mm\/dd\/yyyy\#"))

 

[PaulD2019]

If your regional date format is different from MM/DD/YYYY - try this:
=DCount("Quoted","tblJobRefBook","[JobStatus] = 'Quoted' AND QuotedDate >=" & Format(Date()-30, "\#mm\/dd\/yyyy\#"))

Thanks @Eugene-LS

my regional date format is DD/MM/YYYY

The field says #Error with that added unfortunately though

 

[Pat Hartman]

1. June used "*" for a reason. The reason is that it is more efficient because it lets the query engine use database statisticis to come up with some counts whereas if you use "somespecificcolumnname", you FORCE to query engine to perform an inefficient operation that might force it to read every row in the table. PLUS, this syntax doesn't work the way you think it works. Assuming "quoted" were actually the name of a column and not the value of some row in the table, the query engine would only evaluate rows of the table where the value of "quoted" was not null.

JobStatus is the name of the field you are checking. "quoted" is the value contained in that field. You CANNOT use the value "quoted" as a substitute for the field name JobStatus.
2. I have no idea what you are currently using as syntax since your statement is incomplete. If you want to validate your criteria, start with a query that pulls rows for the correct date range. Then count the distinct values of JobStatus for that set of records. I think you will be able to zero in on the records with the invalid values.

 

[Eugene-LS]

The field says #Error with that added unfortunately though

Checked it out.
It worked for me like this:
=DCount("*";"tblJobRefBook";"JobStatus = 'Quoted' AND QuotedDate >=" & Format(Date()-30;"\#mm/dd/yyyy\#"))

 

[June7]

Ooops again. Yes, "Quoted" is data, not field. So use "*" as shown in my and Eugene's examples.

 

[Pat Hartman]

Do NOT ever format a date unless you actually want to convert the date to a string. If QuotedDate is defined as a date, then the query is valid as written. If you needed to format any date, it would NOT be the Date() function, it would be QuotedDate.

When you are working with dates in a querydef, as long as the dates are defined as date/time data types (and Date() would ALWAYS be correctly defined), then you should NOT format anything.

If you are building an SQL string in VBA, now you have the situation where your date argument since it is text rather than a date/time data type, MUST be formatted as mm/dd/yyyy or the unambiguous yyyy/mm/dd BECAUSE SQL Server assumes an ambiguous string date is in US date format so 01/02/23 would be interpreted as Jan 2, 2023 rather than Feb 1, 2023. This is the only time you ever need to specifically format a date except for the purpose of display.

When you format a date as a string, it will sort like a string and it will compare like a string so 01/02/24 is less than 12/31/23 because 0 is less than 1 and strings are sorted character by character, left to right.

Internally dates are stored as double precision data types. They are NOT stored as strings.

 

[PaulD2019]

@Eugene-LS

That says the expression you entered contains invalid syntax

@June7
Your query seems to be close to working, I am really scratching my head trying to suss out why the figure counted is wrong though

@Pat Hartman
Thanks for your info

 

[Eugene-LS]

Do NOT ever format a date unless you actually want to convert the date to a string.

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

 

[Eugene-LS]

That says the expression you entered contains invalid syntax

Try changing semicolons to commas ...

 

[Eugene-LS]

That says the expression you entered contains invalid syntax

Publish your table (part of the data) here - we'll see. There may be something wrong with your data.