If statement to export query if record count is not null

Greetings, thank you very much to all who are reading this.  Basically I have 
an automated email that goes out every Monday.  Very simply it uses 
sendobject to email a query.  I would like to add some logic that basically 
looks at the record count and if it isn't null...sends teh email as is...if 
the record count is empty I would like to have it send an email that 
basically says "The record set this week is empty."

Below is the module converted from the original macro.  I was having trouble 
modifying the module so any help would be greatly appreciated.  Thanks in 
advance for any ideas.

Option Compare Database

'------------------------------------------------------------
' macro2
'
'------------------------------------------------------------
Function macro2()
On Error GoTo macro2_Err

    DoCmd.SendObject acQuery, "Override - Invoice comments", 
"MicrosoftExcelBiff8(*.xls)", "h0mely@h0mely.com", "", "", Eval("(Date()-7) & 
"" to "" & (Date()-1) & "" - Overrides granted in TEMS"""), "", False, ""
    DoCmd.Quit acExit


macro2_Exit:
    Exit Function

macro2_Err:
    MsgBox Error$
    Resume macro2_Exit

End Function

0
Utf
1/4/2010 8:20:02 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

1 Replies
833 Views

Similar Articles

[PageSpeed] 49

DCount() will return 0 if there are no records selected.

The following IF statement will count the number of records in a table named 
tblOrders that have an OrderDate of Today. If you're dealing with something 
complex invovling multiple tables where a query is needed, the DCount() can 
operate on a query as in the later example...

If DCount("Id", "tblOrders", "OrderDate = Date()") = 0

If DCount("Id", "qryOrdersByDateByRegion", "OrderDate = Date() and 
RegionId='Florida'") = 0


"H0MELY" <H0MELY@discussions.microsoft.com> wrote in message 
news:C37E9026-C987-4325-BB53-FC4183D17ED7@microsoft.com...
> Greetings, thank you very much to all who are reading this.  Basically I 
> have
> an automated email that goes out every Monday.  Very simply it uses
> sendobject to email a query.  I would like to add some logic that 
> basically
> looks at the record count and if it isn't null...sends teh email as 
> is...if
> the record count is empty I would like to have it send an email that
> basically says "The record set this week is empty."
>
> Below is the module converted from the original macro.  I was having 
> trouble
> modifying the module so any help would be greatly appreciated.  Thanks in
> advance for any ideas.
>
> Option Compare Database
>
> '------------------------------------------------------------
> ' macro2
> '
> '------------------------------------------------------------
> Function macro2()
> On Error GoTo macro2_Err
>
>    DoCmd.SendObject acQuery, "Override - Invoice comments",
> "MicrosoftExcelBiff8(*.xls)", "h0mely@h0mely.com", "", "", 
> Eval("(Date()-7) &
> "" to "" & (Date()-1) & "" - Overrides granted in TEMS"""), "", False, ""
>    DoCmd.Quit acExit
>
>
> macro2_Exit:
>    Exit Function
>
> macro2_Err:
>    MsgBox Error$
>    Resume macro2_Exit
>
> End Function
> 


0
David
1/4/2010 8:46:24 PM
Reply:

Similar Artilces:

export to sharepoint over https
Dear Community, I would like to export some Access query results to a Sharepoint document library. The site requires authentication, uses https, so a simple export to the webfolder does not work. How can I copy the results with VBA? I understood that I need to use webdav. I tested with TotalCommander and its webdav plugin can connect to the document library on the site. I looked at many code samples on the internet, but none of them worked, maybe because all of them used simple http connection. Please advise, Thanks, vm ...

ADS
X-Posting to microsoft.public.visio, microsoft.public.visio.networkdesign.documentation Hello there, I don�t know how to import data into the controller environment. I did the the following -> Visio is installed on the controller 1. I opened Visio 2000 2. I imported the life directory from the controller. 3. I added a new OU in the domain structure. 4. In the menu, I went to directory services and chose "export to ldif" and then "export changes" Then I gave a ?.ldf name for the file. ("?" = standing for the filename) 5. Having done this I tried to...

New to excel
Hello, I am new to excel currently working with Moss sharepoint I have a "ticket" that needs to pull data from another excel workbook when they need to update a budget and they send it in for approval using moss. Is that possible? I am new to this and looking for ways to make this easy for my users when they use the "ticket" template requesting a new budget change and the data they are getting is from a set budget form for a project. Can someone lead me to the right direction on how to handle this. Thank you for your time. I don't have the terminology to be ...

Corstabquery or normal query
Hello, Maybe I have a langguage problem in expressing my difficulties, but here is what I wanted Normal report based on the normal query: Year 2005 No. Position Name 1, Elder Mr. A 2. clerck Mr. B 3. Deacon Mr. C Year 2006 1, Elder Mr. D 2. clerck Mr. E 3. Deacon Mr. F Year 2007...same way downward I wanted it that the year is Sideway" No. Position Name Year 2005 Year 2006 Year 2007 1, Elder Mr. A Mr. D ....... 2. clerck ...

Sum of a UNION Query
Hello, I want to sum the values together that are returned from the following two queries joined with a UNION from within the same query (instead of creating a second query that will SUM the two values that are produced from this one). I tried "wrapping" this code within a SELECT SUM, but it returns with "this is not allowing in subqueries". SELECT Count(*) AS SubCount FROM [TABLE1] WHERE (([TABLE1].USER)="JOHNSMITH") UNION ALL SELECT Count(*) AS SubCount FROM [TABLE2] WHERE (([TABLE2].USER)="EJONES"); The results are: SubCount -------------- 380 45...

Make Table Query
I have a Macro and at the end of the Macro I have a Make Table Query that I would like to automatically save as todays date in the current database. Does anybody have an idea on how I can do this? On Wed, 06 Jun 2007 11:40:24 -0700, dswiders@gmail.com wrote: >I have a Macro and at the end of the Macro I have a Make Table Query >that I would like to automatically save as todays date in the current >database. Does anybody have an idea on how I can do this? A date is just a field value. Saving today's date as a Table is almost certainly VERY bad design. Why do an expensive, awk...

Append Query every day at certain time automatically
Hi, So i have a query qryStats. Basicaly i want to be able to see how the stats have changed over a period so i was thinking of turning that simple query into an Append Query so that it just add the results to a table tblStats. I tested it and it works great so i can design reports and the result is exactly what i want. Problem is im going to forget to run the query each day. What are my options? Can i write code of some kind that runs the append query each day at, lets say 16:00? YES but your db would need to be running in order for that to work. You could also create...

Code that Runs nightly to export folder into a csv
I had posted on other forums but I haven't gotten a reply and really hoping someone here can help me or point me to someone who can. I was wondering if you can give me any information on how I can do this. I am not familiar with VBA programming other than excel recording macros and editing them slightly so sample code would be greatly appreciated. The task I am trying to perform is as follows: Every night, I would like to export all contents of a single folder "XXX" into a single .csv file and then move the contents to another folder named xxxx_mmddyy . Any ide...

Count unique names in a list
hi How do I count a list of names, some names appear more than once. I only want to count unique names. I use excel 2007 Thanks Cassie One way... =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")) -- Biff Microsoft Excel MVP "Cassie" <Cassie@discussions.microsoft.com> wrote in message news:B610A13C-FAF8-46EF-A63C-AAE88EEFB89F@microsoft.com... > hi > How do I count a list of names, some names appear more than once. I only > want to count unique names. > I use excel 2007 > Thanks > Cassie For ...

Null or NOT in another table
Hello. I have a query that verifies that required fields in my records are populated and correct. Right now it returns the records that have required fields=NULL. some of the fields need to be verified for legal values from another table as well. So I need to return values where the value of field [Tag]! [function_code] is not in [WM_GOC_Conversion]![Function_Code] My current query is a datasheet with autoformat that marks the null- fields with yellow background. I'd like to be able to mark the wrong information with red text, but first I need to have a query that return the...

Script for Query-Based DL's
Pardon me if posted in the wrong place. We are scripting a large amount of OU structures and administration groups with delegation through dsacls. This is something that is repeated many times. One item that is asked to create is a query-based DL. Since it would need to be ran so many times and for consistency I am trying to find if we can have the script create the Q-B DL's. I have found scripts for dsacls and the Q-B DL's but I'm having a hard time finding how to script the creation of the DL's. Does anyone know how this would be done? Thank you, Michael ...

Unique records from a range
Hi, I have a problem that I cant work out... I have a spreadsheet that plots out employees names and % worked o programs (Rows) against a list of programs (Columns). As the people can work on many programs I need to be able to create formula that analyses the rows, creates a unique list of names and the sums up their complete time worked on programs. Obviously if it sums up to over 100% per employee, then I know th resources are stretched and we need to hire in more people. Is there any way of doing this? I have attached a file that illustrates what I am trying to acheive. I tried using ...

New Record Problem when filtering
I'm using Access 2003 and have what at first seems like a common problem. On one of my forms if a user hits the scroll button on the mouse, it scrolls to a new record which then causes problems. I don't have sufficient rights to deploy a dll file so I got around it by changing the AllowAdditions setting to "No". Theoretically that should have worked and indeed it does if I open up a form directly. But in this database I often open up a form to a particular record using the "Where Condition" argument of the DoCmd.OpenForm command. When I do that, it opens...

Open form to a particular record
Hi, I have a continuous form listing some employees. One of the fields displayed is the EmployeeID field. Is it possible to double-click a particular EmployeeID field on the continuous form and another form (called "frmEmployees") open and display the record for the employee that I double clicked? I tried the following code and the frmEmployee form opened but at the first record, not the record for the person I double-clicked in the continuous form. ********************************* Dim strWhere As String strWhere = "[EmployeeID] = """ & Me.[Emplo...

No Minimum Wage Balance master pay record exists for this employee
We received this warning message on the payroll build this week. Minimum wage is not an issue with our employees and this particular staff was set up exactly the same way as other staff. Has anyone else gotten this message? You might check the employee's Additional Information button to see if the Calculate Minimum Wage Balance checkbox got marked by mistake. -- BrianB "sandwest" wrote: > We received this warning message on the payroll build this week. Minimum > wage is not an issue with our employees and this particular staff was set up > exactly the same w...

how to down load more than 1 year statements
HI I like to download more than one year of statment from my brokerage. But it only goes back to 1 year. I want to download statement from 1-1-2003 to 12-31-2003. but since I am downloading in March 2004 it only goes back to march 2003. I had money 2001 it didn't have the option to download more than one year of statement. I got mony 2004 but same thing it only goes back to March 2003. I have noticed that it can arrange reports from 1-1-2003 to 12-31-2003. But still missing the actual data from 1-1- 2003 to March 2003. Please advise how I can achieve that goal. Thanking in anticipa...

Exporting Exchange mailbox List
I need to get a full list of all users mailboxes sizes in exchange, i did that by entering each store ,opened mailboxes folder and export the list that appears, the problem that the total number of mailboxes in stores is less than the users in active directory which have a mailbox, can any body tell my why this is happining, and if there is a way to export all users names with their mailbox sizes for statistical reports without the need to open each store. The farm we have are windows 2003 active directory, no SP1 installed, and i have Exchange 2003 with SP1. Hi, It is possible for a...

Exporting As Text with X Number of Spaces Between Data
Is there a way to export data in a MS Excel spreadsheet as text and define the amount of spaces between the data being exported? The number of spaces need to vary. This concept is the opposite of importing text as "Fixed Width" and creating column break lines. Instead, I'd like to export columns into a text file and specify the amount of spaces between the text that is being exported from each column. The amount spaces in the text file needs to be different between each set of column data. Thanks! One way: Say you have 5 columns, and you want 2 spaces between A &...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

Copying Mail and Contacts but can't export
I had to reinstall my OS due to new chipset on my computer (new motherboard :>) anyways my old mail which I had saved in the drafts folder as well as my addressbook can not be accessed? I should have exported them before I installed the new motherboard but I did not think my os would need reinstalling. Anyways is there a way to find the specific files containing the mail and addressbook and import them into the outlook. I have outlook 2002 installed. Thanks All of your Outlook data is stored in a file with a .PST extension. Search your drive for all .PST files. If you're u...

A If then running count
I am trying and just cannot get it. I am trying to get a formula that does this: If have cell n3 at numbers up to 9 then cell v3 I want to read "1" If have cell n3 at numbers up to 14.9 then cell v3 I want to read "2" If have cell n3 at numbers up to 19.9 then cell v3 I want to read "3" If have cell n3 at numbers up to 24.9 then cell v3 I want to read "4" and so on. My attempt is to make the numbers increase by 1 when counting by 5. can anyone help me on this. How about this? =IF(A1<=9,1,ROUNDDOWN(A1/5,0)) ************ Anne Troy www.OfficeArtic...

Help with a simple query
Hi guys and gals, Can anyone help me with this query? I am having some issues with pulling in all names even though (it appears) I have the join correct. SELECT tblEmployee.EmpRptID, tblEmployee.UserName, Sum(tmpReports.NumOfSets) AS SumOfNumOfSets FROM tblEmployee LEFT JOIN tmpReports ON tblEmployee.UserName = tmpReports.UserName WHERE (((tblEmployee.IsCQATech)=True) AND ((tmpReports.CompleteDate) Between #2/8/2010# And #2/12/2010#)) GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName; What am I doing wrong? You have defeated the left join by applying criteria to the right s...

Avoiding Redundant Records
It is my understanding that surrogate keys are generally recommended to ensure uniqueness of records. Is it not true that using surrogate keys implies taking extra precautions to prevent duplicate records? I mean, with surrogate keys there is nothing to prevent the proliferation of multiple records all containing the same data, but each having a unique key. I would appreciate your help with this in the following context: AGREEMENTS AgrmtID (PK) InsuredID Agrmt fields… CERTS CertID (PK) AgrmtID ProducerID Cert fields… POLICIES PolicyID (PK) InsuredID PolicyTypeCo...

Macro Action Fails on Duplicate Record
I have a form with a subform whose visibile property is set to no. I have a macro that runs when a choice is made in a combo box that sets the visible property of the subform to yes when the selection=2. The macro sets the visible property of the subform to no when the selection does not =2. This is how I have the macro: Condition: [Appearance]=2 Action:Set Value Item: [Forms]![Appearance Request Basic]![OffSiteApp].[Form].[Detail].[Visible] Expression: True Condition:... Action:StopMacro Condition:(this I left blank) Action: SetValue Item:[Forms]![Appearance Request Basic]![OffSiteApp...

Previously used/opened record
I would like to have a button to hit to get me to my previously opened/used record. Can anybody please oblige me with some code to achieve that? Thanks, RF. Define "previously"... No, seriously, until you can tell Access HOW to find the "previous" record, Access won't know what to do. By "previously opened/used" do you mean "most recently" (a single record) or ALL records that have been opened/used. If the latter, since when? If a record is only "opened" (?looked at but not changed?), does that count? What do yo...