Results 1 to 4 of 4
  1. #1
    Lorlai is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    107

    Select query greater than concatenated fields

    I am trying to pull dates that are greater than the user entered date. We have a set table that contains budgeted hours. These hours are summed monthly, and are entered from July2012 to December 2013. I am trying to find a way that will show the sum of the hours after the user entered date. For example, if the user enters 9/30/2012, I want to show the sum of all hours from October 2012 to December 2013. My table structure is below:

    Employee text
    Yr Number (2013)
    Mo Text (January)
    MonthHrs Number
    MonthYr Text (January2013)
    MoNumber Number (1)
    MoYrNumber Number (12013)



    I have created a query that is supposed to pull all records that are greater than the current MoYrNumber. MoYrNumber is made up of the month integer (1 for January, 2013 to indicate the year). However, this only pulls the records greater than that month. For example, when I enter 9/30/2012, the MoYrNumber is 92012, and the following records are retrieved: 102012, 112012, 122012, 92013, 102013, 112013, 122013.

    How can I adjust the query so that the MoYrNumber will pull all of the records that are greater than the user entered number? (entering 9/30/2012 will open 102012, 112012, 122012, 12013, 22013, 32013,..... 122013?)

    My query is below:
    Code:
    SELECT Budget.MonthHrs, Budget.Resource, Budget.moyrnumber
    FROM Budget
    WHERE (((Budget.moyrnumber)>(Month(CVDate(([Forms]![Form1]![PeriodTextBox].[Value]))) & (Year(CVDate(([Forms]![Form1]![PeriodTextBox].[Value])))))));
    Thank you for your help

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Well, as you've probably found your structure doesn't really support what you're trying to do. I'd use date fields, but if you want to convert your MoYrNumber field you can format it like yyyymm which will sort correctly, and should work in a criteria. You can use the Format() function to pad the zero in the month. In other words, Format(MoNumber, "00") will produce 04 for this month.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Paul, I was just simulating the issue and had similar response and about to post when i saw your response

    Here is the test routine I had since I did the testing.
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : testlor
    ' Author    : Jack
    ' Date      : 09-04-2013
    ' Purpose   : simulate a value mDate (a string) and
    ' compare it to MonthYearNumbers in a table
    '****
    ' issue is to find records where records with MonthYearNumbers greater than mDate
    ' are selected.
    '
    '---------------------------------------------------------------------------------------
    ' Last Modified:
    '
    ' Inputs: N/A
    ' Dependency: N/A
    '--------------------------------------------------------------------------
    '
    Sub testlor()
    Dim mDate As String
       On Error GoTo testlor_Error
    
    mDate = "09/23/2012"   '    try various numbers "11/20/2012" 'this represents the form textbox 
    Dim recDates(15) As Long
    recDates(0) = 201209                  'format is YYYYmm in order to get proper numbers
    recDates(1) = 201210
    recDates(2) = 201211
    recDates(3) = 201212
    recDates(4) = 201301
    recDates(5) = 201302
    recDates(6) = 201303
    recDates(7) = 201304
    recDates(8) = 201305
    recDates(9) = 201306
    recDates(10) = 201307
    recDates(11) = 201308
    recDates(12) = 201309
    recDates(13) = 201310
    recDates(14) = 201311
    recDates(15) = 201312
    'from forum
    '(Month(CVDate(([Forms]![Form1]![PeriodTextBox].[Value]))) & (Year(CVDate(([Forms]![Form1]![PeriodTextBox].[Value]
    
    For i = 0 To 15  'this is intended to mock up the select query
     If recDates(i) > (Year(CVDate(mDate))) & (Month(CVDate(mDate))) Then
    Debug.Print recDates(i)
     End If
     Next i
    
       On Error GoTo 0
       Exit Sub
    
    testlor_Error:
    
        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testlor of Module AWF_Related"
    End Sub

  4. #4
    Lorlai is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    107
    Thank you gentlemen, I guess I was making it far more complicated than it needed to be! I created a date field per your direction and was able to very quickly and easily perform my calculations. Thank you for the quick assistance!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-24-2012, 01:16 PM
  2. Replies: 6
    Last Post: 08-15-2012, 04:05 PM
  3. Replies: 17
    Last Post: 01-12-2012, 11:25 AM
  4. Sum values in concatenated fields
    By jdrubins in forum Reports
    Replies: 4
    Last Post: 09-01-2009, 07:20 AM
  5. Replies: 0
    Last Post: 04-03-2009, 01:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums