How to set 'SET' when UPDATING listbox with query

[CuriousGeorge]

Hello,

im trying to get my filter button to work.

On my form i have a list box and i would like to filter the items that are listed there with help from three combo boxes, combodate1 combodate2 and combotype.

The listbox is using Query1 to list items so i thought i could go on using something like the code below for my filterbutton.

I dont know though how to set the "SET part" on the UPDATE expression.

If the box lists the following:

SomeDate 2010-12-12 TypeOfFile 000001-D00
SomeDate 2010-12-25 TypeOfFile 000001-D00
SomeDate 2011-01-01 TypeOfFile 000002-D00

And combodate1 box is 2010-12-12. combodate2 is 2010-12-25 and combotype is *00 i want it to filter within that range and display the upper two rows.

I mean if I would use the syntax 'SET Query1.TypeOfFile = [Forms]![frm]![ComboType]' it would set that to either D00,D01,D02,D03,D04 since im using the LIKE identifier in that combo box to ident files with those extensions. I want to filter on one of those types but i want the listbox to display all files that have that extension.

How do i handle the SET part here? Is it possible to use the LIKE operator here as well? How do i set SET when im using a range of dates from the other two combo boxes?

Maybe im totally off here...

Thanks v much

Private Sub FilterLoadedFiles_Click()
Dim qdf As DAO.QueryDef
Dim s As String
Dim specname As String
specname = "ExportSpecs"
s = "UPDATE Query1.TypeOfFile, Query1.SomeDate FROM Query1 SET Query1.TypeOfFile = ???, Query1.SomeDate = ??? WHERE (((Query1.SomeDate)>=[Forms]![frm]![ComboDate1] AND ((Query1.SomeDate)<=[Forms]![frm]![ComboDate2])) AND ((Query1.TypeOfFile) LIKE [Forms]![frm]![ComboType]));"
Debug.Print s
Set qdf = CurrentDb.QueryDefs("Query1")
qdf.Sql = s
DoCmd.OpenQuery ("Query1")
End Sub

 

[Galaxiom]

Objects (such as tables, queries and fields) in query string cannot be direcetly referenced as variables or values from other objects.

To achiecve your goal, the query string must be constructed in VBA by concatenating the actual names of the objects from the variables.

 

[CuriousGeorge]

Thank you for your answer.

But even if changing my string expression by concatination i still dunno how to set the 'SET' clause.

How do i approach that part?

Cheers

 

[CBrighton]

Can I ask why the end users are creating / editing a query at all?

Is this not something which can be done in a recordset or via adding a dynamic filter to a static query?

 

[CuriousGeorge]

What i have in my form is a list which displays all files that have been added to the database. This could be 1000 of files and millions of rows in the database table. Im using this display to let the user knowing what to choose in the combo boxes and exporting files to a text document.

To make it more easily overviewed towards the user, he can choose to filter the listbox by choosing date and filetype from the combo boxes.

This was the approach i came up with (and the only way i could come up with since im new to this).

Is there a better way, easier way, of doing this?

Why isn't updating the query, that is used in the list box, with help from the combo boxes a good way to filter the list box?

Cheers

 

[CBrighton]

If the query is used purely for this one process, and it's either locally stored on the front end or only one person will do it (i.e. multiple people won't try to edit the same query at the same time) it's not so bad.

However, I'd get rid of the query entirely. If the row source for the listbox should be dynamic then lets use an SQL statement instead of a saved query.

Personally I'd go so far as to start with the rowsource blank. when the user initially opens the form why should they have to wait for the database to load every single record before we let them filter it? If they will only want to see a portion of the records then lets let them select their criteria before we make them wait.

What I'm going to do is put together something with 7 controls which should do what you want to do.

First we have your 2 date controls. I'd change these from comboboxes (which I assume they are from the control names you provided) to text boxes, but that's not vital.

Additionally, I'd add an additional control to go with the date fields. This will be a combobox containing "On, Before, Since, Between". I'd start with the two date controls disabled and enable 1 if "On", "Before" or "Since" is selected and both if "Between" is selected.

The type combobox you have is the 4th control.

The 5th control is a command button used to apply the criteria we have set with the first 4.

6th is the listbox itself and 7th is a button to do whatever you want to do with the filtered data (text export, whatever).



With all the controls sorted the 4th control (search button) needs to have it's VBA setup. Somethign like:

Dim strSQLFilter as string
strSQLString = ""
 
If Not IsNull([B]DateControl1[/B]) And Not [B]DateControl1 [/B]= "" Then
   If [B]DateSelectionType [/B]= "Between" Then
      If IsNull([B]DateControl2[/B]) And Not [B]DateControl2 [/B]= "" Then
         MsgBox "Check start date selections."
      End If
      strSQLFilter = strSQLFilter & "SomeDate Between #" & Format([B]DateControl1[/B], "mm/dd/yyyy") & "# AND #" & Format([B]DateControl2[/B], "mm/dd/yyyy") & "# AND "
   ElseIf [B]DateSelectionType [/B]= "Before" Then
strSQLFilter = strSQLFilter & "SomeDate < #" & Format([B]DateControl1[/B], "mm/dd/yyyy") & "# AND "
   ElseIf [B]DateSelectionType [/B]= "After" Then
strSQLFilter = strSQLFilter & "SomeDate > #" & Format([B]DateControl1[/B], "mm/dd/yyyy") & "# AND "
   End If
End If
 
If Not IsNull([B]FileType[/B]) And Not [B]FileType[/B]= "" Then
    strSQLFilter = strSQLFilter & "TypeOfFile = '" & [B]FileType[/B]& "' AND "
End If
 
If Len(strSQLFilter) > 6 Then
    strSQLFilter = Left(strSQLFilter, Len(strSQLFilter) - 5)
End If
 
[B]ListBox[/B].RowSource = "SELECT * [B]Fields[/B] FROM [B]Tables[/B] WHERE " & strSQLFilter"
 
me.requery

This is untested code, but hopefully it will at least show you one way to do it.

 

[CuriousGeorge]

Cheers CBrighton,

I'll have a go at it tomorrow when i'm back on my windows computer

Always good to see alternative approaches, good way of learning.

Are code like this more efficient and faster compared to queries or are there no difference?

Thanks, appreciate ur help.

/CG

 

[CBrighton]

To be honest I couldn't tell you if it is faster.

However it offers you a far greater level of control.

Instead of having the SQL define a statis query this lets the user add their own criteria without actually editing anything in the database. It lets you decide how to handle empty controls, etc.

 

[CuriousGeorge]

Cheers, no worries.

Im trying to get the code work but nothing is happening when i press the button.

Ive left the listbox rowsource empty and added a value list with "Between" etc.

Any ideas?

Thanks

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 & "ReturnDate(AbsTime) Between #" & Format(Me.TextDateOne, "yyyy/mm/dd") & "# AND #" & Format(Me.TextDateTwo, "yyyy/mm/dd") & "# AND "
   ElseIf Me.DateSelectionType = "Before" Then
strSQLFilter = strSQLFilter & "ReturnDate(AbsTime) < #" & Format(Me.TextDateOne, "yyyy/mm/dd") & "# AND "
   ElseIf DateSelectionType = "After" Then
strSQLFilter = strSQLFilter & "ReturnDate(AbsTime) > #" & Format(Me.TextDateOne, "yyyy/mm/dd") & "# AND "
   End If
End If
 
If Not IsNull(Me.ComboType) And Not Me.ComboType = "" Then
    strSQLFilter = strSQLFilter & "TypeOfFile = '" & Me.ComboType & "' AND "
End If
 
If Len(strSQLFilter) > 6 Then
    strSQLFilter = Left(strSQLFilter, Len(strSQLFilter) - 5)
End If
 
ListBox.RowSource = "SELECT * Fields FROM TableData WHERE " & strSQLFilter & ";"
 
Me.Requery
 
End Sub

 

[CBrighton]

Can you upload a copy of the database?

mdb format rather than accdb though please!

 

[CuriousGeorge]

yeah i'll try and upload, see if it works.

hmm how do i save in .mdb? Think this is in accdb, but saved it as 2003 since you seem to use that version.

Here we go;


Thanks

----edit---


allright it says "Your submission could not be processed because a security token was missing."

file to big??

 

[CBrighton]

Possibly, try zipping it.

 

[CuriousGeorge]

its there now - in message above = )

cheers

 

[CuriousGeorge]

Maybe u are wondering why the type combo is showing D00,D01 etc, but its suppose to ident filetypes with those extensions so usage of LIKE rather than '='.

Cheers


----edit----

I noticed that ive forgot to change TypeOfFile to TableData.Banafield which is the actual name of that very field in the table.


Allthough the problem remains that nothing is happening.

I also noticed that the row source in the listbox is never set. Why is that?

Thanks

 

[CBrighton]

The field names in your code don't match the ones on the table.

You refer to two fields, ReturnDate(AbsTime) and TypeOfFile, neither of which are present in the table.

 

[CuriousGeorge]

yeah i know, u managed to reply before i had time to edit my comment above.

Ive changed that field.

The reason for using ReturnDate(abstime) is that abstime is given in seconds and im using the returndate function to change those to dates.

So Abstime is the actual field so that has to work by calling the function like that?

 

[CBrighton]

What might be a good idea is to create a query to base the listbox on.

That way you can add a new calc'd field for the absdate, and you may be able to have one with the type, though that may be easier to use like instead as you mentioned.

 

[CuriousGeorge]

Yeah,

ive created a query which contains filetype "BananaField" and SomeDate which calls the function and AbsTime from the tabledata.

allthough now i get the following error;


Object required

and following line is highlighted:

ListBox.RowSource = "SELECT " & Query1.BananaField & ", " & Query1.SomeDate & "  FROM Query1 WHERE " & strSQLFilter & ""

Do i have to add something like:

Dim qdf As DAO.QueryDef
'Set qdf = CurrentDb.QueryDefs("Query1")
'qdf.Sql = strSQLFilter
'DoCmd.OpenQuery ("Query1")

?

Thanks



---edit---

addded the lines above but got
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (Error 3129)"



where "qdf.Sql = strSQLFilter" is highlighted

 

[CBrighton]

You're overthinking it.

All you need to do is change

ListBox.RowSource = "SELECT * Fields FROM TableData WHERE " & strSQLFilter & ";"

To

ListBox.RowSource = "SELECT * Fields FROM [B]Query1 [/B]WHERE " & strSQLFilter & ";"

:edit:

btw, not sure why "fields" is in that SQL at all. You should be fine with:

ListBox.RowSource = "SELECT * FROM [B]Query1 [/B]WHERE " & strSQLFilter & ";"

 

[CuriousGeorge]

still doesn't work, nuttin happening when pressing the button


debug looks like this:

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