Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert Value List to SQL?

Posted on 2004-08-22
9
Medium Priority
?
243 Views
Last Modified: 2007-12-19
I am using the following code to generate a Value List as the Row Source for a listbox.  I am trying to find a way to be able to sort the listbox.  All the methods that I have found so far involve changing the ORDER BY portion of an SQL statement and since I don't have an SQL statement, I can't use those methods to sort the listbox.

So. . . ,
- is there an easy way to convert my Value List to an SQL statement,
or
- is there an easy way to sort a listbox when the row source is a value list and not a SQL?

Thanks.  here is the code:

    DocumentList = "Document Name;Date;Time;"
    DocumentName = Dir(OPTCPath & Right(CurrentYear, 2) & "\Correspondence\*.*")
    Do While Len(DocumentName) > 0
    If Left(DocumentName, 15) = CurrentRoll Then
        DocumentDate = Format(ShowFileInfo(OPTCPath & Right(CurrentYear, 2) & "\Correspondence\" & DocumentName), "dd-mmm-yy")
        DocumentTime = Format(ShowFileInfo(OPTCPath & Right(CurrentYear, 2) & "\Correspondence\" & DocumentName), "hh:mm:ss AMPM")
        DocumentName = Right(DocumentName, (Len(DocumentName) - 15))
        DocumentList = DocumentList & DocumentName & ";" & DocumentDate & ";" & DocumentTime & ";"
    End If
        DocumentName = Dir
    Loop
    Me.lstDocList.RowSource = DocumentList
0
Comment
Question by:DanielAttard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 34

Accepted Solution

by:
flavo earned 1200 total points
ID: 11867004
maybe use a temp table to do it?? {btw i had problems in adding too many values to a value list too}

Dim db as dao.database

db.execute "delete * from tempTable"
DocumentName = Dir(OPTCPath & Right(CurrentYear, 2) & "\Correspondence\*.*")
 Do While Len(DocumentName) > 0
 If Left(DocumentName, 15) = CurrentRoll Then
        DocumentDate = Format(ShowFileInfo(OPTCPath & Right(CurrentYear, 2) & "\Correspondence\" & DocumentName), "dd-mmm-yy")
        DocumentTime = Format(ShowFileInfo(OPTCPath & Right(CurrentYear, 2) & "\Correspondence\" & DocumentName), "hh:mm:ss AMPM")
        DocumentName = Right(DocumentName, (Len(DocumentName) - 15))
 End If

db.execute "INSRET into temptable ( docName, [date], [time] ) values ( '" & DocumentName & "', #" &   DocumentDate  & "#, #" & _
               DocumentTime & "# );"

DocumentName = Dir
  Loop

Me.lstDocLis.requery


you can then use all the ordeby's etc with the table and SQL
Idea???
0
 
LVL 11

Expert Comment

by:Quetzal
ID: 11867022
Or you can read the entries into an array and do a sort (like an exchange sort) for whatever sort order you want, then build the value list from the sorted array.
0
 
LVL 7

Assisted Solution

by:rockmuncher
rockmuncher earned 400 total points
ID: 11867034
>> is there an easy way to sort a listbox when the row source is a value list and not a SQL? <<

If you load all of your values into an array (seperate elements of course), then you can sort them within the array, then build your value list from there.

That's as easy as it gets, but I would recommend using a table in this case as the size of the value list can not be reliably predicted.  

FLAVO has provided a rather excellent solution for this.  His solution also provides most of the answer for part one of your Q.  Your listbox's rowsource would indeed become an SQL statement (or a reference to a query), such as:

  SELECT docName, Date, time FROM temptable ORDER BY docName
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 34

Expert Comment

by:flavo
ID: 11867053
Daniel,

Also as a side not, its not the best to use feild names called

Date
Time
Properties
Field (you cant anyway)
Now
Image (had probelms with ASP .Net and this one)
and any toher reserved words in access....

Try using something like dtFile or FileDate or something....

it may fall into the too hard basket now, but remember it for next time, it can cause pain....  generally you canget around it by using [date].

Dave
0
 

Author Comment

by:DanielAttard
ID: 11869788
Thanks for the idea Flavo.  I've tried to implement your suggestion, but I get the following error on the db.execute line:

'Object variable or With block variable not set.'

Any ideas?

I was going to try the other method of reading the variables into an array and then doing an exchange sort and building the list from the array, but I couldn't figure out how to do that with a two column listbox.  I found a good example at Q_20975053, but it was only for one column and I couldn't figure out how to change it to handle two columns.  Perhaps if someone could explain that?  Here is the code from Q_20975053:

   Dim FinishedSwapping As Boolean
    Dim UpperBound As Long
    Dim Temp As String
    Dim strValue() As String
    Dim i As Long
   
    'Add each value in the list to a temporary array (for sorting)
    For i = 0 To lstSerials.ListCount - 1
        ReDim Preserve strValue(i)
        strValue(i) = lstSerials.ItemData(i)
    Next i

    UpperBound = UBound(strValue())

    'Sort array into ascending order
    Do
        FinishedSwapping = True
        For i = 0 To UpperBound - 1
            'If they are bigger then perform a swap
            If strValue(i) > strValue(i + 1) Then
                Temp = strValue(i + 1)
                strValue(i + 1) = strValue(i)
                strValue(i) = Temp
                FinishedSwapping = False
            End If
        Next i
    Loop While Not (FinishedSwapping)

    'Clear list
    lstSerials.RowSource = ""
    lstSerials.Requery
   
    'Add sorted items into list
    For i = 0 To UpperBound
        lstSerials.AddItem strValue(i)
    Next i
0
 
LVL 4

Assisted Solution

by:naivad
naivad earned 400 total points
ID: 11870822
for multiple columns...use the semicolon to seperate the items in the list. Make sure you set the column count property and the column widths in the design view...


 For i = 0 To lstSerials.ListCount - 1
        ReDim Preserve strValue(i)
        strValue(i) = lstSerials.ItemData(i)
    Next i

I would recommend taking out the redim on every iteration of the loop and put it before the loop

    ReDim strValue(lstSerials.ListCount - 1)
    For i = 0 To lstSerials.ListCount - 1
        strValue(i) = lstSerials.ItemData(i)
    Next i
0
 

Author Comment

by:DanielAttard
ID: 11870919
Thank you naivad.  Where exactly would I use the semicolon to separate the items in the list?
0
 
LVL 4

Expert Comment

by:naivad
ID: 11871262
The RowSourceType needs to be "value list"


the RowSource can the be "1;red;#ff0000;2;green;#00ff00;3;blue;#0000ff"   etc...
for a 3 column list/combo box.
0
 
LVL 34

Expert Comment

by:flavo
ID: 11875092
>> 'Object variable or With block variable not set.'

Need to add a refrence to Microsoft DAO Object Library.

To do this, in the VBA window Select Tools - Refrences and tick it from the list.

Dave
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question