VBA/SQL Select problem

[johncassell]

Hi all, could someone help with this please. I am running a report and simply trying to count the jobs and group by depot. This works fine but it pulls in a row for Depot5 (which we don't use anymore). when I put the WHERE clause in to exlude this depot, I end up with an empty row at the top with a zero in the count column. Not sure how to avoid this empty row?

Please Login or Register  to view this content.

Thanks
John

[Arkadi]

What is the source of the data? Access, Excel MySQL?

I tried using Access to test your problem, but within access the case function won't work, I tried this sql there and it returned no blank rows:

Please Login or Register  to view this content.

[Arkadi]

I believe you should also be able to just to a count of a.JobType, and add a.JobType = 'Type1' to your WHERE clause.

[CK76]

You should check your table.

Since your syntax looks to be for SQL server. Ran it on test table and confirmed it returned no empty row.

You can also try using SUM instead of count.

Please Login or Register  to view this content.

Edit: Removed unnecessary "," after 'Type1'

[kasan]

Not sure you need comma here:

Please Login or Register  to view this content.

[CK76]

Woops a typo. Edited code. Thanks for pointing it out

[buran]

Since your syntax looks to be for SQL server.

because I was participating in another thread - the database is visual foxpro

[johncassell]

Hi, thanks very much for the replies. It was a Sybase database that I was querying through ODBC/Excel. I actually have 6 depots and there are an additional 2 that I wanted to ignore. For some reason "<> these 2 depots" produced the blank header row but specifically choosing the correct depots (WHERE depot = depot1 OR depot2 OR depot3 OR depot4).... worked perfectly. Bit bizarre but got there in the end. :-)

Thanks again for the replies.

[johncassell]

Hi, thanks very much for the replies. It was a Sybase database that I was querying through ODBC/Excel. I actually have 6 depots and there are an additional 2 that I wanted to ignore. For some reason "<> these 2 depots" produced the blank header row but specifically choosing the correct depots (WHERE depot = depot1 OR depot2 OR depot3 OR depot4).... worked perfectly. Bit bizarre but got there in the end. :-)

Thanks again for the replies.