If value in cell Then perform function #2
Many thanks for the solutions - they both work!
emm8080's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1185
View this thread: http://www.excelforum.com/showthread.php?threadid=27483
...Merging from a query
I have a database I am working on that has a bunch of addresses in it. I need
to be able to select a chunk of dates to be sent to word for a mail merge. I
need a way to pass the date criteria data (from a message window) to a query
to get the right records, and send the data the query returns to word in by
activiating one click event. What is the best way to do this?
...TEXT Function: please help
I have a sheet that keeps track of MAC to IP adresses. I've created a
formula that will output the required format for DHCP server import.
The formula was quite long and it was suggested to use the TEXT function .
Thanks to Bob Phillips and my friend LavaDude my rather legthy formula
has been reduced to the folowing, but it doesnt work correctly.
hardware ethernet "&TEXT(D2,"...Append query bloat
I have an access database where I use an append query quite often. I will
use this same query over and over by changing the table I'm appending from,
then I save the query. During the save process, the database bloats from
~50KB to ~150KB. Our company recently upgraded to 2007 and that is when I
noticed the problem. Is there a way to stop this from happening? I can
manage it by compacting and repairing after each save, but recently I added 4
tables in one day, so this was a pain.
Don't even worry about it. An Access file can be up to 2 GB in size. 150 KB
...Query Problem 03-27-10
I have 2 tables, tbBaby and tbVaccineInjection. tbBaby stores the babies'
personal data like Name, BirthDate etc., tbVaccine stores VaccineName,
DoseNumber (1st, 2nd, 3rd, etc.) and InjectionDate, etc. These 2 tables are
one to many in relation, because each baby may have several vaccinations on
I'd like to build a query to retrieve babies with their most recent
injection date, i.e. only one record for each baby with latest injection date.
my query is as follow
SELECT tbBaby.*, tbVaccine.*
FROM tbBaby INNER JOIN tbVaccine ON tbBaby.ID=tbVaccine.B...Does SQL Run Behind Queries?
When I run a query does Access, "go away" and run the SQL for what I see in
If the answer is, "yes" is there anything that I can switch on to see the
SQL that it is running?
What I would like to do, if it is possible, is to see the query in the GUI
and, at the same time, see the SQL that is being used to select the data from
the database; that way I can understand things (exactly what is happening)
more clearly and more quickly.
TIA for any replies.
> Access 2007
> When I run a query does...I have a question about using a function in a form
I have used the "workingdays2" function in a query to calculate the number
of working days between two days like so...
I have tried using this in a text box in a form but the calculation won't
Another stupid moment. Date4 was earlier than Date3, duh!
> So I assume WorkingDays2() is a user-defined public function?
> SLA_Date3 and SLA_Date4 are fields on your form?
> Try putting = sign first:
> &quo...Database Query #4
I have created a data table in a worksheet and named it “data”. Then I
created a separate worksheet, in the same workbook, and created a database
query of the data table, by using Data…Import External Data…New Database
Query…Excel Files* and defining the database name by browsing to the same
workbook. The query worked fine. However, when I moved the workbook (which
included the data source) to a different folder and tried to refresh the
database query, I got the following error message:
“[Microsoft] [ODBC Excel Driver] The Microsoft Jet database engine could not
find the object ‘data’...DNS clients Does not query the secondary DNS
I have 2 w2003 Dcs - both DNS servers Primary and secondary.
I have Clients running XP static Ip and in both DNS servers IP is configured
as Primary and Secondary DNS.
My Primary DNS server went down, all clients are not quering the secondary!
using NSLOOKUP it showed that clients are still querying the primary with a
tomeout error !! all name resoultiond stoped in the organization!
NsLookup does not automatically fall through to alternate DNS servers,
it is not a good way to test server redundancy.
If you ping a host, which will use the DNS Cl...Combined Functions
I am trying to streamline my work. I am trying to apply two functions
to the same data at once. Is this possible?
How would I combine the above two and have the result appear in one
fncuis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26466
View this thread: http://www.excelforum.com/showthread.php?threadid=397792
Good afternoon fncuis
---------------------------...Problem with a Function
I had posted earlier some formulas I am trying to reduce to functions
and didn't get any response. I guess it was too hard or maybe can't
Anyway, I'm taking a small piece of it and trying things on my own. In
a cell I have the following function:
The first argument is week number corresponding to a sheet name and
the second arguement is the position of the player corresponding to a
cell on the sheet. The function returns an error (#Value):
Here is the function definition:
Public Function QBRushYds(W, Q)
' Calculates Points for R...Word 2007 Find/Highlight Function
In Word 2007, the Find function allows you to find and highlight words or
phrases in a document. However, saving the document with those 'found' words
highlighted does not save the highlighted text. Additionally, if searching
for multiple different words in a document, when you search on a 2nd or
subsequent word, the 1st or previous 'found' word highlight all disappear.
This functionality was possible in Word 2003 and XP environment.
Question: In Word 2007, is there a way to highlight and keep highlighted
multiple word searches/highlighted words and save the do...Lookup function doesn't work
I am using a lookup function to compare the results in sheet1 column 1 to
sheet2 column 2 and return the results of sheet2 column2. The function
seems to work for the first few entries, but then returns the wrong results
for some of the other entries.
I am using "=LOOKUP(A7,Sheet2!$A$2:$A$16,Sheet2!$B$2:$B$16)" and it is
copied down a column of text/numeric entries in the first sheet.
Has anyone else had problems with this?
-- Frank L
Are the values in the lookup_vector
i.e. "Sheet2!$A$2:$A$16" in ascending order ?
Please respond, in new...Calculate difference in rows in a query
I have a query that calculates how much revenue is claimed by month per job
but then I have to take the new month less the prior month. The problem is
the data is in row format. I don't know how to subtract February from
January, March from but don't know how. I know how to do it in Excel but not
Access. Please help...
Order Month JTD Clm Variance
101026521 January $511,525 $0
101026521 February $511,525 $0
101029438 January $1,238 $0
101029438 February $3,713 $2,475
101033168 January $21,465 $0
101033168 February $51,460 $29,995
101034011 Janu...How to use colmn headings in paramter query
Sorry if this has been asked before, but I am not able to locate it if it
has. But here is my delima:
I need to be able to select the salesperson sales based on the month (1-12)
from table 1. I* can create a parameter query, but am not sure hwo to query
onlya certain month.
ID salesperson 1 2 3 4 5 6 7 8 9 10 11 12
1 joe 12 0 0 2 12 0 1 1 5 7 8 10
2 steve 2 5 6 18 0 0 2 3 4 11 12 1
select * from table1 where salesperson =joe
that is where I get stuck. I need to be able to select a column he...Preserving formulas when using sort function
If I have a two columns of data say, A and B and the are added together in
rows in Column C eg
A1 + B1 = C1
A2 + B2 = C2 etc
If I then move the contents of cell A1 and put it in say A23 the value of C1
does not change because the C1 become A23 + B1.
However, if I sort Column A then the value of C1 changes and the formula
appears to be wrong because it now takes the new value of cell A1
How can I make sure that when the sort takes place that wherever the value
in A1 ends up it is still added to B1 to create a value in C1?
Any help appreciated
Why aren't you moving th...Extracting invoice data from GP 9 using a SQL query (ODBC)
I'm working to extract invoice detail information from a client's GP 9 MSSQL
database, with the goal of being able to mock up their invoices in external
systems. Just to keep things interesting, they aren't using GP to it's
maximum ability - quotes are generated from another software package, and
when converted to a sale, minimal data is entered into GP and processed (such
as shipping address, job name, job number, PO number, and occasionally a line
it of what appears to be free-form text. I have hard copies of several
invoices, various web resources, and access to t...VBA Function to explode string
I am wanting to know hbow I would go about getting the following 3
Cells A1, A2, A3:
1-4,6,8 50 S1B1::
Into a form that looks more like this
::A1 A2 A3
1 8.33 S1B1
2 8.33 S1B1
3 8.33 S1B1
4 8.33 S1B1
6 8.33 S1B1
8 8.35 S1B1::
How it does the rounding (and on which one), it does not matter!
tomjermy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24666
View this thread: http://www.excelforum.com/showthread.php?threadid=382414
You haven't exp...Query Does Not Count "2" Records
Good Evening Everyone...
I have a bit of a logical puzzle that I need to solve. I have various
queries which have various criteria for them. One query involves
retirees or spouses being under the age of 65 and the other involves
retirees or spouses being over the age of 65. Both the retiree and
spouse are listen in the same records and on one line of the master
table and queries. The situation that I am running into is that I can
have a retiree who is over 65 and a spouse under 65 and the record
comes up twice in the two different queries b/c it meets the criteria,
which is perfect. What...Web Query Help...
If I try and use a web query with the following site
I get a message that says the web query returned no data. I click on the
arrow right by player and it highlights all the players names and their
stats. Anyone know why it doesn't import the data into excel? Is there too
much data? Thanks
Works fine for me, although one have to do some editing since it imports
some trash as well (I would import it, then select the table itself from the
import and copy and paste into a new sheet, then import the next 50 and...Rand() function
Excel spreadsheets using the rand() function in Excel
2000 are not behaving the same way in Excel 2003. The
distributions that were "almost-normal" are now
significantly non-normal. Does anyone have a pointer to
how to fix this problem ?
The RAND function returns negative numbers in Excel 2003
Otherwise, RAND() in XL03 is *far* more "almost-normal" than XL00 or
Description of the RAND function in Excel 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;828795...I need to compose a ticket for a school function.
Mary Sauer MSFT MVP
"nikk" <firstname.lastname@example.org> wrote in message
...Formatting Result of Custom Function
We are trying to format the result of a custom function. Is this possible.
The code below does not do this. Here we are trying to format the letters
"cde" in the "test" function below using Macro1
Function test() As String
test = "abcdefgh"
On Error GoTo iErrors
With ActiveCell.Characters(Start:=3, Length:=3).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
...COUNTIF Function 04-19-10
I am trying to use the countif function by rows and it is not calculationg
correctly. I try to drag the formula down and it just repeats the same value
as the origin cell. Is there some type of malfunction with tis function?
It would help immensely if you had posted your formula for us to evaluate.
Without it, my best guess would be that your formula currently looks
when it should be:
"csmith13" <email@example.com> wrote in me...Fetch XML to Query Expression
I'm trying to convert the following Fetch XML statement to a Query
Expression to post using the RetrieveMultiple webservice:
<attribute name="name" />
<link-entity name="systemuserroles" from="roleid" to="roleid">
<condition attribute="systemuserid" operator="eq-userid" />
I haven't been able to find an documenta...