If Statement and Multiple Criteria
I am using a formula to bring back some text about some data. I am using the
=IF(N12>M12,"is larger for girls","is larger for boys"), so that if the data
in cell N12 is greater than the data in cell M12 then it says "is larger for
girls" and vice versa.
This works fine, apart from if negative numbers are in the cells then it
brings back the value closest to zero - rather than the largest negative
Does anyone know how to get round this.
In addition - I would ideally like to alter the formula so that it says that
if the two...Help creating a script in SQL or Calculated field in Crystal
How do I take this script and manipulate it to give me one long string with
static text as well as SQL data in specific positions within the string.
Positions / Data:
1-3 / 173 (Static text)
4-6 / spaces
7-10 / "X_UPR30300"."YEAR1", (has to show up as 2007. Showing up as 2,007)
11 / 4 (Static text)
12-22 / "UPR00100"."SOCSCNUM"
23-57 / "UPR00100"."LASTNAME"
58-92 / "UPR00100"."FRSTNAME"
93 / "UPR00100"."MIDLNAME"
94-120 / "UPR00102"."ADDRESS1"
121-148 / &q...macro for comparing fields in two work books
I have two excel spread sheets (say A1 & A2). A1- is the master sprea
sheet and A2 is smaller spread sheet with very few details.
Suppose Column B in A2 has 100 partnumbers and Column C in A1 has th
superset of partumbers (1000) and corresponding details for each par
number, I need to:
1. Check if all the 100 part numbers in A2 has a corresponding match i
2. Extract the info for the matched partnumbers from A1 and list in
3. Even if there in no match A1, let's say for 40 of them, it shoul
still list those parts in the new sheet.
Can anybody help me with t...Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat
in Excel. Here's my problem:
I have a workbook with 2 worksheets; one is the main data, the other i
the statistics from the main data. Within the main data, there is
column titled "date paid" and another column titled "total paid" (ther
are a bunch more columns, but they don't matter for this problem). Th
"date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O
the stats page, I have columns titled for each month of the year an
would like each to include how much was pa...Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns:
ColID - PRIMARY KEY
Col1 - Number
Col2 - Date/Time
Next I created a multi-field index using Col1 & Col2. I entered the
following row in the 1st row:
Next when I tried to add the above row again, as expected, I wasn't
allowed to enter the same row. Next I entered the following 2 rows:
Both the rows were accepted. After this I deleted the 3 records, went
back to the design view & changed the data type of Col2 from Date/Time
to Number keeping the multi-field index...partially turning off auto calculate?
Any way to NOT auto caluculate 1 particular formula, while leaving the
rest of excel to auto calculate? In particular, I have an SQL query I
would prefer to run manually, while not interferring with the rest of
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
Calculation is an all or nothing setting. You can't prevent calculation of
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ...Vlookup based on multiple criteria
I have 2 workbooks:
Workbook one: Account number, name, January to September + total
(columns A9 to L240)
Workbook two: I need to populate with data from Workbook one
Column A has the account numbers
all accounts that contains department 71(xxxxx-71-xx) must go to
location 1 (Jan - column B, Feb =96 column E etc)
and all others goes to location 4 (Jan =96 column C, Feb =96 column F
Is there a basic a VLOOKUP or other function that can automatic the
Thanks in advance.
Account Number Jan-10 Feb-10 Mar-10
41001-71-01 6,000.00 - 1,000.00
41001-71...dynamic field content of parameter combobox
I have to filter my Crystal 9.0 report with a parameter combobox field in
Microsoft CRM. The data for this parameter field has to be loaded out of a
database field dynamically. I have found the possibility to load database
field values in a parameter field but this will not be dynamically. If there
is a change in the database content the parameter field content will not be
updated. I hope to create a combobox control is no problem.
Is there a way to turn this into reality?
Thanks for your help
...counting using multiple criteria
I have a problem for which I can't seem to find a working solution. On the
one hand I have a vertical table with employee names, the number of years
they have been with the company, and all this sorted by the employee's age
(in years). This table will be updated from time to time, and as such it
doesn't have a fixed length.
On the other hand I have a table which shows the number of employees in a
particular age category (-20, 21-25, 26-30, etc) horizontally, and the number
of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs,
...Deleting a single character in a text field
In one of my tables i have a field that has a text values like '123.23123' i
would like , to delete the full stop. How can i do this. I cant do it in
excel as the number of records that i have is over 200000.
> In one of my tables i have a field that has a text values like '123.23123' i
> would like , to delete the full stop. How can i do this. I cant do it in
> excel as the number of records that i have is over 200000.
Create a update query, use
Replace([yourField], ".", "")
as new value.
--> stefan <--
...Validating two fields
I am trying to create a database in which some of the fields should be
mandatory only under certain conditions.
In my "Type of Injury" column, I want the user to be required to enter a
value in this column, only if the value in the "Type of Document" column is
"Nursing Report". Is this possible? I am only just beginning to teach
myself Access, so please bear with me. Thank you.
Put a validation rule on the *table* (not field.)
2nd example under the the Validation Rules for Tables section here:
http://allenbrowne.com/Validati...Adding Fields and Tables to MSCRM
Is there a link or some reference on the ability to add fields to existing
tables in the CRM database and/or adding complete tables to the CRM
database?? I am asking this from the point of view relating to other
existing CRM products (saleslogix, frontrange - shh). From what I have
played with so far from playing with my SBS2k/MSCRM install is I my need to
have a MS developer skillset.
Using the schema manager in the deployment Manager MMC snap-in, you can add
fields to the existing tables. However, there is no way to add new tables.
"Robb D" <robbd@eproductscons...Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English
to Metric conversion routines in the Access VBA code and would like to run a
query on the data that will return coverted values. I need to be able to
execute this from outside the database (run the query from another program).
Yes. The function must be a Public Function in a standard module. You use a
calculated control to return the results of the query.
Dave Hargis, Microsoft Access MVP
> Can I call a VBA subroutine from within an ...Button on Form to Run Report based on Query with Parameters
I am busy working on a stock control database (and job control).
When I have a Form Open, it shows several fields, including JobID (Which is
the PK for that job) (frm_Invoicing)
I also have a Query that when Run, it prompts for the JobIDNo.
I have a report based on that query. (rpt_CustJobsInvStock)
I have added a button onto the form (onclick -> DoCmd.OpenReport) to open
and automatically print the report,
But I just cannot figure out how to get it to get my JobID from the form,
and to Automatically add it as a Parameter for the que...Modify Calculation
When a user enters data into a cell, he has to tab out of
it before he can push the "calculate" button. Is there any
way to change this so that the calculation can be
performed while the cell is still selected? I appreciate
Change Tools>Options>Edit>Move selection after enter, and use Enter to
complete entry rather than Tab. This prevents the selection from moving to
Note that calculation can never be done while the cell is being edited.
"Sisilla" <email@example.com> wrote in message
news:0b4e01c34b9b$d59ebcf0$a301280a@p...I'm trying to display the next month in a text field
I've gone through all the forms and can't seem to get the code straight for
displaying the next month on a text field on my form.
I just want the entire month name and nothing else and I have been struggling
DateSerial(Year(Date()), Month(Date()) + 1, 1)
but its showing the day and year too and thats not what I need.
Thanks in advance!
>I've gone through all the forms and can't seem to get the code straight for
>displaying the next month on a text field on my form.
>I just want the entire month name and nothing else...Mail (outlook 2002), 'To' field, general question
Why do some names of some messages in the 'To' Field appear with single
quotes in my Sent Box? I'm using Outlook 2002.
I'm sure there's an easy explanation but it stumps me.
...Changing the names of fields in tables after creating other object
I just leaned about the naming conventions after I have created my tables,
reports and several queries. Can I change the name of fields in my tables
(to remove the spaces and give them unique names ie not just last name but
childlastname) without destroying the work I have done in queries, reports,
Positive Direction for Youth & Families, Inc. (www.pdfyinc.com)
Possibly. If you are using a newer version of Access, say 2003 or 2007 AND
you have Name Autocorrect, and all it's options, enabled, it MIGHT work. I
found it somewhat buggy.
Before you...extracting numbers from a text field and applying formula
here's my question...
i have a cell that has a text field of this type: '9-19' (a-b)
I want to create a formula that will subtract 19-9 (b-a)
and return the result as a number//
can anyone help?! is this even doable? I'd really appreciate it!
also, i'm very fairly new at excel so a detailed response with th
formula would really help!
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
=MID(A1,FIND("-",A1)+1,99)-LEFT(A1,FIND("...Conditional formatting formula with multiple criteria
Have been puzzling over this for a while now, and can't get it right!
Using conditional formatting, I want to:
If any of the cells in Column B are blank, and the cells in Column C are
blank, and the date in Column D is less than todays date - 3 days, turn the
cell bold and red
Can someone please help?
I have tried all different variations and just can't get it!
Your questions sound like you wish to pick up whether there is a blank
ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that
the date in a single cell in colum...Calculate data on 2 different worksheets
I have a very large workbook and for efficiency reasons I
have calculation set to manual. I need to calculate 2
different worksheets, but because they refer to each
other, I have to calculate them a couple of times to make
all of the formulas update correctly. I've tried naming a
Range3 = range1,range2
then I use Range("Range3").calculate
but the result if only one iteration thru the formulas and
the final result still needs one more calculation.
Short of putting all the data on one sheet, is there
anyway I can insure 2 separate worksheets are completel...Problems with add new field
I want add new field to the opportunityproduct object but I get the follow
Error in the Eventlog.
dmLog: Failed to grant access to the regenerated view OpportunityProduct
after inserting new attribute 'attributename'
I'm login as local administrator with crmroll 'systemadmin'.
Note, in other object's I can add new fields.
Why I get these Error and how can I solve it?
...Do all fields have "onchange" event script blocks??
I was wondering if there are onchange event handlers for all fields in
I need to automatically send an email from CRM when the "statuscode" of
an Incident changes to "Closed", which is a value that I added to the
I have seen onchange script blocks in other objects like the Contact
object for the "accountrolecode" field, but there doesn't seem to be
one for the "statuscode" field in the incident object?
Have I got this wrong, or do not all drop down lists have "onchange"
event handling script blocks??
Thanks in advance,...Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records
where the Zip codes match). The problem is that in one table, the listing
of zip codes, it is 5 digit zip codes. In the main table some of the zip
codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I
want all the records as long as the 5 digit zip codes matches.
I quess I could delete the - and the last 4 digits on the main table, but it
would be nice to retain the whole zip codes. Is there a way to do this?
to compare to the original ...mobilephone field
Anybody have idea why mobilephone field for lead object has max length 20,
but same field for contact object has max length 50?
Is it a bug?
without checking it sounds more like a simple design flaw ie someone forgot
to make both fields the same length
Microsoft CRM MVP
"Renatas" <Renatas@discussions.microsoft.com> wrote in message
> Anybody have idea why mobilephone field for lead object...