help with query (Month as text ) up to given month

[Colm O'Brien]

I
have a field called year end month where records hold the month financial
year end for accounts is stored.

i need to query the data base and return all records where year end month is
less than or equal to user input (eg april) any ideas

[Squirrel]

Colm,

This might give you some ideas. It's a workaround for your db holding date
information as a text field.

I created a lookup table like this. I called it tblMonths:

MonthNumber MonthName
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
and a second table like this, which I called tblMonthsAndData :

MonthName Value
April 50
January 34
March 45
November 85
December 90
and then created a form with a combobox (cboMonths) on it and a command
button (cmdGetData).

cboMonths.rowsource = "SELECT [tblMonths].[MonthNumber],
[tblMonths].[MonthName] FROM tblMonths;"
cboMonths Column Count is 2
cboMonths Column Widths is 0";1"
cboMonths List Rows is 12
cboMonth Limit To List is Yes

The user will choose a month from the combobox, the month number is in the
hidden column.

This is the code behind the command button: (Be sure to set a reference to
Microsoft DAO 3.6 Object Library).

Private Sub cmdGetData_Click()
Dim strSQL As String
Dim qdf As QueryDef

On Error Resume Next
CurrentDb.QueryDefs.Delete ("tmpQry")
If Not IsNull(Me!cboMonths) Then
strSQL = "select * from tblMonthsAndData where MonthName in " & _
"(select MonthName from tblMonths where MonthNumber <= " & _
Me!cboMonths & ")"
End If
Set qdf = CurrentDb.CreateQueryDef("tmpQry", strSQL)
DoCmd.OpenQuery "tmpQry"
End Sub

HTH. Linda
"Colm O'Brien" <colmobrien(no spam)@btconnect.com> wrote in message
news:c0**********@sparta.btinternet.com...

I
have a field called year end month where records hold the month financial
year end for accounts is stored.

i need to query the data base and return all records where year end month is less than or equal to user input (eg april) any ideas