How To Add Spaces To Text String?

Sep 21, 2005

Access 2000. I have table of many hundreds of records. One of th fields of text looks like this: XXXXXXXXXXXX and I want it to look like this: XXX XXX XXX XXX by adding the spaces. Would someone please show me how to write the function or code to add the spaces to the text as shown : Thanks :o

View Replies


ADVERTISEMENT

Spaces In String

Aug 8, 2005

How can I remove the spaces in the example string!

HICX108-Standard Top -Vendered

Thanks in advance!

View 2 Replies View Related

Delete Spaces In Text Field

Jun 28, 2006

We have a table with approx 2m records. Just three fields:
Key (primary)
Postcode
Postcode reference

The problem is with the postcodes. Many of these have a double space in them such as this DE13 7EL but we need DE13 7EL.

Find and Replace runs into a problem because of the number of records.

An update query seems obvious but we can't figure out what to put in Update to as this appears to return the literal value.
We are not VBA programmers so that isn't really an option for us.
How can we strip out that extra space?

View 6 Replies View Related

Reformatting A Text Field With Spaces

Dec 7, 2006

Hi

I have a table with postcodes (1.6M rows) that have had all the spaces removed. I want to match another table to it and ordinarily would have removed the spaces from the postcodes in that but I think because of the vol (2.5M rows) Access is reluctant (it either crashes or only updates a few thousand).
A better way would be to reformat the postcode using some SQL or VBA code. The trouble is the postcodes are different numbers of characters and the space would either need to be after the 3rd when there are 6 characters or after the 4th when there are 7 characters.
eg PO121DL becomes PO12 1DL
PO91UL becomes PO9 1UL

I can concatenate fields and count characters but am unsure as to the best way to approach this in terms of coding and structure? Using Access 2002.

Any thoughts/tips etc would be appreciated thanks

View 2 Replies View Related

Removing Unwanted Spaces In A Text Box

Feb 2, 2006

I have two questionaire forms in a database. There are no tables or queries involved.
The answers to each questionaire are compiled into text boxes, one on each Form. On the main form I have another text box that puts all the answers together so that this can be transferred to another program. An example of what is in each text box is as follows:

Text1: IIf([Check325]=True,[Combo77] & " " & [Combo59] & " " & [Text65] & " " & [Text69],"") & " " & IIf([Check329]=True,[Combo78] & " " & [Combo61] & " " & [Text66] & " " & [Text70],"")

Text2: IIf([Check326]=True,[Combo102] & "" & [Combo188] & " " & [Combo91] & " " & [Text94] & " " & [Text97],"") & " " & IIf([Check330]=True,[Combo103] & " " & [Combo92] & " " & [Text95] & " " & [Text98],"")

These are just small extractions. My problem is, if an answer is not necessary in the first lot of text, but an answer has been supplied in the second lot, how do I remove the spacing that has resulted from my Formula in Text1. -

e.g: Normal answers would show perhaps
Yes Twice 16 25 Red Peter Ford Football 1965 - Whereby the first 4 answers are from Text1 and the remaining answers from Text2.
If some answers are not necessary from Text1 I would get the following result:
SPACE SPACE SPACE Twice Red Peter Ford Football 1965

Any assistance would be gratefully accepted.

View 3 Replies View Related

Search String Using Spaces As "Or"

Sep 29, 2006

Friends,

I'm using a search string to filter a continuous form. In an attempt to duplicate "Google", I'd like to use a single search field for multiple criteria.

Is there a way to use the space bar as an "OR" event in my search string?

example: my fat head
would look like: "My" OR "Fat" OR "Head"

I presently use code which strings together multiple search fields with an AND. Here's what I've been using:


Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtLocation) Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtLocation & "*"") AND "
End IF
If lngLen <= 0 Then
strWhere = Left$(strWhere, lngLen)
Forms!frm_SearchSortEvents.filter = strWhere
Forms!frm_SearchSortEvents.FilterOn = True
End If


End Sub

Thanks for you input!

View 2 Replies View Related

Export NULL Spaces To Text File?

Nov 10, 2004

Hi members,
I have two empty columns in my access database whose total width is supposed to be 385 chars (255+130). I am trying to find a way to be able to export this blank field with a total of 385 blank spaces onto an ASCII text file.
A sample row looks like this:
L ***** 385spaces needed here ***** 888888 EXAMPLE DEALER NAME 1234 EXAMPLE LANE EXAMPLE ADDRESS LINE 2 EXAMPLE CITY CA88888 80088812348008881234FORD JOE DEALER NAME 8008881234

After the first letter L in position 1, I need to have 385 blank spaces and then at 387th place I have some other data to follow.
I tried a lot to use the export wizard, but it doesn't help. Is there a way to do this?
I would really appreciate any help.
Thanks,

View 4 Replies View Related

Show Text Box In A Form Based On Character Within A String In Another Text Box

Dec 21, 2012

I have a form where I want a textbox [txtMaxOrdLimit] to be visible only if another text box on the same form [PaNumber] contains the letter D in the string. This is the code I have on the forms On Current property but I'm missing something because textbox [txtMaxOrdLimit] doesn't show on the form at all.

If Me.PaNumber = "*D" Then
Me.txtMaxOrdLimit.Visible = True
Else
Me.txtMaxOrdLimit.Visible = False
End If

View 3 Replies View Related

Look Within Text String For A Value

Oct 25, 2006

Hey Guys,

I've got a field called [WordLink] on a form where the user enters the file name of the Word or Excel document asociated with the record.

I have a button that opens Word or Excel and then opens the file...not a problem there as I'm using the Call Shell("""C:Program Files command and that works just fine.

However, I need the button to look to see if it's got ".doc" or ".xls" within the text string in the field so it knows whether to open Word or Excel.

I've tried something basic to launch something like a msgbox (see code below) but obviously it doesn't work. How do I look within a text string for a value?

Cheers,

Russ
:D



If Me.WordLink_filename.Value = "*.doc" Then
MsgBox "This is a word file."
Else
End If

View 2 Replies View Related

Get The Value Of F2 If F1 = Text String

Feb 7, 2005

What is the VBA code for this:

VariableName = Select Table1.Field2 From Table1 Where Table1.Field1 = "TextValue"

I would like to set the VariableName to the value of field2 where field1 is equal to a text string.

Thank you for taking the time to review and answer this for me.
Marty

View 5 Replies View Related

String Text

Apr 25, 2006

I have created a report that has string text from a query I created. The query, with the string text, take the information from a table. When I run the report the string text gets cut off (The box is big enough to hold the information). The table I have is a linked table to a text file that gets imported from a program. I try creating the same table but not having it linked and the string text worked.

Is there some way I can get around this? Is there something in the linked table that is stoping the string text?

View 14 Replies View Related

Getting All Records In One String Text

Aug 7, 2005

First of all I'm not very good with VB. What I'm trying to do is get all my customers email addresses in one text string. So it would go like 123@aol.com; bob@yahoo.com; fred@hotmail.com; and etc. My customer could then copy it and paste it in her TO: box of her email server. Any help would be great.

View 6 Replies View Related

Forms :: Max Value Of Text String

Aug 21, 2014

I have a text field with receipt numbers in the format 0001-00000### and I would like my data entry form to default the max existing value + 1.

If max value is 0001-00000201, then the new record should suggest 0001-00000202.

The problem is that if I use the "max" function, it does not work (I think because that function is intended for numbers, not text strings).

Table: RECEIPTS
Field: receiptnum

I also have a query with just one field that lists only the receiptnum unique values so that I can use them in comboboxes in other forms... it may be useful I guess...

Query: unique-receiptnum
Field name: receiptnum

View 4 Replies View Related

Removing Selected Text From A String

May 23, 2006

Dear All,

Thank you for taking the time to look at this Question.

I am using a form in which i only need the last sections.

example:-
G/001 - Ceiling - Tiles - Perforated. this is what i get at the second, but i would like to drop the "G/001 - " and just have "Ceiling - Tiles - Perforated.", has anybody got any ideas on how to do this please.

View 1 Replies View Related

Formating Number Within Text String

Oct 6, 2006

looked around but could not find the answer.
i have a text box that might or might not contain text and number data. an example of the data in the text box might be;

bob roberts, 17/03/2006, aged 23

what i would like is some VB code that would:

check anywhere in the text box for number characters that are 8 characters in length with a comma at the end [12345678,] as in the first example. and change this to 12/34/5678,

bob roberts, 17032006, aged 23
would become 'on lost focus'
bob roberts, 17/03/2006, aged 23

anybody any idea how to do this?

thanks

View 2 Replies View Related

Format Number Within Text String

Oct 19, 2006

hi
I'm no expert and can't seem to find what i'm looking for.

i have data i enter into a text box. the data might be in the format

"name"
OR
"name, date, age"
OR
"name, date"

if on the second or third example above i entered the data like shown below how would i VB code the afterupdate to do this:

turn
David Roberts, 12122006
to
David Roberts, 12/12/2006

OR turn
David Roberts, 12122006, 56
to
David Roberts, 12/12/2006, aged 56

any ideas??
I'm working along the lines of instr, mid, etc. but i'm not sure of the correct procedure
thanks in advance

View 5 Replies View Related

Queries :: Pull Text From String?

Apr 2, 2015

I have a text field in a Table and on a Query called "Notes" In that field that has data like below:

[04/02/2015:BD] Project is to be assessed by Solutions Planning
[03/27/2015:BD] Project prioritized
[03/14/15:BR] Entered to system

Im trying to find a way to pull just the most recent line of text, in this case

[04/02/2015:BD] Project is to be assessed by Solutions Planning

into the field next to "Notes" or wherever - an empty field in the query. I searched around, found some stuff and I was thinking of having the code look at the first "[" and count the length to the next "[" and pull out whats in between. Looks like the bracket causes issues in the module.

View 10 Replies View Related

Converting Text String To Its ASCII Value?

Mar 18, 2015

I have a string that includes mm/dd/yyyy + person's name. All of this is concatenated and is recognized as a text value. Then I want to convert all of these characters to a number. Since each character has an ASCII code, I would like to find ASCII equivalent.

In Excel, this would involve using something like the CODE ( ) function, but that only gives the numerical equivalent for the first character in the string, but I want all characters at once.

When I try the VAL ( ) function, it returns the first character which is the number of the month.

View 4 Replies View Related

Extracting Middle Initial From Text String

Dec 21, 2005

Hello,

Is there an easy way to split a full name into firstname, middle name and lastname? I have a field name with names such as James R Lowes and i would like to split the name in 3 like.
Firstname : James
Middle Initial: R
Lastname: Lowes

I was able to find a module to extract the first name from a string, but don't know much about VB and cannot figure out to do the rest.

Please help!

thanks,

Pablo

View 5 Replies View Related

Extracting Middle Initial From Text String

Dec 21, 2005

Hello,

Is there an easy way to split a full name into firstname, middle name and lastname? I have a field name with names such as James R Lowes and i would like to split the name in 3 like.
Firstname : James
Middle Initial: R
Lastname: Lowes

I was able to find a module to extract the first name from a string, but don't know much about VB and cannot figure out to do the rest.

Please help!

thanks,

Pablo

View 14 Replies View Related

Help Passing A Text Box String To A Saved Query

Sep 21, 2005

I have a multi-select list box that runs a "For intCounter" and builds a sting into a text box. That works fine the text box will populate as designed <"Closed" OR "On-Going">.

(I know that I should be dynamically creating the query in the first place ... normally I would ... but this is a quick fix that I want to get it into an existing application, while the redesign requirements are being written.)

Now I want to pass that string to a pre-existing query, but I'm not sure what to write in the criteria section.

I've tried...
Like "*" & [Forms]![ViewReports]![TextPickList] & "*"
IIf([Forms]![ViewReports]![TextPickList]="","",[Forms]![ViewReports]![TextPickList])

and just plain old ...
[Forms]![ViewReports]![TextPickList]

but my query is coming up blank. What do I need to write into the criteria section of this saved query?

thanks in advance!

View 2 Replies View Related

Formatting A Date In A String Also Including Text

Feb 2, 2006

Hi there,

I have to format a date following dd-mmm-yy in an expression also containing text.

Expr1: "Text" & " " Date()

Although separately formatting the date is easily done. I need to keep both int he same expression.

View 3 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 :: Return Value In A Text Box On A Report If String Contains?

Mar 24, 2014

In my query, I have the week number and year arranged like this - "Y14-W11"

I want to return a value in a text box on a report if the string contains, for example, W11. In this textbox I've put the expression

Code:
=IIf([Y##-W##]="*" & "W11" & "*","2100000","BLAH")

But this just returns the falsepart no matter if the string contains W11 or not.

View 3 Replies View Related

General :: Extract Text And Numeric From A String

Feb 12, 2013

"Att Mr/Mrs Vezi Your DEFAULTED account has been handed over to XXX. Payment DUE IMMEDIATELY Ref 1315519509. Tel 0009377500"

I need to extract the word "Ref " plus the number that follows it from the above string. The problem is that the word "Ref " is not always in the same place and the number following it is not always the same length. (ie it could look like "Ref 123456 " or "Ref 1234567 ") The only commonality between the records is that the number will always follow the word "Ref ".

View 1 Replies View Related

Queries :: Can't Evaluate Date In Text String

Apr 17, 2014

I'm working with a table of financial transactions. I need to know the date a record relating to cash received actually arrived in our bank, but the software doesn't allow the user to enter this, so I've asked them to enter it into a text reference field, e.g. 'Other Text XX 16/04/14', and then I've got a formula - CDate(Right(Trim([RefField]),8)) - which takes the date from this reference.

This works fine, but it's really important I don't miss any of these records due to the date not being entered, so what I'm trying to make is a formula which will give me the date from the ref field as in the formula above if there is one, but if that formula gives an error (so the date has been missed out or incorrectly entered) then I want to take the date from another field, called [DateAdded] as a failsafe.

I can't work out a formula that won't give me an error, i've tried loads. First I tried :

iif(iserror(CDate(Right(Trim([RefField]),8))),[DateAdded],CDate(Right(Trim([RefField]),8)))

But that gives an error, and I think from looking at forums that Access evaluates both parts of an Iif function so it'll do that.

It seems like other people are saying that you need to use Nz, IsDate or IsNumeric or something along those lines instead of IsError to evaluate the field, but I can't get that to work as it's not a field, it's a portion of a text field.

This is my latest attempt:

Join Date: IIf(IsDate(Right(Trim([RefField]),8)),[DateAdded],[Calc Date]),

where [Calc Date] is an expression field with CDate(Right(Trim([RefField]),8)) in it, but there's the #Error again.

How can I use iif to pick either a date from a text string, or where that results in an error then another date field?

View 9 Replies View Related







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