Multiple criteria on DCount including date field (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 22:01
Joined
Nov 23, 2019
Messages
75
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

AWF VIP
Local time
Today, 13:01
Joined
Mar 9, 2014
Messages
5,497
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.
 
Last edited:

PaulD2019

Registered User.
Local time
Today, 22:01
Joined
Nov 23, 2019
Messages
75
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

AWF VIP
Local time
Today, 13:01
Joined
Mar 9, 2014
Messages
5,497
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.
 

Josef P.

Well-known member
Local time
Today, 23:01
Joined
Feb 2, 2023
Messages
848
Test it with a query:
Code:
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

AWF VIP
Local time
Today, 13:01
Joined
Mar 9, 2014
Messages
5,497
Ooops, too many ending parens in your attempt. Remove the one after 30.

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

PaulD2019

Registered User.
Local time
Today, 22:01
Joined
Nov 23, 2019
Messages
75
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

Registered User.
Local time
Tomorrow, 00:01
Joined
Dec 7, 2018
Messages
484
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

Registered User.
Local time
Today, 22:01
Joined
Nov 23, 2019
Messages
75
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

Super Moderator
Staff member
Local time
Today, 17:01
Joined
Feb 19, 2002
Messages
43,513
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

Registered User.
Local time
Tomorrow, 00:01
Joined
Dec 7, 2018
Messages
484
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\#"))
 

Attachments

  • 2023-09-24_013401.png
    2023-09-24_013401.png
    7.5 KB · Views: 54
  • 2023-09-24_013509.png
    2023-09-24_013509.png
    4.9 KB · Views: 52
  • 2023-09-24_013540.png
    2023-09-24_013540.png
    8.6 KB · Views: 50

June7

AWF VIP
Local time
Today, 13:01
Joined
Mar 9, 2014
Messages
5,497
Ooops again. Yes, "Quoted" is data, not field. So use "*" as shown in my and Eugene's examples.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:01
Joined
Feb 19, 2002
Messages
43,513
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

Registered User.
Local time
Today, 22:01
Joined
Nov 23, 2019
Messages
75
@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
 

Users who are viewing this thread

Top Bottom