I must be missing something here.

[NJudson]

I was hoping someone may be able to take a look at this at point out what I may possibly be doing wrong. I'm using Access 2000 and I have an event procedure on a form which runs an SQL statement and outputs the results to a listbox on my form. It looks like this:

Me.ListTotals.RowSource = "SELECT top 10 First(Cell) AS [MTX], Count(Cell) AS [Number Of Events], [Month-Day], Switch " & _
"FROM tblMTX160 " & _
"where [Month-Day]= '" & sDATE & "' AND Switch = '" & Me.cboSwitch & "' " & _
"GROUP BY [Month-Day], Switch, Cell " & _
"ORDER BY Count(Cell) DESC;"
Me.Refresh

This works just fine. I then try to creat an event procedure in the double-click for the listbox and wrote an SQL statement like such:

Me.txtFilteredField = MTX
Me.ListTotals.RowSource = "SELECT SWITCH, CELL, VCH, Count(VCH) AS [Number of Events] " & _
"FROM tblMTX160 " & _
"WHERE CELL = '" & MTX & "' " & _
"GROUP SWITCH, VCH, CELL " & _
"ORDER BY Count(VCH) DESC;"
Me.Refresh

When I run this 2nd SQL the listbox appears blank like it found no records that fit the query. I believe that I've narrowed the problem for this doubl-click SQL statement at the "WHERE" part. When I remove the "WHERE" statement then the SQL works and outputs the results to the listbox on the form. The funny thing is the "WHERE" statement is no different(from what I can tell) from any other SQL statements I've used and those work. Can someone tell me if this is the wrong way to write it or if there is another way to write it?

WHERE CELL = '" & MTX & "' " &_

Could this just be one of those unfortunate hickups in the Access dbase. I've been looking and working on this for some time and I think I really need another pair of eyes at this point. Thanks for any help.

[This message has been edited by NJudson (edited 04-25-2002).]

[This message has been edited by NJudson (edited 04-25-2002).]

[This message has been edited by NJudson (edited 04-25-2002).]

 

[SimonC]

I don't understand where the value MTX is coming from in the second SQL statement. Is it possible that the value isn't set (or maybe even defined) anywhere?

I'd suggest that before you run the second SQL statement you insert a debug.print statement to output the SQL code to the debug window. Then you can see exactly what's being generated.

If it's still not clear what's going on, you can even copy the printed SQL code and paste it in the query designer to have a closer look.

 

[NJudson]

I finally got it working. I just didn't have the correct syntax down for the WHERE criteria.

The thing I don't understand at this point is all my other SQL's that had a WHERE criteria followed this format:

WHERE JUNK = '" & Something & "'

but here it wouldn't work until I removed the apostrophes at the left and right of it like this:

WHERE JUNK = " & Something & "

Perhaps it has something to do with the particular aggregate function I was trying to run. Thanks for taking a look at it Simon. By the way the MTX is coming from the 1st field displayed on my listbox. If you doubleclick the listbox the Me.Listbox value is equal to the 1st field displayed on whatever row you click. Thanks again.

 

[David R]

If Something is a numeric field you don't need the single quotes. If it's a text field you need them. I'm assuming sDate is a real Date/Time field, so it didn't need them.

 

[NJudson]

Thanks again David. I didn't realize that subtle difference between the text and numeric properties. That'll be a useful tidbit of info for the future.