Pull data from Access with SQL---Urgent

[yn19832]

I have designed an UserForm in Excel, the aim is to copy selected data from Access (tblIndex) to Excel. 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. What I want to do is to build a recordset based on the records with selected Country, Type, Index, and Date from the start date and end date. I think the following codes shoudl work, but it does not.

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.Date > = #" & StaDate & "# AND tblIndex.Date <=#" & EndDate & "#"
  strSQL = strSELECT & strFROM & strWHERE
  Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
  Debug.Print strSQL
  Next i

I am really a starter in VBA and Access, and could anyone give me some advice?

 

[w1dge]

I have designed an UserForm in Excel, the aim is to copy selected data from Access (tblIndex) to Excel. 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. What I want to do is to build a recordset based on the records with selected Country, Type, Index, and Date from the start date and end date. I think the following codes shoudl work, but it does not.

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.Date > = #" & StaDate & "# AND tblIndex.Date <=#" & EndDate & "#"
  strSQL = strSELECT & strFROM & strWHERE
  Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
  Debug.Print strSQL
  Next i

I am really a starter in VBA and Access, and could anyone give me some advice?

What errors are you getting when you run the code? Also, I am not sure that you are referencing your user form correctly

From memory, try using something like

Forms!LiqForm!ListCT.List(i,0)

when you are trying to assign your variables

W1dge

 

[pbaldy]

I guess you're giving up on this one?

http://www.access-programmers.co.uk/forums/showthread.php?t=125506