Numeric currency to words
I would like to use a user created function to read a cell containing a
numeric currency value and put it into words, ie. $103.40 is One Hundred
Dollars and Forty Cents. Any help? Thanks!
Here is one way (you would want to use "Dollar" as the second argument to
Go into the VB Editor (Alt+F11) and add a Module to the Workbook
(Insert/Module from the VBA menu) and then paste in all of the code
appearing after my signature into the Module's code window. You can call the
function from your work sheet like this =NumberAsText(A1) where A1 is
assumed ...Excel Query #4
I have a list of around 50 values of which around 40 of
them make up a specific value. Is there a way that I can
find which cells make up this value?
Value = 7
Therefore value is made up of A2 + A3.
Thanks in advance
I can' find the link, so will have to post the code.
You may have to play with this to match your setup (Dim
statement), but in order for it to run do the following:
In B1, enter your target amount
In B2, enter the # of numbers to use
In B3 thru B#, enter your numbers from which to select
Leave Col C open for a he...Query Criteria 02-10-10
I have a query in which I only want the top 10 stores for item velocity for
multiple groups. I have used the top value and max criteria but it only
brings back the top or max for the entire dataset, not for each group within
the data. What am I missing???
Post your table and field names with datatype that you would use in the query
along with sample data (need not be real but workable).
Build a little, test a little.
> I have a query in which I only want the top 10 stores for item velocity for
> multiple groups. I have used the top...Database text converted to numeric
I Imported data from a SQL Database, it is numeric and alpha numeric but
mostly numeric numbers (Serial numbers). I need to compare this list to
another Excel list containing the same information but with some
numbers missing. I pull this into Access and when I try and compare
them the error is that the format has to be the same.
I changed the format of the list I imported to Numeric (most of the
numbers is only numbers) but now I have to press F2 and enter on every
single number (database is only 227500 lines) to convert it to a
I thought it was because it still saw itself as a Dat...Queries fromm ACC97 to ACCXP
I have many queries like 'select foms!form1!datestart as Expr1, ...' which
worked fine in access97. When I do the same in AccessXP I can see nothing in
field Expr1. Or sometimes I can see 'Chinese' characters.
The problem is only when 'foms!form1!datestart' is datetime data.
Why it doesn't work in AccXP?
When I try to add 'foms!form1!datestart' to parameters it works only when
its datatype is string.
Any suggestions welcome
I guess the first thing to do is to check the date/time formats and
regional settings on your comp...auto numbering in a query
I need to build a query that have in a field auto numbering and in a
is it posible?
Is this to produce data that will be viewed in a report, by any chance?
"thread" <email@example.com> wrote in message
> Hi all,
> I need to build a query that have in a field auto numbering and in a
> certain formating
> for example:
> is it posible?
On Apr 5, 2:42 pm, "BruceM&q...LDAP query help
What would be the correct syntax for an LDAP query that shows all members of
a particular distribution/security group? This would be under users and
computers --> View --> Filter Options --> Create Custom --> Advanced.
Thanks in advance!
Never mind, i just figured it out..
"jim" <jim@NOSPAM.com> wrote in message
> What would be the correct syntax for an LDAP query that shows all members
> of a particular distribution/security group? This would be under users
> and computers --> View --> Filt...Query to find all zip4's inside polygon
I need to find a way to run a SQL query to find all lat/long points in a
table which are within a polygon which is defined by several lat/long
For example, I have this table which includes ALL ~700,000,000 possible
Zip+4 positions with the corresponding lat/long values.
CREATE TABLE [dbo].[Zip4Coordinates](
[Zip4] [varchar](10) NOT NULL,
[Latitude] [decimal](12, 9) NOT NULL,
[Longitude] [decimal](12, 9) NOT NULL)
And say I have 4 lat/long points (moving in clockwise pattern on map) which
make up my polygon.
In this case, it's 4 lat/long points, but could be ...Query of items ordered by month
Using access 2002 I made a query in design view that lists all orders for a
certain item over a few years. There are only 3 relevant columns although
others were used to get this info:
order number, quantity, date
I'm interested in knowing the steps to alter this query so it sums up each
month. Ultimately I want a report that shows a bar chart with the quantity
sold per month (and maybe show the number of orders too)
Here's my current query I made in design view
SELECT tblOrderHdr.InvoiceNo, tblOrderDetails.Quantity, tblInventory.ISBN_B,
tblOrderHdr.UserID, tblOrder...List of where queries are used?
I have an access database which has many queries which are leftover from
various tests and temporary data extract needs. Is there a tool which can
tell me which queries are used in all reports, queries, macros, or modules
in the database?
You might want to take a look at the Total Access Analyzer (<
http://www.fmsinc.com/MicrosoftAccess/BestPractices.html>). Or you can use
Access's own Documenter. Run it on all the objects in Access with maximum
reporting settings, and then export/save it to a file of some sort that can
be searched (text, PDF, Wo...Random Conversion of alpha/numeric text with letter "e" to exponen
OS - w2003, office 2003, desktop XP Pro/ w2000 Pro
My company uses a six digit alpha - numeric string for customer account
a few are all numeric, most are in the format: ##X###, where # = numeric and
X = an alpha character.
A few accounts, 101 out of 360, that contain an "e" as the alpha character
are converted to an exponential by Excel. the other 259 are not. These 101
accounts where the first accounts created using "e", the subsequent 259
accounts are not converted by Excel.
Anyone have a clue as to what is going on and how I can stop it?
--...what is wrong with my query?
I have been using this query:
SELECT table1.Name, table1.activity, table2.wann
FROM table2 INNER JOIN table1 ON table2.id = table1.id
WHERE table2.week = False And myDate = DateValue(" 22.11.2007 ") And
table2.morning=true and table2.done =false and table2.lfd_nr
in ( select Lfd_Nr from persons where deaktiv =false and GroupID = 2)
ORDER BY table1.Name;
But now I get as the result also a person who belongs to group1 (GroupID=1)
The problem must be in the part "table2.lfd_nr
in ( select Lfd_Nr from persons where deaktiv =false and GroupID =
I have a lease database which I need to input monthly rents through the
lease term based on an annual lease figure. Example:
Lease 1 - Lease term April 1, 2005 through March 31,2008
Annual Rent: $120,000 through March 31, 2007
Increases: April 1, 2007 to $130,000 through March 31, 2008.
Is there a query that can do this for me?
Any help would be appreciated. Thanks in advance.
On Oct 26, 12:19 pm, D. M. <D...@discussions.microsoft.com> wrote:
> I have a lease database which I need to input monthly rents through the
> lease term based on an annual l...SUMIF with Mutiple Ranges & Criteria
I'm doing a sales report and have a single sheet with all my call/resul
metrics on it.
I want to use SUMIF (I think ;) ) to calculate the cells in
particular column if a cell in a specific column has a specific tex
entry as well as a different text entry in another column (the tw
columns are region and district). I know how do do a singl
range/criteria in a SUMIF statement, but I don't know how to ad
multiple SUMIF range/criterias.
So basically here is what I am looking for:
Sum a particular column G if column F equals "East Enterprise" an
column V equals "CSI"...sorting alpha numerics
I have a list of documents that I need to sort by title. Example
After I sort, the list looks like this
How do I make it take the entire alphanumeric into account?
Let A2:A4 house the sample to sort.
In B2 enter & copy down:
Sort A2:B4, with column A in ascending order and column B in descendin
> I have a list of documents that I need to sort by title. Example
> After I sort, th...text form to numeric ?
E.g A cell contain 1233- but the value is actually a numric value whereby
the "-" behind is a negative sign, wat function do I use to convert it to a
numeric form causing it to to become -1233 ?
One way, assuming that the values are mixed (some with/without "-" sign)
and that the values are in colA, which is formatted as text;
"crapit" <firstname.lastname@example.org> wrote in message
news:OSAgKf$1DHA.2324@TK2MS...Code for Criteria in a Pass Through Query
I have read all of the posts on this forum about Pass Through queries and I
just do not get it. Does anyone have any sample code of how they used data
on a form as criteria in a Pass Through query. Particularly for date ranges.
I have a Pass Through query that looks like this.
select [detail].[sd_term_cntry], [detail].[sd_datekey],
[detail].[md_tran_amt1], [detail].[sd_key], [detail].[sd_resp_cde],
from [detail] (nolock)
where ((([detail].[sd_term_cntry]) <> '999')
and (([detail].[sd_term_cntry]) <> ' ')
and (([detail].[sd_...Rules queries
I currently use Outlook Express for emailing, with Office 97, but I think I
need to move to Outlook97 now because ...
I regularly receive 3-5 important emails per week from each of around 110
(specified) people - so around 330-550 emails per week (plus about the same
number again of spams and other miscellaneous emails). For all the emails
from the 110 specified people I want to do two things:
1) Print them
2) Maintain an electronic copy of the email in a specific folder (different
folder for each person - so 110 folders will be set up).
I need these two things to happen automati...Clear Unbound Text box used as input parameter to query
I've looked at everything I can find, tried all the suggestions but still
can't get this to work. I have a form that opens blank. You input your
parameter into an unbound text box then press a command button that performs
a requery of the database and returns the results in a continuous form. When
the results are returned, the parameter is listed as part of the detail in
the form so I want the parameter box to be cleared out and ready for the next
My command on the Search button looks like this:
Private Sub SearchEPICode_Click()
...Can multiple criteria be used for IF formulas?
I am using the below formula to calculate percent of change from one year to
F3 being the amount spent in previous year
U3 being amount spent in current year
The question I want my formula to answer is: If F3 and U3 have a quotient
greater than zero, then calculate. However, I also want it to yield a 100
answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are
Please see responses to your later reposting.
----- original message -----
> I am using the b...COUNTIF for two criterias
I would like to use the countif function but using two criterias.
I want to count the times the string X appears on column B for
certain criteria in column A.
In this case I would like to count the X's matching the value in colum
A = 1, the result should be 3.
Does anyone know, how to do this?
Thanks in advance for any help
Message posted from http://www.ExcelForum.com
>I would like to use the countif function but using two
crite...Merge different queries
I have two tables where I enter my stock trades as well as foreign
exchange (FX) trades. The two tables contain different fields as the
trading instruments require different entries. I then created two
seperated queries in order to calculate the profit from each stock and
each FX trade. I need two queries as the calculations are different,
Now, I would like to merge the two queries in order to see my overall
profit and losses no matter if the trade is a stock or FX trade. That
is, in the final query I would like to see the trade ID (which is the
primary key in eac...convert alpha to a numeric value
I received data from a user where negative numbers are
sent as : "31Neg" for a "-31".
Can you suggest a way to create a real negative number.
Thanks in advance for you help.
You could use a macro
For Each c In ActiveSheet.UsedRange
v = c.Value
If Right(c, 3) = "Neg" Then
c.Value = Left(v, Len(v) - 3) * -1
Not very efficient though, so if the sheet is huge it will take time.
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
...Parameterized query in form
My form exports a query. The query needs to select some or all "MICAP"s and
some or all "Project"s. My form has an option box [optMICAPS] and another
option box [optProjects].
When the option radio button is cleared, a combo box is made visible for
selecting a MICAP or Project (cbMICAPID, etc.)
The underlying query has a "where" clause like:
WHERE MICAPID =
Similarly for the Project selection.
The MICAPID is numeric, as is the ProjectID. When th...Numeric -> Text conversion
I've got a date field, and have extracted the month using the MONTH
I want to put the month into a text field, two digits in width. e.g. 07 for
July, 09 for September, 12 for December.
I don't want it 'formatted' so that it looks like two digits. I want the
actual value to be two digits.
Any quick solutions ??
Using Office 2000.
Followup to newsgroup only, please.
"Mike Gorton" <email@example.com> skrev i en meddelelse