countif Access 2000
I,m trying to count how many numbers, in a column, that have a value greater
than zero. Any ideas anyone
In query design view, type something like this into a fresh column in the
[MyField] > 0
Depress the Total button on the toolbar.
Choose Count under this field.
If you wanted to do it in code:
DCount("*", "MyTable", "MyField > 0")
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Zab" <Zab@discu...How can I begin numbering pages(Pub) on first page after inside
I want to begin automatic page numbering on the actual third page. In other
words, the cover and inside cover as well as the back cover and inside back
cover will not be numbered. Can this be done?
What version Publisher?
Go to page 3, Insert, Section, Check Begin a section with this page. Start at
Insert page numbers, if you are viewing a single page spread you will have to
apply the page numbers on both sides.
Create a new master page. Apply to the last page and cover.
"bamatoni" <email@example.com....Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It
basically comes in as "33 %" but Excel registers this as text, not a
I have a cell that will be used to add the numbers, but since they are
text it doesn't work.
Given this information, is there a way to convert the imported data
into numbers. I would prefer to include this into my formula. The
potential numbers are:
0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A
I would prefer a function, again if possible, that could convert any
number. Please note, the space between the nu...Create new records from existing ones based on 'rule'?
I am trying to pull apart records I uploaded that are set up something like
MainField | Other Fields | AssociatedFIeld1 | AssociatedFieldN
There can be anywhere from zero to 6 Associated Fields. Trying to break a
new record apart for each e.g.
MainRecordX | Other Fields | WidgetA | WidgetB
MainRecordY | Other FIelds |WidgetC | Widget D | WidgetE
MainRecordZ | Other FIelds
MainRecordX | Other Fields | WidgetA
MainRecordX | Other Fields | WidgetB
MainRecordY | Other Fields | WidgetC
MainRecordY | Other Fields | WidgetD
MainRecordY | Other Fields | Wid...How can i use the dash character, "-", to sort data?
If i wanted to sort the info below into two different columns, how would I do
it?SOMRVLLE - SOMERVILLE
SOUTH - SOUTHERN REG.
ST. JOHN - ST. JOHN VIANNEY
ST. ROSE - ST. ROSE HIGH
ST. ROSE - ST. ROSE HIGH
STAF - SOUTHERN REG.
use 'Data - Text to columns' to split the content into two columns
"Val" <Val@discussions.microsoft.com> schrieb im Newsbeitrag
> If i wanted to sort the info below into two different columns, how
would I do
> it?SOMRVLLE - SOMERVILLE
How can I create consequent numbers in a spread sheet? I
would like to create a personalised invoice template for
my business. Everytime I open the file it should change
the "invoce number" N+1, like in a counter.
Thanks for your help,
Name a cell InvNum by selecting any cell and then typing in InvNum in the
space to the left of the formula bar or use insert>name>define
Then put this in the ThisWorkwook module by right click on the excel logo
just to the left of FILE and inserting this.
Private Sub Workbook_Open()
[InvNum] = [InvNum] + 1
Then, you get wha...Autofilter Wildcards Not Working on Number Column
Hello everyone...n00b question.
I'm using Excel 2003 to split up a daily spreadsheet between me and 2
of my peers. We split the orders based on the last 2 characters of the
order. The orders are all 7 digits and all numerical. Exp... 3215453.
For example... my number range is 00-33.
Every morning I have to scroll through the spreadsheet and manually
select my orders to obtain my daily workload, which can take up to 40
minutes. I tried using auto-filter with wildcards * & ?, but they
don't work. I'm typing my custom filter values as follows ?????33 and
*33, but they don't ...Finding if UserID/PW Exists in XML File Using XPath?
I have PW/UserID info as follows and would like to use xpath to find if a
userid and pw exists within the xml file. What Xpath query statement do I
need and what query command do I need?
Thanks for your help
BTW, where Can I find a good resource on using XPath
...Excel page numbering in the header
In a workbook of nine worksheets, I was able to get numbering like 1 of 9, 2
of 9, etc. as advised by Ragdyer. But there are two problems:
1. The numbering in the center of the header was got by using "page 1 of ?".
But the size of the fonts on the different sheets are different and I can
find no control to be able to adjust these both for size and bold.
2. If I seek to do the same in the spcial header and special footer, my only
choice is to use two icons next to each other--one is like a # sign, and the
other two signs. No matter how I try, I cannot get these two to give pag...Filter for 225 or 233 numbers #3
just like to say thanks for all your help, really appreciated.
I think I may have got it working by putting a 5 in the code
instead of ==OR(MID(A1,4,3)="233",MID(A1,4,3)="225
and the same for your one frank but does this mean that it counts fro
the left ?
sometimes the no.s come in like this
hoganc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1498
View this ...Random Number Non Repeating ...
I have the numbers 1 - 55 that I would like to display in a column randomly
without repeating. The numbers should be whole numbers only. Can you help
me? I tried following some of the other posts, but I couldn't get it.
Enter the numbers 1-55 in a column, and in an adjacent collumn enter =RAND()
and fill down. Now sort both columns by the RAND column and you'll have the
"a_ryan1972" <firstname.lastname@example.org> wrote in message
> I have the numbers ...Numbering, tab doesn't work
I have some numbered bullets and I use to be able to tab to ident to the
next level or shift-tab to back out a level, but now it doesn't work.
Is there a switch somewhere to turn this back on?
In the AutoCorrect dialog box, click the AutoFormat As You Type tab. Make
sure that "Set left and first indent with tabs and backspaces" is selected.
To display the dialog box:
- Click Tools | AutoCorrect Options (Word 2003).
- Click the Office button, and then click Word Options; in the Proofing
category, click AutoCorrect Options (Word 2007).
-- ...where do I find my 25 product key number on a pre installed PC
I bought my acer computer from tesco, it was pre installed with microsoft, I
did not get a CD with it and have looked everywhere on the packaging but I
cannot find the 25 figure product key number.
Look on the computer itself, it's often there.
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
"stainforth" <email@example.com> wrote in message
news:59673B2E-E271-4C04-A792-FAC52E039FD1@mic...How can my formula handle inserted rows?
I am using (more likely misusing) COUNTA to validate that all cells within
the specified range are populated, but I have a question regarding how to
handle inserted rows. Here is my current formula:
=IF(COUNTA(A1:A10, B1:B10)=20,"All cells within specified range are
However, if someone inserts a row between the 1 and 10, the contents in row
10 get shoved to row 11, and I would want to expand my formula range
dynamically to accomodate this insert. How would I accomplish this? Thanks
for any insight!
If that range were simpler--just a single area, the...Adding a -L to the end of a list of numbers in a column.
I would like to add -L to the end of each number in the column without doing
the manual process. How can I do this?
2...How to count existing records where StudentID = ThisStudent
I'm trying to count the number of classes a student has in their "Student
Schedule" table as we enter more records for that student. Assuming the
records are entered in the order of the student's preference, then we can
set the "Preference" field to be equal to the number of records. But I'm
new to Access and it doesn't behave as I expect. I get runtime error
'2001', "You cancelled the previous operation". Perhaps I need some kind of
post or 'save record' command?
I've attached the following code to the event called A...inserting PDF into publisher?
Hi, I'm putting together a magazine in Publisher and have been sent some ads
in PDF format. When I use the snapshot tool the quality seems to deteriorate
- is there any other way of getting the files in without losing quality?
Sarahs999 <Sarahs999@discussions.microsoft.com> was very recently heard
> Hi, I'm putting together a magazine in Publisher and have been sent
> some ads in PDF format. When I use the snapshot tool the quality
> seems to deteriorate - is there any other way of getting the files in
> without losing quality?
Use GhostScr...number list compare
How do a compare a number to a list of numbers to get the next highest and
next lowest number?
In article <6DBB1D07-4ABE-4885-8DAE-5552AC7603C6@microsoft.com>,
"jpmmschi" <firstname.lastname@example.org> wrote:
> How do a compare a number to a list of numbers to get the next highest and
> next lowest number?
Assuming that your list of numbers are in Column A...
Next lowest number:
Next highest number:
....where B1 contains the number being compared to. Both these formulas
need to be entered...making a cell fixed number to a input number
I am doing a caclculation. Some of the time I want to do it with a
percentage and sometimes with a dollar amount. If I punch in a dollar
amount I want it to come up with the persentage. If I type in the
percentage I want it to come up with the dollar amount. How can I make
the formula stay in a cell even if I punch something in.
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
And how do you tell .20 (%) from .20 (cents)
"fwday" <fwd...Advancing A Number With The Push A Button
I have an invoice that uses a button to advance the invoice number by
1. When I wrote it the numbers were just numbers, but since then a
letter has been added to the front of the number (was 17445 now
D-017445). My advancing code no longer works and I don't know why!
Here is the code:
Private Sub GetNextInv_Click()
Range("InvNoLast").Value = Range("InvNoLast").Value + 1
Anyone have an idea on how to fix it?
Any help would be most appreciated.
why not format the cell with the custom format:
"D-"00000...Phone Numbers Repeated
I have a chart in excel that looks like this
Ladd Design Koch Stephanie Ms. 156 Farm Rd.
New Canaan, CT 06840 2039663032 F2039660170
except in excel each piece of data is a different cell so in total i have 9
Co. Name, Last Name, First Name, Title, Address, Town & State, Zip Code,
Phone Number and Fax number. There are about 1700 Rows in total. And there
are many repeats as a lot of the data was copied and pasted from other
sources. My problem is these repeats need to be removed. The repeats are in
the phone numbers, if there is a repeated phone number the entire ...Numbers as Text
I am copying a column of numbers, formatted as Number w/ zero decimals, to a column formatted as Text. I am using Paste Special - Values only. The numbers are all four digits, such as 1101, 1105, 1111, 1123, etc. After the Paste, the numbers show up 'rounded' in the new (Text) column, with the last number always a 0. 1101 becomes 1100. 1105 becomes 1110. 1111 becomes 1110. 1123 becomes 1120. Any ideas why?
why do you want to put this numbers in a column formated as 'Text'?
> I am copying a column of numbers...LOOKUP function does not work with row vector of numbers
Here is a spread sheet on Excel 2003:
1 =1.11+2 2
The B2 cell is #N/A, although A1 is evaluated to be 3.11
It seems if you match against 1.1+2, it works.
Could anyone tell me where things is going wrong?
What are you trying to do?
Do you want to return the *last* (rightmost) numeric value in the range?
If so, try this:
Microsoft Excel MVP
"JRaSH" <email@example.com> wrote in message
> Here is a...print batch number on receipts
is there a way to add the batch number to a receipt?
Returns the number of the batch currently being processed.
> is there a way to add the batch number to a receipt?
...How to create a formula to remove multiple . in a numbers?
Ex: 11.2101.1030.0000.0000.1982 how can I remove these dots in multiple
numbers on a spreadsheet?
If you really mean a formula, meaning you will retain the dotted values and
display the number without the dots in another cell, then use this
If, on the other hand, you are trying to modify each cell's value in place,
then you can use Edit/Replace from Excel's menu bar... put a dot in the
"Find what" field and leave the "Replace with" field blank (empty).
Rick (MVP - Excel)