UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dcount Syntax With Multiple Criteria/form Fields, Access 2016    
 
   
dr19
post Jul 11 2018, 10:18 AM
Post#1



Posts: 8
Joined: 11-July 18



Hi I am having an issue with my dcount syntax, I am trying to count the results of a yes/no field with a date criteria supplied by two form fields (StartDate & EndDate) - Can anyone assist? thanks.

Here is my current syntax:

IntX6 = DCount("Contributory_Causes", "tblACC", "Contributory_Causes = 'True'" & "And Incident_Date Between [StartDate] and [EndDate]")
Me.CC_Cause = IntX6
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 10:26 AM
Post#2


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

Hi,

Try:
CODE
IntX6 = DCount("Contributory_Causes", "tblACC", "Contributory_Causes = 'True' And Incident_Date Between " & Format([StartDate], "\#yyyy\-mm\-dd\#") & " and " & Format([EndDate], "\#yyyy\-mm\-dd\#"))


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
dr19
post Jul 11 2018, 10:29 AM
Post#3



Posts: 8
Joined: 11-July 18



thanks for the reply d , but I get a type mismatch in criteria expression error.
Go to the top of the page
 
dr19
post Jul 11 2018, 10:30 AM
Post#4



Posts: 8
Joined: 11-July 18



data type mismatch.....
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 10:35 AM
Post#5


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


What datatype is field Contributory_Causes?

If it is a boolean (Yes/No) then remove the single quotes around 'True'.

Also, to double-check, is Incident_Date a datetime field?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 10:36 AM
Post#6


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


Try it this way:
CODE
IntX6 = DCount("*", "tblACC", "Contributory_Causes = True And Incident_Date Between " & Format([StartDate], "\#yyyy\-mm\-dd\#") & " and " & Format([EndDate], "\#yyyy\-mm\-dd\#"))


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
dr19
post Jul 11 2018, 10:50 AM
Post#7



Posts: 8
Joined: 11-July 18



the code ran error free but the results are off a bit, it returns 7 items when it should be 4.
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 10:53 AM
Post#8


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


Without any visibility of your data it's trick how to advise!

--------------------


Regards,

David Marten
Go to the top of the page
 
dr19
post Jul 11 2018, 10:55 AM
Post#9



Posts: 8
Joined: 11-July 18



understood, and thanks for the help but I am unable to share my db. I will keep at it.
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 10:57 AM
Post#10


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


Perhaps post a few bits of data as an example.

E.g the seven records' Incident_Date's and the dates you have entered in [StartDate] and [EndDate]

--------------------


Regards,

David Marten
Go to the top of the page
 
dr19
post Jul 11 2018, 11:01 AM
Post#11



Posts: 8
Joined: 11-July 18



I had a filter wrong - that worked perfectly! thank you. This is my first post, how do I thank you correctly?
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 11:49 AM
Post#12


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


You just did!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
dr19
post Jul 11 2018, 12:04 PM
Post#13



Posts: 8
Joined: 11-July 18



David, do you have time for one more? what if I wanted to add another criteria to that syntax, like SLine = 'DAT'?


IntX6 = DCount("*", "tblACC", "Contributory_Causes = True And "SLine = 'DAT' And Incident_Date Between " & Format([StartDate], "\#yyyy\-mm\-dd\#") & " and " & Format([EndDate], "\#yyyy\-mm\-dd\#"))

Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 12:10 PM
Post#14


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


With DCount(), or other domain aggregate functions like DLookup(), DMax() etc, the criteria should form a valid SQL 'WHERE' clause (without the 'WHERE'!)

SO you are using VBA to concatenate strings and variables to try and end up with a criteria clause that evaluates to:
CODE
Contributory_Causes = True And SLine = 'DAT' And Incident_Date Between #2018-07-01# And #2018-07-31#


So, try:
CODE
IntX6 = DCount("*", "tblACC", "Contributory_Causes = True And SLine = 'DAT' And Incident_Date Between " & Format([StartDate], "\#yyyy\-mm\-dd\#") & " and " & Format([EndDate], "\#yyyy\-mm\-dd\#"))
'                                                             ^
'                                                             |
'                                                 Removed extra double quote here



hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
dr19
post Jul 11 2018, 12:23 PM
Post#15



Posts: 8
Joined: 11-July 18



thanks again for your help, I ended up getting this run time error 2471, The expression you entered as a query parameter produced this error: 'SLine'
Go to the top of the page
 
projecttoday
post Jul 11 2018, 04:55 PM
Post#16


UtterAccess VIP
Posts: 10,051
Joined: 10-February 04
From: South Charleston, WV


Try enclosing SLine with [...].

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 05:24 PM
Post#17


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


Does table 'tblACC' contain a field called 'SLine'?

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st September 2018 - 12:56 AM