Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013

    Unanswered: Dcount Between Dates

    Good Afternoon All

    Would someone kindly look over the following Syntax for me as I have been trawling forums for most of the day trying to resolve the issue

    In a form I have a unbound Text box with the control source set to the following

    =DCount("*","Parking Violations tbl","[Site] = 'Park'" & "'AND [Date] between #01/05/2013# & #10/05/13#")

    Breaking it down the Dcount calculates for the Fist part up until the between date range, then I recieve a "ERROR" in the text box.

    Any help would be much appriciated


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    DCount Function - Access - suggests that you must have an expression as the frist term, "*" may not be adequate
    =DCount("*","Parking Violations tbl","[Site] = 'Park'" & "' AND [Date] between #01/05/2013# & #10/05/13#")
    so replace "*" with a column name
    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 -
    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

    =DCount("MyColumnName","[Parking Violations tbl]","[Site] = 'Park'" & " AND [Date] between #01/05/2013# AND #10/05/13#")
    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

    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

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    I would try:
    =DCount("*","[Parking Violations tbl]","[Site] = 'Park' AND [Date] between #01/05/2013# AND #10/05/13#")
    Be aware that the interval as expressed is from January 5 to October 5, see: Format the date and time field in Access - Access -
    Have a nice day!

  4. #4
    Join Date
    Feb 2013



    Thank you for your Response coding worked perfectly with also the explanation of the order of MM/DD/YY. Much Appreciated.


    Thanks for the explanation and information will change the table and stop using spaces, and also the reserved word "Date" should have taken note of the warning access did indeed give when issuing the column with the Title.

    Once again thanks, hopeful one day I will be able to help someone on this site form my experience.

    Have a nice Day

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts