SQL- select record from Access

[yn19832]

This part seems correct. But another problem came out, for example, when I add "Sentiment Canada" "Sentiment Chile" to the listbox, 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 think it is about what you said, for the loop it just select the last record, but I could not figure it out. Could you give me some advice? The codes are as following:

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

 

[pbaldy]

Think about it. You have a loop, during which you go through the selected items. That loop will repeat until it finishes with the selected items, so the code below to actually copy that data to Excel will not run until the loop is complete. Because you just keep setting the same recordset, that recordset will contain the last selected value at that point. You need to move your "copy to Excel" code inside the loop, right after setting the recordset. I'd guess you're also going to have to fiddle with the code to keep track of the row in Excel, so that subsequent selections don't overwrite the first one in Excel.

The date appears to be a simple formatting problem. Just format that column in Excel to a date.

 

[yn19832]

Thank you very much for your reply.

First, could you please tell me how to set the cellformat of those cells that recieve the date field to something date-like.

Secondly, based on your advice about the loop, I revised my codes for copying data to excel as following:

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) 
     
    If Not rec.EOF Then 
        rec.MoveLast 
        rec.MoveFirst 
        intRecord = rec.RecordCount 
        varArray = rec.GetRows(intRecord) 
         
        intFieldCount = UBound(varArray, 1) 
        intRowCount = UBound(varArray, 2) 
         
         'Make Sure Sheet1 is Activate
         
        Sheets("Sheet1").Activate 
         
         'Determine the next empty row
         
        NextRow = _ 
        Application.WorksheetFunction.CountA(Range("A:A")) + 1 
         
         'Set worksheet range
         
        If i = 0 Then 
            Set TheRange = Range(Cells(1, 1), Cells(intRowCount + 1, intFieldCount + 1)) 
        Else 
            Set TheRange = Range(Cells(NextRow, 1), Cells(NextRow + intRowCount, intFieldCount + 1)) 
        End If 
         
         'Copy the record to Excel
         
        TheRange.Value = Application.WorksheetFunction.Transpose(varArray)

          End If
  rec.Close
  
  Next i

' Clear the controls for the next entry

   LiqForm.TextBoxSta.Text = ""
   LiqForm.TextBoxEnd.Text = ""
   
   Unload LiqForm
   
End Sub

It works in a strange way, sometimes when you enter two countries, it turned out to be one, sometimes it turmed out to be two. When you enter three countries, it turns out to be two or one, and for each of them, the records are doubled. For example, when I enter " Australia Sentiment" "Austria Sentiment" from 01/01/1980 to 05/01/1980, it is like following:

29251.00 Australia Sentiment 1/5/1900
2/29/1980 Australia Sentiment 8.7776
29311.00 Australia Sentiment 26.1377
29341.00 Australia Sentiment 22.6108
29251.00 Australia Sentiment 5.6739
29280.00 Australia Sentiment 8.7776
29311.00 Australia Sentiment 26.1377
29341.00 Australia Sentiment 22.6108
29251.00 Austria Sentiment 53.1886
29280.00 Austria Sentiment 52.9153
29311.00 Austria Sentiment 53.0859
29341.00 Austria Sentiment 55.3118
29251.00 Austria Sentiment 53.1886
2/29/1980 Austria Sentiment 52.9153
29311.00 Austria Sentiment 53.0859
29341.00 Austria Sentiment 55.3118

 

[pbaldy]

For the format, a trick I often use is to go directly into Excel and use the Record Macro feature. Start one recording, perform the desired action, and then stop the recording and look at the code created. I can usually use the same code from Access, sometimes with some tweaking.

As for the data, you use a couple of techniques I wouldn't. That's not to say they're wrong, just different that how I might do things. That means I'm not clear on what they might do wrong. The best thing I can suggest is to set a breakpoint and step through the code line by line, examining the variables and such as you go, making sure they are as you expect.

 

[yn19832]

Thanks a lot for your help.
I have been testing the codes the whole day, it turned out they are not as I expected. In fact, they are anything but I expected. I just could not figure out the problem as I go line by line. I really appreciate if you can give me a clue how you would do it, or help me with my codes.

I have attached my workbook and a sample database, if it helps.

 

[yn19832]

For the workbook, I leave the following two lines blank, they are supposed to be the paths for the database.

Public Const cstrDB = ""
Public Const cstrPath = ""

 

[yn19832]

I have noticed a problem. When I entered one country for one type, from 01/01/1980 to 05/01/1980, there should be four records, as the data are monthly data. But when I use

Debug.Print intRecord

It comes out 8 in the immediate window,that can explain why it copy the data for each country twice, but I do not know why.

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)
  
  If Not rec.EOF Then
  rec.MoveLast
  rec.MoveFirst
  intRecord = rec.RecordCount
  Debug.Print intRecord

...

From the immediate window

SELECT tblIndex.* FROM tblIndex WHERE tblIndex.Type='Sentiment' AND tblIndex.Country='Australia' AND tblIndex.Calendar>=#01/01/1980# AND tblIndex.Calendar<=#05/01/1980#
8