I have a field called destruction date in a table which I enter a year, for
example 2002. I also have a field called years which is a selection from a
table years. In my query I am adding the destruction date (2002) plus the
years field (5) in a field called expression. My problem is that if I do not
select any years in the expression field it show the original destruction
date (2002). How can I keep this addition function working but if there is
no Years selected if remains blank. Thanking you in advance.
Confused I am.
If you do not select any years to add to the destruction ...Pivot table and value range
I have built a simple pivot table with 2 columns : Amounts and count
amount/Nb of amount
Would it be possible to have a range of value in the pivot table to
have a result like this:
Thank you for your help
I would add a column to the raw data that categorized the amounts.
Then drag this down the data. And use it as the field in the pivottable.
...Multiple Cell Values, please help
I have a problem and i would be very happy if someone could resolve it.
This is my question to you: a would like to have one table in one sheet
that has, let's say 20 columns and 80 rows. And now somewhere on the
sheet there is something like a autofilter function in which I choose
which date i want. And now i choose one date and this table is blank
and i write things in the table,now i choose some other date and again
i write things in this table and so on. So I want to have like
20,30,40... tables at the same spot in one sheet and just to pick one
date and the table has values for t...Smartlist Search Query Doesn't Produce Data
Using the payroll history trx (PHT) option in smartlist, we attempt to search
for transactions using the audit control code column name in the search
definition. If we search on UPRCC00000001 thru 7, records are returned. If we
set the filter to UPRCC00000999, no results are displayed.
UPRCC0000099 has been verified as a valid control code in the system. I've
found some KB articles that refer to other smartlist item searches not
working, but non refernecing PHT and nothing about intermittant search
Any help is appreciated!
...SELECT @@IDENTITY in A2010 with Triggers
Over on StackOverflow someone was asking about how to get the last
Autonumber value, and I suggested SELECT @@IDENTITY in the same
connection as the insert is made, and said "just like SQL Server."
Someone responded that in SQL Server you should use the
SCOPE_IDENTITY() function instead, so I looked it up:
....and that made me think that in A2010 with table-level data
macros, SELECT @@IDENTITY might end up not being reliable if the
insert causes a trigger to insert a record in another table (as
described in the MS...How can I select pivot table row totals to fill color them
I have a pivot table with total rows. Other people in my team at work can
hover over the left edge of one of the total rows and all total rows get
selected so that a fill color can be applied. It does not work for me
From the PivotTable tool bar, Click on the PivotTable dropdown, select -
"Ruth5155" <Ruth5155@discussions.microsoft.com> wrote in message
>I have a pivot table with total rows. Other people in my team at work can
> hover over the left e...Query 08-24-07
I have two tables. One contains contigent employees the other full-time
employees. I would like to create a query that combines these two tables, so
the contigent and full-time employees are all on one report. How can I
Thanks for your help
Is there a business reason these two tables are kept separated? For
instance, the data elements you keep about "contingent" employees differs
from the data you keep about "full-time" employees?
If you have two identical tables (same structure), one for each "type" of
employee, your data is structured ...Change the default value for F11
The default value to automatically create a line chart is to highligh
data and press F11. I would like to change the line chart to a pi
graph when hitting F11. Any suggestions
Message posted from http://www.ExcelForum.com
To change the default chart type:
Select a chart
Choose Chart>Chart Type
Select the Pie chart type, and one of the subtypes
Click the 'Set as default chart' button
Click Yes, to confirm
ih8elvis < wrote:
> The default value to automatically create a line chart is to highlight
> data and press F11. I would like to change the line chart ...Limit Values
I have the following issue which I am not able to solve. I want to limit the
value possibilities of a cell to #,0 and #,5 For example: 8,5 or 7,0 but not
6,2 and so on. I tried to do it with validation but it doesn't work. Hope
anyone can solve this out for me.
assumed the cell A1 need to limit the number to ( 0,5,7,8)
Go to data | validaton | allow:custom | formula : =3DOR
(A1=3D0,A1=3D5,A1=3D7,A1=3D8) | ok
On Nov 30, 2:33=A0pm, Noepie <Noe...@discussions.microsoft.com> wrote:
> I have the following issue which I am not able t...how do i increase the value of numbers in a column by a percentage
i have an excel stocklist with 6000 items on it ,i just need to know how to
increase the whole price list column by a percentage , thanks
Say we want to increase a column of values by 6%. In an unused cell, enter
1.06. Copy this cell. Select the column of numbers. PasteSpecial > Multiply
Gary''s Student - gsnu200909
> i have an excel stocklist with 6000 items on it ,i just need to know how to
> increase the whole price list column by a percentage , thanks
1. In cell H 1 I have:-
2. In cell I 1 I have:-
...Pass through query and variables
I'm currently building a pass through query which ties to an IBM Iseries ODBC
driver. Is there any way to include an "in" statement from another non-pass
through table or query in the same access database? For the sake of example,
let's say I have a static table of user names (name: tbl_users). I have a
pass through query built that I would like to only return results based off
of the tbl_users content. What would be the syntax to accomplish this?
In theory, the logic would look like this but I'm not sure of the exact
Where O...Queries & Reports
First I will enter info into TBL Radio Receipt, from there I want the
information to jump to TBL Radio Info and prompt me to complete this table.
Then it will jump to either TBL New Radio Information OR TBL Repair
Information. This will need to be completed. How does this happen, I mean--
How do I create the relationships to reflect this path?
PS How do I attach a pic of my problem?
Jeannette Cunningham already posted a response to your question back on
12/21, but perhaps you didn't see/understand it so we can try again.
Your question is a little hard to understand, so...parameter value in command line switch
I have a database that is opened from an AS400 database using a command line.
I would like to pass the record ID the user is on into Access to open a form
on that record. Is there any way to pass that info in the command line? If
so, how do I capture that on the access side to use as a parameter?
You can use the /cmd switch to pass a value, and the Command function to
To auto-fire this when Access starts, use the AutoExec macro, or code in the
Open event of your startup form.
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbr...Update workbook each time a different worksheet is selected
I need to update a workbook each time the user goes to a new worksheet either
by selecting the tab for that worksheet or by hyperlink. How can I to this
without using a macro? (using macro is ok if necessary but I am trying to
keep things simple)
"Update" means what?
More details please.
Gord Dibben Excel MVP
On Tue, 13 Dec 2005 13:50:02 -0800, DJL <DJL@discussions.microsoft.com> wrote:
>I need to update a workbook each time the user goes to a new worksheet either
>by selecting the tab for that worksheet or by hyperlink. How can I to this
Have the specific knowledge base instructions.
Instructions state to in the chart, select the data series, then via Format
menu, Axis tab, click the secondary axis.
I can't seem to select the individual data series. It tends to select all
the data series and the Axis tab grays out the secondary axis toggle.
I have a simple chart requirement
total # projects
totals $ value of projects
need left axis to scale to # projects
need right axis to scale to $ value of projects.
these data are divergent and doesn't display well in a single y-axis chart.
Here is another way to sele...Using Access form to assign values of variables in an Excel VBA program
I have created a VBA program for Ms Excel.But,to get desired results,
every time, I go into the code to change the values of variables.Is
there a way to get the values of variables stored in an Access table?
YOu need to do a search for ADO method (ActiveX Data Object). There
are plenty of example of code on the web. You need to add two
references in your VBA to use the method.
from VBA menu tools - References
1) Microsoft Access XX.X object library
2) Microsfot ActiveX Data Objects X.X library
Use the latest versio of the two libraries on your PC.
You will make a conn...Append Query problem
I have created an append query in vba code which is inside a For...Next loop.
I create an SQL statement to find matching related records in one table, and
then insert these matching records into a temporary table. I repeat for each
value of unique primary key that is provided (in an array).
The issue I have is that the append query only works for the first matching
record, not the subsequent records. I have verified that correct SQL
statements are generated during each iteration of the loop, but for some
reason no inserts occur after the first one. The temp table is cleared at the
beginning o...This item's make/buy type has not been selected...
hello - we are setting up inventory and PO and when testing our setups, after
we enter an item on the PO we get this error. It will not let us use the
item until you go to the item engineering screen. We do not have
manufacturing registered. Any help is appreciated.
Hi, Fixed asset part is really new to me and this would be a very simple
question.(However, I could not find 'how to' from the manual.)
Several assets have been recorded to GP manually last year.
Therefore, I am going to update the asset value directly.
Which window should I need to use?
I tried to change at the window : "cards>fixed assets>book," but I saw the
" IF LTD Depreciation is entered, Depreciation to Date cannot be greater
than the last day of the year for this book."
* How can I find the last day of the book of each asset?...Select Statement in query to append to a table
I have a table with the following
Master Stock Code
and I want to append this information to a table, kind of ike a cross tab
query but I want the value of the header ID to be in the column not as a
column name. I have a max of 12 header id's, so I have 12 column names in
the table i want to append to (ie. Hdr1, Hdr2 etc)
There could be more than one Header ID per stock code.
WHat is the proper syntax for the select statement in my query so I don't
get duplicate rows per stock code.
...Union Query with two crosstabs
Hi…I attempted to do a Union Query with two crosstabs. I received an error
“Characters found after end of SQL statement. I then read some posts but
couldn’t find the solution. It appears that it is possible and I read
something about not having two TRANSFORM statements but it wasn’t clear to
me. I have the same number of fields and defined the PIVOT part by IN(“x”,
“y”, “z”) and used UNION ALL. What else do I need to do to join the two
Thanks for any suggestions.
Do you have a semi-colon after the first query? If so that can cause the error?
If the two cr...Pass-Through Query problem in access 2007
I have an Access 2007 SQL Pass-Through Query to MS SQL Server 2000
that executes a stored procedure with parameters. The SP just inserts
some rows in a table and returns an integer as a return code
indicating success or failure. When the "ReturnRecords" property is
turned on so the code is returned to the client, it appears that the
query runs twice since the table shows twice as many rows inserted
than it should. When the "ReturnRecords" property is turned off, the
correct number of rows are inserted. This is a strange bug. Any ideas?
I'm using an ODBC connection. Th...Secondary Axis Values Disappear
When I elect to "show data table" my secondary axis values disapper. As soon
as I uncheck "show data table" in chart options, the axis values reappears.
Can't I have both (secondary axis values AND data table)?
Excel's chart data tables have many limitations. It depends what type of
chart you have. Some types won't let you have a data table at all, some only
for data on one axis. Some, like a column chart, will let you have a data
table even if you have both primarty and secondary axes. If you have a
combination chart,...Query Question... 02-25-10
I have filenames listed in a DB with '.pdf', I would like to to run an update
query to loose the '.pdf' since everything is scanned as a pdf. There are
hundreds of entries. Can someone give me a hand with the script?
In an update query AFTER BACKING UP DATABASE use this --
Replace([YourField], ".pdf", "")
Build a little, test a little.
> I have filenames listed in a DB with '.pdf', I would like to to run an update
> query to loose the '.pdf' since everything is scann...Selecting property pages with a tree control
I'd like to construct a dialog box similar to a property sheet,
except that instead of selecting pages using tabs, the user chooses
the page by selecting entries in a tree control on the left side
of the dialog.
Is this within the bounds of possibilty, or better yet, been done
Simon and Garfunkel lyric snippets for sale. You'd better hurry up
and order one, our limited supply's very nearly gone.
Nish [VC++ MVP]
"Mark Robinson" <firstname.lastname@example.org> wrote i...