TRIM FUNCTION in QUERY

Hello,

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: PO_WORKCENTER_ID

Not sure
WC: TRIM(PO_WORKCENTER_ID) ????

0
Utf
3/11/2008 1:50:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
3281 Views

Similar Articles

[PageSpeed] 51

That should work.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Cam" wrote:

> Hello,
> 
> 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: PO_WORKCENTER_ID
> 
> Not sure
> WC: TRIM(PO_WORKCENTER_ID) ????
0
Utf
3/11/2008 2:35:02 PM
Reply:

Similar Artilces:

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. Hi 300K is not that big but have a look at: http://www.contextures.com/xlfaqApp.html#Unused >-----Original Message----- >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...

Merge records function
Is it possible to reuse the record merge function in CRM. Fx to call the function with a valid Account GUID with a URL-call to mergerecords ? I want this to open the merge windows with the GUID as the master record. ...

Excel functions
Hi everyone, Hope somebody could help me with these problems. Problem #1. row 1 =DSUM(database,field,'$A4:$A5) where A4 = "destin"; A5 = "Consolidator 1" row 2 =DSUM(database,field,'$A6:$A7) where A6 = "destin"; A7 = "Consolidator 2" ..... row n =DSUM(database,field,'$An:$Am) where An = "destin"; Am = "Consolidator n" I waste a lot of rows for this criteria only because I do not know ho to create it another way, I mean (A4, A6 and so on) Problem #2 I have named several range of cells in workbook. I want to change...

Writting Function using VBA
Hi I am trying to write a function to return an address but instead I get #VALUE!. Public Function fnd(a, b) fnd = Range(a).Find(b).Address End Function Please help. Thanks. ..Find won't work in UDF's called from the worksheet until xl2002. Depending on the range (a), you could use application.match() through each column. If the range is small, you could just loop through the values in that range, too. nc wrote: > > Hi > > I am trying to write a function to return an address but > instead I get #VALUE!. > > Public Function fnd(a, b) > >...

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, Dan Townsend 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...

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...

Union Query
A union query I developed has suddenly stopped working. The syntax is: SELECT * FROM [myQuery - Part 1] UNION SELECT * FROM [myQuery - Part 2]; 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...

converting hand drawn sketches into Visio Cross Functional Horizon
Hi , I am new to Visio 2003 . Can anybody tell me how can one convert hand drawn sketches into Visio Cross Functional Horizontal diagrams? Thanks Harsha Are you asking about a way to scan images into Visio and transform them into drawings? Or are you asking how to assemble a Cross Functional diagram? To make a Cross Functional diagram, you first choose how many bands to create. Then you click on the headings for the bands to change the text to the names you want. Switch to the Basic Flowchart shapes in the Shapes Window and drag out the Process shapes to create the steps in you...

Select subform's query criteria from form's unbound cbo
I want users to be able to select a name from a form's unbound combo box and that selection to automatically become the criteria for the subform's underlying query, without the parameter box for the query popping up. How do I do this? I'm new to VBA and not an Access 2003 whiz either. TIA for your help! WorkerBee wrote: > I want users to be able to select a name from a form's unbound combo > box and that selection to automatically become the criteria for the > subform's underlying query, without the parameter box for the query > popping up. How do I do t...

Excel super slow on paste function in 2007
When you copy and paste one cell to another I get the circular waiting bar and after 4-5 seconds it pastes. If I'm doing a bunch of cells, it still takes 4-5 seconds per cell and the cells will appear right to left, 1 every 4-5 seconds until it's complete. If I hit ESC, everything copies and pastes right away. This started happening when we converted a 2003 file to 2007. It affects everyone so it's not my PC, it's the document. There are no active add-ons but I recently downloaded an add-in that I got from this discussion board to find hidden links (it's d...

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? Thank you! If you check Word Tools->Options->General->"Confirm conversion at open" and go through the whole insertion process aga...

Automatic Functions in Excel
Hi There I am developing a Time Sheet in Excel for Staff to record times worked on it. I am using the 1904 date system as occasionally a negative total will be displayed. I would like it if someone was on annual leave they would be able to record this by typing in "Annual Leave" or "A/L" or something similar then the total for that day to display as 7:00 (this is in hours). At present the "total" column runs a calculation of the time finished minus the time started. Is this possible? Regards Colin Hi Colin, StartTime in column A, end ti...

is there a baby-sitting function in Exchange
SBS2003 Premium. One user is not very diligent on reading and responding to emails. He is on the road quite a bit and when he gets back he will not remember that one important email that we needed to respond to. Is there a way that I can have other users have his mailbox open as well, so that they can monitor it and make sure things get replied to? There appears to be such functionality in OL2003, but it just doesn't open the mailbox. Plus his password changes every 30 days, as per the administrator settings, how do we deal with that? Thanks in advance, Robert Assign Full Mailbox ...

Relationship error in a query
Hello Everybody, 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 says: "One or more of the tables isn't related to the others. Click OK to edit system relationships." At least according to the error message (and this would have been my question anyway),...

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...

Query table using criteria from an Excel document
Hello, 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. Any ideas? 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. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/A...

How to Declare Pointer to Member Function?
I have a thread that needs to make data visible to a function inside a separate class. In the past, I was using PostMessage to get my information to the class function, but occasionally a message gets lost this way. I could declare the class function as static, but then the function would not be able to interact with other parts of the class. The data is thread safe. // header: typedef void (*PtrToLpMsg)(LPTSTR lpMsg); PtrToLpMsg g_AddStatusMsgFn; // code: CMain::CMain() { g_AddStatusMsgFn = AddStatusMsgA; } void CMain::AddStatusMsgA(LPTSTR lpMsg) { // other code } On Mon, 16 ...

fail to create IXSSO.Query object
I'm moving classic asp sites from Windows 2000 / IIS 5 to Windows 2008 R2 / IIS 7. 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 : <% Dim sSearchString Dim oQuery Dim results() ...

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 ...

Priv.edb query utility
Recently I ran across a utility on technet somewhere that you can run against the priv.edb for exchange 2003, and it can spitback how much of a percentage that each users mailbox is compromised of specific attachment formats, by extension name, not by querying the attachments mime information. It spat back graphical information in pie chart format, to show for example: user 1's mailbox is 15% compromised of mpegs, and avis etc It can also spit back the total % of the overall priv.edb is comprised of 15% specific formats(in our case, movie file formats, mpeg, avi etc). Does anyone...

random incomplete query results
Hi to all, on a form I'm filling a "box" (I don't know the english name, is like a combo without the input selection row) from a table on the same database. To do that, I'm using the property "RowSource", also to change the ordering field. The problem is that, when I'm using the database on a PC with Windows 2000 and Office 2000, "sometime" (exactly! not all the times!) the list on the box is covering only a fraction of the entire table! After some trials I get the full list... The same database, on my PC (XP and Office 2003), is working alw...

Need query to return absence of record
Hello all, hope everyone is doing well this fine morning! I need some help in constructing a query. I have three tables, designed as so: Personnel FullName(PK) Department Classes ClassName(PK) TrainingHistory *Junc table between Personnel and Classes* FullName(FK) ClassName(FK) DateTaken TrainingHistory records every instance of a class taken by all personnel. I need to be able to find people who HAVE NOT taken a given class. I need to be able to pick what class I search for each time I do the query. For example, I need to know who in the organization has NOT t...

Function to control how data displayed
I have 2 columns that I exported from Access to Excel. In Access th columns were Yes or No. In excel they display as True or False. I wan them to show as Yes or No what is the function to make this happen? Thank you! -- LOgle531 ----------------------------------------------------------------------- LOgle5318's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2659 View this thread: http://www.excelforum.com/showthread.php?threadid=39860 LOgle5318, Assuming your True and False are in columns A & B then In C1 put =IF(A1="True","Yes") and...

One query to multiple databases
Hello, I have five different databasesd that I access through five different odbc connections. They bases are identical, but the data in them are not. Several times, every day, I run the same query on each of them, and then I combine all the results I get from each base. Is there anyway that I can do this automatically? Use a UNION query. -- Build a little, test a little. "bvdahl" wrote: > Hello, > > I have five different databasesd that I access through five different odbc > connections. They bases are identical, but the data in them are...

Local Data Queries Stored? Client or Server?
I have 3 complex queries I need to deploy to 35 users. After creating the 5th one from scratch, I figured the data's gotta be stored somewhere! I looked around in the registry and couldn't find any clues. Where are the local data queries stored? I'm thinking I could copy the queries (update for any unique GUIDs, of course). Thanks. It is stored on server -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "Avogadro" wrote: > I have 3 complex queries I need to ...