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

    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

    Pjaw

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    DCount Function - Access - Office.com suggests that you must have an expression as the frist term, "*" may not be adequate
    Code:
    =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 - 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

    Code:
    =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
    Code:
    =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.

    ferisntance
    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
    ParkingViolations
    OR
    Parking_Violations

    you dont' need to use full words, providing the meaning can be easily derived it should be good enbough
    eg
    PkgViols

    why?
    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 15:08.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would try:
    Code:
    =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 - Office.com
    Have a nice day!

  4. #4
    Join Date
    Feb 2013
    Posts
    99

    Resolved

    Sinndho

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

    Healdem

    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
  •