How do I increase query field length (>20 characters) ?
I want to exclude 3 or 4 variables from a particular query. Using Not
"xx"or"yy" is fine, but when excluding more than this, query fails. I think
the problem is that the total text characters is quite large (approx 60
characters in total, from four exclusions). Ideas, please ?
60 characters is not the limit. 1024 characters is the limit.
POST the SQL of the query that is not working (View: SQL on the menu) or at
a minimum post what you are attempting as the criteria.
Also "query fails" does not tell us if you get an error (what is it) or the
wrong resul...Calculate the % increase for two columns
I have a pivot table, the data was first display by date, i know i can use
the grouping function to group data into monthly basis. But I want to know
that can I set the formula to calculate the months difference between, say
the sales amount of June & July, and the % of the difference??
If you have a grouped field, you won't be able to add a calculated item
to the pivot table. In the source data, you could add a column to
calculate the month for each record.
Refresh the pivot table, and add the new field
Add another copy of the Data field to the data area
Right-c...Count column difference
Using MSExcel 97.
I have two columns of data
e.g. A1: A4, containing values 5,10, 3, 6
B1:B4, containing values 3, 8, 7, 4
I wish to perform a count (e.g. in C5) of the number of rows where the
value in column A exceeds the respective value in column B (in this
case count = 3, as A1>B1, A2>B2, and A4>B4).
Just cannot get my formula right. Tried using an array (but difficult
when comparing the difference between two columns), and COUNT.
Thanks in advance for any suggestions.
~~ Message posted from http://www.ExcelTip.co...Forms in two differant views
Is there a way to use the same sub form showing two differant views within
the same Main Form. For example: Can I show on one tab a datasheet view and
on the other a regular form view.
Create a button on your form and under the "OnClick" event put
'Me.DefaultView = Datasheet
Alternatively you could use a checkbox
If me.checkbox1 = 0 then
Me.DefaultView = 2 ' Datasheet
Me.DefaultView = 0 'Single Form
This should flick the form between datasheet and form views.
> Is there a way to use t...Query wildcard symbols
Just a heads-up in case this bites you, too.
I migrated an application from Access 2002 to Access 2007.
I have a search form for the users to enter some criteria ... the form
dynamically builds a SQL WHERE clause before opening a "results" form based
on an underlying query that includes all the fields available to search on.
Once in Access 2007 format, the search form opens empty every time?!
When I add selection criteria to the underlying query directly, the result
set is empty every time?!
Oh wait, I'm using the "*" (anything/everything) wildcard character. W...Delivery Status Notification (DSN) different if sent from outside
When my users mailboxes are full above quota and not accepting any more mail,
the sender receives a differently worded DSN depending on whether they sent
email to that user from an internal account (one that is part of the exchange
org) or an external account (not part of the Exchange organization).
The one sent to the a user that is external is much less human friendly and
is causing me a problem with some scirpted automation in my app.
Is there a way to make the text the same for both? Examples pasted below:
Here is the if sender is internal:
Your message did not reach some or all o...Run two copies of Outlook with different profiles
I'm trying to run two copies of Outlook with different
(outlook)profiles at the same time.
Normally i can choose the profile with the /profile switch, but if
there is already a copy of outlook running, this switch seems to be
Any ideas how to handle this are welcome.
"Reinhard" <email@example.com> wrote in message
> I'm trying to run two copies of Outlook with different
> (outlook)profiles at the same time.
> Normally i can choose the p...Excel does not calculate my formulas
This is very strange...Excel does not calculate any formula. I can do a
simple =A1+A2, and it just return a zero value.
I made sure of the following:
- Cell is set to General (tried it with different cell settings too)
- Autocalc is on in settings (even a manual F9 calc doesn't work)
We might need a few more clues:
What is in A1? What is in A2?
"Martin" <Martin@discussions.microsoft.com> wrote in message
> This is very strange...Excel does not calculat...Importing a table from Access query
What is the quickest and easiest way to do the above?
Thanks in advance.
The way I usually do this is run the select query, click on the top-right
box (which selects all records) and use Ctrl+C to copy and Ctrl+V to paste
into my workbook. An alternative is to right-click on the query in the
Database Window and left-click on Export. In the Save As dialog box, select
"Trish" <Trish@discussions.microsoft.com> wrote in message
> What is the quickest and easiest way to do the above?
>...Bug in receiving entry. error calculating Prev Qty Shipped
Dynamics GP Bug Report, version 9.0 service pack 1.
Version Information: 9.00259
Location: Receiving Transaction Entry
Description: As one enters receiving transactions lines and adjusts the Qty
Shipped, the quantity previously shipped is calculated incorrectly. This PO
transaction was for a quantity of 120. A quantity of 15 were received on a
previous shipment. The Qty previously shipped should display 120-15-30=75
To make this error occur continue to change the Qty Shipped. Sometimes the
error will occur quickly, other times it might take 25 changes.
Products Load...Reading empty query returns
When I open a form that has a bound query as the record source, no
data appears (including the control objects of the form) if the
query's select statement does not finds records that match the
criteria I specify. This causes a problem in my VBA code as the
variable I use to check the number of records returned by the query
does not seem to be able to read empty query returns, even if I use
isnull. So I guess I am asking how can I get Access to read an empty
result set in VBA from a SQL select statement that is run???
Here is part of my code for this:
DoCmd.OpenForm stDocName2, , ...different versions in visio ole-object?
i have some problems printing a word document which includes some visio
In word i can see the visio object like i edited it but when printing
the word-document, a completly old version of the visio object is
printed out. The printed object is older that 2 weeks.
I tryed printing the document from other PC without changes.
I tryed copying the ole-object into an other word dokument (copy/paste)
Printing the document from wordpad works great ....
I also opened the visio object (doublecklick in word) and edited the
object, saved the file and exited back to wo...Query Flaw???
I need only one value from an aggregate Query - TheStores, which means the
Number of Inspections in a "From - To" Timeframe.
I use that as part of a calculation. The "Inspections" table contains only
a Store and an InspectionDate.
It gives me the result I need...but no Join...the Query has several other
Fields from "Randy_F_FindFreq", but has been trimmed as an example here.
SELECT Randy_F_FindFreq.AAll, Randy_Insp.TheStores,
Round([AAll]/[TheStores],4) AS PerAll
FROM Randy_F_FindFreq, Randy_Insp;
Is this "sound"?
TIA - Bob
Without a join yo...Difference 05-21-04
What is the difference between CRM standard and professional?
> What is the difference between CRM standard and professional?
Does it have anything to do with Outlook integration?
I am Johnny!
Your Internet penpal.
I have a scooter, how about you?
"Johnny" <firstname.lastname@example.org> wrote in message
> Johnny wrote:
> > What is the difference between CRM standard and professional?
> > -Johnny
> Does it have anything to do with Outlook inte...Multiple Accounts held at the SAME Institution with DIFFERENT logons
I am trying to sync Money 2006 to my Banc of America Investment Services
accounts. I have two accounts with two DIFFERENT logons, an IRA and a
regular brokerage. Money will sync fine to one or the other, but I can't set
it up to sync to both because when I click on Online Services it is already
setup so I can't add a different login, etc.
Project Lead - Software Engineer
Worldwide Environmental Products Inc.
In microsoft.public.money, Daniel wrote:
>I am trying to sync Money 2006 to my Banc of America Investment Services
>accounts. I have two accounts w...calculating quantity on hand based on date-time stamp
I am always unclear on the proper syntax for pulling date-related data. I
need to pull a field in my query, below, based on the field called
"IV00118.CHANGEDATE_I". This is the date in which an end user changed the
cost of an item.
To give you some background, the IV00102 table in my query below is the Item
Quantity Master table, while the IV00118 table is the Item Cost Change
The field that I want to pull based on that date field is the
IV00102.QTYONHND field. This field is the inventory quantity on hand field.
How would I format the syntax bel...feature difference btw 2000 & 2003
I currently have Outlook 2000 and wanted to know if its worthwhile to
upgrade to outlook 2003 ? I only use the mail portion of outlook and
don't use calendar or any of the other options. Is 2003 more
compatible with WinXP ?
If you are just strictly e-mail (no calendar, contacts, tasks, .etc) then
you are missing a good portion of what Outlook 2003 is bringing to the
table. However some of things that you might like in Outlook 2003 are:
1) Better security. (bots, pictures, .etc that load from the web in html
are blocked by default)
2) Cool new ...copy data to differences places based on selection
I have a master list of entities name in master name sheets. Let say
entity 1 to entitiy 100.
If I select Entity 1 to Entity 10, these 1 to 10 entities insert below
group1, group2, group 3 in sheet1. It also insert below group4, group5
in sheet2. I don't have preference about how to select the names in
master list. Anything like check, or yes is fine to me. I am open to
any idea or method. thank you in advance.
...Microsoft Query #8
I'm using Excel to query an oracle database. When I go to Microsoft Query to
edit my query, I can't view my criteria or tables. I click on the view menu
and am unable to select view "criteria" or "tables". Thus, all that shows up
in the screen is the data in my query. This has happend to me before. Can
anybody help me?
I was able to answer my own question. It seems when query is complicated
enough, you are unable to view the criteria, since you are now using SQL to
query the data. It seems that if you click on the SQL button and scroll
down, delete th...Totals Query Help!
Hi, I have a table with the following fields:
What I'm trying to do is write a query that will give me an average Value
for each month, but I want the average to be based only on records where the
Model is consistent across all months. So if Model x is missing from one or
more months all records for that model would be excluded from all the
overall Monthly averages. So the query will only average records where the
Model is present in each month.
Any help with this would be greatly appreciated.
It'll help us if you can ...Compare 2 excel files for differences?
Is there something within MS Office 2007 which will do this?.
Alternatievly anyone any recommendation?
This site is great for finding Uniques/Duplicates:
Also, for things pertaining to Data Validation, you may want to check this
....and if you feel really ambitious:
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
"Dave" <email@example.com> wrote in message
news:85...Need help with the query.
How can I get desire results below. I cannot seem to figure this out.
Any help is greatly appreciate.
IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
CREATE TABLE #Temp
UserHistoryId INT NULL,
UserId VARCHAR(6) NULL,
EventDate DATETIME NULL
INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (664,
'User1', CONVERT(DATETIME, 0x00008eac00000000))
INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (1014,
'User1', CONVERT(DATETIME, 0x00009c2600ef152a)...Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes
reaches around 500 characters. Not huge, but larger than the Text limitation
to which is what Access 2007 of course converts this. How can I force the
data type to be Memo while I'm in the query so the resulting table displays
all the data without any truncation.
I don't believe you can.
I think you'll have to create the table first, and then append to it.
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"E...Using single button to run multiple query
I want to use single button on the form to run multiple queries. How it can
be done? Where should I write queries and code?
One way to do this would be to create your queries, then use the command
button to run code that calls each query. Another approach would be to
create all your queries, then create a macro that runs all your queries, and
call that macro using code behind a command button.
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
"Gajanan&qu...Query Based Distribution Lists #3
I can create QBDL based on STATE, ZIP, LASTNAME and many other fields. I
would like to make a list based on Group Membership. There is a field under
USER called MEMBER OF but it does not give me any results when I do a
I have many employees who work in multiple locations and need to belong to
several different groups even though they have only one login and work out of
a single office with the same address etc. So I made them members of groups
based on the different location in which they work and wanted to send mail
based on this membership.