 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / March 2008how to verify all numeric in field
Hello, Can somebody help me with a problem I have? I am importing a text file into a data base and the data in the file is simular to this. 10001007684.tif 10001112414.tif 10001113542.tif ....etc. Using Mid([filename],6,6) I pull out the 5th to 11th digits to import into a numeric (Long Integer) field. In this case.. 1007684 1112414 1113542 Works great until someone saves to a name like any of these that when my Mid formuala is applied creates a non-numeric result that doesn't fit into my numeric field. 1000007684.tif Mid([filename],6,6)= 07684. 1000112414-2.tif Mid([filename],6,6)= 12414- 1000C1113542.tif Mid([filename],6,6)= C11135 As I cannot put contstraints on how the files are saved what I would like to do is identify the files that would have non-numeric charaters in them before I import. My question is how can I test the results of Mid([filename],6,6) to be numeric? >Can somebody help me with a problem I have? > [quoted text clipped - 27 lines] >My question is how can I test the results of Mid([filename],6,6) to be >numeric? This calculated field in a query will be True if there is a non-numeric character in the field" Mid(filename,6,6) Like "*[!0-9]*" You can then filter those out records by using a criteria of False.  SignatureMarsh MVP [MS Access] There is also a VBA function, IsNumeric( ) which will evaluate a value that is passed to it to determine whether it is numeric or not. What happens if they really screw it up and don't enter at least 12 characters? Dale SignatureDon''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. > >Can somebody help me with a problem I have? > > [quoted text clipped - 33 lines] > You can then filter those out records by using a criteria of > False. I also thought of IsNumeric; HOWEVER. IsNumeric will return True for "12345-". It treats that as a negative number. Also strings such as "1234e5" and "123d12" will return true. I think Marshall's solution is best if you want to ensure that no other characters beyound 0 to 9 are in the string. SignatureJohn Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . > There is also a VBA function, IsNumeric( ) which will evaluate a value > that [quoted text clipped - 46 lines] >> You can then filter those out records by using a criteria of >> False. Yes Marshall's answer was just what I needed. Once I changed the starting and end point of the mid string it worked just great. Mid([filename],5,7) Like "*[!0-9]*" returned: 007684. 112414- C111354 Only the records that should not be imported. Perfect! Thanks all! > I also thought of IsNumeric; HOWEVER. IsNumeric will return True for > "12345-". It treats that as a negative number. Also strings such as [quoted text clipped - 53 lines] > >> You can then filter those out records by using a criteria of > >> False. >There is also a VBA function, IsNumeric( ) which will evaluate a value that >is passed to it to determine whether it is numeric or not. > >What happens if they really screw it up and don't enter at least 12 >characters? >Dale Dale, Not only does IsNumeric allow leading and trailing plus, minus and dollar signs, but it also recognizes all the legal representations of floating point numbers. Some examples are 2D3, 14E15, 1.2D+3-$, etc. All that IsNumeric is good for is to check if a single character is a digit or if a string can be **converted** to some kind of numeric type value. SignatureMarsh MVP [MS Access] Thanks Marsh/John, I knew it had its limitations, but didn't consider the ramifications WRT this particular situation. I knew about the scientific notation, but what is the 2D3 or 1.2D+3 representation mean (is that some sort of currency representation)? Dale >>There is also a VBA function, IsNumeric( ) which will evaluate a value >>that [quoted text clipped - 14 lines] > character is a digit or if a string can be **converted** to > some kind of numeric type value. I don't really know. I think it is supposed to represent Decimal, but ... It seems to pretty much behave as scientific notation. SignatureJohn Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . > Thanks Marsh/John, > [quoted text clipped - 23 lines] >> character is a digit or if a string can be **converted** to >> some kind of numeric type value. That's kind of a holdover type specifier in some variations of the Basic language. E implied Single and D implied Double. Access accepts either syntax, but converts both to Double. SignatureMarsh MVP [MS Access] >I knew about the scientific notation, but what >is the 2D3 or 1.2D+3 representation mean (is that some sort of currency [quoted text clipped - 13 lines] >> character is a digit or if a string can be **converted** to >> some kind of numeric type value. |
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.