Query fields

Is it possible to write a criteria where the value of an empty field is "0.00"?

Background: I have three queries with different customer account groups. Not 
every  salesperson has customer accounts in every accountgroup - so, he will 
not shown up in that query. But he has accounts included in another query.
Now, I would like to get a sum of commission earned by each salesman 
calculated from all three queries together.
Since the salesman has no record in one query the total sum of that specific 
salesman is not shown.

Any idea how to solve that problem?
Thanks
Klaus
0
Utf
8/29/2007 2:46:04 PM
access 16762 articles. 3 followers. Follow

1 Replies
752 Views

Similar Articles

[PageSpeed] 28

On Wed, 29 Aug 2007 07:46:04 -0700, Amateur
<Amateur@discussions.microsoft.com> wrote:

>Is it possible to write a criteria where the value of an empty field is "0.00"?
>
>Background: I have three queries with different customer account groups. Not 
>every  salesperson has customer accounts in every accountgroup - so, he will 
>not shown up in that query. But he has accounts included in another query.
>Now, I would like to get a sum of commission earned by each salesman 
>calculated from all three queries together.
>Since the salesman has no record in one query the total sum of that specific 
>salesman is not shown.
>
>Any idea how to solve that problem?
>Thanks
>Klaus

Use the NZ()  - Null to Zero - function. Instead of [Commission] use
NZ([Commission]).

             John W. Vinson [MVP]
0
John
8/29/2007 4:56:04 PM
Reply:

Similar Artilces:

Reimbursable checkmark field in transaction form #2
Microsoft Money 2004 Small Business I updated yesterday to Money 2004. In the transaction form is a checkmark field <Reimbursable>. It is disabled. I can't find anything about this in Help or the User's Guide. What can I use this for? How can I activate it? Thanks Henrik ...

Filter from multiple fields using the same filter criterion
Hi, I have a user who would like to know if she can filter multiple fields, using the same filter criterion for each field, but she doesn't want to set the criteria for each field because there are over 100 fields. I haven't seen the database yet, but can only assume it's not set up very well, if she is looking for the same criteria in multiple fields within the same table. I haven't found a way to filter directly through Access without setting the criterion for each field. Does anyone know a way? Is there a way to do it through VBA, maybe? We are using Access 2007 /...

Excel Web Query
Hello, I would like to perform a web query that pulls data from an online page and enters it into excel. I know the basics for creating web queries and also know how to pass parameters with the web queries (when I know the parameter names). The problem arises when I do not know the parameter names for the textboxes, for which I want to to retrieve data...specifically I want to retrieve data from http://www.msha.gov/drs/drshome.htm. ; I have a specific MSHA Mine ID for which I would like to be automatically entered and then directed to the next page - how can this be done? A...

Is it possible to add a lookup field to the schema?
Hi all; I couldnt add a lookup to the incidents schema. Is there a way to do this? Alper Can Alper, In the currentl release of MSCRM you cannot add a lookup field to the schema. Matt On Mon, 1 Mar 2004 10:49:02 +0200, "Alper Can" <alperc@datasistem.com.tr> wrote: Hi all; I couldnt add a lookup to the incidents schema. Is there a way to do this? Alper Can No, you can't add your own lookup fields (at least not without hacking it into the metabase). Mike greaterthantwo media Printable Quotes, Orders and Invoices, with line-item details, now available for MSCRM. h...

no automatic fill of from-field
when I start up a new e-mail message, the FROM field does not aumatically fill with my own e-mail address. why is that and what should I do about it ? Erik Dont do anything, only use it if you want to send from another user in your organisation. "erik" <et57@hotmail.com> wrote in message news:m1bov1hih3baqf2h7hmlkfhlpmnmq9hj4d@4ax.com... > when I start up a new e-mail message, the FROM field does not > aumatically fill with my own e-mail address. > why is that and what should I do about it ? > > Erik On Wed, 22 Feb 2006 09:31:06 -0000, "Noel All"...

Contract Admin
Is there any way to configure the User Defined Fields in Contract Management? There are 5 of them, but I don't see any setup screens where these can be confiured to have the proper label I want. Even more importantly, I would like to be able to select from a list of options to populate these. Any advise is appreciated. Hi Adam, you can find the user defined fields under Microsoft Dynamics GP menu --> Tools-->setup-->Project-->Contract Setup. When setting up Contract Administration, either open each setup window and type information, or use the Setup Checklist window ...

Use VBA to update custom field on Collection Main screen
I've modified the Collection Management main screen by adding a field to display the e-mail address linked to the AP address ID. The code to update that field fires after the user tabs away from the customer ID field. However, if the user wants to use the VCR buttons at the bottom of the screen, they have to click twice to go to the next record. When they do that, the field doesn't update itself. I know I am probably being dumb, but am having a huge mental block on how to get that field to update itself when the user advances to another record on the screen. Any assistance is appr...

HELP: Auto Populate Excel Userform fields from access query result
Hi All, How do I populate Access query results in excel userform text fields? For eg; I have a Query: "SELECT Student_ID, Course, Grade, Qtr_ID" & _ " FROM Student_Info WHERE Student_ID = " & _ Me.StudentId.Value Here Me.StudentId.Value is a text box in excel userform And here is what the query results look like: Student_ID Course Grade Quarter_ID JP1124 Math A SP01 SP1164 Phy B SP01 Here "Student_ID, Course, Grade, Quarter_ID" are the column headers ...

Finding Duplicates with many fields
I have a table that consists of many Clients with many plans (fields) that may be the same and maybe different. There are 3791 clients and I was going to try to do a duplicate query but, it only allows to look up to 10 fields. I need find all the clients that have the same exact plans for 20+ fields any suggestions? Thank you for all your assistance On Mon, 12 Apr 2010 14:15:01 -0700, LG <LG@discussions.microsoft.com> wrote: >I have a table that consists of many Clients with many plans (fields) that >may be the same and maybe different. There are 3791 clients and...

CRM 4.0: Report Wizard - Query execution failed for data set 'DSMain'.
How come the following report made by Report Wizard fail ? An error has occurred during report processing. Query execution failed for data set 'DSMain'. The column 'accountid' was specified multiple times for 'account0'. The report is designed as follows: New Report, Start a new report, Reportname = "test" Primary record type : Accounts Use saved view: Active Accounts Add a single column = AccountName All the rest is default choices. Changing saved view to My Active Accounts yields the same result. Changing saved view to Accounts: Responded to Campaingns ...

Business Portal web part query
I have a group of records I want to display in a list view web part in BP 3. Their distinguishing characteristic is one of the fields in the results being displayed is empty. How can I get one of the restrictions in the query I am making for the web part look for an empty field? Thanks. -Joe joejoe851@gmail.com, Thanks for using the newsgroups. If you're using the MBF querying subsystem, you should be able to do this. Try creating an advanced restriction on your empty field. Choose the "is equal to" option, and for a value, enter two double quotes (""). T...

Blank fields
I have a form with 2 combo boxes and I want to filter the second one based on the selection in the first. I coded it as follows: Table 1: Department = DepartmentID & Departments Name Combo Box1 = Department Combo Box2 = Specifications Answered in your second post: http://www.accessmvp.com/Arvin/Combo.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "tinaemail" <tinaemail@shaw.ca> wrote in message news:1186291104.156204.21600@i13g2000prf.googlegroups.com... >I have a form with 2 combo boxes and I want to f...

Extender field #2
Hi, I have a two part question. 1. I have added a checkbox field called Factored using GP extender to SOP entry window. Where is this field kept so I can run reports based on it. I also have smartlist builder. How can it be used to creste list with this field. Basically we want reporting of factored and non-factored. 2. On this same factored screen can the default AR gl account be changed based on the checkbox. Thanks Dexter Southerland 1. To use an Extender field in GP reports, there is some technical information you can get from GP Support via the knowledgebase. As for SmartList ...

Delete duplicates QUERY
I'm having two tables, each with one field which contains some data: ___________ Table1 Field1 Per Pert Perte Perter ___________ Table2 Field2 Pert ___________ Now, I want to make a DELETE Query in MS ACCESS 2003. I go by the "Find Unmatched Query Wizard" (1st point on Table2, 2nd point on Table1, 3rd click on Field2<=>Field1, 4rd point on Field2 to see. Then I have "Table2 Without Matching Table1" (I then change Is Null to <>"False" and show Table1.Field1 in stead of Table2.Field2)) I'll then end up with this SQL...

Web Query #9
Hallo, When we run a WebQuery (Data/Get External Data from Web) with URL http://www.abnamromarkets.nl/showpage.asp?N=0&pageid=125&instrategy=12, sometimes the translation is missing the decimal denominator. ING Turbo Long should have 9,205 EUR as Reference, but it appears in the cell as 9205. Any suggestion is welcome. Thanks in advance. Tany.VanAerde@HERAEUS.com Probably a Yahoo problem but if you want to send you file to my address below I'll take a look. I do a lot of web query work for clients especially with Yahoo. BTW I'm a retired ING Reg Mgr. -- Don Guille...

linking two tables in query
I'm trying to link two tables using an ODBC table in query. Not all records in the left table have an entry in the right table. I've tried all combinations of the link properties but I either get only those records that have entries in both tables or each record in the left table duplicated by the number of records in the right table. Any advice gratefully received --- Message posted from http://www.ExcelForum.com/ Hi there You need to create the link in Query by dragging one field onto the other, then double-click on the link line. You'll either want option 2 or option ...

Change button right before the formula field
Hello everyone, Right before where you enter values/formulas you have a button. In some Excel versions it is a "=" button that you can push this if you want to. Now I have a FUNCTION button there instead and that is somewhat irretating. Does someone know how I can change the button right before the formulafield? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your favorite toolbar (nicely positioned). It's not quite the same, ...

Blank/empty field confusion
I'm quite new to Access DB design. My problem is that I am not sure how certain fields that are left blank/empty are treated. Is there in fact "something" inside that field. This relates to a DB search form which I have in Visual Web Developer 2008 - it has 5 fields - 3 text, 2 drop-down. It seems that when this search form queries the Access DB, it is behaving like a blank/empty field in a record in fact has something inside it. Is this correct? How can I get the empty fields in the DB to be ignored during the search process? Thanks! Mart "Martin Stabrey" &...

#ERROR in Custom Field
I wrote a simple equation Additional Costs = ([Cost]) * 0.08 It was accepted into the equation but in the column it show "#ERROR" Does anyone have any suggestions or know what I did wrong? Hit the F9 key to recalculate. What type of field is the Additional Costs field? -Jack Dahlgren "Erin" <Erin@discussions.microsoft.com> wrote in message news:EE0A8BFF-7179-4168-80EC-DE1881F8E284@microsoft.com... > I wrote a simple equation > > Additional Costs = ([Cost]) * 0.08 > > It was accepted into the equation but in the column it show...

retrieving maximum value from query
hi there, I have a table of the following schema build --------------------------- build_id AutoNumber build_date Date/Time label Text CL Number ---------------------------- for a simple query like SELECT build.CL FROM build; how can I apply a filter so that I only get the max CL value? thanks in advanced. SELECT Max(CL) FROM build If you want the details about the entry with the largest value, you could try: SELECT build_id, build_date, label, CL FROM build WHERE CL IN (Select Max(CL) FROM build) Recognize, of course, th...

Unable to view Property Details on a Query
Service Pack 2 was loaded onto our computer systems and it has taken away the visibility of the property details on a query. Would anybody know about viewing them without having to right click and actually going into the object properties? We want them to show under the Date Created and Date Modified under the Navigation pane. It is not an Option for us to remove Service Pack 2. Hi Jenn, If you right-click on the "All Access Objects" label, and then select View By > Details, I think you will see the missing properties again. Note: It is always best to includ...

Update Query Problem 10-01-07
All - I've been racking my brain with no avail on something that should be an easy task... I have two temporary tables that I'm using to come up with a single set of variables. The first one (tmptblItemsOKToBill) is the result of a query. The second (tmptblInvoiceTable) is a table that summarizes the data from the first as well as pull some data from another table. The problem I'm having is that there are multiple items in the first table that need to be calculated and placed in the second table. First Table (tmptblItemsOKToBill) Project ActualUnits ActualUnit...

3075 Error Trying to search for key word in a comments field.
OK so here is where I am and what I am trying to do next. Where I am. I have my Visual Basic code tied into the OK command button. I have a Form to search through my data and only put the requested information in a query. So far I have all of this working fine. I have the code clear the query each time it is used and repopulate the querry with the new choices.(The Where From etc...) The Problem with what I am trying to do next. It has been decided that there needs to be a comments column. They then what the option to search for a key word in the column to define the results. The...

Query selecting those players who played under par
Hello, I have the following 3 fields in an Access 2 query Under: ([Nett1]-[CCR1]) Sort Ascending Criteria < 0 CCR1: DLookUp("Venues.CCR","Venues","VenueID=" & 4) Nett1 File: Players Criteria > 0 defined as Integer I wanted to calculate their nett score for day 1 (nett1) minus the course rating (ccr1) The values for "under" range from -9 to any number over zero. Despite using the criteria for "under" being any value under zero, I am getting players all players from -9 to -1 correctly, but the query gives the players with 0,...

query filter not like
I have a field that contains 6 digits, then an astrik, then 3 digits, then an astrik , then possible 2 digits, then an astrik and 1 digit. I need to filter for all but the records that contain the last astrick and 1 digit. I have tried "Not Like "######" & "*" & "###" & "*" & "#" & "*" & "#" - but no luck - can anyone help ? How about another tack. Seems that you want fields with less than 14 characters. Put something like this in the Field of the query. TheLenght: Len([TheFieldName) I...