yn19832
03-27-2007, 06:58 AM
I have designed an UserForm in Excel and tried to select data from Access (tblIndex) . The UserForm contains a ListBox "ListCountry" and a ComboBox "ListType" to select country and type, and a ListBox named "ListCT" with two columns for selected country and type, and two TextBox (TextDateSta and TextDateEnd) for the user to Type in start date and end date.
A problem came out, for example, when I add "Sentiment Canada" "Sentiment Chile" to the listbox named "ListCT", it just copy the sentiment chile's data twice to excel and the date is a mess as following:
29251.00 Chile Sentiment 33
29280.00 Chile Sentiment 26
29311.00 Chile Sentiment 13
29341.00 Chile Sentiment 14
1/31/1980Chile Sentiment 33
29280.00 Chile Sentiment 26
29311.00 Chile Sentiment 13
4/30/1980Chile Sentiment 14
I guess there should be sth wrong with the loop, for which I want it to select all the records in "ListCT", but I could not figure it out, I would appreciate your advice. codes are:
Num = LiqForm.ListCT.ListCount
For i = 0 To Num - 1
TypeArray = LiqForm.ListCT.List(i, 0)
CountryArray = LiqForm.ListCT.List(i, 1)
StaDate = LiqForm.TextBoxSta.Text
EndDate = LiqForm.TextBoxEnd.Text
strSELECT = "SELECT tblIndex.* "
strFROM = "FROM tblIndex "
strWHERE = "WHERE tblIndex.Type='" & TypeArray & "' AND " & _
"tblIndex.Country='" & CountryArray & "' AND " & _
"tblIndex.Calendar>=#" & StaDate & "# AND tblIndex.Calendar<=#" & EndDate & "# "
strSQL = strSELECT & strFROM & strWHERE
Debug.Print strSQL
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
Next i
Thanks in advance!
A problem came out, for example, when I add "Sentiment Canada" "Sentiment Chile" to the listbox named "ListCT", it just copy the sentiment chile's data twice to excel and the date is a mess as following:
29251.00 Chile Sentiment 33
29280.00 Chile Sentiment 26
29311.00 Chile Sentiment 13
29341.00 Chile Sentiment 14
1/31/1980Chile Sentiment 33
29280.00 Chile Sentiment 26
29311.00 Chile Sentiment 13
4/30/1980Chile Sentiment 14
I guess there should be sth wrong with the loop, for which I want it to select all the records in "ListCT", but I could not figure it out, I would appreciate your advice. codes are:
Num = LiqForm.ListCT.ListCount
For i = 0 To Num - 1
TypeArray = LiqForm.ListCT.List(i, 0)
CountryArray = LiqForm.ListCT.List(i, 1)
StaDate = LiqForm.TextBoxSta.Text
EndDate = LiqForm.TextBoxEnd.Text
strSELECT = "SELECT tblIndex.* "
strFROM = "FROM tblIndex "
strWHERE = "WHERE tblIndex.Type='" & TypeArray & "' AND " & _
"tblIndex.Country='" & CountryArray & "' AND " & _
"tblIndex.Calendar>=#" & StaDate & "# AND tblIndex.Calendar<=#" & EndDate & "# "
strSQL = strSELECT & strFROM & strWHERE
Debug.Print strSQL
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
Next i
Thanks in advance!