can't get SELECT to work

  • Thread starter Thread starter Gator
  • Start date Start date
G

Gator

Nothing is showing in List 13 on the click event...any ideas?

Private Sub List11_Click()
List13.RowSource = "SELECT DateDep, Account, AccountName,
Format(SUM(Amount),'currency')" & _
"FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
"GROUP BY Month(DateDep)" & _
"ORDER BY DateDep DESC;"
End Sub
 
Gator said:
Nothing is showing in List 13 on the click event...any ideas?

Private Sub List11_Click()
List13.RowSource = "SELECT DateDep, Account, AccountName,
Format(SUM(Amount),'currency')" & _
"FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
"GROUP BY Month(DateDep)" & _
"ORDER BY DateDep DESC;"
End Sub
Click to expand...


I don't see anything obvious, but here are some ideas:

Is List11 a multiselect list box, by any chance? If it is, it will have a
value of Null and you won't get any records returned.

Is Fund a text field? The code assumes that it is. If it's a number field,
the quotes around the value from List11 would be wrong.

The only problem I see in the actual SQL is that you have no space before
the FROM keyword. However, I think that would still be parse properly
because of the preceding parenthesis.
 
Gator said:
multiselect - No
Fund in List11 is 'Text' field in Funds Table.

still the same....
Click to expand...

You tried correcting the minor SQL error I pointed out, and that didn't
help?

The next thing to do is add a line

Debug.Print List13.RowSource

after you set it the rowsource, then examine the rowsource query as it is
printed in the Immediate window. Does it look correct? If it does, try
copying it and pasting it into the SQL view of a new query, and see if that
query returns any rows.
 
The (" & _) is actually in the code to break lines...which I don't think is
having any affect.

I tried adding a space before the FROM like this....
" FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _ .........instead
of.......
"FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _

The form is in a Tab control.

The Debug.Print is in the code like below......and I'm not sure how it is
supposed to work..............
Private Sub List11_Click()
List13.RowSource = "SELECT DateDep, Account, AccountName,
Format(SUM(Amount),'currency')" & _
" FROM Deposits" & _
" WHERE ((Fund)='" & List11 & "'))" & _
" GROUP BY Month(DateDep)" & _
" ORDER BY DateDep DESC;"
List13.Requery
Debug.Print List13.RowSource
End Sub

thanks
 
No, putting a space before FROM did not help....

Dirk Goldgar said:
You tried correcting the minor SQL error I pointed out, and that didn't
help?

The next thing to do is add a line

Debug.Print List13.RowSource

after you set it the rowsource, then examine the rowsource query as it is
printed in the Immediate window. Does it look correct? If it does, try
copying it and pasting it into the SQL view of a new query, and see if that
query returns any rows.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Gator said:
The (" & _) is actually in the code to break lines...which I don't think
is
having any affect.
Click to expand...

I didn't say anything about the line continuations. I know what those are
for.
I tried adding a space before the FROM like this....
" FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _ .........instead
of.......
"FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
Click to expand...

That should take care of that minor issue, which I didn't think was really
the cause of the problem anyway.
The form is in a Tab control.
Click to expand...

You mean this is a subform on a tab control? I don't see the relevance, so
long as the code and the two list boxes are all on the same form object.
The Debug.Print is in the code like below......and I'm not sure how it is
supposed to work..............
Private Sub List11_Click()
List13.RowSource = "SELECT DateDep, Account, AccountName,
Format(SUM(Amount),'currency')" & _
" FROM Deposits" & _
" WHERE ((Fund)='" & List11 & "'))" & _
" GROUP BY Month(DateDep)" & _
" ORDER BY DateDep DESC;"
List13.Requery
Debug.Print List13.RowSource
End Sub
Click to expand...

You don't need the line "List13.Requery", so get rid of it. Changing a list
box's RowSource always requeries it.

The "Debug.Print" line is just there for debugging, to cause the RowSource
to be printed to the Immediate Window. With that line in place, click on
List11, then press Ctrl+G to display the Immediate Window. You'll see the
value you assigned to List13.RowSource there. If inspection doesn't reveal
any errors, copy the SQL statement from there to the clipboard, then create
a new query, put that query into SQL View, and paste the SQL statement into
it. The switch that query to datasheet view and see if any rows are
returned.
 
Bob Quintal said:
you have a totals query with 4 columns.

A totals query must have an aggregate function or a Group By entry
for each column. I count 1 aggregate (the Sum) and 1 group by which
is not a column , so Access throws up an error message.

I bet you have an on error resume next statement above what you sho
us. Comment that out, and report the error message.

Fix the SELECT Statement "SELECT month(DateDep) ...
add ", Account, AccountName" to the Group By
Click to expand...


Good spot, Bob. I wish I'd noticed that.
 
Hi Gator,

If I follow the conversion with the others you still do not have
success. So, a really basic question. It the code even being executed?
Maybe put a MsgBox "Clicked on List13" at the beginning of the subroutine.
See if you get the message when you click.

Clifford Bass
 
Back
Top