Multiple criteria on DCount including date field

[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

 

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

 

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

 

[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: