IIf Statement in Query Criteria 12-11-09
I have the following code in a query:
SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN,
FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON
WHERE (((qryPointsWithDropOffDates.INCIDENTDATE) Between
[qryDisciplineNoticesReprint.LASTINCIDENTD...How to return a range of values in a drop-down.
Is there a way to have Excel return a range of values in a drop down in one
cell based on input from another cell.
As an example:
Cell A1 has the text "PVC Pipe"
I want cell A2 to get input from A1, read through a table, and return the
corresponding values (sizes) of PVC pipe. The results in A2 would be the
following in a drop down:
If cell A1 contained the text "Metal Pipe" it would return values (sizes)
of metal pipe from a table and not display the sizes of PVC pipe.
The bottom line and...Running a query against a password protected spreadsheet
I used to be able to run an access query against a linked excel spreadsheet
and update information in the spreadsheet.
The spreadsheet is now password protected, and although I can run the query
against it OK, I can no longer update data in it.
Does anyone have any solution to this.
What version of Access and how long ago were you able to do this? The reason
that I ask is that Microsoft lost a patent lawsuit a few years ago on
updating linked Excel worksheets from Access. I believe that they blocked
this ability from Access 2002 and newer.
http://support...Using SUMIF to add data between a range of dates
Hi, I am developing a cashflow spreadsheet, and need to add a range of values
(in column B) based on the criteria that they are relating to a set week, ie
in column B has the amount to be paid, and column C has the date the amount
is due. I need to find out the total amount due between 2 dates. Does anyone
know how I can do this?
With start date in B20 and end date in B21 try this:
"Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen
news...Out of Range
This is my VBA macro. I'm getting a "Subcription out of range #9" error
message on: Windows(fname).Activate
Can someone help fix it, if possible?
fname = Application.GetOpenFilename
Drop the line:
The opened workbook is the active workbook.
"Jeff" <Jeff@discussions.microsoft.com> wrote in message
> This is my VBA macro. I'm getting a "Subcripti...Unable to delete cell range.
Operating System: Mac OS X 10.5 (Leopard)
New to excel and following a video tutorial. I am using auto-fill to create a range of numbers. The cells are selected and I try to delete all the content in the range by pressing the delete button per the tutorial but only the first cell in the range is deleted. Please help a newbie.
Are you using a laptop or a condensed keyboard by any chance?
If so, the key labeled 'delete' is the equivalent of 'backspace' & does
delete only the content of the active cell in the range. You need to hold
...Summing a range within a range
I need the formula that will sum a range within a range. Example: the primary
range consists of the following numbers: 1,2,3,4,5,6,7,8,9,10 and I need to
know how many numbers are >3 and <8. First I need to know the sum ot the
qualifying numbers; second I need to know how many items there are.
Stumped and exhausted...anyone's help will be greatly appreciated!
Need to Know
For the SUM:
For the COUNT:
...Querying for Extended ASCII Characters
I have an imported flat data file that has extended ASCII characters (i.e.
189, 239 and 191) in a Customer Name field. The extended characters are not
always in the same location of the Customer Name field and are not always the
same extended ASCII characters.
My question is there a simple way to query for extended ASCII character
(>127) in Access to identify such records and then clean them out of the name
THANKS IN ADVANCE!!
You can use a Replace function.
Replace([Customer Name], "┐", "&...Add to a query Expression!
Overdue: IIf((DateDiff("d",[MyDate],Date()) Between 30 And 60) And
How would I add theses 3 expressions into the expression
above.............Thanks for any Help.........Bob
Between 60 And 90) And ([Payable]>1),"2 Months",Null
Between 90 And 120) And ([Payable]>1),"3 Months",Null
Between 120 And 2000) And ([Payable]>1),"3 Months Plus",Null
I find the easiest way to do this is to build the expression one component
at a time. I use Editpad lite which is like notepad. You can use a tes...Percentage on x axis and period range on y axis
I'm trying to plot the length of time staff have worked for a company
on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a
seperate column that has converted the months worked into each of
I then would like to show on the x axis the percentage of staff that
fall into each period.
I would certainly appreciate any assistance!
Wayne Beasley wrote:
> I'm trying to plot the length of time staff have worked for a company
> on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a
> seperate column that has converted t...Query Help. 11-30-09
I need to return CompanyName only once and all the FullName associates with
it. Below is the Business rule.
Thank you so much in advance.
IF OBJECT_ID('Tempdb.dbo.#Test', 'u') IS NOT NULL
DROP TABLE #Test
CREATE TABLE #Test
CompId INT NULL,
CompanyName VARCHAR(30) NULL,
FullName VARCHAR(30) NULL
INSERT INTO #Test VALUES(10, 'Sears', 'John Smith')
INSERT INTO #Test VALUES(10, 'Sears', 'Lisa Smith')
INSERT INTO #Test VALUES(11, 'Target', 'Peter Smith'...Display range name when a cell within the range is selected
How to display the name of a range when a single cell within that named range
This displays the named range(s) that the cell belongs to, if any, in
the Status bar.
Put this in the ThisWorkbook code module of your workbook.
Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim sRanges As String
Dim nmName As Name
Dim bInRng As Boolean
On Error Resume Next
For Each nmName In ThisWorkbook.Names
Debug.Print nmName.Name, nmName.Re...Data Range
I have searched through this forum cannot find a solution to what I need so
here goes (or perhaps I have missed it).
I have a workbook containing 11 worksheets - one for each country with the
first worksheet containing a set of standard charts which are reused for each
country. I am currently manually amending the data range and selecting the
worksheet and highlighting the data for each country (so have to do this 10
times). The data is in the same location on each country worksheet so really
the only amendment is the worksheet name.
How do you recommend that I speed up t...Same query: Subform 0, combobox 1, wins by KO
I have a table I want to modify but after it's filtered by an ID number.
I have created a query that works great and added a listbox that displays
the IDs and when I select the ID it requeries the query.
Because I want the users to be happy, I have created a form (where the
listbox is), and a subfom object that has created a subform where my query is
ran. I then have a nice form with a datasheet display underneath to tick the
checkboxes I need.
BUT, the problem is that the subform displays the first ID correctly - but
not the other ones. I have tried to link the WHERE to ...How do you point to a named range in linked workbooks?
Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I
point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2?
Is this what you're looking for...?
In workbook 2 enter "=[Book1]Sheet1!$A$2" in cell A2. Do the same for cells
B2 through K2 with the correct respective links.
Hope this helps.
> Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I
> point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2?
Or, if you don't like to ...Crosstab Query
I've created a crosstab from a table with data laid out like this:
Group Section Title
A One John - Manager
A One Joe - Analyst
A Two Jane - Director
B Three Sue - Director
B One Bill - Manager
C Two Bob - Analyst
C Two Tom - Director
C Three Jim - President
D One Linda - Analyst
D Two Lucy - Manager
I'd like to end up with a cross tab like this (you may have to use your
imagination on layout since couldn't paste a picture) basically trying to get
multiple items to display in the value field.
Group One Two Three
A ...subtracting a range from another range and getting a range result
I have two ranges of XYZ coordinates. One is named R1 and the other
R2. Is there any way in Excell 2002 to bascially say R2-R1=R3 so that
R3 is a range the same size as R1 and R2, where each cell in R3 is is
subtraction of the corrisponding cells in R1 and R2? Sort of like
of course I need to do this on a two dimentional range, there are
multiple XYZ points.
Are the three values of R1 (3,4,5) in separate cells?
Are you adding or subtracting?
Please spell out the problem in more detail
Bernard V Liengme
remove caps from e...Cell Value as Named Range Reference
Little bit of a quirky question...
Trying to use a cell value as a reference in a formula, where that
cell value is the name of a named range.
So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2.
I want to get the correlation vale for A1:A3 and B1:B3
So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the
formula: =correl(D1,D2). But I get an error. Have also tried using
Indirect to no avail.
Any help would be hugely appreciated. Thank you.
http://www.wimgielis.be = Excel/VBA, soccer and music
"ste...refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could
apply to both
please could you take a look at the following post
Is there some reason you can't post your question here instead of asking us
to look elsewhere?
Microsoft MVP Excel
"Blinds Nottingham" <firstname.lastname@example.org> wrote in
news:email@example.com...highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy:
1. applying the calculation, which should be value of cell * 1000
2. special past values only to same selected sells
You enter 1000 in a separate blank cell and copy it then highlight the range
to be multiplied and past special-> multiply. that will multiply all the
cells by 1000 in the pasted range.
"S Himmelrich" wrote:
> I know how to select a range of cells and copy:
&g...Check a range for a value
I have what seems a pretty straightforward question -
How, in *one* formula, can I check a range for a value?
i.e. Check whether any cell in the range A1:A9000 contains a zero,and
Any help greatly appreciated......Jason
Something like this will do it:
and will not count blanks as equivalent to zero.
Hope this helps.
On Nov 23, 12:41 am, Jay <s...@dummyaddress.spam.com> wrote:
> I have what seems a pretty straightforward question -
> How, in *one* formula, can I check a range for a ...Pivot Table
This is my first post here...I hope that i've chosen the right
My problem is similiar to other people's when it comes to consolidating
ranges with Pivot Table (sorry for my english...). I've searched the
forum but I haven't found either solution or workaround for my
My data is divided into 2 sheets, because i need to use more than 255
columns (about 433). It looks like this:
col1(ID) col2(Chain store category) col3(address) col4(date)
col5-col255 (product's data). One product uses 9 columns like: space on
a shelf, price, comments....
Bacause...Parameter Query and Report
This may already be answered in other threads but I can't get my head round
the answers so hope you can help.
I have a very simple report in Access 2000 and a simple parameter query
which when opened prompts for start date and end date and successfully works
I have added the parameter query to the record source of the report but it
does not select the records asked for. In summary the parameter query works
when you run it on its own but when I open the report (even though the record
source is the correct query) it does not work. I simply get the report
displaying a...Still confused about queries to similate vlookup
Normally I would just dump this into excel, but the file that I am dealing
with has over 1.5 million rows so excel can't stomach it. Here is what I
have done. I have one table (MAIN) that is my main table. It contains all
the raw data. I need to convert a city code to a city name. I have another
table (CITY) with the conversions from city code to city name. Both tables
contain all the city codes. All I want to do is put the city names into my
MAIN table next to their corresponding city code (there are about 4000
different cities). I have related the tables relating th...=IF(Range=Range, Range)
I am trying to use Excel to check if column B = column A then = c.
Which works fine for one row, but I would like to sum the results.
x x 1 y 1 y 6
x x 2 y 1 y 5
x x 3 y 6 y 4
x x 4 y 3 x 3
x x 5 y 9 x 2
x x 6 y 1 y 1
x x 7 y 8 y 0
x x 8 y 2 x 9
x x 9 y 1 x 8
= ?? = ?? = ??
hooper222's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30108
View this thread: http://www.excelforum.com/showthread.php?threadid=497943
Sounds like you can use ...