Select query greater than concatenated fields

[Lorlai]

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:

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

[pbaldy]

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.

[orange]

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.

'---------------------------------------------------------------------------------------
' 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

[Lorlai]

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!