SQL- select record from Access

[yn19832]

I 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:

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.ListCount
For i = 0 To Num - 1
strWHERE = "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 i

strSQL = strSELECT & strFROM & strWHERE

Could anyone give me some clue?
many thanks

 

[pbaldy]

See anything wrong with these?

strSELECT = "SELECT tblIndex.*"
strFROM = "SELECT tblIndex"

together they'll be:

"SELECT tblIndex.*SELECT tblIndex"

In addition to the wrong keyword, watch your spaces. Secondly, in the WHERE clause, nothing outside of VBA will know what "i" is. You have to concatenate literal strings with the form references, like:

"WHERE FieldName = " & Me.Whatever & " AND Field2 = " & Me.Another & " ...

so that the final string is:

"WHERE FieldName = 123 AND Field2 = 456 ...

 

[yn19832]

many thanks

The first two clause shoud be
strSELECT = " SELECT tblIndex.*"
strFROM = " FROM tblIndex "

The most difficult part is the "WHERE" clause, how can I refer to the selected value, I mean tblIndex.Type = ? For the ? , I want to define it as the selected value LiqForm.ListCT.List(i,0)

 

[pbaldy]

Already gave you that:

"WHERE FieldName = " & Me.Whatever & " AND Field2 = " & Me.Another & " ...

replace Me.Whatever with the reference to your listbox. Keep in mind that text values need to be surrounded by single quotes and dates by "#".

 

[yn19832]

Already gave you that:



replace Me.Whatever with the reference to your listbox. Keep in mind that text values need to be surrounded by single quotes and dates by "#".

"with the reference to your listbox", that is exactly what I want to ask, how to refer to the value, for example, for the field country, I want to refer to the value in the first column of the list box "ListCT", and for the date, I want to refer to all the dates between the value of the "textboxsta" and the value of the "textboxend"

Thank you very much

 

[pbaldy]

Like:

Me.ListCT.List(i,0)
Me.TextBoxSta
Me.TextBoxEnd

Basically you already have those in your sample, you just need to concatenate them in, as I described in post 2.

 

[yn19832]

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
Next i

strSELECT = " SELECT tblIndex.*"
strFROM = " FROM tblIndex "
strWHERE = "WHERE tblIndex.Type = '" & TypeArray & "'AND _
tblIndex.Country = '" & CountryArray & "' AND _
tblIndex.Date > = # " & StaDate& " # AND tblIndex.Date < = # " & StaDate& " #"
strSQL = strSELECT & strFROM & strWHERE

Can you help me look at these codes?

 

[pbaldy]

Does it work? I'm guessing not, though you're getting closer. For starters, you set the values inside the loop, then do something with them after the loop. Given that, it will only act on the last values set. I see a couple of other things, but it might be helpful for you to run into them and learn how to debug problems. In fact one tool you should know about is to add

Debug.Print strSQL

after that variable is set, which will print the "final" SQL string out to the Immediate window, where you can examine it or even copy it to a blank query for testing.

 

[yn19832]

Thank you very much for your help, and I think the following codes shoud work, but it does not. I would appreciate your advice.

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)
  Next i

 

[pbaldy]

I wouldn't expect it to, but it would be helpful if you said why it didn't work. I see 2 lines that will probably throw an error and one logic issue. Also, what are you trying to do here? I don't see you doing anything with the result.

 

[yn19832]

Thank you very much for your reply.
It says error "The Microsoft Jet database engine does not recognize <name> as a valid field name or expression. (Error 3070)". Besides, I was wondering whether these codes just generate a recordset containing one record, instead of all the selected records as I expected. I was trying to do that, but do not have a clue at all.

What I want to do next is to copy the selected Country, Type, Date, with the corresponding Index to the Excel WorkSheet, That is the aim about this UserForm. The codes I used next are as following:

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

'   Set worksheet range
    Set TheRange = ActiveCell.Range(Cells(1, 1), Cells(intRowCount + 1, intFieldCount + 1))
    
'   Copy the record to Excel
    TheRange.Value = Application.WorksheetFunction.Transpose(varArray)
    
    End If
    rec.Close

Many many thanks.

 

[pbaldy]

What line does it highlight?

 

[yn19832]

I entered country, type, date and run it again, and the error turns out to be" syntax error (missing operator) in query expression "WHERE tblIndex.Type = 'sentiment' AND tblIndex.Country = ' US ' AND tblIndex.Date > = # 01/31/1980 # AND tblIndex.Date <=#21/31/1980#" "(Error 3075)" , and it highlights "Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)"

 

[pbaldy]

Did you try the Debug.Print line as I recommended earlier? Copy/paste the results of that here. The date is messed up, but that may be a typo.

 

[yn19832]

I entered Country, Type, and Date and run it again, it turns out to be Syntax Error (missing operator) in query expression " tblIndex.Type = 'Policy' AND tblIndex.Country ='Canada 'AND tblIndex.Date > = # 3/01/1980 # AND tblIndex.Date <=# 21/01/1980#'

 

[yn19832]

I do not think the reason is because of the date, it is just the type error The date I entered into the UserForm is correct. It should be sth else, but I just could not figure it out.

 

[pbaldy]

The debug.print would have helped us, but you don't appear to want to use it. You may think the date is right; I'm not sure Access likes it:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

 

[yn19832]

I tried debug.print strSQL, but the immediate window does not show anything.

 

[pbaldy]

Then it would be the first time it ever failed. In any case, I suspect the link is relevant to your problem.

 

[yn19832]

Thank you very much for your reply and finally the immediate window shows "strSQL" as following:

SELECT tblIndex.* FROM tblIndex WHERE tblIndex.Type = 'Sentiment' AND tblIndex.Country ='Canada 'AND tblIndex.Date > = #1/2/1980# AND tblIndex.Date <=#3/21/1980#

My Short Date format has been set to be US, So the date format in Access is like mm/dd/yyyy, I suppose it should be fine. But the error message come out again

Run-time Error "3075":

Syntax error (missing operator) in query expression 'tblIndex.Type = 'Sentiment' AND tblIndex.Country = 'Canada' AND tblIndex.Date >=#1/2/1980# AND tblIndex.Date <=# 3/21/1980#'.