Remove spaces in the middle of a text value entered

This is my input mask.... >LLL####LL.LL

And this is what i get in the datasheet view ....  MGW1___HT.OK

Instead, i want to have this .... MGW1HT.OK or MGW12HT.OK or
MGW123HT.OK.

Can someone help me please?

E

0
emerlita
4/13/2007 8:28:46 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
1160 Views

Similar Articles

[PageSpeed] 43

On 13 Apr 2007 13:28:46 -0700, emerlita@shaw.ca wrote:

> This is my input mask.... >LLL####LL.LL
> 
> And this is what i get in the datasheet view ....  MGW1___HT.OK
> 
> Instead, i want to have this .... MGW1HT.OK or MGW12HT.OK or
> MGW123HT.OK.
> 
> Can someone help me please?
> 
> E

So why are you using an Input Mask if it doesn't do what you want?

If you are just looking to get all Upper Case characters with no
spaces, let the user enter the data however the wish, in upper or
lower case, with or without spaces.

Then code the control's AfterUpdate event:

Me![ControlName] = Replace(UCase([ControlName]), " ", "")

-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
4/13/2007 10:06:25 PM
Input masks can't handle that sort of variable pattern. 

Instead, use an input mask of 
	>
to convert everything to upper case, and a validation rule to check the
pattern. Something like this should do:
	
LIKE "[A-Z][A-Z][A-Z]###[A-Z][A-Z].[A-Z][A-Z]" OR
LIKE "[A-Z][A-Z][A-Z]##[A-Z][A-Z].[A-Z][A-Z]" OR 
LIKE "[A-Z][A-Z][A-Z]#[A-Z][A-Z].[A-Z][A-Z]" OR IS NULL

Omit
  OR IS NULL
if you don't want to allow the field to be left blank. 


On 13 Apr 2007 13:28:46 -0700, emerlita@shaw.ca wrote:

>This is my input mask.... >LLL####LL.LL
>
>And this is what i get in the datasheet view ....  MGW1___HT.OK
>
>Instead, i want to have this .... MGW1HT.OK or MGW12HT.OK or
>MGW123HT.OK.
>
>Can someone help me please?
>
>E

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.
0
John
4/14/2007 11:56:54 AM
On Apr 14, 5:56 am, John Nurick <j.mapSoN.nur...@dial.pipex.com>
wrote:
> Input masks can't handle that sort of variable pattern.
>
> Instead, use an input mask of
>         >
> to convert everything to upper case, and a validation rule to check the
> pattern. Something like this should do:
>
> LIKE "[A-Z][A-Z][A-Z]###[A-Z][A-Z].[A-Z][A-Z]" OR
> LIKE "[A-Z][A-Z][A-Z]##[A-Z][A-Z].[A-Z][A-Z]" OR
> LIKE "[A-Z][A-Z][A-Z]#[A-Z][A-Z].[A-Z][A-Z]" OR IS NULL
>
> Omit
>   OR IS NULL
> if you don't want to allow the field to be left blank.
>
> On 13 Apr 2007 13:28:46 -0700, emerl...@shaw.ca wrote:
>
> >This is my input mask.... >LLL####LL.LL
>
> >And this is what i get in the datasheet view ....  MGW1___HT.OK
>
> >Instead, i want to have this .... MGW1HT.OK or MGW12HT.OK or
> >MGW123HT.OK.
>
> >Can someone help me please?
>
> >E
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newsgroup and not by email.

Thank you all!!!

0
emerlita
4/17/2007 4:05:38 PM
Reply:

Similar Artilces:

Unable to enter a transaction
Has anyone a solution for the problem where Money does not allow you to enter a transaction, such as a purchase or sale of a stock? I attempt to record the details of a transaction and after I select 'OK' to enter the transaction details to my account, I receive a dialogue message, "Unable to enter transaction". I deleted many older transactions from my account, however, the problem still exits. Help...Thanks! See http://umpmfaq.info/faqdb.php?q=89. "Antonio Fazzalari" <antonio_fazzalari@yahoo.com> wrote in message news:2d9201c47e2a$8e853fc0$a60128...

Random text selector
I'm looking for a formula similar to the RANDBETWEEN, but for text No numbers. I'm trying to create a drawing, a macro when i run wil select any random name from any number of cells. thanks -- Message posted from http://www.ExcelForum.com You must have a lot of time on your hands <g> Have you considered Illustrator for drawings? OTOH, Illustrator is a crappy spreadsheet program, although it has the capability to create some pretty darn good charts. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "blamenoone >" <<blamenoone.zs4f4@excelf...

SumIF based on part of text in a cell
Hi, Is there any way that I can use the SUMIF function but looking for only a part of the text in a cell as a criteria. See the example below: On a raw data spreadsheet i have: A B C BB 120x600 Time Out 10,121 15 BB 468x60 Time Out 9,979 2 BB 120x600 PC Titles 33,823 114 BB 468x60 PC Titles 35,507 49 BB 120x600 ROS 5,726 15 BB 468x60 ROS 5,712 2 And on a formatted report sheet i have: A B C Time Out x y PC Titles x ...

Change a cells value with a function
Here is my function contained in O74. C70 has "Water Heater" in it and I73="2". Why doesn't N74 have FALSE in it? Also, is there a way to control a Forms checkbox with a function? =IF(C70="Water Heater",IF(I73<39,N74="FALSE",N74="TRUE"),IF(I73<99,N74="FALSE",N74="TRUE")) -- Cheers, Ryan A formula can't edit the value of another cell; it just returns a value to the cell containing the formula. Try this formula in N74: =IF(C70="Water Heater",I73>=39,I73>=99) I think the ...

Return every 50th Value?
Excel2003 ... Range N3:N5002 ... contains 4 char Text (0200, 0340, 1050, etc) each value repeats 50 times. In Col D ... I wish a formula to return 1st occs only of each Value found in Col N ==> I can get results using "Advanced Filter Unique Records", but I need to do this by formula (if I can?) Thanks ... Kha something like this maybe pasted down for the entire range? it should show the number of the occurance for each cell =COUNTIF(N3:$N$3,N3) "Ken" wrote: > Excel2003 ... > > Range N3:N5002 ... contains 4 char Text (0200, 034...

returning a value in a table referencing ranges
I have a chart 1 that has a list of ranges and if the number falls with in the range I want the commision value returned to my new work sheet ex: Chart 1 Col A Col B Col C 500 599 50.00 600 699 55.00 if the calculated total on my seperate worksheet A is between 500 599 I want the 50.00 to pull to worksheet A. -- Tammy Assuming your data in A2:C3, and your criteria in E2 =LOOKUP(E2,A2:A3,C2:C3) "tveith" wrote: > I have a chart 1 that has a list of ranges and if the number falls with in > the range I want the commision va...

Null display if value is $0.00?
I have a report with multiple rows and columns which takes on sort of a spredsheet appearance. Is there a way to have fields where the value is $0. 00 (currency format) appear as if they're blank? All those 0's tends to make the report a bit cluttered. Thanks! Slez -- Message posted via http://www.accessmonster.com hi, Slez via AccessMonster.com wrote: > I have a report with multiple rows and columns which takes on sort of a > spredsheet appearance. Is there a way to have fields where the value is $0. > 00 (currency format) appear as if they're blank? If your re...

How to solve problem (equal to 0) with unknown value
I'm trying to solve a complex problem with an unknown value that is equal to zero using solver. How do I do this? ...

Combobox values as query releted to current record
Is it possible to populate a combobox (just to show related data, not for selection) on a subform. And in such a way that in datasheet view the combobox when clicked show the data for the relevant record of the subform. The subform is related to the mainform of course. Just a mindgame, suppose I have a mainform which displays persons who own farms. On the subform all the farms are listed in datasheetview with some related info. Now I would like to add a combobox which when pressed displays the animal groups (dogs, cats, pigs, cows etc.) which are living on that particular farm, so when you go...

find formula that will look up a value in a specific column and .
I am looking for a formula that will find a certain text value (open) in a column (J) and pull that row (or row #) to a new sheet. Does anyone know if there is a way to do that? Ksgirl Providing the data starts in J1 the formula below will return the row number. Bear in mind that a formula can only return a value, not move data. You may also be looking for a VLOOKUP, it was just the way your question was phrased that made me return a row number =MATCH("Open",Sheet1!J:J,0) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS ...

How to add commas to the result of a formula with a text operator?
="Balance is $"&ROUND(N53,2)&"." The display is "Balance is $12345678.90." How can I add commas? Formatting the cell to currency doesn't do anything. Workarounds like putting the text in an adjacent cell aren't acceptable here. Use ="Balance is $"&TEXT(ROUND(N53,2),"$#,##0.00")&"." -- Regards, Peo Sjoblom (No private emails please) "jlrichman" <jlrichman@discussions.microsoft.com> wrote in message news:65C6DB8C-04C2-4EC5-81F7-6B807D308E64@microsoft.com... > ="Balance is $&...

conditonal format A2 using value in another cell C2
Using MS office 2007. How to conditional format cell A2 basing on the value of the cell C2. The color fill is white if the value of the cell C2 is "ongoing" and red if the value is "completed". 1. Select the cell A2. 2. Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format. Enter the formula in the box below. =C2="completed" 4. Click Format Button>Pattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria &...

Value 1 in excel cell show as 0.99999999999999978 once save in XML format
Hi all, I receive an excel sheet , with a column which is format in number. The value that i seen from the screen is '1'. Later, when i save the file in . xml format (File->Save As, choose 'XML Spredsheet' .xml' ) i found that the '1' had become '0.99999999999999978'. I relook into the spread sheet again, the value is really show in '1' in the formula bar, and double check on the format it is formatted in number with zero decimal place. I don't have a clue where the '0.99999999999999978' is come from ? Could it be an excel bug ? o...

Outlook Format and line spacing.
I write a message/letter on HTML format single line spacing, and when the Reply comes back to my Inbox my original message is in double line spacing. Even when I write a "test" letter to myself, again in HTML single line space, the letter appears in my "In box" in double line spacing. How can we cure this? ...

Text Box Tool Greyed Out
Went to use the text box tool on a sheet and found it greyed out. Does anyone have any idea why this may have happened? TIA Zorro I expect you have sheet protection turned on http://www.mvps.org/dmcritchie/excel/grayedout.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Zorro" <zorro@nexia99.freeserve.co.uk> wrote in message news:k1B1c.3660$Kc6.507@news-binary.blueyonder.co.uk... > Went to use the text bo...

Changing text on convert lead webpage dialog
In the Opportunity form, I have renamed the "Potential Customer" Attribute to "Reseller". But on the Convert Lead webpage dialog, under Opportunity - the lookup field still displays as "potential customer". Is there anyway of changing this text? Hello, I think this webpage dialog is not customizable using the standard tools. I remember having a customer wanting to add functionality to the convert lead webpage. If someone has an idea about this would be nice. Best regards, Merijn van Mourik "kwhite" wrote: > In the Opportunity form, I have ...

incremenT CountIF Value #3
Sorry alex you are right it does work - didn't quite get it the firs time! Thanks very muc -- infojma ----------------------------------------------------------------------- infojmac's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1078 View this thread: http://www.excelforum.com/showthread.php?threadid=27313 ...

Removing Layaways from RMS
Is there a quick way to remove all past layaways from the RMS SQL Database? When layawyas were used in QS2000 at my store, they were never entered correctly. I would like to blast all old layaway information, instead of deleting each single line at a time. Thanks in advance, Ryan ...

5.5 database does not release free space
I've 5.5 Std on NT and a 16 Gb database. :( After many users reduced their mailboxes using .pst and released at least 2Gb, I can't obtain free space doing eseutil /d commands I've already backed-up the database and restarted and no good news Any Idea ? I'm considering to upgrade to 5.5 Enterprise. (When i discover how to obtain the software) Thanks Germa On Tue, 14 Jun 2005 21:01:11 +0200, "GG" <germaguerra@hotmail.com> wrote: >I've 5.5 Std on NT and a 16 Gb database. :( > >After many users reduced their mailboxes using .pst and released ...

Remove vendor from item
How does one remove a vendor tied to an item (Item Vendor Maintenance) in the following scenario? A purchase order was created; an incorrect vendor was chosen for an item. The Purchase Order was cancelled....Now when we try to remove the vendor from the item in the Item Vendor Maintenance window, a pop-up box states "You can't delete this item; it is being used on a purchase order" We are new to GP, so any guidance would be appreciated. Ken, If the purchase order was cancelled, you can remove its history under Microsoft Dynamics GP > Tools > Routines > Purchas...

Text Box Movement Problems
Hi all. I some how changed the way my curser box moves around the spreadsheet. I usto be able to move the curser box around in the spread sheet by using the arrow keys on my key board. Now in all spread sheets, when I use the arrow keys on my keyboard, the spread sheet moves and the curser box stays put in the cell it was left in. Does anyone know what I inadvertently changed to cause how my worksheets move around? I would like to get back to using the arrow keys on my keyboard to move the curser box from cell to cell. Hi It sounds like you have Scroll Lock on (just near Functi...

count number of values between plus signs in addition calc
I'd like to count the number of values that a user enters in an excel simple addition calculation. For example, if the cell has "=17+256", I'd like to get the count of "2". If the cell has "=101+2+0+65", I'd like to get the count of "4". Is there a way to parse this 'function' string? Everything I've tried so far starts *after* the cell value has been calculated (e.g. it's working with a single value of 273 and a single value of 168 for the two examples above, respectively). Thanks, - Cathy Hi Cathy, Use this Use...

How to change a value returned in a listbox
Hi all, I have created a search screen which returns records with values that I have specified. I am displaying the results in a listbox which is fine. However I am wanting to change how the returned values are displayed. For example I have a column with values of 1 and 5 which I want to display as 1= Active and 5 = Closed. Is there a way to do this in the code. Thanks Alex On Tue, 19 Jun 2007 00:31:58 -0700, Alex.Spodkiewicz@gmail.com wrote: >Hi all, > >I have created a search screen which returns records with values that >I have specified. I am displaying the results in a l...

I get gaps in bar chart when using weekday values only in x-axis
Plotting a stock bar chart and using weekdays only (no weekend date values) as the x-axis (category labels) leavs med with gaps in the bar chart. How do i plot the chart without the gaps? Regards If you are talking about gaps between the bars for each day, double click on the series you want to format and select the OPTIONS tab. Adjust the GAP WIDTH as needed. "Bj�rn Elfvin" <Bjrn Elfvin@discussions.microsoft.com> wrote in message news:B91F7487-0D1E-4F58-96B8-2FCA2DBE507E@microsoft.com... > Plotting a stock bar chart and using weekdays only (no weekend date values) ...

Custom CEdit Text Control
Ok I spent over 24 hours on groups just trying to sub class an MFC CEdit control. All the information I found was correct but I still had to work out a good 30% of the solution myself. Bits of vital - assumed - information was missing. I am posting this in an attempt to stop anyone else having to spend so much time to perform such a simple task. First forget SubClassDlgItem(). This can be used if required, but just obfuscate the code. Ill highlight points of interest and gotchas I found!!!!! Here is the source: This is the custom class .h file which inherits from CEdit. ///////////////...