Conditional Formatting not holding in pivot table querying Access
I have created a pivot table in Excel 2007 based on an Access query. I then
created some conditional formatting. My values in the pivot table are
percentages. I have red, green and yellow based on what the percentage is.
Everything is fine until I refresh the pivot table. The pivot table size
does not change (No new rows added). It wipes out all of the color coding
though. When I select a cell in my pivot table and select Conditional
Formatting, the rules are still there but there is not color. I originally
did this file in Excel 2003 and did not have this problem. Can some...parameter query
Hi, I need help to finish my Query, I want to let the user put a serial
number to get it or put several serial numbers or collect it all
so I did this ...
(',' & [Enter SN's] & ',') Like ('*[, ]' & [Requisition Lines]![SN] & '[, ]*')
this is work to give a specific serial, but if I want let the users show all
serial numbers by clicking enter without write any number what is missing in
I appreciate ur cooperation
Try entering the following as the criteria:
((',' & [Enter SN's] & ',') Like...if, and statement
I need to do an if and statement something like this-
if A1=1 and A2x(A1)1>10 then no more than 10, but
if A2=2 and A2xA1>25 then no more than 25
so if A2= 12 and A1=1 then the result will be 10
but if A2= 12 and A1=2 then the result will be 24
but if A2= 15 and A1=2 then the reult will be 25
I know it is a lot, but you can guys figure it out.
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------...help with SQL query for HQ
Need two queries.
1. to clear the bin location field for all items
2. to assign a numberical value for the bin location field for each of our
stores. ex. store 1 bin location field to read 1, store store bin location
field to read 2
any help would as always be greatly appreciated...
1. To Clear bin location Run this
UPDATE ITEM SET BINLOCATION=''
2. To Update the bin location with store id
UPDATE ITEM SET BINLOCATION=storeid
> Need two queries.
> 1. to clear the bin location field ...is it possible to set a persistant variable from a query?
I am trying to set a specific value to a variable to be retrieved on
another line in the query where a specific value changes then the
value from the variable will be returned, but so far am having no
luck. here is the code for the module that contains the variable and
the sql that I am using any help would be greatly appreciated.
Option Compare Database
Global GBV As String
Public Function init_globals()
GBV = Null
Public Function globalvar(ivalue) As String
GBV = ivalue
SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051...Custom Number Format #11
I want a format that will show "35.4" as "35.4 lb/ft" and "35" as "35 lb/ft".
The trick being the decimal is not shown for the second number.
I currently use two different versions depending on the number, but I would
like this to be automaticly done in the format.
Assuming 35.4 is in A1, in another cell, enter =A1&" lb/ft". Drag that down
the column to capture the 35 and change it to 35 lb/ft.
> I want a format that will show "35.4" as "35.4 lb/ft" and "35" as &qu...Macro and If Statement
Here's the situation: I have a worksheet that will have an "X" in
either cell F3 or G3 or H3. What I would like to do (via a macro), is
to examine F3, G3 and H3, and if there is an "X" in anyone of those
cells, to run another specific macro.
Run a macro (for example called "totalsummary")to determine...
If there is an "X" in F3, then run a macro called "summaryEL";
If there is an "X" in G3, then run a macro called "summaryHT";
If there is an "X" in H3, then run a macro called "summaryFA&quo...Downloading statement shows items to review but I can't find them (2007)!
The last few days, when I download my checking account statementm it
succeeds and shows items to review (4 yesterday, 5 today) but the
downloaded items are not showing up in the account. The last item in
the register is from 11/21 (5 days ago).
I have the current vesion (MSM Deluxe 2007, Version 16...1024). I've
been using MSM for many years.
Anyone have any ideas on this?
In microsoft.public.money, GarDavis wrote:
>The last few days, when I download my checking account statementm it
>succeeds and shows items to review (4 yesterday, 5 today) but the
>downloaded ...Cross tab query solution
I have table Named OutStationTab having the data as below field
EName - Text Field
OsDate - Date Field
OsAmt - Numeric field
I want to make a cross tab query in which
Column Heading- Expr1: Format([OsDate],"dd-mm-yyyy")
Note the data is entered from 15th Date of a month to 14th date of next
month. One Employee take 1 outstation charges only in a day.
I am able to make the crosstab query and its working perfect. Like I filter
the query (From 15-02-2010 to 14-03-2010), then made the CrossTab
query. It works perfect.
It does n...IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked.
I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried
Can anyone give me some help on what I've done wrong? I just get #NAME in the box
Thanks for the help in advance
Could it be that IFF should be IF?
I have a number of things I need to test in the query. I need to use IIF
statement with AND (for example in Excel you can do it),.
I need this
how to do it in access?
On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote:
> I have a number of things I need to test in the query. I need to use IIF
> statement with AND (for example in Excel you can do it),.
> I need this
> IIf (and(cat="H...Passing a value from subform or main form to subform query
I am using MS ACCESS 2003
I have a main form and a subform that pulls up an existing case for the user
to update the information already entered. The subform is designed and opens
up in form view not in datasheet view.
The subform knows which case to pull in based on the case number on the main
The textboxes on the main form are bound fields from a query. The user
enters an ID which is how the main form query knows which record to pull and
The textboxes on the subform are bound with data from a different query (so
I have a query for each form)
The que...XML query
Hi I am new to using XML, and have a bit of an issue. How do I define
how the XML file looks when I export a query from Access?
On Sep 12, 4:50 pm, rmorri...@davislangdon.com.au wrote:
> Hi I am new to using XML, and have a bit of an issue. How do I define
> how the XML file looks when I export a query from Access?
XML is a markup language:
That means, it is a plain text file, it uses tags.
When you export a query from Access 2003 to XML, several files with
different purpose can be generated.
1. A file with .xml exten...Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer.
I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query.
But I'm just a part time progra...IF statement question
Is it possible to test for more than 2 different conditions? ...example
total number of hours paid hours
5 hrs 5 hrs
10 hrs 9.5 hrs
12 hrs 10 mins 12 hrs
IF "total number of hours" <6 this will be "paid hours" .....
IF "total number of hours" >6 but <12 "paid hours" = "total number of
IF "total number of hours" >12 "paid hours" = "total number of hours" -.1
-----= Poste...Update Query 07-11-07
I have a link to a text file that is updated daily. I run an append query to
add any new records on the text file into a table stored in my database.
I am attempting to update previously appended records based on a field that
may change on a daily basis. The record remains on the report, but one field
goes through 4 changes through the records duration on the report. (linked
Day 1 Field 21 = "Prelim"
Day 2 Field 21 = "Prelim"
Day 3 Field 21 = "Submitted"
Day 4 Field 21 = "Finalized"
I attempted to set up an update query that es...Help Please
How can I update on an address list "Po address" name to read P.O. with out
changing the rest of the address. I just want to change the "Po to read
"P.O."? Any help will be greatly appreciated. Thank you.
SET Address = "P.O. " & MID([Address],4)
WHERE Address Like "PO *"
Addresses to be changed all start with PO and a space
If PO can be buried within the address (Route to PO Box 2132 Westchester)
then it becomes a bit more complex
SET Address =TRIM( Replace(" " & [Address],&q...iIF clause
Can you help me build the right IIf field in the query with the following
The field DDU consissts of :
To the above expression i must also add 0,001 if size = 205, etc according
to the following table :
However Acces does not accept my query, obvioulsy i have errors :
DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001)
Will you help me ?
...Excel printing query
How do you print an excel worksheet with row numbers?
<File> <PageSetUp> <Sheet> tab,
And *check* "Row & Column Headings".
Please keep all correspondence within the NewsGroup, so all may benefit !
"Linda" <Linda@discussions.microsoft.com> wrote in message
> How do you print an excel worksheet with row numbers?
File | ...What Does +IIF Do as Opposed to IIF?
What Does +IIF Do as Opposed to IIF?James Igoehttp://code.comparative-advantage.com/ AFAIK, there's no such function as +IIf in VBA. If I had to guess, I'd say that the + is simply arithmetic: add the results of the IIf function to whatever preceeded it.How are you seeing it used?-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<firstname.lastname@example.org> wrote in message news:email@example.com...>> What Does +IIF Do as Opposed to IIF?>> James Igoe> http://code.comparative-advantage.com/> On 7 Mar 200...IIF question
Hello. Doing a report that uses an IIF in a textbox that is text. If the
IIF is false I would like the text (mainly 1 or 2 words) not to be
underlined. How do I end the IIF so the text is not underlined?
I am not sure how you mean, but if you want this underlined based on the
true or false then use the properties box and choose the text decoration
item and write an expression in there.
"JohnE" <JohnE@discussions.microsoft.com> wrote in message
> Hello. Doing a report that uses an IIF...Changing Background Color in "IF" statement
Is there a way to color cell background from within an "IF" statement ?
not with a formula. But use 'Format - Conditional Format' for this
Paul Pruchnik wrote:
> Is there a way to color cell background from within an "IF" statement
Not without using Conditional Formatting or VBA.
Formulas alone cannot change cell formats.
You can place an "IF" statement in CF "formula is"
Or perhaps just a &quo...lookup with 2 criteria #2
works great for getting me the correct acct #. Thank you again Frank!
But now I need to take it a step further... The data that I'm working with
will eventually be imported into another database program where we already
have some of this data. We do not want duplicate data so I need to delete out
what has already been input to the database.
On my main sheet, cell P2 contains the month # for that invoice.
On Gaps page my columns look like this:
A B C D E F G H I
Site Fuel Acct Meter Feb'04 ...Money 2004 does not see downloaded bank statement...
I just upgraded (a couple of weeks ago) from 2003 to
2004. I am running Windows XP Pro. In 2003, I used to use
Internet Explorer, go to my bank, select my date range,
and download my transactions for "Money 99 or higher". If
I had Money 2003 open, it would automatically see the
downloaded file and import the transactions.
I just did the EXACT same thing - about 10 times - in
Money 2004 and it does NOT see the downloaded file or
import the transactions. The download happens so fast
that I don't know where the browser saved off the file. A
quick search of the hard drive d...change a date query fra a single to a several
I’m coming from Denmark, so excuse me for the bad English.
I’ve got a table in an Access , I Have made by a Query, from a Employee
“Clock in / out system”.
They are also using the “Clock in / out system” to planed free (holliday),
At first , they put in the Employee number , next the start date
( 26-11-2007 ) and next the end date
( 30-11-2007 ) and at last the letter for Holliday “ H “ . and now the
Because all these information’s is taken into one records ( line )
Employee id Start date End date Letter for free H, …….