SQL select record from Access

[yn19832]

have designed an UserForm in Excel, the aim is to copy selected data from Access (tblIndex) to Excel. The form of the Access database is as following:
Country Type Date Index
....... ...... ...... ......

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 copy the records of the selected Country, Type, and Index from the start date to end date. I suppose I should use SQL like:

Code:
strSELECT = "SELECT tblIndex.*"strFROM = "SELECT tblIndex"strWHERE, I do not know how to define the condition here. The clause below is what I mean, but it looks weird and it does not work.Num = LiqForm.ListCT.ListCountFor i = 0 To Num - 1strWHERE = "WHERE tblIndex.Type = LiqForm.ListCT.List(i,0) AND _tblIndex.Country = LiqForm.ListCT.List(i,1) AND _tblIndex.Date > LiqForm.TextBoxSta.Text AND tblIndex.Date < LiqForm.TextBoxEnd.Text"Next istrSQL = strSELECT & strFROM & strWHERE
Could anyone give me some clue?
many thanks

[JimmyTheHand]

Hy yn19832,

Can you post the code in a readable way?

Jimmy

[yn19832]

I am so sorry about the mess. I post it again.

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. 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


When I run it and enter a country, type and start date and end date into the UserForm, the following error message come out

Run-time Error "3075":

Syntax error (missing operator) in query expression 'tblIndex.Type = 'Price' AND tblIndex.Country = 'Canada' AND tblIndex.[Date] >=#1/21/1980# AND tblIndex.[Date] <=# 5/20/1980#'.

Could you please help me with it?

[JimmyTheHand]

Look at this part:
" tblIndex.Date > = #" & StaDate & " I think the [SPACE] between ">" and "=" is wrong. I see nothing else that could be a source of error.
Remove that space and try the code. If it still doesn't work, then it may be a date issue (again). Try the WHERE clause without dates, i.e. with only type and country. Does it work?
If yes, try the whole expression with hardcoded dates.

Jimmy

[yn19832]

Many many thanks , it did work. But another 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

Could you please help me with this?

[JimmyTheHand]

I think I know a cure for the messed up layout.
First of all, looks like you need to set the order of fields in the query:

strSELECT = "SELECT [Country], [Type], [Date], [Index] "
strFROM = "FROM tblIndex "
Secondly, you need to set the cellformat of those cells that recieve the date field to something date-like.

As for the duplicate chile sentiments, I have only one shot.
I fail to see how the data goes from a recordset into the worksheet. Surely there must be some other, unposted part of the code? I suspect you run the loop that executes the queries, but actually never extract any data from the recordsets that were created during the loop. After that you run another loop to extract the data from the recordset, but this returns data only from the last recordset, because the query loop is already finished, and the recordsets don't change anymore.

Also, I think this unposted, data-extracting code could be modified to handle date values better.

Jimmy

[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


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 foe 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

[JimmyTheHand]

I don't see the end of the the code. No End If, and no Next i. What else is missing, I wonder?

By the way, it's sort of difficult to pinpoint any error without being able to test the code. Why don't you upload the workbook, and a sample database, of course, so that we can play with it.

[yn19832]

Thank you very much for your reply. I have added the missing codes, sorry about that. I uploaded the workbook and a sample database.

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]

here is the database, called tblIndex

[JimmyTheHand]

Hi :hi:

First your code didn't work for me because of some date format problems.
Then I changed type of two variables from date to string:
Dim StaDate As String, EndDate As String With this modification it worked, but it didn't produce the strange behavior that you described. In other words, it worked perfectly.
So I suggest that you try to change the variable definitions first, then we'll see.

Jimmy

[yn19832]

Hi, thank you very much for your time.
Unfortunately, after I changed the type of the variabels, it still works strangely as I described before. Is it because some data format problem as you said, or sth wrong with my excel?

[yn19832]

I have noticed a problem. When I entered one country, from 01/01/1980 to 05/01/1980, there should be four records. 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.

[JimmyTheHand]

What version is your MS Office? At my workplace I have access to MS Office 2000, 2002 (XP) and 2003. I'll test it on each one.

[yn19832]

Thank you very much for your help. My version is 2003.
A colleague just found that there are duplicate records in the Access Database, and that explains why it copies some records twice. I am so sorry that I made such a mistake, and you have to testing the codes.

The problem now is that there are records with empty Index value in Access, and when I select these records, an error message comes out, saying"Type Mismatch". For those records, I just want to copy them to Excel with empty Index value.

When I debug, it highlights "TheRange.Value = Application.WorksheetFunction.Transpose(varArray)"

[yn19832]

I creat a new sample database containing empty Index value.

Thanks in advance.