How to set 'SET' when UPDATING listbox with query (1 Viewer)

[CBrighton]

ok, can you re-upload please and I'll have another look.

 

[CuriousGeorge]

Sure,

here it comes,

thanks

 

[DCrake]

SomeDate Between #2011-03-03# AND #2011-04-01# AND BananaField LIKE '00000001-D00'

Have you got a field called SomeDate?
Why are you using Like when not using masks?

 

[CuriousGeorge]

Yeah i got a field in query1 called SomeDate and a second field called BananaType.

The BananaType field contains names such as 0000001-D00, 0000001-D02, 0000001-D01 etc.

My comboType box contains the extension names D00,D01...D04 so that i can filter the BananaField in Query1 on the types that matches those extensions.

Since im new to this that was the only way i could come up with when matching names

Thanks

 

[DCrake]

LIKE '00000001-D00'

is exactly the same as

= '00000001-D00'

in both instances only records that match the entire string will be returned.

 

[CuriousGeorge]

yeah i agree on that.

I just added the whole string name 00000001-D00 as a selection when i had = instead of LIKE in the code. Just to test the code.

At the moment i have *D00, *D01 etc as options in the comboType box.

So the debug string currently returns;

SomeDate Between #2010-09-01# AND #2011-04-06# AND BananaField LIKE '*D00'

cheers

 

[DCrake]

So what does your final syntax look like?

 

[CBrighton]

Start by creating this query:

SELECT TableData.Current, TableData.Voltage, TableData.Temp1, TableData.Temp2, TableData.ReqDriveLineTq, TableData.NElMachine, TableData.CtrlStrat, TableData.VehicleSpeed, TableData.ChargeEnergy, TableData.DischargeEnergy, TableData.AbsTime, TableData.CtrlMode, TableData.Soc, TableData.BatCapacity, TableData.BananaField, ReturnDate([AbsTime]) AS AbsDate, Right([BananaField],3) AS Type
FROM TableData;

For the purposes of my code I have called this qryTest.

Change the VBA to the following:

Private Sub FilterLoadedFiles_Click()
Dim strSQLFilter As String
strSQLFilter = ""
 
If Not IsNull(Me.TextDateOne) And Not Me.TextDateOne = "" Then
   If Me.DateSelectionType = "Between" Then
      If IsNull(Me.TextDateTwo) And Not Me.TextDateTwo = "" Then
         MsgBox "Check start date selections."
      End If
      strSQLFilter = strSQLFilter & "AbsDate Between #" & Format(Me.TextDateOne, "yyyy/mm/dd") & "# AND #" & Format(Me.TextDateTwo, "yyyy/mm/dd") & "# AND "
   ElseIf Me.DateSelectionType = "Before" Then
strSQLFilter = strSQLFilter & "AbsDate < #" & Format(Me.TextDateOne, "yyyy/mm/dd") & "# AND "
   ElseIf DateSelectionType = "After" Then
strSQLFilter = strSQLFilter & "AbsDate > #" & Format(Me.TextDateOne, "yyyy/mm/dd") & "# AND "
    ElseIf DateSelectionType = "On" Then
strSQLFilter = strSQLFilter & "AbsDate = #" & Format(Me.TextDateOne, "yyyy/mm/dd") & "# AND "
   End If
End If
 
If Not IsNull(Me.ComboType) And Not Me.ComboType = "" Then
    strSQLFilter = strSQLFilter & "Type LIKE '" & Me.ComboType & "' AND "
End If
 
If Len(strSQLFilter) > 6 Then
    strSQLFilter = Left(strSQLFilter, Len(strSQLFilter) - 5)
End If
 
ListBox.RowSource = "SELECT * FROM qryTest WHERE " & strSQLFilter & ";"
 Debug.Print strSQLFilter
Me.Requery
End Sub

Open the form, edit the ListBox properties: "Row Source Type" = Table/Query



This should work now, however you may want to tweak the SQL to return whichever fields you want to see in the ListBox.

:edit:

Also, change the Row Source of the type combobox to "D00";"D01";"D02";"D03";"D04", the astrix's are no longer needed as the query now has the type in its own field.

 

[CuriousGeorge]

Yeah it worked, even with my old query1 Seemed to be the setting of the rowsource type.

Gonna try tweaking that string a bit now to also display the file type.


I really appreciate all the help. Thank you.

/CG

 

[CBrighton]

Yeah, the row source type was what was stopping the listbox from displaying as it should.

The ListBox is already 2 column, you should be able to alter the widths to unhide one column and change the "SELECT *" to "SELECT Field1, Field2" to let you display the fields you want.