Use of the DSUM function

Who can give me a hand?

I am using the function below; so far so good.
DAvg("[wng_OPP_SLPK1]";"TBL_Woningen";"[wng_CFT Code] = """ & [pdt_CFT code] 
& """")

Now I need to generate a sum on a field that contains the value "J". How can 
I change the function:
Sum(IIf([TBL_Woningen]![wng_COD_INDCV]="J";1;0))
into a
DSum function where the field to sum is wng_COD_INDCV (with the text "J"), 
the table TBL_Woningen and the criteria [wng_CFT Code] = """ & [pdt_CFT code] 
& """"

I can not seem to get the syntax right.

Ron

0
Utf
2/9/2008 6:10:00 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1135 Views

Similar Articles

[PageSpeed] 7

It looks like you actually want to count

DCount("[wng_OPP_SLPK1]";"TBL_Woningen";"[wng_CFT Code] = """ & [pdt_CFT 
code] & """ AND wng_COD_INDCV='J'")

-- 
Duane Hookom
Microsoft Access MVP


"RoBo" wrote:

> Who can give me a hand?
> 
> I am using the function below; so far so good.
> DAvg("[wng_OPP_SLPK1]";"TBL_Woningen";"[wng_CFT Code] = """ & [pdt_CFT code] 
> & """")
> 
> Now I need to generate a sum on a field that contains the value "J". How can 
> I change the function:
> Sum(IIf([TBL_Woningen]![wng_COD_INDCV]="J";1;0))
> into a
> DSum function where the field to sum is wng_COD_INDCV (with the text "J"), 
> the table TBL_Woningen and the criteria [wng_CFT Code] = """ & [pdt_CFT code] 
> & """"
> 
> I can not seem to get the syntax right.
> 
> Ron
> 
0
Utf
2/9/2008 6:42:01 PM
Hi Duane,

Thank you very much, this definitely is the solution to my question.

Ron Bouma

"Duane Hookom" wrote:

> It looks like you actually want to count
> 
> DCount("[wng_OPP_SLPK1]";"TBL_Woningen";"[wng_CFT Code] = """ & [pdt_CFT 
> code] & """ AND wng_COD_INDCV='J'")
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "RoBo" wrote:
> 
> > Who can give me a hand?
> > 
> > I am using the function below; so far so good.
> > DAvg("[wng_OPP_SLPK1]";"TBL_Woningen";"[wng_CFT Code] = """ & [pdt_CFT code] 
> > & """")
> > 
> > Now I need to generate a sum on a field that contains the value "J". How can 
> > I change the function:
> > Sum(IIf([TBL_Woningen]![wng_COD_INDCV]="J";1;0))
> > into a
> > DSum function where the field to sum is wng_COD_INDCV (with the text "J"), 
> > the table TBL_Woningen and the criteria [wng_CFT Code] = """ & [pdt_CFT code] 
> > & """"
> > 
> > I can not seem to get the syntax right.
> > 
> > Ron
> > 
0
Utf
2/9/2008 7:02:00 PM
Reply:

Similar Artilces:

How do you get the attribute value using XPath in VB.Net 2003?
Hi, How do you get the attribute value using XPath in VB.Net 2003? Many thanks, aushknotes "aushknotes" <aushknotes@discussions.microsoft.com> wrote in message news:508426F2-1C8A-4AD2-A52E-B80B9798AC0C@microsoft.com... > Hi, > > How do you get the attribute value using XPath in VB.Net 2003? > Prefix @ to the name of the attribute value. XmlAttribute attrib = (XmlAttribute)dom.selectSingleNode("/path/@attributeName"); -- Anthony Jones - MVP ASP/ASP.NET aushknotes wrote: > How do you get the attribute value using XPath in VB.Net 2003? ...

Data Validation using List (But needs unique list in drop down lis
Hi all, In sheet 1, column A is my title name while column B is person name. Sheet 1 is my database where i do data entry in this. In sheet 2, contains my query page. In cell A5, i uses data validation - list, on this cell. Say in sheet 1 : column A column B XXXXXXX Mr A YYYYYYYY Mr A ZZZZZZZZ Mr A AAAAAAA Mr B WWWWW Mr C DDDDDDD Mr C But In sheet 2, cell A5, I saw in the drop down list as follows: Mr A Mr A Mr A Mr B Mr C Mr C But i want to see this in cell A5 instead (Unique name that is) : Mr A Mr B Mr C ...

Lookup Function in user form
I have a combo box (cboitem1) and a text box (txtprice1). I am using the code below to have txtprice1 fill with the corresponding price the worksheet: Private Sub Cboitem1_Change() If cboItem1 <> "" Then txtprice1.Value = Excel.WorksheetFunction.VLookup(cboItem1.Value, Sheets("Pizzas").Range("A1:B65536"), 2, False) End If End Sub It works perfectly when you enter an existing value in cboitem1. However if 'the user' accidently types a letter that is not the first letter of one of the existing items VB debugging pops up with error &qu...

Track in CRM function
Hello. Upon selecting the “Track In CRM” button from the CRM line in Outlook I have two questions based on situation: 1) If the contact exists in CRM it appears to attach the email but provides no notice unless you try to attach it again? True? 2) If the contact does not exist it attaches the email where? In v1.2 it opened a window and made the user create a record. I think it is attaching or tracking the email in CRM somewhere because trying to do it again, I'm asked if you want to attach/track it again. Does this make sense? By the way, we are using CRM 3.0 Thanks. After c...

Use CountIf for a literal string
I am trying to count the number of cells in a range that contain the string <0.1. I don't want the number of cells that contain numbers less than 0.1, I want the actual string. Is there an escape character for comparison operators like the tilde for wild cards? Thanks, Chad Try this... =SUMPRODUCT(--(A1:A10="<0.1")) -- Biff Microsoft Excel MVP "chadkwelch" <chadkwelch@discussions.microsoft.com> wrote in message news:73F17509-1C52-4935-A45D-66DCC63B20C6@microsoft.com... >I am trying to count the number of cells in a range that ...

Tracked changes function misbehaving
I have a simple workbook (2002) in which the Track Changes feature is enabled. End users have reported periodic problems with the contents of cells "mysteriously" changing. In process of investigating these, have found that when tracked changes are highlighted, the comments that describe changes are not always associated with the correct cell. Example, comment on cell B114 states "Changed cell B114 from '<blank>' to '12345'". In fact, contents of cell B114 is "67890" and contents of cell B104 is actually "12345". Has anyone...

Posting Date used in Revenue Expense Deferral
I have a PM Invoice with Document Date 16/11/2009 and Posting Date of 01/12/2009. Entered Deferral details starting 01/12/2009. In GL the deferral charges commence 01/12/2009 and are all as expected. The Credit entry for the full invoice is posted to GL with posting date of 01/12/2009. The problem I have is that the Debit entry for the full invoice amount is posted to the document date of 16/11/2009 and I want it to be the same as the invoice posting date (01/12/2009) Is there a way to do this? Thanks Audrey ...

Can't Send Messages Using Outlook Web Access & XP
Our users with Windows XP cannot send messages using OWA. I've read a few KB articles about some incompatabilities between XP and OWA, but none of the proposed fixes (using the "basic" rather than "premium" client, or changing the security settings in IE) seem to solve this last problem. When they click on "send", it generates an unspecified "error on page". The problem occurs using IE and Firefox, but just on XP machines. My Windows 2000 machine works fine using IE and even my Macintosh using Safari (gasp!) works. ...

How do I specify an address when using "ActiveDocument.SendMail"
Hi Here is my code: If Not IsNull(([Forms]![Process Bookings]![Booking Form].Form.Email)) Then Options.SendMailAttach = True objWord.ActiveDocument.SendMail How do I specify the recipients address? Stapes ...

Could very much use a Field List
I apologize if this is out there already, but it seems that at least a few field names changed from 1.2 to 1.3. Although I had downloaded a field list before, from here, I cannot even find that original post, let alone any more recent that might contain the correct field names. I need to do some custom SQL and would prefer not to reinvent the wheel. Thanks in advance very much to anyone who could help. Bud Izen Salem Oregon Do you have MS Access? Make a new project and attach to the database as your data source. You will be able to see all the tables and the field names. It has been ...

How to Print string (Windows Printer) using RMS QSRules.
A windows printer is set as a receipt printer for a register. I need to print strings to this printer using RMS QSRules. How can I do this? I tried using Register.ActivePrinter.PrintNormal(Station as Long,Data as String) but it won't work. By the way, what is Station in the parameter? ...

Adding a VCard to an e-mail using Outlook 2000 and Outlook 2003.
Hello. I have a question. Does anyone here know how to add a VCard to an e-mail in Outlook 2000 as well as in Outlook 2003? I want to be able to do this after I click new and I have a new blank message on my screen. Any and all help would be greatly appreciated. Thank you. -- darylakagod Outlook provides no way to add a vCard .vcf file in this scenario. The built-in way to do it is to start with a contact, not a message, and choose Forward as vCard on the contact's Actions menu. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Admin...

List of kernel mode compatible functions
Hi, Is there a list of kernel mode compatible C/C++ functions (preferably for WDK 7600)? Thanks, Jan Wouldn't that be the WDK Help file? Thomas F. Divine "Jan M" <jan_m@jerseymail.co.uk> wrote in message news:e7fhXcanKHA.1548@TK2MSFTNGP02.phx.gbl... > Hi, > > Is there a list of kernel mode compatible C/C++ functions (preferably for > WDK 7600)? > > Thanks, > > Jan > Your right of course. I just didnt look hard enough. Jan ...

Set Out of Office (or a rule) to only function on weekends
Hello, I am trying to set up so that every weekend my internal e-mail gets forwarded to my home(smartphone) account. I can only find options to run a rule or out of office assistant to run for specific date ranges. How do I set up either a rule or the out of office assistant to 'push' mail to me only on weekends? HCA.CWT.RN wrote on Fri, 02 April 2010 09:04 > Hello, I am trying to set up so that every weekend my internal e-mail gets > forwarded to my home(smartphone) account. I can only find options to run a > rule or out of office assistant to run for spec...

FORECASTING SALES (please let this be a worksheet function)
Hi I have recently been given the task by my boss to forecast one of ou key customers monthly usage until monthly until end of 2006, I am starting with this customers indidual branches monthly usage fo the past 3 years, Some branches will have opened and closed during thi time, And i am looking to be able to predict monthly usage for the nex 18 months Has anyone got any idea's on the best way to forcast within excel, I a currently using trend (fomulae given to me on this sight) but people ar saying this is not the best way Exponentionally has been mentioned by a few of my collegues but...

Monitoring directories using FindFirstChangeNotification
All, I would like to monitor a few different directories using FindFirstChangeNotification. I have successfully used it in the past to monitor one directory. All the directories I would like to monitor are on the root of a drive, but I do not want to monitor all the directories on the root. Example: the root of the T drive, T:\Dir1 T:\Dir2 T:\Test T:\source T:\Backup T:\Update How can I montor the T:\Test, T:\source, and T:\Backup at the same time using FindFirstChangeNotification? Thanks in advance. Hi, You need to use the following API's to monitor the changes in a specific ...

Using outlook for email and outlook express for newsgroups
Hi I had outlook set up for my email messages and outlook express set up for reading newsgroups - now when I go to the newsgroups oe is automatically checking for email messages and downloading them - this never happened before and I think the only thing different is that I have installed a broadband modem and am waiting on freeserve activating my account - could something have happened to change this, and if so how do I change it back. Thanks Sharon "Sharon" <sharon@nospam.freeserve.co.uk> wrote in message news:c80adp$dva$1@newsg1.svr.pol.co.uk... > Hi > > I ha...

Using a OR() like function in an IIF statement
Hello, I am trying to create an IIF statement to test if the first character in a field is a 1,2,8 or 9. Something like the following: IIf(Left([possible_SO_match],1)="1 or 2 or 8 or 9",[Possible_SO_Match],"No Match") Is there a way to create it without going to a 4 level nested IIF statement? Thanks, Kerry -- Message posted via http://www.accessmonster.com kkulakow via AccessMonster.com wrote: > Hello, I am trying to create an IIF statement to test if the first > character in a field is a 1,2,8 or 9. > Something like the following: > > IIf(Left([possib...

Can't use address book
I am not able to add contacts in my address book. I receive the error: You cannot create entries for this address book when I try to add a new entry. I use Office 2000, with full Outlook. I would like to have an address book again... I have uninstalled and reinstalled office 2000 - no change in the problem. You get this error in trying to create a new item in your contacts folder? (sounds like you are select tools | address book | highlight "outlook address book" and trying to create a new item. you can't create items in the "outlook address book". this f...

Print dialog box using VBA in excel
Hi Everyone, I like to add a print button (that will select several worksheets and print them) in my excel worksheet. I have recorded my action using the macro recorder the problem is I can't select the printer everytime I run the macro it print in the background without asking the user to select the printer and uses the default printer. Can anyone help?? I just want the user to be able to select the desired printer every time before printing. My current code: Sub Print_All() Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 5)).Select 'intent...

Using DAO instead of ADO
I have an Access 2000 database that was converted from Access 97. On one of my users computers, her system was re-installed with Access 2000, and the database has not worked correctly since that time. If I remember correctly, we had to set up the computers for these users to use DAO over ADO. It's been over two years since we did this, and (I hate to admit it - but) I've forgotten to how to set up the DAO to take precedence over ADO. Can someone please remind me? I thought it was part of the Add-ins, but when I go into Add-in Manger, I have NO add-ins available. I've checked i...

How do a use a string as a param for a cmdlet when it contains opt
I have a script that cleans out old files, currently it is in this format write-host "\\sapecc01\Integration\SCC\Archive\* -Include *.txt" $a = Get-ChildItem \\sapecc01\Integration\SCC\Archive\* -Include *.txt foreach($x in $a) { $y = ((Get-Date) - $x.CreationTime).Days if ($y -gt 28 -and $x.PsISContainer -ne $True) {$x.Delete()} } #Keep DESADV for 28 days write-host "\\sapecc01\Integration\SCC\Archive\* -Include DESADV_*.xml" $a = Get-ChildItem \\sapecc01\Integration\SCC\Archive\* -Include DESADV_*.xml foreach($x in $a) ...

very simple and useful, email password recovery tool
Outlook Password Recovery is a easy-to-use and wide compatiable tool, capable of instantly recovering email passwords for popular email clients, such as Outlook, Outlook Express, Windows Mail, Incredimail, Eudora, etc. http://www.top-password.com/outlook-password-recovery.html -- johneou johneou wrote: > Outlook Password Recovery is a easy-to-use and wide compatiable tool, > capable of instantly recovering email passwords for popular email > clients, such as Outlook, Outlook Express, Windows Mail, Incredimail, > Eudora, etc. > > http://www.top-password.com/outlook-pa...

can cells apply conditional formatting using the internal clock?
I am using excell to keep track of my production schedule and I wanted to know if there was a way to tie the cells in a worksheet to the internal date and time in the computer,so that the cells will update automatically. Example: Row A10 would be my production start date, Row A1 would be my projected finish date, I would like the cells in between to go from green to red as I near the finish date without manually inputting the date in each cell. Can you help me? Thyanks Set the normal format as desired (I selected a Pattern of Green). Select A1:A10, then select Format | Conditional Fo...

Using Access database to "populate" Excel Sheets
Please help!!! I am willing to PAY anyone who can get this to run fo me. I have been trying for 5 days now trying to use a DBVlookup function t populate fields in Excel. I used examples from 4 different forums usin this function, but I cannot get any of them to work. I am somewhat ne to VBA so forgive me if I am not making sense in my questions. I have Excel Spreadsheet called "Account_Number". It is set up a follows: Column A is called "Account Number"..... Column B is called "Looked u description in Access". Account Number Description...