Results 1 to 4 of 4

Thread: DCount question

  1. #1
    Join Date
    Jun 2006
    Posts
    14

    Unanswered: DCount question

    I'm trying to write a function to count the occurences of a particular month in my database. Here is the code:

    Private Sub Count_Daily_Transactions_Click()

    Dim message, title, default
    Dim inputData As String
    Dim transactions As Integer
    Dim outputMsg As String

    message = "Count transactions for which month?"
    title = "Count Monthly Transactions"
    default = Format(Date(),"mm")

    inputData = InputBox(message, title, default))

    transactions = DCount("[Datum]", "All_Inventory_2006", "[Datum] >= #" & inputData & "/01/2006#" And "[Datum] <= #" & inputData & "/31/2006#")

    outputMsg = MsgBox(transactions & " transactions for month " & inputData, vbOKOnly, title)

    End Sub


    The problem arises in the transactions = line. If I change that line to read:
    transactions = DCount("[Datum]", "All_Inventory_2006", "[Datum] >= #" & inputData & "/01/2006#") the function runs properly and returns the correct result (Count of entries with date xx/01/2006 or above. However, adding the And "[Datum] <= #" & inputData & "/31/2006#" at the end of the code gives a syntax error. I'm very new to VB and haven't quite figured out the proper syntax to make this work. Any help would be greatly appreciated.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Well I'm guessing that if you used a month with 31 days in it it would work but probably wouldn't for months with less than 31 days.

    A cleaner way to do your criteria might be along the lines of:

    "Month([Datum]) =" & inputData & "and Year([Datum]) = 2006"

    inputData in this case needs to be an integer.

    hth
    Chris

  3. #3
    Join Date
    Jun 2006
    Posts
    14
    Thanks for the suggestion. I implemented the Month([Datum]) idea. Here it is:

    transactions = DCount("[Datum]", "All_Inventory_2006", "Month([Datum]) = " & inputData)

    This code works perfectly.

    However, when I try to implement the Year([Datum]) function as well, I keep getting a type mismatch error. The Datum field is in Date/Time and the inputData and inputData2 variables are both As String.

    Any ideas how I could get this code to work without type mismatch error?

    transactions = DCount("[Datum]", "All_Inventory_2006", "Month([Datum]) = " & inputData And "Year([Datum]) = " & inputData2)

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your placement of quotes and ampersands is not correct. You might want to revisit them, keeping in mind the need to separate components of the criteria from variables to be concatenated into that structure. Perhaps this will be of some assistance:

    http://www.mvps.org/access/general/gen0018.htm
    Paul

Posting Permissions

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