How to explain this.
In a range of cells, row J4:AB4
I will be entering numbers weekly, eventually filling up to AB4.
When I enter a number into, lets say, O4, I want a calculation
of the average of ONLY L4, M4, N4
Next week, when I enter a number into P4, I want a calculation
of the average of ONLY M4, N4, O4
So the result, in the cell that the formula is in, is changing weekly.
I hope this is clear.
Message posted from http://www.ExcelForum.com
assuming that there are numbers and they will ...Hide duplicates in a query ...
I would like to hide any duplicate records in a query. I have already
used the "Unique Values"parameter, but I would like to hide any
records where the first field is duplicated.
Did that make any sense?? ...
Field 1 Field 2
I would only like to show one record for "1" & one for "2" etc .....
Can anyone help?
If it makes no difference which record of the group you want to see, you
could use an aggregate query where you group by field 1 and specify firs...Union Query
A union query I developed has suddenly stopped working. The syntax
SELECT * FROM [myQuery - Part 1] UNION SELECT * FROM [myQuery - Part
Pulling up each query individually works fine. When i run the above
union query I receive "Invalid use of Null". The number and order of
the columns in each query are identical. Access 2003 here.
Any ideas what could be the problem?
I suspect it is the VBA code that you are using around the query, not the
query, that stopped working. To confirm, does your query is executable from
the query editor?
If it does not, you also confirm t...open saved query in Excel 2003
I have many queries I created in Excel 2000. In 2003, there is no option to
open a saved query. Is this a new feature of 2003 or does someone know how
to open my older queries?
Data, Import External Data, Import Data will let you open/run any saved
queries (IQY files).
"jaylou" <firstname.lastname@example.org> wrote in message
|I have many queries I created in Excel 2000. In 2003, there is no option
| open a saved query. Is this a new feature of 2003 or does someone know...Ldap Query
I want to make a query where i can see the members of an administrative group or Distribution list. Is there someone who has such a query?
administrative group would be based on something similar to
(legacyexhangedn="/o=Microsoft/ou=First Administrative Group/cn=Recipients")
dl would be based on (memberOf="DN of Group") This works fine as long as
the group isn't the users primary group
Hope that helps,
This posting is provided "AS IS" with no warranties, and confers no rights.
Please do not send email to this address, post a reply to this...Including Null values in query design
I am using a form to enter the parameters for a report. The parameters
are drawn from a number of combo boxes on the form. The report is
based on a query. Several of the query fields have expressions such
This works fine as long as the fields that are being queried have no
null values - if they do, then in the situation where I want the "*"
to apply, the query only returns the records which have a value.
Really, I want to be able to replace the "*" pa...Use Stored Procedure developed in C# in a SQL Query
I created a Stored Procedure by selecting a SQL Server Project in Visual
Studio 2008 using C#. It receives a SqlString as a parameter and return how
many times a specific character was found. I deployed the procedure to a SQL
Server database and can see it listed under the database. One caveat is that
it has a small lock icon on it.
I have a select query where I want to use the stored procedure. I am
thinking this is doable in a select query but can't seem to get it to work.
What I want to do is to have a select statement listing some fields and one
of the fields being th...Countif query
Take 2 - I don't think my first request got sent.
I am attempting to count the number of cells in AB2:AB26 that have a number
less thn zero. I have attempted this formula without success:
> Take 2 - I don't think my first request got sent.
> I am attempting to count the number of cells in AB2:AB26 that have a number
> less thn zero. I have attempted this formula without success:
Can an Access append query be run from an excel spreadsheet?
If so, how?
...Excel Query Error
I have an Excel template that contains a query. This
information goes back to an Access database. There are
three worksheets with three queries in this template. It
would have been my choice to keep it all contained in
Access, but I didn't design it. When this template is
opened it automatically refreshes the information.
However, on one computer we receive an error that says the
ODBC is not connected no driver manager. We have verified
there is a full install of Office on that pc. I don't
know how to fix this so she can run this from her pc. The
query is not complex ...Supporting international phone numbers shouldn't be so difficult.
Why can't Great Plains have a pulldown for the country on any address cards
that then changes the address and phone number formats accordingly?
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser...Building an expression from a query
I am a self learned Access07 user. I have created a database and need some
experience user knowledge.
In a form, i would like once information is enter in to one text box another
text box automatically updates according to a query.
I worked with someone with a little more knowledge then I, but they were
unsure of how to complete it.
Put the following code in the AfterUpdate event of the first textbox:
This assumes you have a calculation in the cntrol source of the other
In the alternative your code could calculate the ...MS Access Duplicates Query
While cleaning data recently, I stumbled accross this built-in query in
Access 2003. Many of you may already know of this tool, if not give it a try
as it is very simple to use. You can quickly locate and remove duplicates in
your source data. Start by Importing your data into Access tables. Then go
to Queries in the Objects pane. On the toolbar above, click the New button
and select the 'Find Duplictes Query Wizard. You can then delete bad records
here or export to Excel if you need to have your data validated or reviewed
by your customer.
...Update Query does not clear all records immediately
I have a form on which there are several "Flag" text fields which users use
to mark records. Next to each flag field there is a clear button that runs
the following code:
Private Sub btn_ClSA1Flag1_Click()
On Error GoTo Err_btn_ClSA1Flag1_Click
Dim stDocName As String
stDocName = "QryUpd_ClearSA1Flag1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
'DoCmd.OpenQuery stDocName, acNormal, acEdit
...Database query, spreadsheet size
I have a spreadsheet with several database queries from
SQL Server. As queries are refreshed, spreadsheet size
continues to grow. Even when I wipe out the queries, the
file stays around 300K. Does anyone know where this
extra data is sitting and how can I clear it out.
300K is not that big but have a look at:
>I have a spreadsheet with several database queries from
>SQL Server. As queries are refreshed, spreadsheet size
>continues to grow. Even when I wipe out the queries, the
>file stay...Access form date query
I have a form in which I have 2 combo boxes. 1 called StartMonth and
another called EndMonth.
I have a query called MonthsAvailable, this gets its info from
Date Out By Month: Format$(JobsInv.DateOut,'yyyy mm')
The combo boxes fill ok with data like 2008 06 and so on no problems.
My problem is that I want to capture all data from StartMonth and
EndMonth, eg 1/6/2006 through to 31/8/2007.
I have this criteria set up in a report:
>=[Forms]![DataDrill]![StartMonth] And <=[Forms]![DataDrill]![EndMonth]
for some reason the data retrieved only read from the 1st of e...fail to create IXSSO.Query object
I'm moving classic asp sites from Windows 2000 / IIS 5 to Windows 2008 R2 /
We have the "Indexing Service" started. Catalogs were created OK.
However, I am unable to create an ixsso.Query object.
Get the following error message
Microsoft VBScript runtime error '800a01ad'
ActiveX component can't create object
/HC/ECO/Announcements/Headlines/SearchResults.asp, line 39
ASP code :
...Form Combo Box Causes Query Error Message
Using Access 2007, I created two similar combo boxes, using the control
wizard for each form, in design view, one for each of two separate databases.
For one database it presents no problem at all. But for the other, when I
create a query, initially the query displays during the creation process.
However, every time I try to re-open the query, it re-opens and displays the
correct data, BUT only after I close an error message box that always pops up.
The error message says:
"An invalid condition was encountered by Access due to a value that you
entered for a field or table prope...The Indexing Service query cannot be completed successfully
I'm trying to do a search for *.txt files containing a certain phrase
and am unable to do so. When I try I get the following message:
The Indexing Service query cannot be completed successfully
because the volumes you have specified are not indexed.
I can do searches for *.txt files containing phrases in other
directories - just not this particular one. How can I reenable it?
R-click the Folder, select Properties, Advanced...
> I'm trying to do a search for *.txt files containing a certain phrase
> and am unable to do so. W...query criteria 12-29-07
I have a criteria test in the [DR].[Received Date] field of a query. The
criteria is based on data which is entered into a form.
>=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received Date],[Forms]![CSR dg]![txtEarliestReceivedDate]))
The true side of the IIF allows for all received dates if no data is entered
into the form. I would like to instead not do a criteria test at all if there
is no date entered into the form. I've tried things like "" or NULL in the
criteria, but to no avail.
The reason for wanting to do this, is that I have two tables linke...Freeze panes query
Is it possible to freeze panes without a line being drawn right accross the
But maybe you coul apply a bottom border (nice thick red border) so that it
looks like you did it on purpose.
> Is it possible to freeze panes without a line being drawn right accross the
> whole page?
...Relationship error in a query
I have four tables in a databse. Each database has at least one column
containing prices of goods & services. However, these columns are not
related in anyway (some other colums are). I am trying to summerize these
price information in one query where I am trying to list the sum of prices
from every table. When I try to do so I am getting an error part of which
"One or more of the tables isn't related to the others. Click OK to edit
At least according to the error message (and this would have been my
question anyway),...Query table using criteria from an Excel document
I have a Access table of names and addresses. There is a postcode (zipcode)
text field. I also have an excel spreadsheet with a single column of
postcodes; no duplicates.
I need to get query to return each name and address in the table which has a
postcode listed in the spreadsheet.
Typing each postcode manually into the query seems clumsy.
Add a link to the Excel data.
Create a new query joining the linked Excel data and your Access table on
the postcode field.
Those that match will result.
Microsoft Office/A...Still stuck: rerunning a query to change criteria
My form lets one set criteria for a query, from which some textboxes display
several Dcounts of the selected clients (grouped and counted by age and
gender). Now I need this query to be rerun once different criteria are set.
But I believe that as long as the form is open, the query wil be run ones and
then never again!
I could of course have people close and open the form again and again, but
that is cumbersome.
Me next try would be to have the criteria be set i the form and then as next
step run and open the query, including the data to be displayed in a subform.
But is ...Insert Database doesn't "see" half my queries?
I am trying to insert a database” from Access 03 into Word 03 using the
database toolbar in word. When I click “Insert Database” in word, and then
browse for my query, the wizard doesn’t “see” any of the queries in which I
changed a field name (ex. Dept: Department). Does anyone know a workaround
for this? Right now, it’s only showing about half my queries which is a
little odd. Is there a different way to insert my database?
If you check Word Tools->Options->General->"Confirm conversion at open"
and go through the whole insertion process aga...