Using a MACRO to run Queries and save as Excel

[rbraitz]

Good Morning, I have an access database where I wrote a simple macro that exports a simple query into excel format and then emails the excel spreadsheet to the recipient. For example, the database is sales records, and basically I am sending each salesperson a list of their daily sales. Problem I have is that some salesmen don't have sales for a particular day, so the macro is sending out empty excel spreadsheets. How can I place something in the access macro that says/looks to see if the query has results. I am not a coder and don't know VBA.....but if I need to change the macro or the query that would be ok too. The macro has 100 events in it, one for each salesperson. TIA

 

[Micron]

Most db developers don't use macros beyond maybe autoexec - me included. What macro actions are you using to send the data (one person example should be enough).
FWIW, this seems like a job for code.

 

[rbraitz]

please see example of macro below

 

[rbraitz]

here is a copy of the query being used

 

[Micron]

Suggest you put criteria in the query that will eliminate records where something is null or > 0 for example. I can't see enough there to be specific. Perhaps if there is an amount column for the sales period, put >0 to get only records with sales for the period. I'm guessing you have 100 of these macro actions because there are 100 emails going out. That must have been a lot of work. Better hope IT doesn't do something like change the email domain (or anything else like that) or you'll be fixing every one of them!

 

[rbraitz]

Suggest you put criteria in the query that will eliminate records where something is null or > 0 for example. I can't see enough there to be specific. Perhaps if there is an amount column for the sales period, put >0 to get only records with sales for the period. I'm guessing you have 100 of these macro actions because there are 100 emails going out. That must have been a lot of work. Better hope IT doesn't do something like change the email domain (or anything else like that) or you'll be fixing every one of them!

Thanks for the reply, the good news is that I am IT!!! so don't have to worry about domains!!! LOL......There are many months where numerous salesreps do not have any activity at all. In terms of the 100 little macros, it wasn't too bad, I just copied and pasted and changed the email address and query name, wasn't too bad. In terms of the query, not sure how I would put criteria in to eliminate records. there is only one table with sales information in it, and I'm only querying that table. In the example above, "Dealerid" is the sales person id.

 

[Micron]

I thought I wasn't able to make a more concrete suggestion because I could not see the rest of the query fields. For data over a sales period that is being emailed, surely there must be some field(s) there that contain no info? If you don't know it's probably because whatever that is, it's not part of the output.
Maybe you already know how but I suggest creating a new test query on that table and
- first double click on * at top of field list
- add your criteria fields and enter your criteria
- uncheck those criteria fields
- run query and look at fields and records to determine what field(s) could be used to spot records with no sales in the period. If you find nothing, you might solve this if you have a related table that you can join to the table shown.

P.S. and here I thought all IT people hated Access!

 

[rbraitz]

I thought I wasn't able to make a more concrete suggestion because I could not see the rest of the query fields. For data over a sales period that is being emailed, surely there must be some field(s) there that contain no info? If you don't know it's probably because whatever that is, it's not part of the output.
Maybe you already know how but I suggest creating a new test query on that table and
- first double click on * at top of field list
- add your criteria fields and enter your criteria
- uncheck those criteria fields
- run query and look at fields and records to determine what field(s) could be used to spot records with no sales in the period. If you find nothing, you might solve this if you have a related table that you can join to the table shown.

P.S. and here I thought all IT people hated Access!

I first learned how to do databases with PAradox 3.5 for DOS!!!! I even knew how to code using their scripting language, and I loved it....I had a hard time leaning Access, guess I was stuck in my ways. Let's just assume the main table has the following fields

Invoice Date
Item Description
Price
Salesrep

On any given day, I could have 1000 records or 2000 records, I might have 75 sales reps selling something and the others nothing.

4/10/2022 - Item1 - 5.00 - REP1
4/10/2022 - Item2 - $10 - REP1
4/10/2022 - Item3 - $15 - REP3

In this case I have macros set up to email REP1 - REP100....but the query would be empty for REP2....so I wouldn't want the macro to run for REP2? since they didn't sell anything on the 10th.

Does that help?

 

[Micron]

I think it does. As noted, I avoid macros as much as possible and your situation is one reason why. Thus I missed the real crux of the problem: your email macro probably will send email regardless of whether or not the query returns anything. The problem then, is that you would need to test for that first and call the email macro from within that testing macro if there is at least one record. I tried to implement query record count in a macro and Googled it, and if there is a way to get a record count from a query that is executed in a macro, I didn't find out how. The only macro way I can think of is to do an IF ... Then action using DCount with criteria.
So IF DCount("[fieldName]","[domainName]","Sales for Joe between 01/01/2022 AND 01/15/2022 criteria here") > 0 Then call email macro. That means create 100 IF macros, and if sensitive to dynamic data like dates, you can see the problem with that. Now you need to reference dates from somewhere. It just keeps getting more and more onerous.

Honestly, it's time to dump macros and switch to vba; at least for this problem.

 

[jackd]

Like micron I don't use macros either. I think the essence of your issue lies in

"I have macros set up to email REP1 - REP100." But you don't want to email all these Reps.
You only want to email those REPs who have had sales in the period.
To do so, identify the REPs with sales, then for each of those--send an email.

I believe this is the logic. I am not familar with macro coding.

I think that's what micron is advising as well.