Query to Count "0" in Row

I am trying to create an expression in a query that says if
CONTRIBUTION BEGIN year is less then 2006 and the AUTE2 = 00/00/00
then count the number of "0" in the rown and multiply that by 58.

This is what I had thought up so far, but I know it isn't right.

CONTRIBUTION DUE: IIF([CONTRIBUTION BEGIN], 'yyyy' <2006, AND AUTE2 =
00/00/00, Count "0" And Multiply by 58

Any input is greatly appreciated.

-Anthony Morano

0
antmorano
8/16/2007 5:44:13 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
920 Views

Similar Articles

[PageSpeed] 1

What kind of data type is being stored in your [AUTE2] field?  If that is 
supposed to be a date, I don't think Access considers #00/00/00# to be a 
valid date.  Note the use of the "#" symbol before and after a "date".  Use 
quotes around text, "#"'s around dates, and nothing around numbers.

Regards

Jeff Boyce
Microsoft Office/Access MVP

<antmorano@gmail.com> wrote in message 
news:1187286253.802666.200490@w3g2000hsg.googlegroups.com...
>I am trying to create an expression in a query that says if
> CONTRIBUTION BEGIN year is less then 2006 and the AUTE2 = 00/00/00
> then count the number of "0" in the rown and multiply that by 58.
>
> This is what I had thought up so far, but I know it isn't right.
>
> CONTRIBUTION DUE: IIF([CONTRIBUTION BEGIN], 'yyyy' <2006, AND AUTE2 =
> 00/00/00, Count "0" And Multiply by 58
>
> Any input is greatly appreciated.
>
> -Anthony Morano
> 


0
Jeff
8/16/2007 7:07:55 PM
What type of field is AUTE2?  If it is a date field then it can't be 
00/00/00, so the assumption is that the field must be a text field.

PERHAPS, what you want is the following.

COUNT(IIF([Contribution Begin] <#2006-01-01# AND AUTE2 = "00/00/00" AND 
SomeField = 0)) * 58

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

<antmorano@gmail.com> wrote in message 
news:1187286253.802666.200490@w3g2000hsg.googlegroups.com...
>I am trying to create an expression in a query that says if
> CONTRIBUTION BEGIN year is less then 2006 and the AUTE2 = 00/00/00
> then count the number of "0" in the rown and multiply that by 58.
>
> This is what I had thought up so far, but I know it isn't right.
>
> CONTRIBUTION DUE: IIF([CONTRIBUTION BEGIN], 'yyyy' <2006, AND AUTE2 =
> 00/00/00, Count "0" And Multiply by 58
>
> Any input is greatly appreciated.
>
> -Anthony Morano
> 


0
John
8/16/2007 7:38:52 PM
Thanks for responding all.  I have just set the AUTE2 field to date/
time and as short date for the input mask.  Every field which should
be a date field is now formatted to be one.  John- I'm having a little
trouble understanding your logic with the AND SomeField = 0)) *58?  I
appreciate all the help.

-Anthony Morano


0
antmorano
8/16/2007 7:58:21 PM
Also, there is no longer 00/00/00.  When I switched the format of the
field to dat/time, it deleted all of those fields and thus they are
now empty.  I'm assuming that it would now be "Is Null" in the
formula?


0
antmorano
8/16/2007 8:00:20 PM
Also John- I am trying what you gave me and I have gotten farther
except for the fact that you have named "SomeField".  The problem is
that it is a range of columns, AUPS1-AUPS12 (for the 12 months of the
year).  How can I create a range to search for the 0's?

-Anthony Morano

0
antmorano
8/16/2007 8:05:56 PM
OUCH! OUCH! OUCH! This structure is making your life tough.

You can try something like the following.

CONTRIBUTION DUE:
COUNT(IIF([Contribution Begin] <#2006-01-01# AND AUTE2 Is Null,
Abs(AUPS1 = 0 + AUPS2=0 + AUPS3=0 + AUPS4=0 + ... + AUPS12=0)
, Null)  * 58

IF any AUPS can be null then you have to decide whether you want to 
treat the Nulls as zeroes or ignore them.  To treat nulls as zero you 
will need to use either an IIF statement or the NZ function.

    IIF(AUPS1 Is Null, 0, AUPS1)=0  + IIF(AUPS2 is Null, 0,AUPS2)=0 + ...

If you wish to ignore the nulls then replace the zero with any non-Zero 
value.

   IIF(AUPS1 Is Null, 22, AUPS1)=0

Have fun
'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


antmorano@gmail.com wrote:
> Also John- I am trying what you gave me and I have gotten farther
> except for the fact that you have named "SomeField".  The problem is
> that it is a range of columns, AUPS1-AUPS12 (for the 12 months of the
> year).  How can I create a range to search for the 0's?
> 
> -Anthony Morano
> 
0
John
8/16/2007 10:38:40 PM
John's OUCH is for YOUR pain.

It sounds like you've created a spreadsheeet (one column per month).  You 
won't get much out of Access' relationally-oriented features and functions 
if you feed it 'sheet data.

If the rows of data you have include a 'value-for-a-month', use just two 
fields, a [Value], and a [WhichMonth].

And you won't need to start all over again next year when another January 
rolls around if you use [ValueDate] instead of [WhichMonth].

If you truly only need and care about the Month-related values, consider 
using a spreadsheet instead of Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

<antmorano@gmail.com> wrote in message 
news:1187294756.985673.10830@57g2000hsv.googlegroups.com...
> Also John- I am trying what you gave me and I have gotten farther
> except for the fact that you have named "SomeField".  The problem is
> that it is a range of columns, AUPS1-AUPS12 (for the 12 months of the
> year).  How can I create a range to search for the 0's?
>
> -Anthony Morano
> 


0
Jeff
8/16/2007 10:55:26 PM
Reply:

Similar Artilces:

Exchange 2003 and Symantec Mail Security 4.0
When I tried to do a manual scan on individual user mailbox using Symantec Mail Security 4.0 tools, the mailbox list was not up to date. Some of my user has already left the organization with their mailbox purge away from exchange 2003. But Symantec Mail Security still able to capture it. And some of my new user mailbox was not reflected in the list. Anyone can help me? ...

Space problem with .NET 2.0
I am loading an XML file and applying the XSLT using .net 2003 (C#). XSLT will add Space character in the xml. I am using the entity value for space character &#32. it works fine in .net 1.1 whereas in .net 2.0 the space character is not showing up in the xml at all. Any idea why this works in ..NET 1.1 and not the .NET 2.0 parser? However, if I use the entity (&#160) then it works well for both .net 1.1 and .net 2.0. Is this a known issue? Any suggestions on why the space is not showing up with .NET 2.0 would be appreciated. Gaurav wrote: > I am loading an XML file and ap...

CRM 3.0: Can't display changes in "incidents" view.
Hi, I want to add columns to the incident view. So far, I customized the view in the parameter section, published the changes, but I'm still unable to view the new columns in the "incidents" view. I did an iisreset, even rebooted the server, without success. Any idea? Did I miss something? Thanks for any help. Jeff Which particular view? There are lots of "incidnet" views, perhaps you customised one and are looking at another. You don't need to publish view customisations (unless I'm completely mistaken..), certainly no need for an iisreset or reboot, ...

Query in Access 2007
I have linked via ODBC tables from another database. I have created a query to pull in two columns of information from the two tables in the query. I would like to add an extra column that is only created when the query is run, to dynamically number each row incrementally, depending on how many rows the query returns. If it returns 10 rows, then the first column should be numbered 1 to 10, if it returns 357 rows, then the rows should automatically incrementally number each from from 1 to 357. Try this but substitute your table and field names. Prim is your primary key. This can o...

12.2.0 will not install
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm trying to install 12.2.0 on a MacBook with System 10.5.7. Everything downloads. It runs through the install process until it gets to destination select. No destination disk shows. Now what do I do? Do you have Mac Office 2008 [v12] installed & updated to at least 12.1.0? There isn't enough info about your current installation. Regards |:>) Bob Jones [MVP] Office:Mac On 7/20/09 6:33 PM, in article 59b78758.-1@webcrossing.caR9absDaxw, "bytwerk@officeformac.com" <bytwerk@officeformac....

CRM 4.0 and MOSS 2007
Can CRM 4.0 Dynamics and MOSS 2007 exist on the same server? I finished installing MOSS 2007 and had to change over to Sharepoint Integrated mode to access reports from sharepoint but now I can't access http://crmserver/reports. I get "This operation is not supported on a report server that is configured to run in SharePoint integrated mode. (rsOperationNotSupportedSharePointMode)" Can someone point me in the right direction on what I need to do? Thanks for your help. Hi Yeshua, Is your problem resolved? Yes, CRM and sharepoint can run on the same server. I had installed sh...

Pos 1.0 Installation
Hi i have the following problem as version 2.0 stil is not available on the microsoft technet resource. When i try to instal the product it runs its setup, then it try to start the pos software however there should be fields shown to input product key, manager name, store name, but the only thing i see is a blanco white screen. it seems there are fields as you can hit enter it says something is not filled in, but i don't see where i can setup it. I have tried it with windows 2003 server, windows xp, and windows vista (there it is not installable as they are "known issuess&quo...

CRM Beta 3.0
OK in Outlook, perhaps I have missed something. You can open a single record and then from the Actions menu, choose Assign. But what if I want to choose multiple Accounts, then assign them to someone else. When I select multiple items, the More Actions menu only shows Sharing... no Assign. If I have to reassign a larger number of contacts say 15 - 20, do I have to do them one at a time or is there a fast multi-select way to so this re-assignment? Thanks! Shauna Create a manual workflow rule that Assigns them to whoever you want, select the accounts you want to assign and choose Appl...

Audio Driver for BT in WinCE 6.0
Hi All, I'm developing an audio driver which takes and sends the data from the Bluetooth, HFP profile on Windows CE 6.0. I have taken the driver(WM8746 codec,driver,using MCASP0) which was already present and tweaked the code to configure the McASP that is dedicated for the Bluetooth(McASP3). The McASP settings had the change of the clock rate, pin dir & frame sync etc depending upon the master slave settings of the McASP with the BT. BT is configured to be the slave in my case. I've also configured the DMA settings, IRQ lines, and the control and data registers for the ...

incorporate the remove #DIV/0! code into another formula to get bl
hi there, i have this formula below, and i somehow want to incorporate this =IFERROR(B1/C1,"") into it with the purpose of having the cell display nothing instead of #DIV/0! so that the column of data can then allow an index and match formula to work as it won't if any of the cells have #DIV/0! in them, any suggestion? =(SUMIF($B$2:$B$2500,">=" & T58,$M$2:$M$2500)-SUMIF($B$2:$B$2500,">=" & (T58+TIMEVALUE("1:00")),$M$2:$M$2500))/(SUMIF($B$2:$B$2500,">=" & T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,">=" & ...

Invoicing Services in GP 8.0
We have a need to invoice field services. We have items set up corresponding to our technicians with the appropriate rates. In our previous accounting application, we could open a line item note window and free-type notes specific to that customer's issue and resolution. GP has line item notes, but they are not unique to the order. I need to be able to change these line item notes (many, many times this is several hundred characters of text) with each new sales order and possibly multiple services with multiple notes on one order. We have found references to "line item c...

CRM v4.0 (Titan) XP and Office 2003 Compatibility
When Microsoft CRM Version 4.0 is released, will I be required to upgrade to Vista or Office 2007? i.e. will version 4.0 work on my current XP environment? Is their any documentation to back this up? Many thanks Neil -- na Those of us testing Titan are under NDA so cannot say anything at this point. ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Neil Max" <NeilMax@discussions.microsoft.com> wrote in message news:4F97C36E-82E0-43CF-8C9C-90AABA4FFBA5@microsoft.com... > When Microsoft CRM Version 4.0 is released, will...

Formulas Divided by 0 =#Div/0!
I usually come across this problem, and I fix it with a simple if statement. But today, I have received a huge spreadsheet and this problem is all over the place, at least 100 entries. Cut and paste the modifed formula with an if statement will not work. Is there an easier way to show a 0 or blank in this cells instead of #Div/0! Thanks Chad If you're only interested in hiding the errors--not changing the values, you could use conditional formatting. Select the cell (say A1) and then Format|conditional formatting (xl2003 menus) formula is: =ISERROR(A1) And make the font color t...

CRM 3.0 Can't Add Users
Hi This is probably a permissions error but when I log in as the administrator I can not add a user in CRM either through the web GUI or through the deployment manager. The web gui just come back with a general error message and teh deployment manager comes back with the following: User 1 ( Username ) failed : Application Error (80048015) - SOAP Server Application Faulted I searched high and low for an answer but no luck not sure if his is related but it seems that the outlook client also has trouble synching records in the database and can't go offline any ideas??? Update: It s...

How do i enable all 1,00,000+ rows in excel 2007
Excel 2007 as more than 1,00,000 rows, but in our organisation setup i see only 65536 rows. How do i enable the remaining. Try to save as Excel 2007 * -- Regards Dave Hawley www.ozgrid.com "free_ur_mind" <freeurmind@discussions.microsoft.com> wrote in message news:A988D314-8344-4CE2-B658-C6FAC58D8B95@microsoft.com... > Excel 2007 as more than 1,00,000 rows, but in our organisation setup i see > only 65536 rows. How do i enable the remaining. .... and then close the file, and then reopen it. You can change the default file format in Office button ...

CRM 3.0 c360 Summary console
Hi Has anyone experience any slow performance load time on the c360 Summary addon (from productivty pack)? If so, did you manage to improve speed? It is taking about 2mins to load an account or contact summary details. Many Thanks We have been running this add-on since 1.2, and now 3.0 with no issues. Are you the first person running the app for the first time? It will be slow the first type its' used. We actually use that caching application to make sure all pages to ensure this doesn't happen. Do you also have slow performance on other pages (IE pulling all the activit...

DynamicsGP 10.0 Installation
I downloaded the 10.0 CDs from Partnersource and installed them fine in VPC running Windows SBS. On my laptop, I uninstalled 9.0 and SQL 2005, then re-installed SQL 2005 (I was using a named instance and wanted to use the default instance). SQL is running fine. However, when I try to install DynamicsGP v10.0, I get an error message "Setup.exe has stopped working". The 1st time I ran it, it loaded the Dexterity component. Oh, I'm running VISTA Business -- and I turned of User Account Control. Hey I came across a similar issue with 9 and it came down to the fact that y...

why this->PostMessage(ID_FILE_NEW,0,0) not works
Hello everyone, On clicking one menu item, I need to do some functions, then do the same action as ID_FILE_NEW is called. I have the following code but it not works. Help! void CMainFrame::OnMenuitemNewWebsite() { // TODO: Add your command handler code here FUN_Website::New (false); this->PostMessage(ID_FILE_NEW,0,0); // ::PostMessage(this->GetSafeHwnd(),ID_FILE_NEW,0,0) ; } Thank you, Kelvin Hi, ID_FILE_NEW is a control identifier, from a menu in this case. The first parameter of PostMessage is supposed to be a window message. The window message you are looking for is WM_...

CRM Mailbox User on the 3.0 Demo Drive ?
Does anyone know what the CRM System Mailbox User is for the 3.0 demo drive? -Luke ...

Changing the Sales Stage in CRM 4.0
Hi, In CRM 3.0 it is possible to change sales stage (skip one or more) without finishing task(s) from the previous stages (by "change stage" option). Is it also possible in CRM 4.0 ? Thanks, Pawel Whether or not your finish or complete the task to move the next sales stage is controlled via workflow. I don't see the action in 4.0 to change the sales stage process. You could overcome this with a custom workflow that will set the sales stage to a specific stage or advance it by 1. Then you will be able to run the workflow on the opportunity to advance it. -- Christopher Co...

Entourage-MAC 2004/V11.4.0- keeps shutting down!
Over the past few weeks Entourage keeps shutting down-consistently and many, many times per day! It seems to occur, firstly, after I have been on a website (using Safari 4.0.4) and I revert back to using Entourage...the latter then crashes and I have to restart that programme. The last time Entourage shut down-actually a few minutes ago-I then experienced a "kernel panic" at which time I "restarted" my MAC. This situation is really, really frustrating :-( Is my MAC telling me it's time for a new MAC with a totally upgraded "Office for MAC" (and a...

Excel 5.0 #2
I need some help. I have been a faithful user of version 5.0 and I missed my opportunity to upgrade easily. Is there anyone who would be willing to part with an intermediate version so I can make the jump. I understand I need at least an Excel 97. I believe 97 can convert 5.0 and 03 can convert 97. Hi Exc�l 97 - Excel 2003 all have the same file format 8and all can read Excel 5.0 files). So just check ebay and get on of the newer versions (Excel 2000 / Excel 2002) -- Regards Frank Kabel Frankfurt, Germany "Tim in Idaho" <Tim in Idaho@discussions.microsoft.com> schri...

RMS 2.0 and PC Charge
Is anyone using PC Charge Pro with RMS 2.0? I haven't been able to get any answers from Verifone as to certification with RMS 2.0. Does PC Charge Pro support the AVS function in RMS or does it do it itself? I'm changing Merchants and the new one requires using PC Charge Pro. Thanks, TomT Funny thing... I have basically the same question. Our MS VAR told me to check with PCCharge (Verifone) . VeriFone told me "They (MS RMS) test their versions with our software and keep a list of which is compatible. If you are using the newer version of RMS then I would say 5.7.1I SP...

Microsoft Dynamics CRM 3.0 Implementation For Large Corporation (August 2006)
Microsoft Dynamics CRM 3.0 Implementation For Large Corporation (August 2006) How Microsoft CRM 3.0 could contribute and be a part of its computer environment. (Sales Module,MS CRM Security,Integration with Microsoft Dynamics GP 9.0/Microsoft Great Plains,Integration with IBM Lotus Notes Domino,International Considerations,Competition) http://microsoft-crm-3.blogspot.com/2006/08/microsoft-dynamics-crm-30.html ...

CRM 4.0
Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client install and has always worked. These ar...