VBA SQL Filtering listbox from date in combobox

[enigma54]

Hi,

I have a combobox (CboFilterFinancialPeriod) containing a selection of dates in format dd-mm-yyyy. I have a listbox (lstsearch) on the same form (frmTenderEnquiryIndexFind) as the combobox that is based on a query (qrytenderenquiryindexfind). The idea is that when a user selects a date from the combobox and clicks a button the listbox filters to display all records containing that date.

I know there are similar threads dealing with filtering a listbox from a combobox but they all seem to deal with a wildcard Like operator (is that the right term?) If I implement this solution then I get multiple months being returned if I filter for January or February.

Is there a way of redoing the code below to return an EXACT match only on the data ...not a wildcard search?

Private Sub Search4_Click()
On Error GoTo Error_Search4_Click:
        If IsNull(Me!CboFilterFinancialPeriod) Then
            GoTo Finish:
        Else
            Me!LstSearch.RowSource = "SELECT * FROM QryTenderEnquiryIndexFind Where [PeriodStartDate] Like '*" & Me!CboFilterFinancialPeriod & "*';"
        End If
Finish:
            Exit Sub
 
Exit_Search4_Click:
    Exit Sub
 
Error_Search4_Click:
    MsgBox Err.Number & Err.Description
    Resume Exit_Search4_Click
End Sub

As always any help is appreciated. I realise this is most likely simple and fail to understand why I havent figured it out yet!

Thanks all

Dave

 

[vbaInet]

For an exact match you use:

Where [PeriodStartDate] [COLOR=Red][B]=[/B] [/COLOR]#" & Me!CboFilterFinancialPeriod & "#;"

If your field isn't a Date/Time field then replace the hash charachters with single quotes.

 

[enigma54]

Haha! I was using something similar but with simgle quotes. Its good to know how to specify dates now so thanks.Unfortunately, when trying your method I realise that the reason it is not finding exact matches is that it by default is searching mm-dd-yyyy. All my dates are in format dd-mm-yyyy.

Without changing my date labelling system, is there any way of editing the sub/sql to search with a UK type date

Thanks again

Dave

 

[vbaInet]

That's fine. The JET engine uses the US date format for querying so your date must comply. Use this instead:

Where [PeriodStartDate] = #" & Format(Me!CboFilterFinancialPeriod, "\#mm/dd/yyyy\#") & "#;"

For display purposes you can still get the UK date by using the textbox's Format property for UK dates. Set it to Long Date.

 

[enigma54]

Haha

Thanks a million. I couldnt get it to work at first, then tried it with a text box (which worked) and realised what I was doing wrong

I had to change the relevent lines of code above to:

Me!CboFilterFinancialPeriod.SetFocus
Me!LstSearch.RowSource = "SELECT * FROM QryTenderEnquiryIndexFind Where [PeriodStartDate] = #" & Me!CboFilterFinancialPeriod.Text & "#;"

Before it was always ever searching with Column 0, the index field! I am glad its Friday!

I couldnt change the display format of the combobox (to long date) without it screaming that I was entering an illegal format. Having said this, being financial periods I didnt really need a day in there anyway so I changed the original table to display mm-yyyy and now it works well.

Thanks again for your assist. You saved a lot of headaches!

 

[vbaInet]

Grand! Glad you got that working.