Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    14

    Unanswered: Count from query displayed in msgbox

    I'm looking to create a command button on a form that will execute a query called "Count Daily Transactions." The SQL for the query is:

    SELECT Count(*) AS [Total Transactions]
    FROM All_Inventory_2006
    WHERE (((All_Inventory_2006.Date)=[Enter Date to Count]));

    The query runs just fine, but the query window pops up with the result of the query in a field I call "Total Transactions."

    I created a macro that performs the query when the command button is pressed, but the query window pops up with the result [Total Transactions].

    What I'd like to be able to do is execute this query from the form's command button, have the user enter the date they'd like to count in an InputBox, and print the results from that count into a msgbox that will pop up.

    I do not, however, wish for the query window to pop up at all.

    I'm very new to access and VB and am unsure as how I should go about doing this. Should I scratch this query alltogether and incorporate the SQL into my private subroutine? If so, what would the code be to create that dynamic query?

    Here's my Click subroutine:

    Private Sub Count_Daily_Transactions_Click()

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

    message = "Count transactions for which date?"
    title = "Count Daily Transactions"
    default = "Not enough information to proceed!"

    inputData = InputBox(message, title, default) 'User's input

    'NEED CODE HERE TO EXECUTE QUERY WITH USER'S INPUT

    MsgBox transactions & "transactions for date " & inputData 'MsgBox displaying count

    End Sub

    The count will be from the All_Inventory_2006 table under the Date column where the date is equal to the user's input date.

    Any suggestions?

  2. #2
    Join Date
    Jun 2006
    Posts
    14

    Update

    I modified the subroutine to incorporate the DCount function:

    Private Sub Count_Daily_Transactions_Click()

    Dim message, title, default
    Dim inputData As Date
    Dim transactions As String

    message = "Count transactions for which date?"
    title = "Count Daily Transactions"
    default = ""

    inputData = InputBox(message, title, default) 'User's input

    transactions = DCount("[Date]", "All_Inventory_2006", "[Date] = inputData")

    MsgBox transactions & "transactions for date " & inputData 'MsgBox displaying count

    End Sub

    However, it generates an error on the line with the DCount statement. Anybody see what I'm doing wrong?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    off hand no.....

    should "[Date] = inputData"
    read "[Date] = " & inputData

    you may have a problem used an SQL/JET reserved word "DATE" as one of your column names

    the next thing could be that you may need to bracket the date (inputdata) with hash symbol (#)

    the next thing is what is the value of [DATE] in yout db - what is setting that value. is it actually a date or is it a date / time value

    what code are you using to to define what value [date] has. if you used now() effectively you are assigning a date AND time value so you expression is possibly looking to match 21 June 2006 @ 12:00:00.

    of course it could just be that you are attempting to assign a string value to a date

    I'd of though it would be better to assign a default which is todays date OR if you wnat ththe default o be null / anything then you need to alter your dcount to soemthing like

    Code:
    Private Sub Count_Daily_Transactions_Click()
    
    Dim message, title, default
    Dim inputData As Date
    Dim transactions As String
    
    message = "Count transactions for which date?"
    title = "Count Daily Transactions"
    default = DATE()
    
    inputData = CDATE(InputBox(message, title, default)) 'User's input
    
    transactions = DCount("[Date]", "All_Inventory_2006", "[Date] =  " & inputData)
    
    MsgBox transactions & "transactions for date " & inputData 'MsgBox displaying count
    
    End Sub
    if you want to use "" as your default

    Code:
    Private Sub Count_Daily_Transactions_Click()
    
    Dim message, title, default
    Dim inputData As Date
    Dim transactions As String
    
    message = "Count transactions for which date?"
    title = "Count Daily Transactions"
    default = ""
    
    inputData = CDATE(InputBox(message, title, default)) 'User's input
    if isdate(inpudata)=true then
      transactions = DCount("[Date]", "All_Inventory_2006", "[Date] =    " & inputData)
    else
      transactions = DCount("[Date]", "All_Inventory_2006", "   insull([Date]) = FALSE " & 
    endif
    MsgBox transactions & "transactions for date " & inputData 'MsgBox displaying count
    
    End Sub
    Warning neither bits of code have been tested, they are air code, not on a MSoft box, so cant test (also cant/wont spell check, but thats another matter entirely)

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2006
    Posts
    14
    Quote Originally Posted by healdem
    off hand no.....

    should "[Date] = inputData"
    read "[Date] = " & inputData
    I impemented this change as suggested

    you may have a problem used an SQL/JET reserved word "DATE" as one of your column names

    the next thing could be that you may need to bracket the date (inputdata) with hash symbol (#)
    I'm not sure what this is referring to, I'm certainly a VB noob

    the next thing is what is the value of [DATE] in yout db - what is setting that value. is it actually a date or is it a date / time value
    The Date field was renamed as Datum and is of type Date / Time. Entries in the table are in the form mm/dd/yyyy

    what code are you using to to define what value [date] has. if you used now() effectively you are assigning a date AND time value so you expression is possibly looking to match 21 June 2006 @ 12:00:00.

    of course it could just be that you are attempting to assign a string value to a date

    I'd of though it would be better to assign a default which is todays date OR if you wnat ththe default o be null / anything then you need to alter your dcount to soemthing like
    Good suggestion. Has been implemented and works accordlingly.

    Code:
    Private Sub Count_Daily_Transactions_Click()
    
    Dim message, title, default
    Dim inputData As Date
    Dim transactions As String
    
    message = "Count transactions for which date?"
    title = "Count Daily Transactions"
    default = DATE()
    
    inputData = CDATE(InputBox(message, title, default)) 'User's input
    
    transactions = DCount("[Date]", "All_Inventory_2006", "[Date] =  " & inputData)
    
    MsgBox transactions & "transactions for date " & inputData 'MsgBox displaying count
    
    End Sub
    if you want to use "" as your default

    Code:
    Private Sub Count_Daily_Transactions_Click()
    
    Dim message, title, default
    Dim inputData As Date
    Dim transactions As String
    
    message = "Count transactions for which date?"
    title = "Count Daily Transactions"
    default = ""
    
    inputData = CDATE(InputBox(message, title, default)) 'User's input
    if isdate(inpudata)=true then
      transactions = DCount("[Date]", "All_Inventory_2006", "[Date] =    " & inputData)
    else
      transactions = DCount("[Date]", "All_Inventory_2006", "   insull([Date]) = FALSE " & 
    endif
    MsgBox transactions & "transactions for date " & inputData 'MsgBox displaying count
    
    End Sub
    Warning neither bits of code have been tested, they are air code, not on a MSoft box, so cant test (also cant/wont spell check, but thats another matter entirely)

    HTH


    Thanks for all your helpful suggestions. The subroutine is still not functioning correctly though. I think it has something to do with the Datum type and Time Field.
    Last edited by mudhoney; 06-21-06 at 11:20.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    then ts probably

    transactions = DCount("[Date]", "All_Inventory_2006", "[Date] = " & cdate(inputData))

    OR

    transactions = DCount("[Date]", "All_Inventory_2006", "[Date] = #" & cdate(inputData) & "#")
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2006
    Posts
    14
    transactions = DCount("[Date]", "All_Inventory_2006", "[Date] = #" & cdate(inputData) & "#")

    This one works!

    Thanks so much for your help, I really appreciate it.

Posting Permissions

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