Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    24

    Unanswered: Monthly Calculations

    Hi all,

    I have some code which counts records that match a selection from a drop down list. This all works fine, my problem is that I would like to also narrow down the count to data from a specific month.

    Here is the code I have:
    Code:
    For i = 1 To 38
         
        StrQLeadSource = CBoxLeadSourceLS.ItemData(i)
        
        CBoxLeadSourceLS.Value = StrQLeadSource
        
        LCtLeadSourceUp = DCount("[Lead Source]", "Leads", "[Lead Source] = CBoxLeadSourceLS.Value")
        
        DoCmd.SetWarnings False    
        Call DoSQL
        
        DoCmd.SetWarnings True    
    Next i
    
    End Sub
    Public Sub DoSQL()
    
        Dim SQL As String    
        SQL = "UPDATE LeadSource " & _
              "SET [Quantity] = " & LCtLeadSourceUp & " " & _
              "WHERE [ID] = " & i
        
        DoCmd****nSQL SQL
        
    End Sub
    (StrQLeadSource & LCtLeadSourceUp are variables (string & long respectively) and CBoxLeadSourceLS is the combobox which forms the drop down list.
    This code goes through each item of the list and updates a separate table with the quantities of each one.)

    and attached is a screen shot of the table with sample data in.

    I would like the code to take the current month and reduce by 1 (to get last month's data) then search for (& count) all records that match my current search plus the month figure generated (this month -1).

    I have been able to get the data for today using:
    Code:
    LCtLeadSourceUp = DCount("[Lead Source] & [Lead Date]", "Leads", "[Lead Source] = CBoxLeadSourceLS.Value" & " " & "AND [Lead Date] = #" & Now & "#")
    but I can't seem to separate the month part of the date field.

    Any help would be greatly appreciated. If you need any more info please ask.

    Thanks,
    Gav
    Attached Thumbnails Attached Thumbnails Sample Data (Leads DB).bmp  

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You culd try

    Code:
    LCtLeadSourceUp = DCount("[Lead Source] & [Lead Date]", "Leads", "[Lead Source] = CBoxLeadSourceLS.Value" & " " & " AND Month([Lead Date]) = " & Month(Date()) & " AND Year([Lead Date]) = " & Year(Date()))
    ??

    MTB

  3. #3
    Join Date
    Apr 2013
    Posts
    24
    Quote Originally Posted by MikeTheBike View Post
    Hi

    You culd try

    Code:
    LCtLeadSourceUp = DCount("[Lead Source] & [Lead Date]", "Leads", "[Lead Source] = CBoxLeadSourceLS.Value" & " " & " AND Month([Lead Date]) = " & Month(Date()) & " AND Year([Lead Date]) = " & Year(Date()))
    ??

    MTB
    That worked great thanks.

    Is there any way of setting it to automatically run this code on a set date e.g. the 1st of every month collect all the data from the last month without anyone having to open/run the program?

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    I would pass the i and LCtLeadSourceUp into the sub


    Public Sub DoSQL(input_I,input_LCtLeadSourceUp)

    Dim SQL As String
    SQL = "UPDATE LeadSource " & _
    "SET [Quantity] = " & input_LCtLeadSourceUp & " " & _
    "WHERE [ID] = " & input_i

    DoCmd****nSQL SQL

    End Sub

    only so you now what been pass in to Sub

    Run Automatically that needs thinking out side the box

    what do is create a new DB link the Tables i need set a autoexe macro
    to call a function which then would run the loop and have the QUIT in the last line of the macro

    then add it to the windows Scheduled Tasks run it once a month job done
    just remember if you change your password you have to rest the Scheduled Tasks with the new password ( a tick i learnt why did my jobs stop working)

    eg I just done on last week where its up date our packing chart hourly so the ceo can open the excel file and see what happen then at 8:0am it prints the packing reporting (charts ) to our main printer in the office which them get put on the notice board.

    the ceo came to me this morning saying thanks for printing them. bugger can't tell him i was still getting to work at that time.
    see you can be in to places at once LOL
    Last edited by myle; 06-20-13 at 08:29. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Apr 2013
    Posts
    24
    Quote Originally Posted by myle View Post
    I would pass the i and LCtLeadSourceUp into the sub


    Public Sub DoSQL(input_I,input_LCtLeadSourceUp)

    Dim SQL As String
    SQL = "UPDATE LeadSource " & _
    "SET [Quantity] = " & input_LCtLeadSourceUp & " " & _
    "WHERE [ID] = " & input_i

    DoCmd****nSQL SQL

    End Sub

    only so you now what been pass in to Sub

    Run Automatically that needs thinking out side the box

    what do is create a new DB link the Tables i need set a autoexe macro
    to call a function which then would run the loop and have the QUIT in the last line of the macro

    then add it to the windows Scheduled Tasks run it once a month job done
    just remember if you change your password you have to rest the Scheduled Tasks with the new password ( a tick i learnt why did my jobs stop working)

    eg I just done on last week where its up date our packing chart hourly so the ceo can open the excel file and see what happen then at 8:0am it prints the packing reporting (charts ) to our main printer in the office which them get put on the notice board.

    the ceo came to me this morning saying thanks for printing them. bugger can't tell him i was still getting to work at that time.
    see you can be in to places at once LOL
    I completely forgot about Windows Scheduled Tasks, I've added it to that now and run some tests it's now doing exactly what I want when I want

    Thanks very much for your help guys,
    Gav

Posting Permissions

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