 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007More validadtion problems!
Hello All I have a worksheet that will be used for simple data entry of name, address, etc etc. - 1 column per field. One of the columns is for National Insurance number, and one is for postcode (UK), and I am struggling to get the validation for either of these. National insurance number: This must be in the format AA000000A (i.e. two letters followed by 6 numerics followed by one letter) - BUT only certain combinations of the first two letters are valid (e.g. WD is OK but WR is not), and only certain letters at the end are valid: I have a list of the valid combinations of first two letters (there are 48 of them!) and the valid last letters (there are 7). Postcode: There are 6 valid formats: AN NAA - e.g. M1 1AA ANN NAA - e.g. M60 1NW AAN NAA - e.g. CR2 6XH AANN NAA - e.g. DN55 1PT ANA NAA - e.g. W1A 1HQ AANA NAA - e.g. EC1A 1BB But - The letters Q, V and X are not used in the first position. The letters I, J and Z are not used in the second position. The only letters to appear in the third position are A, B, C, D, E, F, G, H, J, K, S, T, U and W. The only letters to appear in the fourth position are A, B, E, H, M, N, P, R, V, W, X and Y. The second half of the Postcode is always consistent numeric, alpha, alpha format and the letters C, I, K, M, O and V are never used. Hope someone can help. Many thanks Les NI Number =AND(ISNUMBER(MATCH(LEFT(C2,2),valid_first,0)),ISNUMBER(MATCH(RIGHT(C2,1),valid_last,0)),ISNUMBER(--MID(C2,3,5))) Postcode This works as conditional; formatting, but doesn't seem to work as DV. It is not pretty, haven't tested all combinations, but seems to comply to your rules Create the following name/referso pairs char1 ={"Q","V","X"} char2 ={"I","J","Z"} char3 ={" ","A","B","C","D","E","F","G","H","J","K","S","T","U","0","1","2","3","4","5","6","7","8","9"} char4 ={" ","A","B","E","H","M","N","P","R","V","W","X","Y","U","0","1","2","3","4","5","6","7","8","9"} charend ={"C","I","K","M","O"} validFirstTwo =AND(NOT(ISNUMBER(MATCH(LEFT(Sheet1!$C2,1),char1,0))),NOT(ISNUMBER(MATCH(MID(Sheet1!$C2,2,1),char2,0)))) validMiddleFew =AND(ISNUMBER(MATCH(MID(Sheet1!$C2,3,1),char3,0)),ISNUMBER(MATCH(MID(Sheet1!$C2,4,1),char4,0)),ISNUMBER(--MID(Sheet1!$C2,FIND(" ",Sheet1!$C2)+1,1))) validLastTwo =AND(NOT(ISNUMBER(MATCH(MID(Sheet1!$C2,LEN(Sheet1!$C2)-1,1),charend,0))),NOT(ISNUMBER(MATCH(RIGHT(Sheet1!$C2,1),charend,0)))) validPostCode =AND(ValidFirstTwo,validMiddleFew,validLastTwo) and then use this formula in CF =NOT(validPostCode) =AND(NOT(ISNUMBER(MATCH(LEFT(C13,1),char1,0))),NOT(ISNUMBER(MATCH(MID(C13,2,1),char2,0))),ISNUMBER(MATCH(MID(C13,3,1),char3,0)),ISNUMBER(MATCH(MID(C13,4,1),char4,0)),ISNUMBER(--MID(C13,FIND(" ",C13)+1,1)),NOT(ISNUMBER(MATCH(MID(C13,LEN(C13)-1,1),charend,0))),NOT(ISNUMBER(MATCH(RIGHT(C13,1),charend,0))))  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hello All > [quoted text clipped - 32 lines] > Many thanks > Les Bob I have got the NI number working as per your suggestion - many thanks for that. Regarding the postcode validation, I am struggling to know exactly how to follow your instructions. I already have lists of valids (on another sheet - sheet2) for the NI number (valid_first and valid_last): where you say "Create the following name/referso pairs" do you mean I should create five more lists, called char1 (with members Q, V and X), char2 (with members I, J and Z), char3 (with the longer list of members as below), char4 (ditto) and charend (with members C, I, K, M and O)? Then, I am not sure what to do with the three expressions you have given for ValidFirstTwo, validMiddleFew and validLastTwo. Are these also entered as 'lists'? Also, where these three expressions contain a reference to sheet1, is this assuming that the main data input sheet is sheet1? With the above in place, I understand (I think!) that I then highlight the entite column that is to hold the postcode data, and do Format>Conditional formatting, change the first combo to Formula is, then enter =NOT(validPostCode) in the adjacent formula bar: is that correct? If so, why is the NOT part there - surely I want the validPostCode expressions to be satisfied, as opposed to NOT being satisfied. Finally, with all the above in place, what do I do with the final expression that you have given - i.e. =AND(NOT(ISNUMBER(MATCH(LEFT(C13,1),char1,0))),NOT(ISNUMBER(MATCH(MID(C13,2,1),char2,0))),ISNUMBER(MATCH(MID(C13,3,1),char3,0)),ISNUMBER(MATCH(MID(C13,4,1),char4,0)),ISNUMBER(--MID(C13,FIND(" > ",C13)+1,1)),NOT(ISNUMBER(MATCH(MID(C13,LEN(C13)-1,1),charend,0))),NOT(ISNUMBER(MATCH(RIGHT(C13,1),charend,0)))) Sorry if I seem to be asking too many questions, but I know I'm almost there - and I am in fact leraning a lot!! Many thanks once again for your continued help. Les > NI Number > [quoted text clipped - 67 lines] >> Many thanks >> Les Les, I am referring to Excel defined names (Insert>Name>Define...), and put the name in the first box, the Refersto value in the bottom box. That last expression should be ignored. That was me trying to do it in one step, and I forgot to remove it. Is that clear now? SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Bob > [quoted text clipped - 105 lines] >>> Many thanks >>> Les Bob OK - I think I'm getting there: can you just confirm that the 3 expressions for ValidFirstTwo, validMiddleFew and validLastTwo should also be entered as excel defined names (in the same way as char1 etc.)? Thanks Les > Les, > [quoted text clipped - 116 lines] >>>> Many thanks >>>> Les That's an easy question. Yes :-) SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Bob > [quoted text clipped - 126 lines] >>>>> Many thanks >>>>> Les Bob I have done everything as per your instructions, but I simply can't get it to work! Whatever I type in the postcode column is accepted. I realise it may be impossible for you to guess why it is not working here if it works at your end, but if you do have any ideas I can try I would be very grateful (I'm very grateful anyway!). Hope we can find the answer. Thanks again Les > That's an easy question. Yes :-) > [quoted text clipped - 128 lines] >>>>>> Many thanks >>>>>> Les Les, I have posted an example here http://cjoint.com/?kvwD1o1nfy SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Bob > [quoted text clipped - 141 lines] >>>>>>> Many thanks >>>>>>> Les Bob I can see the example in an Internet Explorer window, but I can't see them 'in excel' so I can't see the defined names. I'm not sure what I should be able to see in the example that I can check against what I have. Sorry to be a pain, but I have gone over your instruction a number of times and just can't see what I've done wrong! Could I email you the workbook I have? Thanks once again. Les > Les, > [quoted text clipped - 146 lines] >>>>>>>> Many thanks >>>>>>>> Les Why don't you download his example then open it in Excel? SignatureRegards, Peo Sjoblom > Bob > [quoted text clipped - 161 lines] >>>>>>>>> Many thanks >>>>>>>>> Les Of course you can Les. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Bob > [quoted text clipped - 161 lines] >>>>>>>>> Many thanks >>>>>>>>> Les Bob OK - the penny has dropped: I now understand that your method works by formatting an invalid postcodes in a way that differentiates them from valid postcodes. Sorry I was a bit slow to see this! I have opened your example in excel (thanks for the suggestion Peo!), and I can see your method working ... except that it does seem to allow some invalid postcodes (e.g. CH47 54R, and CFR 7BT). I have tried looking at the list of defined names to see if I can work out how to amend them to correct these problems, but I'm afraid the code is beyond me (but I hope you'll agree that I've come a long way from when I was reluctant to even try using your code!). The other issue here is that I have also been trying to implement an alternative solution, from Nick Rothstein in this thread, which looks like it will work through data validation and so would in fact be preferable (as it would prevent invalids rather than highlighting them). So although I am certainly very grateful to you for all your help with this I think that perhaps I will now run with Rick's solution: I hope you are not offended! In case you are interested (as I certainly still am) in why your method isn't working in my workbook I am emailing it to you seperately. Many thanks once again. Les > Of course you can Les. > [quoted text clipped - 164 lines] >>>>>>>>>> Many thanks >>>>>>>>>> Les No of course not, it is your problem, you need to call it. Good luck. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Bob > [quoted text clipped - 194 lines] >>>>>>>>>>> Many thanks >>>>>>>>>>> Les Hi Les, Looks like the National Insurance Number valdation is easier to set up. Go to Data > Validation. Set Allow = Custom. You need to write a comprehensive formula into the Formula field to validate your entries. You will need to employ the following functions. LEFT(), RIGHT(), MID(), ISNUMBER(), ISTEXT() etc. You can use AND(test1,test2,...,test n) to add several tests where all must be tre. Or OR(test 1, test 2, ....., test n) where one of must be true. TEST 1. So NINO should be a 9 alphanumeric string so the following should be true =len(A2)=9 TEST 2. positions 3-8 should be numeric =isnumber(value(mid(A2,3,6))) TEST 3. For example WD is one of 48 valid combinations for the first two positions, but also CF is valid also. Use OR =OR(left(A2,2)="WD",left(A2,2)="CF")...... you need to add all 48 combinations To test all of these, stack them up in an AND formula. For example. =AND(LEN(A2)=9,ISNUMBER(VALUE(MID(A2,3,6))),OR(LEFT(A2,2)="WD",LEFT(A2,2)="CF")) Hope this helps, It is obviously very fiddly but the principles are quite straightforward, I would use these functions in the worksheet first by adding each test to a different cell and then stack them up when you know they all work. Let me know if you need anything further, Ant http://www.excel-ant.co.uk This formula should correctly handle the National Insurance Number... =AND(NOT(ISERROR(SEARCH(LEFT(A1),"ABCEGHJKLMNOPRSTWXYZ"))),NOT(ISERROR(SEARCH(MID(A1,2,1),"ABCEGHJKLMNPRSTWXYZ"))),SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),"0123456789")))=6,IF(LEN(A1)=8,TRUE,IF(LEN(A1)=9,NOT(ISERROR(SEARCH(RIGHT(A1),"ABCD "))))),ISERROR(SEARCH(LEFT(A1,2),"BGB*NKN*TNT*ZZ"))) As for the UK Postcodes... I once posted the macro function below my signature to handle that. If you can make use of a macro function in your spreadsheet, then put the function below in a Module so that you can use it within your spreadsheet just like a built in function. If you are not sure how to do that.... right click the worksheet's tab and select View Code from the popup menu that appears; once inside the VBA editor, click on Insert/Module from its menu bar and simply Copy/Paste the code below into the Module's code window; that's it, simply use =Validate(A1) in any cell on the spreadsheet to check the postcode in A1. Rick Function ValidatePostCode(ByVal PostCode As String) Dim Parts() As String PostCode = UCase$(PostCode) Parts = Split(PostCode) If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _ (Parts(1) Like "#[A-Z][A-Z]" And _ (Parts(0) Like "[A-Z]#" Or _ Parts(0) Like "[A-Z]#[0-9A-Z]" Or _ Parts(0) Like "[A-Z][A-Z]#" Or _ Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _ Parts(0) Like "A[BL]#*" Or _ Parts(0) Like "B[ABDHLNRST]#*" Or _ Parts(0) Like "C[ABFHMORTVW]#*" Or _ Parts(0) Like "D[ADEGHLNTY]#*" Or _ Parts(0) Like "E[CHNX]#*" Or _ Parts(0) Like "F[KY]#*" Or _ Parts(0) Like "G[LU]#*" Or _ Parts(0) Like "H[ADGPRSUX]#*" Or _ Parts(0) Like "I[GPV]#*" Or _ Parts(0) Like "K[ATWY]#*" Or _ Parts(0) Like "L[ADELNSU]#*" Or _ Parts(0) Like "M[EKL]#*" Or _ Parts(0) Like "N[EGNPRW]#*" Or _ Parts(0) Like "O[LX]#*" Or _ Parts(0) Like "P[AEHLOR]#*" Or _ Parts(0) Like "R[GHM]#*" Or _ Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _ Parts(0) Like "T[ADFNQRSW]#*" Or _ Parts(0) Like "W[ACDFNRSV]#*" Or _ Parts(0) Like "UB#*" Or _ Parts(0) Like "YO#*" Or _ Parts(0) Like "ZE#*") End If End Function > This formula should correctly handle the National Insurance Number... > > =AND(NOT(ISERROR(SEARCH(LEFT(A1),"ABCEGHJKLMNOPRSTWXYZ"))),NOT(ISERROR(SEARCH(MID(A1,2,1),"ABCEGHJKLMNPRSTWXYZ"))),SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),"0123456789")))=6,IF(LEN(A1)=8,TRUE,IF(LEN(A1)=9,NOT(ISERROR(SEARCH(RIGHT(A1),"ABCD > "))))),ISERROR(SEARCH(LEFT(A1,2),"BGB*NKN*TNT*ZZ"))) Using the number test part that 'excel-ant' posted (it's much better than the one I used), we can shorten the above formula a little bit... =AND(NOT(ISERROR(SEARCH(LEFT(A1),"ABCEGHJKLMNOPRSTWXYZ"))),NOT(ISERROR(SEARCH(MID(A1,2,1),"ABCEGHJKLMNPRSTWXYZ"))),ISNUMBER(VALUE(MID(A1,3,6))),IF(LEN(A1)=8,TRUE,IF(LEN(A1)=9,NOT(ISERROR(SEARCH(RIGHT(A1),"ABCD "))))),ISERROR(SEARCH(LEFT(A1,2),"BGB*NKN*TNT*ZZ"))) Rick I just noticed in both my postings, my newsreader picked an "unfortunate" spot to wrap the formula at, so I am thinking that could have happened to you also. In both cases, there is a space character following the ABCD (and immediately before the closing quote mark) toward the end of the formula. Just thought I would mention that in case there was any confusion as to the intent at that location. Rick >> This formula should correctly handle the National Insurance Number... >> [quoted text clipped - 8 lines] > > Rick Rick Many thanks for this. I'm sure I'm being thick, but I can't get your postcode validation module to work. I have pasted the code into a new module, but when I tried to set the validation for the first cell in the postcode column (columnI) I did Data>Validation, selected Custom in the 'Allow' combobox, then entered =Validate(I2) in the 'Formula' box, but I then got the message "A named range you specified could not be found". What have I done wrong? Ultimately I need the validation to be carried out on every row of columnI - at the time the postcode is entered. Should I be able to use the code you provided in this way? Thanks for your continued help. Les >I just noticed in both my postings, my newsreader picked an "unfortunate" >spot to wrap the formula at, so I am thinking that could have happened to [quoted text clipped - 17 lines] >> >> Rick I can't get it to work directly in Data Validation either. I'm guessing a UDF can't be used that way. The function does work directly in a spreadsheet cell though, so you can implement your Data Validation in this indirect way. Find an unused column (for purposes of this example, I'll assume that column is Z) and put this formula in its 2nd row (Z2)... =ValidatePostCode(I2) where I am assuming your PostCode column is Column I (as mentioned in your message) and copy it down as far as necessary. Now, so your users don't see it, select the entire Column Z and Hide it. Now, select all of Column I and click on Date/Validation in Excel's menu bar. On the Settings tab in the dialog box that appears, select "Custom" from the "Allow" drop-down and put =I1 in the "Formula" field. Set whatever you need to on the other Tabs and then click OK. Just one more thing... in the code on the Module, change the function's procedure declaration statement to this... Function ValidatePostCode(ByVal PostCode As String) As Boolean All I did is add "As Boolean" to the statement you now have there. Okay, your Data Validation should now work. Rick > Rick > [quoted text clipped - 35 lines] >>> >>> Rick Rick I don't know what I'm doing wrong, but it's not working! I think it is something to do with some other code that I have on this sheet, because when I followed your instructions below, on entering =ValidatePostCode(I2) in cell AW2 (column AW is the first unused column), I get the message "You did not enter a valid time", and the cells displays #VALUE!. This message is in fact generated by the other code (which I have pasted below), but what I don't understand is that the other code should only affect the range U2:AV99 - i.e. it should not affect columns AW and after. The other thing I don't understand is when you say "select all of Column I and click on Date/Validation in Excel's menu bar. On the Settings tab in the dialog box that appears, select "Custom" from the "Allow" drop-down and put =I1 in the "Formula" field." Is this right? To my mind this validation is saying that the value of I2 must be the value of I2, and the value of I3 must be the value of I3, etc. etc. I wonder if I should be putting =AW1 in the "Formula" field? But first of all I have to sort out the formula =ValidatePostCode(I2) in AW2, etc In case it's relevant, each value in row1 is a column heading - so any validations etc should only be applied to row2 and after. I hope this makes sense to you and that you can see where I'm going wrong. Many thanks once again Les NB The other code on this sheet is: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const WS_RANGE As String = "U2:AV99" Dim TimeStr As String On Error GoTo EndMacro If Target.Cells.Count > 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Application.Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & Mid(.Value, 2, 2) & _ ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & Mid(.Value, 3, 2) & _ ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If If Me.Cells(.Row, Me.Range(WS_RANGE).Columns(1).Column).Value <> "" And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(2).Column).Value <> "" And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(1).Column).Value > Me.Cells(.Row, Me.Range(WS_RANGE).Columns(2).Column).Value Then MsgBox "Start time earlier than end time" .Value = "" End If If Me.Cells(.Row, Me.Range(WS_RANGE).Columns(3).Column).Value <> "" And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(4).Column).Value <> "" And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(3).Column).Value > Me.Cells(.Row, Me.Range(WS_RANGE).Columns(4).Column).Value Then MsgBox "Start time earlier than end time" .Value = "" End If If Me.Cells(.Row, Me.Range(WS_RANGE).Columns(5).Column).Value <> "" And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(6).Column).Value <> "" And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(5).Column).Value > Me.Cells(.Row, Me.Range(WS_RANGE).Columns(6).Column).Value Then MsgBox "Start time earlier than end time" .Value = "" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub >I can't get it to work directly in Data Validation either. I'm guessing a >UDF can't be used that way. The function does work directly in a [quoted text clipped - 62 lines] >>>> >>>> Rick Sorry about the delay in getting back to you.... just recovered from a major system crash. Still testing things out, but I wanted to respond to the two points you raised. See inline comments. > I don't know what I'm doing wrong, but it's not working! > [quoted text clipped - 6 lines] > below), but what I don't understand is that the other code should only > affect the range U2:AV99 - i.e. it should not affect columns AW and after. I'll get back to you on this one when time permits (hopefully, later on today or this evening). For some reason, you are getting your Worksheet Change event's error message. I don't see anything obvious, so I will need to set your code up and actually try it out to see if I can duplicate the problem. If you start a new spreadsheet and only put in the code I gave you in my previous postings and follow the directions I gave you previously (making the one correction I note below), you should be able to see the code in action. > The other thing I don't understand is when you say > "select all of Column I and click on Date/Validation in Excel's menu bar. [quoted text clipped - 6 lines] > =ValidatePostCode(I2) > in AW2, etc Instead of saying =I1, it should have said =Z1 (using my example column)... given the information above, that formula should be =AW1 as you noted. Rick Rick Many thanks for your further reply. I have done as you suggested and started with a new workbook, and ... it works! Having said that, I may need to make a minor alteration to your code, because the Post Office have altered the rules since you wrote it! (or perhaps your code is in fact more up to date than the rules at http://www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm. - I note that these rules are dated Sept 2002). According to these rules the letters M, N and some others should not be allowed in the third position, but I think your code allows any letter in that position. I have had a look at your code hoping to be able to 'spot' how to update if if necessary, but it was beyond me! If you could guide me in the right direction I would be very grateful (although I know I need first to find a more up-to-date ruleset!). I also still can't work out why my 'other' code is interfering with yours in my original workbook: have you had any luck with that? I really am extremely grateful for your help with this. Les > Sorry about the delay in getting back to you.... just recovered from a > major system crash. Still testing things out, but I wanted to respond to [quoted text clipped - 37 lines] > > Rick > Having said that, I may need to make a minor alteration to your code, > because the Post Office have altered the rules since you wrote it! (or [quoted text clipped - 8 lines] > in the right direction I would be very grateful (although I know I need > first to find a more up-to-date ruleset!). Nope, I missed that particular restriction when I first investigated the UK Postcodes. The modification to my function was real easy to do (Like operator comparisons are relatively easy to work with) and I have included it below. Rick Function ValidatePostCode(ByVal PostCode As String) Dim Parts() As String PostCode = UCase$(PostCode) Parts = Split(PostCode) If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _ (Parts(1) Like "#[A-Z][A-Z]" And _ (Parts(0) Like "[A-Z]#" Or _ Parts(0) Like "[A-Z]#[0-9A-Z]" Or _ Parts(0) Like "[A-Z][A-Z]#" Or _ Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _ Parts(0) Like "A[BL]#*" Or _ Parts(0) Like "B[ABDHLNRST]#*" Or _ Parts(0) Like "C[ABFHMORTVW]#*" Or _ Parts(0) Like "D[ADEGHLNTY]#*" Or _ Parts(0) Like "E[CHNX]#*" Or _ Parts(0) Like "F[KY]#*" Or _ Parts(0) Like "G[LU]#*" Or _ Parts(0) Like "H[ADGPRSUX]#*" Or _ Parts(0) Like "I[GPV]#*" Or _ Parts(0) Like "K[ATWY]#*" Or _ Parts(0) Like "L[ADELNSU]#*" Or _ Parts(0) Like "M[EKL]#*" Or _ Parts(0) Like "N[EGNPRW]#*" Or _ Parts(0) Like "O[LX]#*" Or _ Parts(0) Like "P[AEHLOR]#*" Or _ Parts(0) Like "R[GHM]#*" Or _ Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _ Parts(0) Like "T[ADFNQRSW]#*" Or _ Parts(0) Like "W[ACDFNRSV]#*" Or _ Parts(0) Like "UB#*" Or _ Parts(0) Like "YO#*" Or _ Parts(0) Like "ZE#*" Or _ Parts(1) Like "*#[!CIKMOV][!CIKMOV]") End If End Function Rick Thanks for this. The amended code is now working ... except that it seems to be rejecting every postcode that I eneter (even my own!). I wish I could understand the code a little better - then I could try to see why this is happening! I hope you don't give up on me! Thanks again Les >> Having said that, I may need to make a minor alteration to your code, >> because the Post Office have altered the rules since you wrote it! (or [quoted text clipped - 52 lines] > End If > End Function > The amended code is now working ... except that it seems to be rejecting > every postcode that I eneter (even my own!). > I wish I could understand the code a little better - then I could try to > see why this is happening! The function appears to be working fine here. You are putting the function in a Module that you add inside the VBA editor (Insert/Module from its menu bar), right? Rick Rick OK - my mistake - sorry! Using the new code you gave however still seems to be allowing A3Z 3AA, etc - i.e. with a prohibited Z (and other prohibited letters) in the 3rd position. Does this happen at your end? Thanks as ever Les >> The amended code is now working ... except that it seems to be rejecting >> every postcode that I eneter (even my own!). [quoted text clipped - 6 lines] > > Rick Until Rick signs in, try this amendment Function ValidatePostCode(ByVal PostCode As String) Dim Parts() As String PostCode = UCase$(PostCode) Parts = Split(PostCode) If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _ (Parts(1) Like "#[A-Z][A-Z]" And _ (Parts(0) Like "[A-Z]#" Or _ Parts(0) Like "[A-Z]#[0-9A-Z]" Or _ Parts(0) Like "[A-Z][A-Z]#" Or _ Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _ Parts(0) Like "A[BL]#*" Or _ Parts(0) Like "B[ABDHLNRST]#*" Or _ Parts(0) Like "C[ABFHMORTVW]#*" Or _ Parts(0) Like "D[ADEGHLNTY]#*" Or _ Parts(0) Like "E[CHNX]#*" Or _ Parts(0) Like "F[KY]#*" Or _ Parts(0) Like "G[LU]#*" Or _ Parts(0) Like "H[ADGPRSUX]#*" Or _ Parts(0) Like "I[GPV]#*" Or _ Parts(0) Like "K[ATWY]#*" Or _ Parts(0) Like "L[ADELNSU]#*" Or _ Parts(0) Like "M[EKL]#*" Or _ Parts(0) Like "N[EGNPRW]#*" Or _ Parts(0) Like "O[LX]#*" Or _ Parts(0) Like "P[AEHLOR]#*" Or _ Parts(0) Like "R[GHM]#*" Or _ Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _ Parts(0) Like "T[ADFNQRSW]#*" Or _ Parts(0) Like "W[ACDFNRSV]#*" Or _ Parts(0) Like "UB#*" Or _ Parts(0) Like "YO#*" Or _ Parts(0) Like "ZE#*" And _ (Parts(1) Like "*#[!CIKMOV][!CIKMOV]")) End If End Function SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Rick > [quoted text clipped - 17 lines] >> >> Rick Bob Many thanks for this. I have tested lots of postcodes with your code, and the only problem I have been able to find so far is that it does not allow an A in the first position (which should be valid). Again, I wish I could understand the code sufficiently to know how to amend it for this, but I can't get it! Thanks for not giving up on me! Les > Until Rick signs in, try this amendment > [quoted text clipped - 56 lines] >>> >>> Rick Les, It allows AB1 1MA, AL1 1MA but no other A* combination. What should it allow starting with A? SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Bob > Many thanks for this. [quoted text clipped - 65 lines] >>>> >>>> Rick Bob Any of the AANN ANN formats - .e.g. AC47 7BT should be OK, but is rejected. Les > Les, > [quoted text clipped - 70 lines] >>>>> >>>>> Rick Well this works for those but it is not really my code so I may break something else. This code as it stands allows A and any second letter except I,J,Z. What about A#, is that valid? Function ValidatePostCode(ByVal PostCode As String) Dim Parts() As String PostCode = UCase$(PostCode) Parts = Split(PostCode) If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _ (Parts(1) Like "#[A-Z][A-Z]" And _ (Parts(0) Like "[A-Z]#" Or _ Parts(0) Like "[A-Z]#[0-9A-Z]" Or _ Parts(0) Like "[A-Z][A-Z]#" Or _ Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _ Parts(0) Like "A[ABCDEFGHKLMNOPQRSTUVWXY]#*" Or _ Parts(0) Like "B[ABDHLNRST]#*" Or _ Parts(0) Like "C[ABFHMORTVW]#*" Or _ Parts(0) Like "D[ADEGHLNTY]#*" Or _ Parts(0) Like "E[CHNX]#*" Or _ Parts(0) Like "F[KY]#*" Or _ Parts(0) Like "G[LU]#*" Or _ Parts(0) Like "H[ADGPRSUX]#*" Or _ Parts(0) Like "I[GPV]#*" Or _ Parts(0) Like "K[ATWY]#*" Or _ Parts(0) Like "L[ADELNSU]#*" Or _ Parts(0) Like "M[EKL]#*" Or _ Parts(0) Like "N[EGNPRW]#*" Or _ Parts(0) Like "O[LX]#*" Or _ Parts(0) Like "P[AEHLOR]#*" Or _ Parts(0) Like "R[GHM]#*" Or _ Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _ Parts(0) Like "T[ADFNQRSW]#*" Or _ Parts(0) Like "W[ACDFNRSV]#*" Or _ Parts(0) Like "UB#*" Or _ Parts(0) Like "YO#*" Or _ Parts(0) Like "ZE#*" And _ (Parts(1) Like "*#[!CIKMOV][!CIKMOV]")) End If End Function SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Bob > Any of the AANN ANN formats - .e.g. AC47 7BT should be OK, but is [quoted text clipped - 76 lines] >>>>>> >>>>>> Rick Bob This is obviously proving problematic: now the A is accepted in the first position, but I think each of the following 'rules' is being ignored: The only letters to appear in the third position are A, B, C, D, E, F, G, H, J, K, S, T, U and W. The only letters to appear in the fourth position are A, B, E, H, M, N, P, R, V, W, X and Y. The second half of the Postcode is always consistent numeric, alpha, alpha format and the letters C, I, K, M, O and V are never used. Is this a simple matter to fix? As ever, I'm extremely grateful for all your help with this: it'll be great when it's perfect! Les > Well this works for those but it is not really my code so I may break > something else. [quoted text clipped - 120 lines] >>>>>>> >>>>>>> Rick > OK - my mistake - sorry! Does that mean you hadn't put it in a Module and that it works now that you have? > Using the new code you gave however still seems to be allowing A3Z 3AA, > etc - i.e. with a prohibited Z (and other prohibited letters) in the 3rd > position. > Does this happen at your end? Yes, it happens here too. I'm guessing the site I used for a reference either didn't mention these restrictions or had it buried in a spot that I overlooked. Try the new function code below my signature and see if I trapped everything now. By the way, that long listing in my code was developed against actually assigned postcodes in the UK proper itself as I remember; but they all seemed to be of the AAN* variety, so I am guessing those other formats are used elsewhere. Rick Function ValidatePostCode(ByVal PostCode As String) Dim Parts() As String PostCode = UCase$(PostCode) Parts = Split(PostCode) If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _ (Parts(1) Like "#[A-Z][A-Z]" And _ (Parts(0) Like "[A-Z]#" Or _ Parts(0) Like "[A-Z]#[0-9ABCDEFGHJKSTUW]" Or _ Parts(0) Like "[A-Z][A-Z]#" Or _ Parts(0) Like "[A-Z][A-Z]#[0-9ABEHMNPRVWXY]")) Then ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _ Parts(0) Like "A[BL]#*" Or _ Parts(0) Like "B[ABDHLNRST]#*" Or _ Parts(0) Like "C[ABFHMORTVW]#*" Or _ Parts(0) Like "D[ADEGHLNTY]#*" Or _ Parts(0) Like "E[CHNX]#*" Or _ Parts(0) Like "F[KY]#*" Or _ Parts(0) Like "G[LU]#*" Or _ Parts(0) Like "H[ADGPRSUX]#*" Or _ Parts(0) Like "I[GPV]#*" Or _ Parts(0) Like "K[ATWY]#*" Or _ Parts(0) Like "L[ADELNSU]#*" Or _ Parts(0) Like "M[EKL]#*" Or _ Parts(0) Like "N[EGNPRW]#*" Or _ Parts(0) Like "O[LX]#*" Or _ Parts(0) Like "P[AEHLOR]#*" Or _ Parts(0) Like "R[GHM]#*" Or _ Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _ Parts(0) Like "T[ADFNQRSW]#*" Or _ Parts(0) Like "W[ACDFNRSV]#*" Or _ Parts(0) Like "UB#*" Or _ Parts(0) Like "YO#*" Or _ Parts(0) Like "ZE#*" Or _ Parts(1) Like "*#[!CIKMOV][!CIKMOV]") End If End Function > By the way, that long listing in my code was developed > against actually assigned postcodes in the UK proper > itself as I remember; but they all seemed to be of the > AAN* variety, so I am guessing those other formats > are used elsewhere. What the above comment was supposed to make clear (but, in re-reading it, didn't) was this... those postcodes beginning with AAN are being checked against actually assigned postcodes, so if the passed in string fails one of those tests, it is because that letter-letter combination is not (presently) assigned. However, the other formats being checked are for shape only (although you correctly pointed out some errors in how I implement them), so it is possible for the code to say a certain postcode is valid when it actually isn't assigned anywhere. If I had a complete list of **all** assigned postcodes, I could modify the function to be more accurate. Rick Rick, have you checked my suggestion? Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> By the way, that long listing in my code was developed >> against actually assigned postcodes in the UK proper [quoted text clipped - 14 lines] > > Rick > have you checked my suggestion? Do you mean the posting that starts off with "Until Rick signs in, try this amendment"? If so, I looked at the function you posted and I don't see where it differs from the first function I posted. What am I missing? Rick I have changed the last Or to an And, between Part(0) and Parts(1). I found that A3Z 3AA passed because the last part was valid. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> have you checked my suggestion? > [quoted text clipped - 3 lines] > > Rick Rick I am surprised that you would want to check against actually assigned postcodes, because new addresses are being created all the time (as our greenbelt turns grey!), and so new, valid postcodes - according to the ruleset - will need to pass the test. Or am I missing something? Les >> By the way, that long listing in my code was developed >> against actually assigned postcodes in the UK proper [quoted text clipped - 14 lines] > > Rick The impression I got from the initial website I used as a reference (this was some 6 months ago or so) was that the "area" for the postcodes were established and that new houses within these areas would be just pick up the post code for the area they were built in. Are you saying that new "areas" are being created (perhaps existing ones being subdivided)? If so, then I can revert the function back to a simple pattern "shape" tester, but that would mean lots of non-existent postcodes (not physically assigned ones) would register as TRUE. You would not be able to tell if they were genuine postcodes that are in use or simply ones that don't violate the "general" pattern "shape" rules. Is that what you want? If so, I'll be happy to modify the function to do it (once we iron out the discrepancies you keep finding<g>). Rick > Rick > [quoted text clipped - 23 lines] >> >> Rick Rick I had assumed that the rules at www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm were absolutely "necessary and sufficient" - i.e. that any combination of alphanumerics that is in one of the 6 given "shapes", and that meets the 5 rules about where certain letters can and cannot appear, is valid - even if in fact such a combination has not (yet) actually been assigned to a real-life address. Although as you say this would allow postcodes that may technically be non-existent, I would still consider them to be valid. Hopefully this makes the validation code simpler than it would other wise be, since there is no need to add the check against assigned postcodes to the rules at the above reference. I hope that makes sense! Has this been the longest thread you've seen?! Thanks as ever Les > The impression I got from the initial website I used as a reference (this > was some 6 months ago or so) was that the "area" for the postcodes were [quoted text clipped - 38 lines] >>> >>> Rick > I had assumed that the rules at > www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm were absolutely [quoted text clipped - 8 lines] > be, since there is no need to add the check against assigned postcodes to > the rules at the above reference. Okay, give this function a try and let me know how it works out for you... Function ValidatePostCode(ByVal PostCode As String) As Boolean Dim Parts() As String PostCode = UCase$(PostCode) Parts = Split(PostCode) ValidatePostCode = (PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _ (Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" And _ (Parts(0) Like "[A-PR-UWYZ]#" Or _ Parts(0) Like "[A-PR-UWYZ]#[0-9A-HJKSTUW]" Or _ Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#" Or _ Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]"))) End Function Rick Rick * * * Eureka * * * We've (sorry - you've) made it!! I now feel bad for not having emphasized earlier that validation on the basic ruleset would be sufficient, as I can see that the solution below is much simpler than those that were aiming to include a check against assigned postcodes. Anyway, on behalf of all the team at PayeDoc (www.gppayroll.org.uk), a huge Thank You for all your efforts with this (I still don't understand how it works though!!). Les >> I had assumed that the rules at >> www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm were absolutely [quoted text clipped - 24 lines] > > Rick Rick We're definitely getting there! My previous mistake was simply misaligning the validation column with the postcode column - so I had AW2=ValidatePostCode(I1), etc. Now sorted. With your new code the 3rd position is validated OK, but the following rules appear not to be: a.. The letters Q, V and X are not used in the first position. a.. The letters I, J and Z are not used in the second position. a.. In the second half of the Postcode the letters C, I, K, M, O and V are never used. I wish I could understand your code sufficiently to know how to amend it, but I can't! Perhaps when I see how you amend it for these 3 rules (hopefully!) I will be closer to it. Thanks once again Les >> OK - my mistake - sorry! > [quoted text clipped - 52 lines] > End If > End Function > We're definitely getting there! Slowly but surely. <g> > My previous mistake was simply misaligning the validation column with the > postcode column - so I had AW2=ValidatePostCode(I1), etc. Now sorted. Great! > With your new code the 3rd position is validated OK, but the following > rules appear not to be: [quoted text clipped - 3 lines] > a.. In the second half of the Postcode the letters C, I, K, M, O and V are > never used. Okay, give this function a try instead... Function ValidatePostCode(ByVal PostCode As String) As Boolean Dim Parts() As String PostCode = UCase$(PostCode) Parts = Split(PostCode) If (PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _ (Parts(1) Like "#[A-HK-Y][A-Z]" And _ (Parts(0) Like "[A-PR-UWYZ]#" Or _ Parts(0) Like "[A-PR-UWYZ]#[0-9A-HJKSTUW]" Or _ Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#" Or _ Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")) And _ Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]") Then ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _ Parts(0) Like "A[BL]#*" Or _ Parts(0) Like "B[ABDHLNRST]#*" Or _ Parts(0) Like "C[ABFHMORTVW]#*" Or _ Parts(0) Like "D[ADEGHLNTY]#*" Or _ Parts(0) Like "E[CHNX]#*" Or _ Parts(0) Like "F[KY]#*" Or _ Parts(0) Like "G[LU]#*" Or _ Parts(0) Like "H[ADGPRSUX]#*" Or _ Parts(0) Like "I[GPV]#*" Or _ Parts(0) Like "K[ATWY]#*" Or _ Parts(0) Like "L[ADELNSU]#*" Or _ Parts(0) Like "M[EKL]#*" Or _ Parts(0) Like "N[EGNPRW]#*" Or _ Parts(0) Like "O[LX]#*" Or _ Parts(0) Like "P[AEHLOR]#*" Or _ Parts(0) Like "R[GHM]#*" Or _ Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _ Parts(0) Like "T[ADFNQRSW]#*" Or _ Parts(0) Like "W[ACDFNRSV]#*" Or _ Parts(0) Like "UB#*" Or _ Parts(0) Like "YO#*" Or _ Parts(0) Like "ZE#*") End If End Function Rick Hi Rick This obviously isn't easy! With your latest code, in the ANN NAA format, certain letters are being incorrectly rejected in the first position - e.g.A, C, D, E and others. Similarly, in the AANN NAA format, certain letters are being incorrectly rejected in the first position e.g. A, F, G, H and others. Have you got a headache: I have!! As ever, many thanks for all of this. Les >> We're definitely getting there! > [quoted text clipped - 53 lines] > > Rick > This obviously isn't easy! It probably is easier than I am making it. <g> > With your latest code, in the ANN NAA format, certain letters are being > incorrectly rejected in the first position - e.g.A, C, D, E and others. > Similarly, in the AANN NAA format, certain letters are being incorrectly > rejected in the first position e.g. A, F, G, H and others. Okay, what about this version? Function ValidatePostCode(ByVal PostCode As String) As Boolean Dim Parts() As String PostCode = UCase$(PostCode) Parts = Split(PostCode) If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _ (Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" And _ (Parts(0) Like "[A-PR-UWYZ]#" Or _ Parts(0) Like "[A-PR-UWYZ]#[0-9A-HJKSTUW]" Or _ Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#" Or _ Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")) Then ValidatePostCode = (Parts(0) Like "A[0-9BL]#*" Or _ Parts(0) Like "B[0-9ABDHLNRST]#*" Or _ Parts(0) Like "C[0-9ABFHMORTVW]#*" Or _ Parts(0) Like "D[0-9ADEGHLNTY]#*" Or _ Parts(0) Like "E[0-9CHNX]#*" Or _ Parts(0) Like "F[0-9KY]#*" Or _ Parts(0) Like "G[0-9LU]#*" Or _ Parts(0) Like "H[0-9ADGPRSUX]#*" Or _ Parts(0) Like "I[0-9GPV]#*" Or _ Parts(0) Like "K[0-9ATWY]#*" Or _ Parts(0) Like "J##" Or _ Parts(0) Like "L[0-9ADELNSU]#*" Or _ Parts(0) Like "M[0-9EKL]#*" Or _ Parts(0) Like "N[0-9EGNPRW]#*" Or _ Parts(0) Like "O[0-9LX]#*" Or _ Parts(0) Like "P[0-9AEHLOR]#*" Or _ Parts(0) Like "R[0-9GHM]#*" Or _ Parts(0) Like "S[0-9AEGKLMNOPRSTWY]#*" Or _ Parts(0) Like "T[0-9ADFNQRSW]#*" Or _ Parts(0) Like "U[0-9B]#*" Or _ Parts(0) Like "W[0-9ACDFNRSV]#*" Or _ Parts(0) Like "Y[0-9O]#*" Or _ Parts(0) Like "Z[0-9E]#*") End If End Function Rick Rick I hugely admire - and am extremely grateful for - your perseverence with this! With your latest code, A44 4BT and C44 4BT etc. are correctly accepted, but AH44 4BT and FH44 4BT etc. are wrongly rejected: but CH44 4BT is correctly accepted! Over to you! As ever ... Les >> This obviously isn't easy! > [quoted text clipped - 44 lines] > > Rick > I hugely admire - and am extremely grateful for - your perseverence > with this! I have obsessive/compulsive tendencies... I don't really have a choice.<g> > With your latest code, A44 4BT and C44 4BT etc. are correctly accepted, > but AH44 4BT and FH44 4BT etc. are wrongly rejected: but CH44 4BT is > correctly accepted! Okay, here is where we come into the "is it an assigned" postcode area or not... AH and FH are not. Here is a list, which I am pretty sure I used in my original code (if not this exact list, then one similar to it), showing all the actually assigned postcode areas... http://www.answers.com/topic/list-of-postcode-areas-in-the-united-kingdom Note that AB, AL are the only assigned postcodes beginning with "A" and FK, FY are the only ones beginning with "F"; hence why my code rejects AH44 and FH44. So, the question comes down to this... do you want to just check the postcode "shape" without regard to whether the postcode is "real" or not; or do you want to verify authenticity also? Rick Rick You're right - that is the question - and the answer is unequivocally that I need the rules as at http://www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm. It is immaterial where or not a valid postcode (as defined by these rules) is actually in use yet ... because it will be sooner or later! Hope that makes sense (I am in fact curious why anyone would want to validate by reference to what is in current useage). Thanks again Les >> I hugely admire - and am extremely grateful for - your perseverence >> with this! [quoted text clipped - 19 lines] > > Rick Les, Just to jump in, I installed Rick's UDF, put it as a formula in a hidden column as suggested and then referred to that hidden column in the DV and it worked fine. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Rick > [quoted text clipped - 63 lines] >> End If >> End Function Rather than a hidden column, you could also use worksheet change with the function Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "E:E" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Not ValidatePostCode(.Value) Then MsgBox "Invalid Postcode" .Value = "" End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Les, > [quoted text clipped - 69 lines] >>> End If >>> End Function
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.