DCount Function - Access - Office.com suggests that you must have an expression as the frist term, "*" may not be adequate
so replace "*" with a column name
=DCount("*","Parking Violations tbl","[Site] = 'Park'" & "' AND [Date] between #01/05/2013# & #10/05/13#")
if you have a space in th etable or column name you MUST encloise the offending element and it is offending in square brakcets
Between...And Operator - Access - Office.com
suggests that the syntax used for the BETWEEN operator is
BETWEEN <lowervalue> AND <highervalue>
as the & sign is the string concatenation symbol thats almost certainly cause problems
you also have a superfluous quote mark After Park and before the AND clause. you don'' need to drop in and out of VBA strings
=DCount("MyColumnName","[Parking Violations tbl]","[Site] = 'Park'" & " AND [Date] between #01/05/2013# AND #10/05/13#")
you may have problems because you are using the reserved word DATE as a column name. Access / JET is usually smart enough to resolve this issue, indeed newer versions of Access stop you creating columns with reserved words. however Access isn't infallable, and if you haven't invested a huge amount of time using a column called date I'd strongly suggest you rename it to something else as it will bite you in the backside at some stage
=DCount("MyColumnName","[Parking Violations tbl]","[Site] = 'Park' AND [Date] between #01/05/2013# AND #10/05/13#")
likewise although its not going to cause intermittent problems using spaces in column and or table names is not smant and will cause problems at a later date if you switch to a server backend. get into good habits early on naming tables and columns.
Parking Violations tbl
contaisn spaces... not good
it contains redundant information the tbl bit
whether you use CamelCase and not spacing or non_camel_case is up to you
you dont' need to use full words, providing the meaning can be easily derived it should be good enbough
well soem SQL engines are limited to an arbitary number of characters, some are sneaky and accept as many character as you type, but only use the first n characters.
another reason for using abbrevations is that when you create intersection tables it can get very very messy and longwinded
Last edited by healdem; 05-12-13 at 14:08.
I'd rather be riding on the Tiger 800 or the Norton