Output data that is not null

[KrissyB]

I have 122 tables that need to be emailed to different sources if they contain information.

I was wondering if anyone knows how I can do this in a Query, differentiate between a table with records and one with null records. Any help would be greatly appreciated.

Christine

 

[WildHare]

If "Select Count({KeyField});" > 0, There's Records in Them Thar Tables...

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[KrissyB]

Thanks for your help. The problem is I am a dunce when it comes to VBA and cannot expect the user to do a select count for each table. Currently the macro pulls vendors information from a master table of products and splits these into 122 tables. One for each vendor. The macro then sends these files in one fell swoop to the vendors via email. The problem is some are empty because there were no items in the master file for that vendor. Within the SQL window in Access can I do some sort of conditional statement? I am also working on some VBA that only creates tables for the vendors that included data, but it is slow going.

Thanks.

 

[WildHare]

You should be able to 'proceduralize' this pretty easily. You can use the TABLEDEF collection to go through each table in the DB and simply issue a "MOVEFIRST" against it - if the command does NOT raise an error number, then there is at least one record in the table and you can send it off.

If no one else here pops in with a particular coded solution, I'll see if I can come up with something tomorrow if I get a slow moment at work.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[KrissyB]

Thanks Jim, i was unfamiliar with either of these commands, but will take a look around!