Eliminating leading spaces in numbers in a combo box

I have designed a database to record saving deposits made by members of a 
local Credit Union.  A number of members act as collectors on a rota basis, 
when the office is open.  The collection form has a combo box which is bound 
to the member's ID number in a "Member" table.  The combo box is set to 
"Limited to List", and I use the "Not in List" event if a new member's ID 
Number is to be added.  

My problem is that several of the members seem to be pressing the space bar 
before they start typing the members ID. This triggers the "Not In List" 
event because it thinks that new new member is being added, causing a 
duplicate name with a similar ID number to be added.  How can I eliminate the 
leading space before the "Not in List" event is triggered.  I have tried 
using the left() and Mid() functions as follows, but the "Not in List Event" 
is still triggered.

If Left(Member ID,1) = " " then
  Member ID = Mid(Member ID,2)
End If

I designed the database on Access2007, and saved it for use on Access2000. 


0
Utf
1/14/2008 6:28:19 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
536 Views

Similar Articles

[PageSpeed] 43

Use the Trim function to remove spaces before and after the string when the 
value is added to the table

= Trim(Value)


-- 
Good Luck
BS"D


"ArkRoyal" wrote:

> I have designed a database to record saving deposits made by members of a 
> local Credit Union.  A number of members act as collectors on a rota basis, 
> when the office is open.  The collection form has a combo box which is bound 
> to the member's ID number in a "Member" table.  The combo box is set to 
> "Limited to List", and I use the "Not in List" event if a new member's ID 
> Number is to be added.  
> 
> My problem is that several of the members seem to be pressing the space bar 
> before they start typing the members ID. This triggers the "Not In List" 
> event because it thinks that new new member is being added, causing a 
> duplicate name with a similar ID number to be added.  How can I eliminate the 
> leading space before the "Not in List" event is triggered.  I have tried 
> using the left() and Mid() functions as follows, but the "Not in List Event" 
> is still triggered.
> 
> If Left(Member ID,1) = " " then
>   Member ID = Mid(Member ID,2)
> End If
> 
> I designed the database on Access2007, and saved it for use on Access2000. 
> 
> 
0
Utf
1/14/2008 6:32:01 PM
In addition

If the values are numeric, using the Val function will remove spaces
Val([Value])


About you code, for future reference, when using a field that is sperated to 
two names or more, you need to put it in square brackets

If Left(Member ID,1) = " " then

Into
If Left([Member ID],1) = " " then
-- 
Good Luck
BS"D


"ArkRoyal" wrote:

> I have designed a database to record saving deposits made by members of a 
> local Credit Union.  A number of members act as collectors on a rota basis, 
> when the office is open.  The collection form has a combo box which is bound 
> to the member's ID number in a "Member" table.  The combo box is set to 
> "Limited to List", and I use the "Not in List" event if a new member's ID 
> Number is to be added.  
> 
> My problem is that several of the members seem to be pressing the space bar 
> before they start typing the members ID. This triggers the "Not In List" 
> event because it thinks that new new member is being added, causing a 
> duplicate name with a similar ID number to be added.  How can I eliminate the 
> leading space before the "Not in List" event is triggered.  I have tried 
> using the left() and Mid() functions as follows, but the "Not in List Event" 
> is still triggered.
> 
> If Left(Member ID,1) = " " then
>   Member ID = Mid(Member ID,2)
> End If
> 
> I designed the database on Access2007, and saved it for use on Access2000. 
> 
> 
0
Utf
1/14/2008 6:35:02 PM

"Ofer Cohen" wrote:

> In addition
> 
> If the values are numeric, using the Val function will remove spaces
> Val([Value])
> 
> 
> About you code, for future reference, when using a field that is sperated to 
> two names or more, you need to put it in square brackets
> 
> If Left(Member ID,1) = " " then
> 
> Into
> If Left([Member ID],1) = " " then
> -- 
> Good Luck
> BS"D
> 
> 
> "ArkRoyal" wrote:
> 
> > I have designed a database to record saving deposits made by members of a 
> > local Credit Union.  A number of members act as collectors on a rota basis, 
> > when the office is open.  The collection form has a combo box which is bound 
> > to the member's ID number in a "Member" table.  The combo box is set to 
> > "Limited to List", and I use the "Not in List" event if a new member's ID 
> > Number is to be added.  
> > 
> > My problem is that several of the members seem to be pressing the space bar 
> > before they start typing the members ID. This triggers the "Not In List" 
> > event because it thinks that new new member is being added, causing a 
> > duplicate name with a similar ID number to be added.  How can I eliminate the 
> > leading space before the "Not in List" event is triggered.  I have tried 
> > using the left() and Mid() functions as follows, but the "Not in List Event" 
> > is still triggered.
> > 
> > If Left(Member ID,1) = " " then
> >   Member ID = Mid(Member ID,2)
> > End If
> > 
> > I designed the database on Access2007, and saved it for use on Access2000. 
> > 
> > 

Thanks for the advise on how to eliminate the space. I  know about the 
convention of putting [] around the 2 word field name, but it slipped my mind 
that there might be some people who are new to Access and would be mislead by 
the way I typed the function, so I extend my apologies to those people.



0
Utf
1/14/2008 6:55:02 PM
Reply:

Similar Artilces:

how to keep cell rounding to 2 places even when someone else pastes number with more.
At work, i receive spreadsheets of bonuses that can be derived from formula. however, when i copy and paste the info into our file fo payroll, it can only be 2 places, otherwise it kicks out an error. now, i've been here almost a year now and it's been near impossible t get the people who submit the file to make sure they ROUND the numbe to 2 places. i'm wondering if there is something i can do in the cell that when the paste their number in it, it automatically rounds. or if that's not possible, if there's a way for me to highlight column then have it round all t...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

Date Elimination
I have a worksheet with mainly dates in column A in the format of '25 Aug 2008'. Is it possible with a macro or similar to delete lines beyond a certain date (2 years hence)? Basically, I'm not interested in data more than 2 years old. This would eliminate a lot of data and make for a more viewable worksheet. try this Sub del_date() ActiveCell.Range("A1").Select Do Until ActiveCell.Value =3D "" dt =3D Date - 730 If ActiveCell.Value <=3D dt Then ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End ...

Optimum number of pages
I am doing a book that should be about 200 pages (each page is half an 8x11). I now have 116 and when I try to add pages (on the INSERT menu) It says ok and then cuts me down to 16! I can't find anything that tells me how many pages one file can handle in Publisher 2003. The last book I did was on the original version of publisher! I can split the publication into two or more files, and probably will have to because pictures aren't inserted yet, but need to know about file size so don't have to do everything twice. Thanks How much memory do you have? What about your s...

combo
have placed a subform, which gives the Price. It is based on the Query. I have given a criteria in the query like [forms]![mainform]![productid]. So, when I select a product from a combo box, on the main form, I should see the price on the subform. The problem, is it does not refresh. It still shows the same price for the earlier product. But, when I move, it gets refreshed. Can I have a code , so that I should see the price as soon as I select the product from the combo box . On the After Update event of your combo box put the following: [Forms]![frmMainForm]![sfrmSubfo...

How do I find the closest number to a given number in an unsorted.
I need to find the first occurence of a number in an unsorted list (stock prices by date). I need to be able to do this from within a formula. The lookup functions and Match functions don't work because of the inability to sort. Any help would be appreciated. Hi use =VLOOKUP(lookup_value,A1:A100,1,FALSE) or =INDEX(A1:A100,MATCH(lookup_value,A1:A100,0)) if you're looking for an exact match. If you need a closest match try the following array formula (entered with CTRL+SHIFT+ENTER):+ =INDEX(A1:A100,MATCH(MIN(ABS(A1:A100-lookup_value)),ABS(A1:A100-lookup_ value),0)) -- Regard...

How do I selectively sum up a series of numbers?
I have a column filled with numbers, but I only want to sum up a few of them. Let's say I have numbers filling A1:A10. Let's say I only want to sum up the numbers located is A3 and A4. I'd like to ability to enter something in the b column (e.g. "y" in cells b3 and b4) and have a formula that adds up the values in the "A" cells that have a corresponding "y" in the "B" column. This formula would allow me to selectively choose which cells needs to be summed. What is the best/cleanest way to write that formula? Thank you. "No Sp...

Eliminate unused columns
I have a table of columns of data per week, which I convert to a table of columns of data per month. This leaves 3 or 4 blank columns between the months. Is there a simple way to consolidate the month columns into a contiguous region, eliminating the unused columns between the month columns? Or... is there a smarter way to do the conversion than sum across an fill down? One approach is to use a macro: if your empty columns are completely empty you could use this macro. Hit Alt-F11, Click Insert/Module (check the project window to make sure your workbook is the active project before...

How to convert an account ta a lead
I have had 100 % of my leads added as accounts by mistake. Can I convert them to leads? I don't think there's a way to turn an account into a lead. Could you delete them all and then re-import them as leads? Dave Ireland "Mats Nilsson" <MatsNilsson@discussions.microsoft.com> wrote in message news:D8F859B8-6510-4CEB-B69B-286DF3DB3037@microsoft.com... >I have had 100 % of my leads added as accounts by mistake. Can I convert >them > to leads? You should be able to delete the acount and contact, then go to the closed leads view and reactivate the lead. ...

Excel should add numbers without leading punctuation
Why can't Excel add numbers in a cell without a leading '=' or '+'. For example, 3+2 returns a cell with "3+2" instead of "5". Transplanted Buckeye wrote... >Why can't Excel add numbers in a cell without a leading '=' or '+'. For >example, 3+2 returns a cell with "3+2" instead of "5". Turn on Transition Formula Entry, Tools > Options, select the Transition tab in the Options dialog, check the entry for Transition formula entry, click OK. Be warned - this will make date entries much more difficult beca...

ignore spaces
Eg. I have all the courses numbered uniquely like first course of year 2008 course nam SMS will be given course no. 001SMS08 by the user. now if the user enters 001 SMS 08 and saves the course number. Then some else when queries about course no. 001SMS08 then it will not show any result. So how to ignore spaces typed (accidently) between characters in the text field of a form save the value with all space removed in between characters. On Sat, 23 Feb 2008 08:53:21 -0800 (PST), Rajtomar wrote: > Eg. I have all the courses numbered uniquely like first course of year > 2008 course nam ...

Placing an CHtmlView on a Dialog Box.
Hai All, I have created a dialogbox . On Above that I placed a Window Created from CWnd , parent of this window is my dialogbox. Now My task is placing a CHtmlView on this Window(Window Created from CWnd). Is it possible. Thanks. George. > I have created a dialogbox . On Above that I placed a Window Created > from CWnd , parent of this window is my dialogbox. > > Now My task is placing a CHtmlView on this Window(Window Created from > CWnd). Is it possible. > You can use CHTMLCtrl: http://www.microsoft.com/msj/0100/c/c0100.aspx --- Ajay ...

End of paragraph line spacing
The last line of a paragraph always has a larger space between the lines then the body of the paragraph. Any ideas why? Format, Paragraph, be certain you have 0pt before and after the paragraph. -- Mary Sauer http://msauer.mvps.org/ "cybun" <cybun@discussions.microsoft.com> wrote in message news:6CA70489-A838-4EBE-8BA8-125338F66E6B@microsoft.com... > The last line of a paragraph always has a larger space between the lines then > the body of the paragraph. Any ideas why? Thank you very much. Worked great. "Mary Sauer" wrote: > Format, Paragraph, be...

Adding a Text Box
Does anyone know how to add a text box to an Excel Document? I don't have a clue as to how to begin so if you could be descriptive, that would be helpful. I want someone to put in a value to the box, hit 'enter', and then have the results of their value come up ...almost like a query... Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Hi there, Go to View-->Toolbars and select Control Box. The Control Box toolbar will now be shown. Howver you...

Excel is defaulting to Number format instead of General format
The programme for 2007 has recently been installed, and my cells are all formated to Number instead of General, and Excel won't let me change it back to General. We tried to uninstall and re-install but this did not help. Maybe you have a workbook template file named book.xlt (or .xltx or .xltm) in your XLStart folder. This template file is used as the basis for starting new workbooks. You could try to edit it (and change the number format) or move it (or delete it). Then restart excel to test. Kim wrote: > > The programme for 2007 has recently been installed, a...

Finding missing numbers in a list
I have a series of client account numbers that range from 001-999. I first imported the client information from Timeslips which where we keep client information into an Excel spreadsheet. What I need to now do is create a macro or a query that will tell me what numbers are not being utilized from the spreadsheet that I created but I don't know how to do this. I have only created one macro before and that was with help from an instructor so I don't know where to begin can someone please help me with this. Lynne If you're just looking for a report... Create a new worksheet Put...

Item Number
Our inventory item numbers have a format of ###-##### where the first three positions are numbers that together represent the manufacturer. The five digits to the left of the dash are generally used sequentially. For example the first inventory item assigned to manufacturer "101" is 101-00001, the second 101-00002, etc. We have had numerous instances where the same part number was entered in multiple times. When we discovered this, we deleted the duplicate entries but have gaps in the numbering sequences. So I now might have 101-00001, 101-00002, 101-00004. As I enter new it...

Searching LIKE Numbers
I have an Unbound text box on a form that searches my database for exact matches. On After Update Event, it moves the focus to NumberID and if a match is found, displays the record on the form. If no match, Microsoft Access displays a message stating that "You can not go to the specified record". If No record is found, I would like to open another form and display the CLOSEST MATCH(s). For instance, I search 1234ABC and it does not exist. However, 1234A does exist. I want to show this record as a possible choice. This where I need some help. So far I have the following ...

URGENT HELP NEEDED PLEASE
hi I am no longer able to see /search for disqualified leads. We used to be able to see any disqualified leads but now i cant. Is there some settings in lead views that i need to check or confirm. Can someone pleaseeeeeeeeee advice me on this. Kind Regards Ridhima Are you talking about quick find or advanced find? Quick find does not locate closed leads(Qualified or disqualified) you can only see them by using Advanced find to run the searches. This same functionality exists for all inactive objects in all entities. You can build a saved query to run however the quick find will still...

Supress Scientific Numbers??
Hi ... Is there any way to get Excel to not display numbers in scientific format?? To force it to display 1.86E+08 as whatever the number really is?? Thanks, Sam Hi Sam Have a look at Format / Cells/ Number -- Michael Mitchelson "Sam" wrote: > Hi ... > > Is there any way to get Excel to not display numbers in scientific > format?? To force it to display 1.86E+08 as whatever the number really is?? > > Thanks, > > Sam > > > What "the number REALLY is"!!! if X=1.86e-3 then that is what x reallly is. The decimal sy...

eliminating duplicates
I need to elimnate duplicates the following script. I believe what is creating the duplicates is the provcont table, there are different provcont ids for each record. Thanks in advance. SELECT PROVCONT.C_FAX, PROVIDER.NAME, PROVIDER.PROV_PATH, PROVIDER.PROV_ID, PROVSTAT.STATUS, PSTATUS.DESCRIPT, PROVCONT.CONTAC_ID FROM PROVCONT INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS WHERE (PROVIDER.PROV_PATH = 2) AND ...

Column Headers showing numbers
Hello there, My Excel spreadsheets are opening up with numbers at the top not letters. I'm sure I must have clicked something! Thank you in advance Julie Hi Julie, Tools | Options | General Tab | Uncheck R1C1 Reference Style --- Regards, Norman "Julie" <anonymous@discussions.microsoft.com> wrote in message news:310801c4b04f$ea2980f0$a301280a@phx.gbl... > Hello there, > > My Excel spreadsheets are opening up with numbers at the > top not letters. I'm sure I must have clicked something! > > Thank you in advance > > Julie > > ...

eliminate duplicates
I have a table with 7 different columns of team members on different teams. I need a count of all team members. How do I query seven different columns and eliminate dups? You've discovered yet another problem with denormalized tables. If you have 7 different columns, each containing the same basic information, you have a repeating group. If you can't go back and redesign your tables, the easiest approach would be to create a UNION query that normalizes the data so that it's in only 1 column: SELECT Member1 AS Member FROM MyTable UNION SELECT Member2 AS Member FROM MyTabl...

How do I count number of cells with info??
I'm writing a list of numbers, in the end I wand to count how many numbers there are, I DON'T want the sum, just to know how many ther are. How can I do that?? Hi! Try this: =COUNT(A:A) Biff "HJK" <hk@get2not.dk> wrote in message news:96AFBFD2-A892-42EC-A685-41EF105A853D@microsoft.com... > I'm writing a list of numbers, in the end I wand to count how many numbers > there are, I DON'T want the sum, just to know how many ther are. How can I > do > that?? Thanks, it works, can I also count specifik numbers or letters?? "Biff" skr...

eliminate message from append query to eliminate duplicate record.
Create a new query in design view. Add the two tables for which you wish to compare. When you see the two tables in the design view of the query click on one of your 2 join fields and drag to the corresponding join field in the other table (a line should appear) then double click on fields to add them to the query. For your comparison field Your going to have to explain how you want to compare the two fields. What type of field is in table B, and what kind of comparison do you want to do? You could start with something simple like entering "ComparisonField:Iif(tableA.field1=tableB.field2,...