List box contents to SQL string 2

[Egglar]

I hate running to the form every time i get stuck, but ive been thinking how to do this and im completly mythed!

I have a list box, lstMonthYear. It takes data from tblPurchaseInvoice, groups Date, and formats the date and fills the list box, eg,

List box shows:

January 2003
March 2003
June 2003
November 2003
etc etc...

Im trying to do some VB code, that takes the selected contents of the list box (multiple selection is on), and creates an sql string, the sql string will do various things, but once i have the basics i should be able to code it on my own.

I was looking at a loop that goes down the list box, determins if the date is selected, and if so adds it to the sql string. WIth a case select which detrmines if the date says January then the date will be #01/2003# etc, of course the year will be dynamic, taken from the list box also.

Is this the best way of doing this? if so how do i code it so it determines whats selected in the list box and puts the info into an sql string.

Or if there are other eaiser ways please let me know.

Thanks in advance, Elliot.

 

[jrbarnett]

I would have a hidden column in the list box (set its width to 0) for the month and the year. You can then combine the two together in your code.
As for accessing the selected objects, if MultiSelect is enabled, you can loop through the Selected collection of the listbox with For Each Next, knowing it is highlighted. If this is not switched on, by accessing the control itself you read the bound column of the currently selected record.

John

 

[tEkHEd]

Hi, I am not 100% about this but hopefully it will point you in the right direction, the code for the filter will work on a single select listbox, but I am not too sure about the multiselect. I also didn't know how you wanted to display the results so have given 2 options for you.

The first opens a subsequent form with a filter, the 2nd will populate a 2nd list box with the returned data.

Private Sub Test()

Dim ctlMonth As Control, ctlYear As Control, stSQL As String, stWhereMonth As String, stWhereYear
Dim varMonth As Variant, varYear As Variant, strFilter As String

Set stSQL = "SELECT * " _
    & "FROM tblPurchaseInvoice " _
    & "GROUP BY Date " _
    & "HAVING MONTH = '" & ctlMonth & "' AND YEAR = '" & ctlYear & "';"
    
If Me.lstMonthYear.ItemsSelected.Count < 1 Then 'nothing is selected so exit
    Exit Sub
Else
    For Each varMonth In Me.lstMonthYear.ItemsSelected
        ctlMonth = varMonth
    Next varMonth
    
    For Each varYear In Me.lstMonthYear.ItemsSelected
        ctlYear = varYear
    Next varYear
End If

'Set up filter
    strWhereMonth = &quot;[Month] IN (&quot; & ctlMonth & &quot;)&quot;
    strWhereYear = &quot;[Year] IN (&quot; & ctlYear & &quot;)&quot;
    
    strFilter = strWhereMonth & strWhereYear

 'Open New form filtered on the selected data
    DoCmd.OpenForm FormName:=&quot;frmMyForm&quot;, wherecondition:=strFilter
'or repopulate another listbox
Me.lstResultList.RowSource = stSQL
Me.lstResultList.Requery


End Sub
[code]

 

[Egglar]

Thanks for your replies. But im affriad i dont understand what neither of you have said. I have had no experience with controls or variants.

Could you elaborate a bit more?

Sorry to be a pain, Elliot.

 

[tEkHEd]

can you post your code? then someone will be able to help you better

 

[Egglar]

I have no code at the moment for this particular function. The only code is the row source sql for the list box which is:

SELECT Format([Date],"mmmm yyyy&quot AS FormattedDate FROM tblPurchaseInvoice WHERE (((tblPurchaseInvoice.Supplier)=Forms!frmPayment!txtSupplierID)) GROUP BY Format([Date],"mmmm yyyy"

Im basicaly looking for code that can retrieve what items have been selected in the list box, and turn the dates into the correct format mm/yyyy and put them in an sql string. How the sql string is structured is not important at the moment as it will be an UPDATE and a SELECT string once i decide how i want to work it.

 

[jrbarnett]

Elliot,

I now see what you're trying to get at.
Go to the listbox properties and on the Data tab, change the column count to 2. Set the column widths property to whatevever the width of the first one is followed by ;0cm to hide the second column. Switch to the Data tab and change the RowSource to this statement:

SELECT Format([Date],"mmmm yyyy&quot AS FormattedDate, Format([Date],"mm yyyy&quot As UnformattedDate
FROM tblPurchaseInvoice WHERE (((tblPurchaseInvoice.Supplier)=Forms!frmPayment!txtSupplierID))
GROUP BY Format([Date],"mmmm yyyy"

This will add a second (invisible) column to the listbox which will return dates such as 01 2003 for January this year.

Regarding obtaining the data. If the listbox is to have only one item selected at any one point, you need do no changes other than use code along the lines of:

YourDate = lstListboxname.Columns(1) ' to get the short form of date
or YourDate = lstListboxname ' to get the long date default column. You could also use YourDate = lstListboxname.Columns(0) but this is a bit of a waste of typing.

To allow selection of multiple items, open the listbox properties and on the Other tab, change Multi Select to Simple, as this is easier to code for (it lets the user toggle each one on and off with the spacebar or clicking their mouse).

To read the selected items, use the following code:

Dim i as Integer

For i = 0 To lstListbox.ListCount-1 
	If lstListbox.IsSelected(i) = True Then
		Msgbox "Item " & lstListbox.Columns(i) & " is selected"
	End If
Next

or use the for ... each ... next loops from tEkHed's posting above.

John

 

[Egglar]

Thats perfect, just what i was looking for, however one problem, im getting an error to do with the row source problem, ive tracked it down to the GROUP BY, but not being too good with sql, i am unable to solve it, the error im getting is
"You tried to execute a query that does not include the specified expression 'Format([Date],"mm yyyy" as part of an aggreate function."

As i said above it is refering tot he group buy section of the sql, because when i get rid of this is works, but the list box displays more than the month once (obvisouley because its not grouped).

Any ideas on this?

Thanks for your help so far!

 

[jrbarnett]

Revised SQL code:

SELECT Format([Date],"mmmm yyyy") AS FormattedDate, Format([Date],"mm yyyy") As UnformattedDate 
FROM tblPurchaseInvoice WHERE (((tblPurchaseInvoice.Supplier)=Forms!frmPayment!txtSupplierID)) 
GROUP BY Format([Date],"mmmm yyyy"), Format([Date],"mm yyyy");

Sorry about the mistake.

John

 

[Egglar]

Excelent! Thanks for that! I modified it slightly so i could put the results into an SQL string, i added another column to the list box and had seprate columns for year and month. I know have a working piece of code that generates an sql string! One problem again, my knowlage of SQL, or rather lack of it is making it qutie difficult. The vb code generates a string that looks like this:

SELECT tblPurchaseInvoice.Date, tblPurchaseInvoice.Supplier, tblPurchaseInvoice.Amount, tblPurchaseInvoice.Date, tblPurchaseInvoice.Reference, tblPurchaseInvoice.Type FROM tblPurchaseInvoice
WHERE (((tblPurchaseInvoice.Supplier)=1) AND ((tblPurchaseInvoice.PaidDate) Is Null)) AND (((Month([date]))=04) AND ((Year([Date]))=2003));

Thats for one date obvisouley, problem is, when i paste this into SQL view on the query to test it, when i run it, it creates another field called "expr1000" which contains the dates of the invoices, just like the Date field. Ive seen these before when using expressions, but why is it doing it? Its making it very difficult to add a sum function (i now also want to sum tblPurchaseInvoice.Amount - how do i do this?)


Again thanks for your help, i am learning quite a lot from this!

 

[tEkHEd]

try this:

SELECT tblPurchaseInvoice.Date, tblPurchaseInvoice.Supplier, SUM(tblPurchaseInvoice.Amount) AS TotalAmount, tblPurchaseInvoice.Date, tblPurchaseInvoice.PaidDate AS PaidDate, tblPurchaseInvoice.Reference, tblPurchaseInvoice.Type
FROM tblPurchaseInvoice
GROUP BY tblPurchaseInvoice.date, tblPurchaseInvoice.Supplier, tblPurchaseInvoice.PaidDate, tblPurchaseInvoice.Reference, tblPurchaseInvoice.Type
HAVING (((tblPurchaseInvoice.Supplier)=1) AND ((tblPurchaseInvoice.PaidDate) Is Null)) AND (((Month([date]))=04) AND ((Year([Date]))=2003));

SQL was creating the column because you included the

((tblPurchaseInvoice.PaidDate) Is Null))

after you WHERE clause (I think)..

As for the summing, You can GROUP BY and change the WHERE for HAVING as shown and then you can perform the SUM

good luck

 

[Egglar]

Woohoo! It all works! I now have an Update, Total calculator and a reocrd source all from that!

Thanks for ur help guys!