dynamically choose columns in query
I'm trying to create a query such that when the user runs it, the proper
columns (fields appear). We have 12 columns, one for each month of our
fiscal year (which goes from July to June). Basically, we run the query once
a month. When we run it, for example in April, the months prior to April &
including April (this would be July thru April), will have $0. The months
after April (May and June) will have dollars. When we run this query in
April, we'd rather not see each of the columns for July thru April because
all the records have all $0 in those columns.
Sea...AR: Bulk Void of Invoices
Is there a way to bulk void a range of AR invoices in Great Plains
10? I have a user who imported in a batch of invoices that they
shouldn't have and there is about 300 of them so 1 by 1 is going to be
Is the batch already posted? If not, you could use the Receivables Batch
Entry screen to choose the batch and delete.
If it's already posted, your only option is a SQL script.
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
> Is there a way to bulk void a range...Accessing Query Description thru VBA
I'm working on a procedure to 'package' a query to export it to a file,
email it and allow the peson on the other end to import it.
I want to be able to package the query description as well - and to write it
on the other end - but for the life of me, I don't see how I can read/write
the description (in the properties) of the query - can anyone put me on the
If the query has a description, you can get at it like this:
If there is no description, this *should* yield e...Access 03 Removing Records during Query that appear to be duplicat
I am working on a report for my company, and I am linking tables to our
accounting software and all information is coming through on the tables, but
when I create a query to narrow down the information, Access is removing
certain records that appear to be duplicate information. This is not the
case however, and I am wondering if anyone knows where I can look to turn
this feature off, if it is a feature that is on? Please help!!!
Queries don't remove records unless it's a delete query. Rather they don't
return records that don't meet the criteria. Therefore we need to k...LDAP query that contains everyone in an OU for an address list
I'm not very familiar with LDAP, but I'm wanting to create an address
list based on membership in an Exchange 2003 OU. For example, I have
an OU called New York where we put all our New York contacts and
mailboxes. When a new contact is created in there the address list
would automatically display them. Has anyone done this before?
>I'm not very familiar with LDAP, but I'm wanting to create an address
>list based on membership in an Exchange 2003 OU.
Queries that use OU's won't work.
>For example, I ha...Parameter query
I make parameter query in excel 2003.
The query works fine and automaticly update.
I used a cell as parameter.
He qurey is on sheet RuweQrYGegevens
And the parameter cell on Sheet basisinfo.
So far no problem.
On sheet basisinfo cell d5=RuweQrYGegevens!c7
That works fine. But when i put no vallue in the parameter cell and press
The query change the formula in d5=RuweQrYGegevens!verw and i must
what is wrong.
...File conversion query
I have a load of .wks files created in MS Works 4.5a which I'd need to
work on in Excel 97. A suggestion, please, on how to import them into
Doesn't excel open those .wks files ok?
If yes, then the next time you have to open each, just save it as an excel
> I have a load of .wks files created in MS Works 4.5a which I'd need to
> work on in Excel 97. A suggestion, please, on how to import them into
On Tue, 19 Apr 2005 15:38:47 -0500, Dave Peterson
<ec35720@netscapeXSPAM...to view data of a customer from union query
sir i have different table having fields of similar names [cause 1)There r
male/ female workers in our office 2) there r temporary and permanent
workers.] and hence we have 3000 files of different categories, there are
about 12 tables, in which (there are about 4 main forms ,each have 3 subforms
having nearly 250/300 fields )
what i want is
i have made a union query containing this tables, and i want to open the
form directly from the union query rather then close the query and then open
the form manually
its a humble request to please show me a way out so that i can save my ...Pass Returned Query Value to Report Text Box
Good Afternoon, as simple as my question may sound I am having a bit of
difficulty passing the results from multiple queries to multiple texts boxes
on the same report. I most certainly would like to learn how to achieve this
functionality through proper coding! And of course I would love to hear
whatever you may think is best! Thank you for your time and consideration!
Message posted via AccessMonster.com
A report is generally based on ONE query. Sure, you can make a report that
includes many sub-reports, each sub-re...Fieldname prefix in query yields both prefixed and unprefixed fieldnames
I have a problem a query in Access 2003 yields both fieldnames thats
are both prefixed and unprefixed. Is there are setting that make all
I am not aware of any setting that would do this.
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
<firstname.lastname@example.org> wrote in message
>I have a problem a query in Access 2003 yields both fieldnames thats
> are both prefixed and unprefixed. Is there are sett...Help with a query 04-21-07
First of all thank you for taking the time to read my question. I am
having a problem running a query on a simple table. Trying to run a
query on a table with a field called ENCOUNTERS and a field called
CODES. What I'm trying to ask get out of the query is this: Every time
an ENCOUNTERS field has a CODES value of 69210, I want it to return
all other CODES results for that ENCOUNTERS number.
On 20 Apr 2007 18:39:20 -0700, "email@example.com"
>First of all thank you for taking the time to read my question. I am
>having a proble...Using a Web Query
I have been trying to retrieve information from the Web by using a Web
query, and then inserting the information into an active worksheet in Excel
In the "Returning External Data to Microsoft Excel" dialog box I want to use
the Parameters button, which is not active,
Could anybody tell me how to activate the Parameters button before using the
"Get the value from the following cell" option button?
Thank you in advance.
It is possible to supply parameters to a webquery.
You can do it programmatically as described in this KB article:
http:/...excel web query
I am trying to use the web query on the data menu in excel
2000 to import a site into excel. Unfrotunately i keep
getting the following error -
"Unable to open http://..../ The internet site reports that
a connection was established but the data is not available"
I am actually able to view the web site through my browser
and i am connected to the net..
Any help who be greatly appreciated
...Extract hour:min in Excell or MS query
I am trying to fetch data from Oracle using MS Excel via ODBC. Here is
the query I ran on Oracle using SQL*Plus. I want to get the same
result in Excel.
select to_char(dt_time, 'MM/DD HH24'), sum(value)
where to_char(dt_time,'Day') not in ('Sunday','Saturday')
group by to_char(dt_time, 'MM/DD HH24')
Thanks in advance for you quick help..
...Query Deleted Items size for all users
We have many users using Exchange 2003 and I'd like to make sure that we are
as efficient with our space as possible. I suspect that many users are not
emptying their "Deleted Items" folder. Is it possible to run a report or
query of some kind to determine which users have large amounts of email in
the Deleted Items folder?
Thanks much for your help,
Perfmon counters - MSExchangeIS Mailbox - i) total count of recoverable
items ii) total size or recoverable items
-------------------------------...Get Random Date to return per record in Query
SELECT tblStaff.StaffName, RandomDateInRange(#1/1/2001#,#12/31/2001#)
GROUP BY tblStaff.StaffName;
Public Function RandomDateInRange(LowerDate As Date, UpperDate As
Date) As Date
RandomDateInRange = Int((UpperDate - LowerDate + 1) * Rnd + LowerDate)
Currently this query returns a random date, however it is the same one
for the entire query. How can I get the each record in the query to
have a seperate random date?
I am trying to generate a random date for each staff member for QA
On 5 Apr 2007 13:58:02...Query/ report- print report each wednesday with the data entered from the previously week
My boss asked me to print a report each wednesday, my next print will
be the 16 of May with data that I previously entered between 7 to 11
If there a way to create a query and then create a report which I can
print each wednesday with the data entered from the previously week?
Your help will be much appreciated!!
Thanking you all in advance,
On 10 May 2007 07:54:32 -0700, Fred's <firstname.lastname@example.org> wrote:
>My boss asked me to print a report each wednesday, my next print will
>be the 16 of May with data th...A field on a query has a value in it I'd like to use to create tot
Cost: Proceeds: G/L St G/L LT
30,000 31,000 $1,000
25,100 24,095 ($905)
4,000 5,000 $500 $500
In the G/L St & G/L Lt field, I would like to pull out the cost/proceeds
values to create a field that I can run a cost or proceed total on. I have
done an IIf function that works in pulling out the values but in the report
footer when I try to sum the data, it doesn't work. FYI: The iif function is
this: Iif([g/l s...IIf statement in query, Null value change to "0"
I have two fileds (OT and DT) that I am trying to add together in a third
field in a query. Access will not let me do so if one of the values is null.
Therefore, I am trying to change the value to "0" if the field is null.
I have come up with the following two expressions:
However when I use the espressions in criteria, the null values do not
change to "0".
Any suggestions? Is there a way to get the values of the two fields added
together without changing the null fields to...Returning a variable from a query
I have a form that needs to create a unique ID upon entry. Like a new
Sales Order Number for a new sale.
What I want to do is run a query that returns the MAX value for a field.
"SELECT MAX(SALNUM) FROM TABLE"
I can then add 1 to that number and create and a new key.
I am having a problem trying to determine how the return the result to a
strA = SELECT ...... or some such thing
I have tried to look this up in the manual (RTFM) but have not had much
Any help appreciated.
Try searching on-line or at mvps.org/access f...Slow Query 02-29-08
I have a log file with about 180K records.
I'm looking to create a ranking of most frequently visited pages.
This is the query I'm currently using:
Select top 10 * from
( SELECT HitPage, URL, count(HitPage) as f1, PageTitle from
where LogDate >=39142
GROUP BY HitPage, url, PageTitle ) order by f1 desc
I have tried indexes on:
LogDate, HitPage, URL, PageTItle
HitPage, URL, PageTitle, LogDate
When the LogDate is less than a month ago, its very quick, but when
looking at a year's worth of data is very slow
Any advice would be appreciated!
I'm not sure if yo...Dynamic Query
I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.
When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."
Below is my access code:
Dim varItem As Variant
Dim strSQL As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strMyDate As String, dtMyDate As Date
dtMyDate = CDate([Forms]![ySalesHistory]![Start Date])
strMyDate = Format(dtMyDate, "yyyymmdd")
strSQL = "procCustomerSalesandPayments '" & s...Excel 2003 This Workbook contains queries to external data
I get the following message box when opening an excel spreadsheet and
displaying an extnernal microsoft query.
This Workbook contains queries to external data that refresh automatically.
Allows you to click either Enable automatic refresh or Disable automatic
I know this is a new security enhancement and is turned on by default. How
do you change the default to off so this does not appear every time
...Simple calc query
I am new to Excel and have Excel 2007 if that makes any difference.
I am just trying to write my own vehicle logbook for my own vehicle which is
used personally and for work. To denote this I have done the following:
value 1 value2 Notation work column Personal column
The above are different cells. Value 1 is the start mileage. Value2 is the
end mileage. Notation is either P or W. Work Column has the formula
=IF(Ex="P",Dx-Cx,) where the "x" equals the cell number (eg, E6 or whatever)
and of course it goes for rows from A downwards. The end result, so far, is
that...Update/Append Queries minus confirmation Dialouge
Does anyone know how to silence the confirmation dialogue appear when you
run an update or append query. The first message box asks for confirmation
before you run and update table records. The second shows the number of
records that will be updated. I'm trying to automate an application with
various queries and macros and need to eliminate these message boxes from
Thanks for the help!
In a macro, you just need to put a SetWarnings/No action before the
first OpenQuery action.
Steve Schapel, Microsoft Access MVP
> Does anyo...