Filter Recordset With Parameter

  • I have robbed and amended the code below from this forum to use ADO & SQL to get data from an Access Database.


    My query is this - can I alter this line of code:


    Code
    stSQL = stSQL & " WHERE ((([01 Interventions].[Start Date])>'01/04/06'));"


    so that when I run my code it will ask me to enter a date. This date would then be used to filter my recordset for all [01 Interventions].[Start Date] s that are greater than this entered figure.


    Any help would be much appreciated.


    Thanks


    JP



    Full Code


    You can go anywhere you want if you look serious and carry a clipboard.

  • Re: Filter Recordset With Parameter


    I've changed it to connect to my Database. I can click on the Run Query and this works fine. However, I am having problems with the checkbox.


    I get the error run-time error '13' type mismatch on the line

    Code
    .List = Application.Transpose(vaData)


    I'm a bit stumped - any thoughts?




    FULL CODE

    You can go anywhere you want if you look serious and carry a clipboard.

  • Re: Filter Recordset With Parameter


    I've tried playing around with the code to try and understand it a bit better.


    I used the northwind examle and used [RequiredDate] instead of [OrderDate]
    this worked fine. I then tried [ShippedDate] it gave me the same error that I had before. I know I'm missing something but I can't see it.


    Code
    With Me
                With .cmbYr
                    .Clear
                    'load the query result into combobox
                    .List = Application.Transpose(vaData)
                    .ListIndex = -1
                End With
            End With


    Can you talk me through what the above code is doing in relation to selecting the different fields ([OrderDate], [RequiredDate] & [ShippedDate]) ?


    Many Thanks


    JP

    You can go anywhere you want if you look serious and carry a clipboard.

  • Re: Filter Recordset With Parameter


    I feel a lot closer now (although this may not be the case).


    I can get the tick box & Combo box to work (in as much as it loads the data into the combo box) but when I run the code after applying the filter I get an error message. (If I don't select the tick/combo box it loads all the records with no error message.)


    VBA Error # :-2147217900
    Generated by : Microsoft JET Database Engine
    Description : Syntax error (missing operator) in query expression ‘[Intervention] = Delivering Neighbourhood Policing’.
    ADO Error # :-2147217900
    Generated by : Microsoft JET Database Engine
    Description : Syntax error (missing operator) in query expression ‘[Intervention] = Delivering Neighbourhood Policing’.
    Source : Microsoft JET Database Engine
    SQL State : 3075


    Delivering Neighbourhood Policing refers to the Intervention that is selected in the combobox.


    I'm not sure why this is happening - I'm guessing it is to do with the parameter strings????


    Any Clues on how to get this right?


    You can go anywhere you want if you look serious and carry a clipboard.

  • Re: Filter Recordset With Parameter


    Small remark:


    if the field of your database is a text-field you have to add quotes:


    Code
    stParam = " WHERE [Intervention] = [COLOR="Red"]'[/COLOR]" & Me.Interventionlbl.Text  & "[COLOR="red"]'[/COLOR]"
  • Re: Filter Recordset With Parameter


    How would I add another filter to the recordset.


    Could I add another Combo box?



    And something like this?

    You can go anywhere you want if you look serious and carry a clipboard.

  • Re: Filter Recordset With Parameter


    Well, you need to think carefully about how you would construct the SQL in the case of multiple parameters.


    Don't forget, for more than 1 parameter, you'd need to use AND as opposed to another WHERE, but basically you have the right idea.


    At work I have what I think is just what you are looking for - it's an addin that I developed for our Finance dept that they use to query an accounting system on SQL. It has options for 3 or 4 parameters using checkboxes & list/comboboxes to pass the parameters to SQL.


    I will dig it out tomorrow and post up the relevant bits of code and the addin itself if I can zip it down.


    Basically it's a question of carefully thinking through all possible combinations that your user could select &^ making sure that the SQL your code then generates is valid. What I would do is forget the rest of the code for a minute and just debug.print the SQL from all different variations... then run that in your Access/SQL query pane to check valid syntax.... then move on to what you want to do with the result.

  • Re: Filter Recordset With Parameter


    basically something like this


  • Re: Filter Recordset With Parameter


    Addin to big to attach, however, in a similar vein to Zimitry, here is the essential VBA


    [vba]
    stAnd = " AND "
    stParam2 = " PERIOD >= '" & Me.ComboBox2.Text & "'"
    stParam3 = " PERIOD <= '" & Me.ComboBox3.Text & "'"
    stParam4 = " TREFERENCE = '" & Me.TextBox1.Text & "'"


    'check & build variable parameters
    'depending on whether checkboxes ticked by user
    If Me.CheckBox2.Value = True Then
    stParam2 = stAnd & stParam2
    Else: stParam2 = vbNullString
    End If
    If Me.CheckBox3.Value = True Then
    stParam3 = stAnd & stParam3
    Else: stParam3 = vbNullString
    End If
    If Me.CheckBox4.Value = True Then
    stParam4 = stAnd & stParam4
    Else: stParam4 = vbNullString
    End If


    'now we build the SQL code
    stSQL = "SELECT AMOUNT as 'Value',ALLOCATION as 'Alloc',TREFERENCE as 'Inv/Chq Ref', "
    stSQL = stSQL & " PERIOD as 'Period',TRANS_DATE as 'Tran Date',ALLOC_DATE as 'Alloc Date',JRNAL_SRCE as 'Who', "
    stSQL = stSQL & " DESCRIPTN 'Description' ,LEFT(ANAL_T1,3) as 'C-Centre' FROM SALFLDG001 WHERE ACCNT_CODE = '" & Me.ComboBox1.Value & "'"
    stSQL = stSQL & stParam2 & stParam3 & stParam4


    'MsgBox Len(stSQL)
    [/vba]

  • Re: Filter Recordset With Parameter


    Thanks for the help folks - but now I'm getting into a bit of a muddle.


    This is what I have at the moment but it doesn't completely work. The filter on Interventionlbl works but not the Yearlbl. Hopefully it is something simple and I'm being a complete donut?


    Also - Yearlbl is a combobox that returns a numeric value - am I using the right code to capture this?

    You can go anywhere you want if you look serious and carry a clipboard.

  • Re: Filter Recordset With Parameter


    dates need to be put a #


  • Re: Filter Recordset With Parameter


    Quote from zimitry

    dates need to be put a #


    Well they do in Access. But Joe's passing a year value, not a date value, so i belive all he needs to do for a numeric parameter is to drop the speech marks


    [vba]
    stParam = " WHERE Initiative = '" & Me.Interventionlbl.Text & "'"
    stAnd = " AND "
    stParam2 = " year = " & Me.Yearlbl.Text
    stParam3 = " ;" [/vba]


    as opposed to


    [vba]
    stParam = " WHERE Initiative = '" & Me.Interventionlbl.Text & "'"
    stAnd = " AND "
    stParam2 = " year = '" & Me.Yearlbl.Text & "'"
    stParam3 = " ;"
    [/vba]

  • Re: Filter Recordset With Parameter


    My fault didn't read the text properly


    Quote


    [COLOR="Red"]'[/COLOR]" & whatever & "[COLOR="Red"]'[/COLOR] Needs to be placed for 'text'


    [COLOR="Red"]#[/COLOR]" & whatever & "[COLOR="Red"]#[/COLOR] Needs to be placed for 'date values'


    As far as i'm aware numeric values are ok and do not need any types before and after them...


    They also need to use if passing from SQL Server...

  • Re: Filter Recordset With Parameter


    Thanks Guys. I've finally got it to work - gold stars for everyone who helped. I had a label in the wrong place!



    This works for what I currently need it for. To show a recordset based on three filters. (Intervention, Year & Quarter). One problem I have is if I want to filter the recordset by Year and quarter (to include all Interventions) it doesn't work. Is there a simple solution to solve this?

    You can go anywhere you want if you look serious and carry a clipboard.

  • Re: Filter Recordset With Parameter


    yeah should work if you take out intervention in your WHERE Clause...


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!