Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
validating against a list - Please Help!
I am wanting to validate numbers as they are keyed in against a list of valid numbers. For example, I will have a list of 1000 - 9 digit numbers, and I would like to check a number as it is entered against that list. Furthermore, I would like a valid number, once entered to no longer be valid, or only be allowed to be entered once
For instance, my valid numbers list is 800000001-800000999 and when 800000235 is eneterd it is determined to be valid, however I do not want that exact number to be allowed again I imagaine that this can be done in either Excel or Access and I am fine with using either Any help would be greatly-greatly appreciated Thanks dcs |
#2
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
validating against a list - Please Help!
Hi
have a look at http://www.cpearson.com/excel/NoDupEntry.htm for preventing duplicate entries. -- Regards Frank Kabel Frankfurt, Germany "dcs" schrieb im Newsbeitrag ... I am wanting to validate numbers as they are keyed in against a list of valid numbers. For example, I will have a list of 1000 - 9 digit numbers, and I would like to check a number as it is entered against that list. Furthermore, I would like a valid number, once entered to no longer be valid, or only be allowed to be entered once. For instance, my valid numbers list is 800000001-800000999 and when 800000235 is eneterd it is determined to be valid, however I do not want that exact number to be allowed again. I imagaine that this can be done in either Excel or Access and I am fine with using either. Any help would be greatly-greatly appreciated. Thanks, dcs |
#3
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
validating against a list - Please Help!
?B?ZGNz?= wrote in
: For instance, my valid numbers list is 800000001-800000999 and when 800000235 is eneterd it is determined to be valid, however I do not want that exact number to be allowed again. All on one line: = DCount("*","ValidNumbers","IDNumber = " & SuggestedNumber) 0 AND _ DCount("*","ActualTable","IDNumber = " & SuggestedNumber) = 0 This works if (a) you delete records from ActualTable and (b) you are allowed to re-use the numbers. If you don't want to re=use them, you need = DCount("*", "ValidNumbers", _ "IDNumber = " & SuggestedNumber & " AND Used = False") but if you have multiple users you run the risk of reading the value twice and trying to use it twice (IYSWIM). In that case you have to set the flag before using it: strSQL = "UPDATE ValidNumbers SET Used = TRUE " & _ "WHERE IDNumber = " & SuggestedNumber db.Execute strSQL, dbFailOnError If db.RecordsAffected = 0 Then MsgBox "That number was not available" Else ' It was okay End If Hope that helps (PS I have not tested any of this code!!) Tim F |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
validating | Excel Discussion (Misc queries) | |||
vlookup and validating data list | Excel Worksheet Functions | |||
Modifying a validating list | Excel Discussion (Misc queries) | |||
validating a list of multiple columns | Excel Worksheet Functions | |||
Validating | Excel Discussion (Misc queries) |