Length of Text

I have a text field with 4,5 or 6 character text content. In a query,
how can I extract all records with a length of 4 and then add 6
leading zeros to it

Thanks
0
TeeSee
11/20/2007 6:14:53 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
706 Views

Similar Articles

[PageSpeed] 48

For a select or update query?

Add an expression field to the query which has len(trim([MyField])) as the
expression, and 4 as the criteria.

Then add another expression field, with the expression as "000000" & [MyField]


If you are using an update query, the "000000" & [MyField] will go in the
Update To row for [MyField].

HTH

John


TeeSee wrote:
>I have a text field with 4,5 or 6 character text content. In a query,
>how can I extract all records with a length of 4 and then add 6
>leading zeros to it
>
>Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1

0
J_Goddard
11/20/2007 7:10:41 PM
On Nov 20, 2:10 pm, "J_Goddard via AccessMonster.com" <u37558@uwe>
wrote:
> For a select or update query?
>
> Add anexpressionfieldto the query which has len(trim([MyField])) as theexpression, and 4 as the criteria.
>
> Then add anotherexpressionfield, with theexpressionas "000000" & [MyField]
>
> If you are using an update query, the "000000" & [MyField] will go in the
> Update To row for [MyField].
>
> HTH
>
> John
>
> TeeSee wrote:
> >I have a textfieldwith 4,5 or 6 character text content. In a query,
> >how can I extract all records with a length of 4 and then add 6
> >leading zeros to it
>
> >Thanks
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1

Never understood the expression field until now. Many Thanks indeed!
0
TeeSee
11/20/2007 7:32:14 PM
Reply:

Similar Artilces:

length of segments
Is there a way to simply display the length of a line segment that has been changed from a straight line to an arc? Is there a way to simply determine the total lenght of two connected line segments or at least list their length for me to add together? The Shape.LengthIU property can give you this information programmatically. There is no way to get this directly from the Shapesheet. -- Mark Nelson Office Graphics - Visio Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "slimjim" <slimjim@discussions.microsoft.com...

Aligning text
XP and 2007. I am copying and pasting text from a PDF into PPT. In PPT, when I have a bullet with two lines of text, the bottom line of text does not align with the top line. It is one space to the left. If I use the middle hanging indent mark on the ruler to move the bottom line to the right one space, the top line moves also and I must then place the cursor in front of the top line and delete a space. This is 3 extra key strokes for almost every bullet. I have tried everything I know to do. Any help will be appreciated. Thanks. There may be a way around it using code, but as ...

How do I make text italic in a publisher text box?
I've copied an item into publisher. The font automatically changed. I changed the font back to what is was originally. That worked, along with items that were once again bold. However, the entire text WAS italicized and now it's not. How do I italicize my text now? Select the text and italicize it. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "81gator" <81gator@discussions.microsoft.com> wrote in message news:35607A56-CAE8-4E6D-8FD3-FC6545B2AA9A@microsoft.com... > I've copied an item into pu...

Macro to delete a text box and copy in new one
I need to set-up a macro to go from one file, file1, into another file, file2, and delete a text box in file2. Then, I need the macro to go back into file1 and copy a blank text box over to file2 and put it in the same location. Is this possible? Any information is appreciated. Thanks You could do it with something like: Option Explicit Sub testme() Dim fWks As Worksheet Dim tWks As Worksheet Dim fShape As Shape Dim tShape As Shape Dim tLocation As Range Set fWks = Workbooks("book1.xls").Worksheets("sheet1") Set tWks = Workbooks(...

Length of Variable names
I must make changes to code produced by developers long gone. Is it unusual to create variable names 50 or 60 characters in length?!? > Is it unusual to create variable names 50 or 60 characters in length?!? It does seem long but its valid. As a practice I seldom use names this long. If most or all variable names are this long, I would say its unusual. --- Ajay I think this is perfectly legal, but it makes it difficult to type the names :o) I've never had a problem describing my variable's function in less than 10 characters, but I've seen plenty of programs where peo...

Unable to add Hyperlink which is 255 characters in Length
Hi, Using windows xp SP3, and I am unable to add a hyperlink in powerpoint using the insert hyperlink option and copying a url which is over 255 characters in lenght. Does anyone know if there is a fix for this as it doesn't affect Ms Word. In article <B2114792-27F8-40F2-BFEC-76196F019D0A@microsoft.com>, Colson wrote: > Hi, > > Using windows xp SP3, and I am unable to add a hyperlink in powerpoint using > the insert hyperlink option and copying a url which is over 255 characters in > lenght. Does anyone know if there is a fix for this as it doesn...

Excel formatting of text-- one cell needed
I want to paste txt or doc text into one cell. Instead it populates the spreadsheet. A colleague who does not know English tried using CNTRL+X and was able to put the pasted text into one cell but could not duplicate it. Any ideas? Sample paste: -- I want to take this list -- Or group of statements -- Into one field in Excel But I get Cell A1 -- I want to take this list A2 -- Or group of statements A§ -- Into one field in Excel Thank you Double click in the cell to enter Edit Mode (or select the Formula box in the Formula bar) before pasting. In article <1D341060-EB4F-41...

how to create leading zeros on variable length numbers
I have a column of variable length numbers/text (6-12 characters). I need to place leading zeros in each of the cells to create a standard length of 15 characters. What's the easiest way to do this? sleect cells or column then pull-down: Format > Cells... > Number > Custom > and enter 000000000000000 in place of general -- Gary's Student "MVPitts" wrote: > I have a column of variable length numbers/text (6-12 characters). I need to > place leading zeros in each of the cells to create a standard length of 15 > characters. What's the easie...

Odd Text Box
Hello there, I have what looks like a text box on my Excel spreadsheet. However, I cannot highlight it and therefore cannot delete it. It does not have the normal borders of a text box in fact it looks a bit like a multiline combo box. Has anyone seen this before?? Thank you for your help Judith Maybe it's a Listbox. If it's a listbox from the Control toolbox toolbar, try showing that same toolbar. Then click on the Design mode icon. Then see if you can select the listbox. It could be from the Forms toolbar, too. Unprotect the sheet (if you need to) and see if you can rig...

can't delete text box word 2007
I am using Word 2007 and Windows Vista. I inserted several text boxes, and now want to delete them. I have selected the box and hit delete, but it is not working. I am sure the cursor is outside of the box, not in the text portion. Any suggestions would be greatly appreciated. Are they floating? This may be a bit of a bug. Try right-clicking and making them In Line. Then select and Delete them. -- Terry Farrell - MSWord MVP "TL" <TL@discussions.microsoft.com> wrote in message news:559DF2DC-1387-4E34-B4C9-CAAE8302090B@microsoft.com... > I am using Word 20...

clearing text from text boxes
Can someone instruct me on how to create a macro that clears text out of text boxes (from the control toolbox) please? Thanks in advance. As an example activesheet.oleobjects("TextBox1").object.text="" -- HTH RP (remove nothere from the email address if mailing direct) "Jae" <Jae@discussions.microsoft.com> wrote in message news:A8B1BEE1-1CA0-4196-BF21-BC67601B5FB4@microsoft.com... > Can someone instruct me on how to create a macro that clears text out of text > boxes (from the control toolbox) please? Thanks in advance. Hi Bob, I'm n...

Length Textbox
I have a textbox control in a template. I would like to put in VBA code (or some other way) to limit the number of characters entered in the field. Thanks, Stanley Use the MaxLength property. -- Cheers! Gordon Bentley-Mix "Stanley" <Stanley@discussions.microsoft.com> wrote in message news:C3CB3AF8-CACD-494D-B11E-41CEC3DE1D04@microsoft.com... > I have a textbox control in a template. I would like to put in VBA code > (or > some other way) to limit the number of characters entered in the field. > Thanks, > Stanley ...

Change Colour OF Text???
If I want to change a specific type of text in a spread sheet what do I have to do? For example; If I want to have a figure of £5.00 in green, but a figure of - £10 in red. How would I be able to tell Excel how to do this? Would it be a IF formulae? Any help would be much appreciated :-) You can email me @: james_dunford@msn.com You can use conditional formatting. There are instructions in Excel's Help, and here: http://www.contextures.com/xlCondFormat01.html James UK23 wrote: > If I want to change a specific type of text in a spread sheet what do I have > to ...

Full Text Index
Hello We have Exchange 2000 and have implemeted full text index on our PF store and it works OK apart from you can't search a public folder *and* it's subfolders I was wondering if a) Can Exchange 2000 be made to do this without 3rd party software b) if not, what is the best 3rd part s/w available that could do this without requiring more than Outlook 2003 on client c) Does Exchange 2003 do this out of the box d) Or is it a part of SharePoint Thank Rob. 1.) Exchange does not allow for Deep Traversal searches on the MAPI Public Folder Tree: http://support.microsoft.com/default.aspx...

Does Access store data in fixed lenght or variable length format
Hi, This is just for my own education and curiosity. Does anyone know if Access stores it data records in a fixed length or variable length records / rows? I realize that the answer is some what immaterial as it works the way it does, but as I said I would like to know for my own knowledge. Thanks, -- Dennis On Sat, 9 Jan 2010 20:27:01 -0800, Dennis <Dennis@discussions.microsoft.com> wrote: >Hi, > >This is just for my own education and curiosity. Does anyone know if Access >stores it data records in a fixed length or variable length records / r...

Hyphenation in text box
Publisher 2007 on Windows XP. When creating Web Page, text boxes do not hyphenate. 1 Tools>Language sub-menu does not show auto/hyphenation command. 2 Format>Text Box dialogue box shows "Hyphenation" checked and hyphenation zone of 1.5 picas, but command is grayed out and cannot be unchecked or zone value changed. 3 Manual hyphens show, but any change in wrapping leaves them in (the wrong) place. 4 Optional hyphens don't work: appear as spaces in Web Page preview Why is hyphenation grayed out and how can I restore? How can I auto hypenate text in Publisher text box...

Preview text disappears
In Outlook 2003 SP1, sometimes when a message comes in which is a reply to a previous message (that is, a sender has replied to the receiver), the preview pane shows the first couple lines of the sender's message, but opening the message shows no text from the sender, just the receiver's original message. I've found one other person on a web site forum who also is having this problem, so I know it's not just my user. Can anyone shed any light on this? Thanks! --- Mike VanHorn Senior Computer Systems Administrator College of Engineering and Computer Science Wright State Uni...

Receipt FOOTER or HEADER Bolder Text or Larger Text
How can I make some of the Footer or header lines on the 40 column receipt stand out. In a larger or Bold text. I want to get peoples attent to go to our website for a promational marketing study. can some give example of what to insert in the xml of the receipt footer header portion. fabiano, You can edit the receipt (xml file) and add another table below the ReceiptFooterLines, create seperate Font entry and put your own custom text in there. You can even get a little fancier and create your own receipt variable and have it appear in the Receipt Format section in Manager. That w...

String length of a Variable
If I assign a value to a variable (i.e. strNumber) then is there a way to know the number of characters (or text length) in VBA. i.e. strNumber = 1234, I want to be able know there is four characters (or text length). if strNumber = 12, I want to know there is 2 characters. Is there a function that will do that. Thank you. Lamar Len(strNumber) Regards Jeff Boyce Microsoft Office/Access MVP ...

Text To Speech
Is there any way to save a "Text To Speech" data into a wave file ?? Jibesh. V.P I did this once using CoolEdit (now known as Adobe Audition). It was extremely tricky, but I found out how to do in with a google search. This was about four years ago, and the best I can do is suggest a google search for "capture wave output" which is either what I used or close to it. joe On Thu, 19 Feb 2004 23:40:45 +0530, "jibesh" <jibesh.vp@m2comsys.com> wrote: >Is there any way to save a "Text To Speech" data into a wave file...

Splt a length record into multiple lengths
Hello I have a table which contains start and end points e.g. Start End 1.00 miles 3.00 miles Is is possible for me to create a query to split this mileage into multiple records in a seperate table e.g Start End 1.00 miles 1.50 miles 1.50 miles 2.00 miles 2.00 miles 2.50 miles 2.50 miles 3.00 miles Cheers Les. Les Not quite enough info ... How many segments? How long for each segment? What about "rounding"? More info, please... -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may h...

Changing Text Size When Replying to a Message
When I reply to a message using Outlook 2003, I am unable to change the text size by blocking (selecting) the text and changing the font size in my reply. Is there a way to do this? Thanks, Dennis ...

Axis label text wrap
I am trying to force my axis category lables in a line graph to wrap to 2 or more lines. I tried entering the alt-enter line break in the label's source cell. The line break shows in the source cell, but no change is showing in my chart. Any other suggestions? Thanks, sharonp7477 type the chart tile with spaces in between words see help under Rotate chart title text or axis text "sharonp7477" <sharonp7477@discussions.microsoft.com> wrote in message news:BE8404F7-EF7F-4EB6-A8EF-4A6D97DAA0D3@microsoft.com... >I am trying to force my axis category lables in a line...

CString maximum length.
Is there a maximum length to a CString? I'm doing a ".Format" for a CString. The resultant string is about 256 characters in length even though it should be longer. I'm looking at this in the debugger and I counted 254 characters, but I might be 1 or 2 off in my count. I can see in the debugger that string should be longer, but it's not. I'm using VC++ version 6. You need to look at the string in the memory window. The watch & variable windows restricts the string to 255 characters. You can also use the add-in mentioned at the following link ...

Text box on picture
Trying to put the year on a pivture so that I can put the picture on an ornament. How can this be done? I'm using Publisher 2003 Thanks, John Lay a text box on top of the photo. Choose a font color that stands out. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "text box on pictures" <text box on pictures@discussions.microsoft.com> wrote in message news:2034252A-08B0-48DC-9A46-4DEAD5098DF0@microsoft.com... > Trying to put the year on a pivture so that I can put the picture on an >...