TRIM FUNCTION in QUERY
What do I put to trim off the spaces after a value in query? The table is
obtained from ODBC link and the values on a field somehow got spaces. Example:
My field in the query is:
WC: TRIM(PO_WORKCENTER_ID) ????
That should work.
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> What do I put to trim off the spaces after a value in query? The table is
> obtained from ODBC link and the values on a field somehow got spaces. Example:
> My...Custom reports/queries across multiple companies in Business Porta
Is there any way to create a custom query or report that can be run inside
Business Portal and which pulls figures from multiple Great Plains companies.
A View can be constructed in one database that pulls data from other
databases. This view can then be used as the source for reports in tools
like Crystal etc
Richard L. Whaley
Author / Consultant / MVP
Documentation for Software Users
Get our Free Tips and Tricks Newsletter and check out our books at
> Is there any way to create a custom query or report th...clicking TOOLS/OPTIONS gives me error msg "cannot access dir D:/"
I need to change settings in the TOOLS/OPTIONS menu. But when I click on any
tab in the OPTIONS menu it gives me the error message "cannot access dir
I may have changed the name of that directory at some point and I suspect
that is the problem. However I still need to change settings in the
How do I fix it?
Add this directory D:/documents and try again
And try again
Regards Ron de Bruin
"Lori Rossi" <Lori Rossi@discussions.microsoft.com> wrote in message news:34A25E1A-D...How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as
will as a heading. In other words the answer and the heading will appear in
the same cell.
Perhaps you mean something like ="Heading Name: "&A1+B1
"Jeracho" <Jeracho@discussions.microsoft.com> wrote in message
> I would like the cell to perform a calculation and then display the answer
> will as a heading. In other words the answer and the heading will appear
in...tab in access
-- I added a tab control to a form what do i do so it should be from right
On Sun, 21 Mar 2010 02:57:01 -0700, Gal
That is currently not supported. Why do you need it?
Microsoft Access MVP
>-- I added a tab control to a form what do i do so it should be from right
I am programing for a company in Israel and they write in hebrew
plz help me if u can
> -- I added a tab control to a form what do i do so it should be from right ...Access ODBC problem: Excel cannot get float columns
I need some help
I'm trying to get Access external data from an Excel workbook, using
the MS Query feature.
Everything is ok except when I try to fetch some tables that ODBC
refuses to get data from. The error message tells that the MS Access
ODBC driver doesn't allow some columns to be transmited because of its
number of characters.
The most strange thing is that I can see the data from MS Query
correctly, but I caannot get it back from Excel.
After some tries, I thing it occurs only with real typed columns.
Can anybody help me?
I use Windows XP Home + Office 2000 spanish versio...Outlook failure to send Access reports
I email reports from Access using Outlook. At times, the email will hang up
in my outbox and will not send even if I open it and try to send it manually.
Any of you fine folks know what's going on and how to fix this?
Thanks - Ken Smith
KMS Technical Solutions
...Create form similar to MS Access 2000 Help
Is it possible to create a form that works similar to the Help - Index form
in Access 2000?
My StreetTable consists of the following fields: Street_ID(autonumber),
I want to be able to type the beginning letters of the street and the List
continually reduces until I see the street I need in the StreetNames window.
I would then
click on that street and the Directions would appear in the third window
Since I'm dealing with approx. 5000 streets, scrolling really isn't an
option that I want to consider.
I found that a combo ...query with inline dummy table
Is there a way in Access to create a query that contains the table
records within the query itself? I want to avoid creating a dummy
table and just use values within the query definition. I was thinking
about using syntax similar to the insert into statement I would use to
populate the dummy table, but I'm not sure if I have a syntax problem
or I'm trying to solve an impossible problem.
The query I'm thinking of might look something like this:
("1/1/07","2/1/07","3/1/07","4/1/07&qu...cant see pictures in publisher 2003
Inserting pictures work ok , then i cant see them , clicking on the area of
picture the outline grabs are there but no visible picture. TOOLS > GRAPHIC
MANAGER shows the thumbprint , thats all , What am i going wrong
>Inserting pictures work ok , then i cant see them ,
clicking on the area of
>picture the outline grabs are there but no visible
picture. TOOLS > GRAPHIC
>MANAGER shows the thumbprint , thats all , What am i going
If you can click on the image and see outline grabs. Do so
and then choose Order from the Arrange ...Outlook Web Access error
Got this error message when trying to access my mailbox
from Outlook Web Access.
"Unable to get renderer"
This happened on a particular machine but work fine on
Anyone know what going on?
...Sending emailes from Access
Once i week i would like an easy way from access to send out a a email
to some customer that query will find.
Whats the eaasyest wasy to send a email to about 20- 30 people, the
email will be the same to every one,
I know i could go throught outlook and do a mail merge but is there a
way i can just have a button on a access form that will automatical
send out an email to lots of people
Thanks very much
Any help would be great
Poke around the Access EMail FAQ that Tony Toews has at
http://www.granite.ab.ca/access/email.htm. There's likely something there...Access Reports Disappear
I had a database that I had used for years with no problem. Lately I've
noticed that some of the reports are disappearing. Yet, if I try to create a
new report with the same name, Access says "Name conflicts with existing
module, project or object library."
So, I created a new database. The only thing I imported was the tables from
the old database. Yet, I'm having the same problem.
Has anyone seen this before?
Make sure you have SHOW HIDDEN OBJECTS set to YES. Then take a look around
for those "missing" reports...
> I had a da...See these critical pack for Microsoft Internet Explorer
Content-Type: multipart/related; boundary="pqazvfpfwgx";
Content-Type: multipart/alternative; boundary="ecwwcjdhekqnzo"
this is the latest version of security update, the
"September 2003, Cumulative Patch" update which resolves
all known security vulnerabilities affecting
MS Internet Explorer, MS Outlook and MS Outlook Express
as well as three newly discovered vulnerabilities.
Install now to mai...How to add summary fields to Group Footer in Access Reports?
How do you add a Summary fields to Group Footers in Access? I have a Detail
field I want to Sum in the Group Footer in my report.
...workaround for non normalized table
I've inherited a database that relies heavily on a non normalized table.
Until I can convince the general manager that I can normalize the data
without losing any records, I've got to have a workaround for certain
situations. Namely, I need to ensure that data is not being badly reproduced
at various stages of our operations. I would therefore like to reference the
information directly from the main table in order to populate certain
information in related tables. In this case, the main table uses [Order
Number] as its primary key, and ties it to a bunch of information like ...Error in code exportin tables to excel
I'm tryin to export a table to excel with the code:
Private Sub Command4_Click()
On Error GoTo Err_Command0_Click
Dim stDocName As String
stDocName = "F01_UT"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, F01_UT,
However, Access displays the message:
Compile error :
What I'm doing wrong? If I want to export more tablas do I have to write
more lines (DoCmd.TransferSpreadsheet acExport, acSpr...Field service table structure and field definitions
The 9 SDK does not reference or define the fields or tables for the Field
service component. Where can I find this information? I need to move RMA
into a data warehouse and am having significant trouble identifying the data
You may want to contact I.B.I.S. They wrote the application.
Charles Allen, MVP
> The 9 SDK does not reference or define the fields or tables for the Field
> service component. Where can I find this information? I need to move RMA
> into a data warehouse and am having significant trouble identifying th...VBA code to hide all the tables on form open
I don't want people to use a blank mdb to import my tables. I manually hide
them all. However, after running the macro to delete all records and import
from .txt, the table become unhide. I do the importation on daily basis.
I posted to macro newsgroup and asked way to hide table after importation
action macro but got no answer. Maybe it cannot be done in macro?
If so, I need VBA code to hide all the tables on form open. Thanks.
Hiding your tables won't prevent people from being able to import them into
a blank mdb. All they have to do is ensure that they've set the datab...Options Group and Query Criteria, revisited
I recently posted a question about option groups passing info to a
query for a Yes/No field, and received some great advice. However,
I've tried to apply that advice in a similar way to a Date field, to
no avail. In the query, I want the user to be able to display (1) only
records with dates present or (2) only records without dates present
or (3) all records.
I changed the query criterion that worked for the checkbox as follows,
and placed it under the Date field which I want to select by (this
statement yields no records):
IIF([forms].[fmQryByClinicSurveys].[emailOptions]=1...Please help with New Database Query
Newbie to New Database Query - Can you change a spreadsheet to a table if so
how? I have a lot of spreadsheets that I would like to work with in New
Database Query. Can this be done. Any help will be greatly appreciated.
In general, if you select a range and give it a name....MS Query will
recognize it as a data source:
Example for data in Cells A1:Z500, with column headings (EmpID, FName,
In the Names box (just above the Col_A heading) type rngMyData1
Next, save your workbook.
Then...Data>Import External Data>New Database Query
When y...Spreadsheet Query
One of my clients has devised a list of items in a Microsoft Excel
spreadsheet. Against each item (in a different cell) is an amount in �,
which represtents the items value.
My client wants to be able to use this spreadsheet, so that he can
select all items under �6,000 for example. Is this possible in Excel,
or should he be using a database ?
Any suggestions would be appreciated.
click in the list, choose data / filter / autofilter
from the drop down list on the value column choose custom
set the first box to less than and type 6000 in the second - click OK
- to...Can't access below toolbar.
I've used Windows Live mail successfully for several months but now I cannot
access anything below the File, Edit, View etc bar. My mouse doesn't get a
response from any button below that line.
This is a multi-part message in MIME format.
That=E2=80=99s a weird one that I haven=E2=80=99t heard before. But =
that bar that starts with File | Edit | View... is the Menu Bar, not the =
Toolbar. I hate to be...Exporting a table from Access 2003 to Excel 2007
I am trying to export a table from Access 2003 with more than 100,000 records
into Excel 2007 and am not having any luck. I do not find the 2007 file
extension in the drop down and choosing the latest version only exports part
of the table. Any thoughts on whether this is possible to do?
This is even hard to do with Excel 2007. You won't be able to do it directly
with Excel 2003. You could export the file as a comma separated value with a
csv suffix. By default Excel usually opens csv files. However you may lose
formatting and other stuff.
I ju...Skip Query Parameter
I Have a combo box that is feeding a query parameter to filter some rows.
sometimes I want to view all records.
the row source for this combo box is a table assigned only for that. and I
want to add this option to be displayed in the combo box "View All". So I
will add a new record in that table that will have the value of "View All"
then the filter will not work. how can i solve it ?
The data type in this combo box is "text". and I have similar case for
"Date" Data type in text box.