How Do I Eliminate Brackets In MERGED Column

Nov 4, 2004

I recently MERGED two columns in a database file using the following code:

SELECT "(" & RESULT & ") " & QUALIFIER AS MERGED FROM SOIL_STL3_MERGE AS SOIL_STL3_MERGE;

The RESULT field was a number which I had to change to a TEXT field since my QUALIFIER was a TEXT field. Anyway the result was the field called MERGED which depicted the data as follows:

(100) U
(500) U
......
.......
I use the Find and Replace twice to get rid of the first ( and run it again to get rid of ) resulting in a field that looks like this:

100 U
500 U

My question is can I solve this through an UPDATE query statement through SQL or some other way with a function.

I would rather automate the removal of the ( ) or change my original code to NOT put brackets around my result.

Any and all help is most appreciated.

I thk you all in advance.

the raven man

View Replies


ADVERTISEMENT

How To Split Letter&number Field Into 2 Column And Skip Brackets?

Dec 2, 2005

HI!
I have big problem I have telephone number field like this
Tel(XX)XXXXX - X are numbers

I have to split it into two columns and skip brackets like this

Column1 - Tel
Column2 - XXXXXXX

Is it possible?

I have no idea how to manage it...I'd be very thankful for any
examples ? Or Help....I'm beginner

Thanks a lot
in Advance

View 1 Replies View Related

Underlined Merged Fields When Merging With Word

Jan 31, 2006

Does anyone know why when i link Microsoft Access with a Microsoft Word document, the merged fields in Word automatically underline?

Is there anyway I can stop the automatic underlining?

View 1 Replies View Related

Calling A Query Using .mde Files And Merged Word Documents.

Jan 17, 2007

Hi,

My situation:

A front end written in Access (back end mySQL).
I have made a .mde file out of this front end.
There are a number of MS Word documents used (as merge documents) for running reports and letters that are linked to queries in the database. However, I can only link these documents to the original .MDB file as you can't link to the queries ni the .mde file.

This works fine until you have a query that requires a parameter from a form. The user will have selected the parameter from the .mde version of the front end that they are using but the query which is being called from the .mdb version (by the Word merge document) can't see this parameter because it is a totally separate database.

How can I get around this problem?

Sorry this is a lengthy post, hope it makes sense.

Matt.

View 4 Replies View Related

Tables :: How To Import Excel Spreadsheet With Merged Cells

Sep 10, 2014

Is it possible to import an excel spreadsheet which contains some merged cells. What I have got is a spreadsheet (like Raw Data in the zip file) and I need it looking like (Finish Data) but in a access table.

View 3 Replies View Related

Reports :: Formatting Report To Resemble Merged Cells

Jun 13, 2014

I'm having trouble with field borders in a report I made. The image at the URL below shows what I've made and what I want (used Excel to make a simple version of my report).Using Access 2007

URL----> s13.postimg.org/wj4ghgz6f/2014_06_13_1045.png

View 6 Replies View Related

Where To Put The Brackets?

Aug 29, 2005

Greetings all,

Continuing to work on my problem and I am now getting and error of '3075' - syntax error (missing operator) in query expression

the error line is

strSQL = strSQL & "HAVING (tbl_data_DispatchDetails.TradingName) = " & varVal2 & " AND (tbl_Data_DispatchLineitems.WineNumber) = " & VarVal0


(I have to admit query language is not my strength)

Thanks in advance.

rbinder

View 3 Replies View Related

Too Many Brackets Query Sql Statement

Mar 1, 2006

WHERE (((zEnquiries.Enq_Forname) Like '*' & Forms!Enquiry_Search!Search2 & '*'))

this statement im sure has too many brackets.. can someone correct it for me

View 2 Replies View Related

Calculate Percentage Within Currency Brackets

Jan 7, 2008

Hey,

I'm unsure of how to do this one, any help would be appreciated.

The database I'm working on has around 2000 records within the parent table. These reflect projects around the world.

the child table has records on how much these projects cost.

I would like to calculate the % of projects cost less than £50,000, then, the % of projects between £50,000-£100,000 and so on.

i'm not entirely sure how to do this, anyone have any ideas?

Thanks

View 1 Replies View Related

Using Square Brackets As Criteria In A LIKE Statement

May 1, 2008

I need to use square brackets as part of the criteria in a LIKE SQL statement. The criteria is:

[ABC123][SR]

ie, find all records which have that as part of a longer text string. How can I amend the SQL statement below to "escape" the square brackets and treat them as part of the string?

SELECT * FROM p_Overall WHERE (((p_Overall.Assigned_Dept)="ICONS_IMAC")) OR (((p_Overall.Brief_Description) Like '*[ABC123][SR]*'));

Help!!!!

Thanks in advance :-)

View 3 Replies View Related

Question On Printing A Negative Number In Brackets: -5 -> (5)

Sep 7, 2004

I want to print negative numbers in brackets without the negative sign:
-5 beccomes (5)
I am doing this on a control that I am summing on. Currently this is what I am doing in the "Control Source" field.
IIF(Sum([field]) < 0, "(" & -Sum([field]) & ")", Sum([field]))

This works, however, I would assume it is running the sum 3 times (I do not know access internals, I may be wrong and would like to be corrected).

Is there a way to do this using the "Format" field??

Thank You
newbie and learning

View 5 Replies View Related

Modules & VBA :: Extract The Text Within The Brackets From String?

Nov 23, 2014

I have a column containing records of the timestamp of an event. I need to extract the date out of each of these records and put them in a separate table. The date and time of each record is contained within a bracket.

E.g.
ES~1~1412179200(Oct 02 00:00:00 2014)~1~ITM_W64MQMONITORLOG00~
0~0~ES~1~1412179203(Oct 02 00:00:03 2014)~1~ITM_Log_Entries~
0~0~ES~1~1412179204(Oct 02 00:00:04 2014)~1~ITM_Log_Entries~

As you can see, the number of characters of each record are different, so I am unable to do the Left$ count thingy.

Is there any other way to do it? I only need the date and time contained in the brackets.

View 2 Replies View Related

Queries :: Text Box Values Used In Query Contain Brackets

Sep 3, 2013

I have a query which uses values in two hidden text boxes, in order to populate a sub form.Unfortunately some times the data in the text box contains brackets within it as follows:

'120/60 ZR17 (55W)'

When this occurs the query returns no data, even though records with a matching code exists in the table I am working with where Speed is 'FR'.

Code:
WHERE (((stockdyn.SPEED)="FR") AND ((stockdyn.DESCRIPN) Like '*' & [forms]![frmSearch]![Text10] & '*'))

How can I get around this, as surely if I used quote marks it would take "[forms]![frmSearch]![Text10]" as the value I am searching for.

View 3 Replies View Related

Queries :: Wild Card Searches With Brackets

Jun 26, 2014

I want to prompt the user to enter a merchant name, but want the results to return close matches.I know how to use the wildcard in the Criteria field of the query, but I want to use brackets.I know that "*Southwest*" Will return Southwest Airlines.So I tried *[Southwest Airlines]* and it treats the criteria like a text string.

View 1 Replies View Related

Tables :: Showing Negative Numbers In Brackets Without Selecting Currency Format

Jul 7, 2014

Is there a way to show negative numbers in brackets without selecting the Currency format?

I want to show (75,000.00) and not -75,000.00

View 2 Replies View Related

Eliminate TextBox

Jul 9, 2007

hello,
another problem that I have is as follows:

I have 2 txtBoxes and a button (txtA, txtB, btn). When opening the form I fill in txtA and txtB. Now when clicking on the button "btn" (OnClick event), the program is supposed to compare the values in the 2 txtboxes. If they are equal (txtA=txtB), then I have to do something which is not relevant to my question. In the code I type:

if (Me.txtA.Value = Me.txtB.Value) then
' DO WHATEVER
End if

and it does not even go through the conditional statement if to do whatever I want the program to do inside the if. I would really appreciate anyone's help.

Thank you very much
PS: By the way, thank you boblarson and rainman89 for the past question, both solutions worked, I used boblarson's but I have to click on the form when it's opened in order for the changes in the table to take effect.

View 2 Replies View Related

Can I Eliminate This Redundancy?!

Sep 13, 2007

Hello guys & gals on here.

I've been struggeling with this problem for a couple of days now and finally decided to post here and kindly ask for assistance. I've got a pretty strong OO background, but haven't worked with databases much...

I'm trying to set up a small client database for my company. The client companies have staff members (StaffClients). Client staff can attend Events and work on Projects, which I've managed via junction tables. Our own Staff can also attend Events and work on Projects.

I've uploaded my first draft of tables and relationships here:
http://img514.imageshack.us/img514/3937/snapjl9.jpg
(sorry for the blurred bits)

Now I think I've done a fairly good job in normalising the tables, but one thing that I keep stumbling across is that I need two almost identical tables: one for our Staff and one for StaffClients.

They are subtly different, e.g. StaffClients have some extra attributes, such as JobTitle, Department and one StaffClient can be the assistant of another StaffClient (also done via a junction table). The other difference is that client staff can have different roles in a project (MainContact or Assistant) compared to our own staff (Manager or TeamMember).

As a result of these differences I have set up separate junction tables for both Staff and StaffClients, but are obviously very similar.

My question is: Is that structure sound? Or can I simplify it somehow before setting up the forms for the data entry?

Your input is kindly appreciated!!

View 9 Replies View Related

Eliminate Duplicates

Sep 25, 2007

Hello,

I have a problem with one of my tables and don't know where to start to fix it. This is where I stand: I have a table with 5 columns, there is no single primary key. I have duplicate entries of 4 of the 5 columns and a count number as the last column. I need to eliminate the duplicates of the combination of the 4 columns but add the count of the duplicates. Let me illustrate for you to understand better:

this is the original table:

col1 col2 col3 col4 col5
A B C D 1
A B C D 5
A B C D 2
W X Y Z 3
W X Y Z 5
W X Y Z 4

I need to obtain this in a new table:

col1 col2 col3 col4 col5
A B C D 8
W X Y Z 12

Oh, and keep in mind that the original table has almost 6 million rows that will probably get reduced to about 500 000 after this process.

Any help would be appreciated. Thanks

View 6 Replies View Related

Eliminate Records That Net To Zero

Apr 14, 2008

Hello All, I am looking for any advice on the following dilemma. I have a table with new monetary transactions that come in every day. I would like to have the transactions(one debit and one credit) that net to zero eliminated from the table automatically. What I was thinking was to have an abs amount column added and run the duplicates query as a delete query based on this column, but that would not only eliminate the amounts that net to zero...but similar debit and credit amounts. Does anyone have any thoughts on the most efficient way to do this? Im sure the answer is staring at me in the face, but I cant get my head around it. Thanks in advance!

View 3 Replies View Related

How To Eliminate A Double Quote

Nov 13, 2007

I have a string with one or more double quotes in it. How can I remove the double quotes? (I was able to remove single quotes.)

Example: Purchase "other items". (This the data in a column.)

I want to change it to Purchase other items

View 3 Replies View Related

Hasdata? Eliminate Error#

Oct 10, 2007

Hi i was hoping someone could help me with this. I’ve spent the last hour trying to figure it out. I'll keep trying, and let you know if i have any luck.

I have a calculation box which is based on a sub form,

(Long story short it counts stock items) when there is no stock available there is nothing to count so it returns the #Error message.

My starting expression is simply =[Child96].[Form]![QtyCalc]

I have read in this forum there is a "hasdata" function that can remove the Error#, but it may only apply to reports?

ASP Forums Link

I have tried this in an IIF statement but can't get it to work.
So far I’ve been looking at variations of the following

iif([Child96].[Form]![QtyCalc],hasdata = false,"0",[Child96].[Form]![QtyCalc])

Null can't be used, and I would prefer not to create a separate query instead of referring to the sub to calculate.

If anyone could help it would be appreciated.

View 9 Replies View Related

Eliminate Append Query Message?

May 27, 2005

Hey,

Is it posible to eliminate an append query or update, delete querey message. So that users wouldnt have to click "yes" in a msgbox that show?

THX

View 3 Replies View Related

Eliminate The Number Of Rows Returned Per Each...

Nov 1, 2006

Hi,

I have made a query from different tables; however, my query is returning about 5 rows for each person because some fields in the query return more than 1 row.

Is there anyway to fix this problem?

Thank you,
B

View 3 Replies View Related

Eliminate Duplicate Records In A Query

Jun 5, 2007

I know there a many posts about eliminating duplicate records because I did search, but I did not see anything that really answers my question.

I have a query in a local Access database that is using a Linked table from an SQL database managed by our corporate IT department. I have read access to only certain tables / fields in the SQL database. My query is based on 3 tables from the database and I have them linked together in my query.

When managers move from one site to another, they are given a different TeamID number. However, until they are replaced, the manager will have more than one TeamID assigned to him in the SQL database. Evidentally, the way our IT has the SQL database structured, when I query open items it duplicates the record for that manager because he has 2 TeamID numbers. So the only field that comes up differnt in the duplicate records is the manager's TeamID number.

I would like for my Select Query to ignore the duplicate record. I know I can use an Append Query to copy the data temporarily to a local table and set the proper fields as Primary Keys to do this. However, it would be nice if I did not have to go to all that trouble.

Is there a way to eliminate duplicate in my Select Query?

As always, I appreciate the help.

Jim

View 2 Replies View Related

Eliminate Duplicate Record In Combo Box.

Sep 15, 2006

Hi all,

This is an intersting question that my friend ask me... hope someone can help to solve it.

In a combo box i use to list out all the company in a table that with the duplicate of the company is allowed. Do someone have a good idea to filter the combo box when detect a duplicate data and just showing one of each company only? or can i create a query with just listing one of each company from the table with all those duplicate company data?

Thank you.

Regard,
alex

View 2 Replies View Related

Eliminate Blank Line In Address

Apr 28, 2008

[Solved] I have a report that is based a query formed from 2 tables. The query pulls the address from one table for a client from the other table. This address then appears on the report. There are 6 lines for the address: Company Name, Contact Person, Person's Title, Address 1, Address 2, City/State/Zip. I have this working fine, until an address is shown where there is no Title, or Address 2. Then a blank line is shown. Any suggestions on how I can eliminate the blank line if there is no information to show?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved