Searching for a non-numeric character in a field

[DubCap01]

Hi all, hope this is the correct forum...

I am importing a .txt file into a generic table called episodes, using the Docmd.TransferText acImportDelim etc routine with no field names. On doing so, I know that the ensuing table will be short text for every field.. That's fine as far as I am concerned, because I can convert whatever fields I want at a later stage to the correct formats, as necessary.

Before I do that conversion though, I want to search through a particular field ([F8]), and see if it contains non-numeric characters. What I know is that either the number 19 or the value <Null> will come into the table in field [F8] on import. I know that because that is what the source system exports for good and bad values.

So what I am looking for is a better solution to what I thought would work which was this, but is obviously flawed
I tried a second option, also in the code below, but that didn't work either

If DCount(Len("F8"), "Episodes", ">2") Then Me.DirtyEP = 1

If DCount("F8", "Episodes", "<>19") Then Me.DirtyEP = 1

My choices of code always return Me.DirtyEP = 1, and all the current records in [F8] show the value 19! So can someone suggest some corrective code to replace mine?
What I thought was a simple <does not equal (numeric) 19> is getting complicated lol!


thanks in anticipation


Pete

[ranman256]

this function will check the field for non numeric characters in the field, or textbox
usage:
Me.DirtyEP =IsNonNum([field])


paste this code into a module.

    'scan each character to see if non numeric
function IsNonNum (pvField) as boolean
dim i as integer

if not IsNull(pvField) then
for i = 1 to len(pvField)
   if not isnumeric((mid(pvField,i,1)) then  
      IsNonNum = true
      exit function
   end if 
next
end if
end function

[CJ_London]

see if it contains non-numeric characters. What I know is that either the number 19 or the value <Null>

I don't see any non numeric characters here - either the value is 19 or null (null is not a character) - or do you mean you have text which says "Null"

It looks like you just want to know if there is one or more F8 values which are not 19 (i.e. null)

if so then your domain function criteria is wrong and your if statement is wrong. try

if dcount("*","Episodes","[F8] is null")>0 then me.dirtyEP=1

or

if dcount("*","Episodes","isnull([F8])")>0 then me.dirtyEP=1

or if you have "null" text

if dcount("*","Episodes","[F8] ='null'")>0 then me.dirtyEP=1

[andy49]

iif(DCount("id", "episodes", "[f8] Is  Null"),Me.DirtyEP = 1,whatever you want to do if its not true)

ID is the primary field

The main issue here is that you're trying to count null values in a field (f8)

The dcount function doesn't count null fields so you have to count another field "where f8 is null"

[DubCap01]

Thanks for the prompt replies guys everywhere!
Can I just clarify?
Field [F8] - Short Text - 255 characters
It's "<Null>", not Null
It's "19", not 19
Where to from here?

[DubCap01]

Thanks Ajax, I have text which says "<Null>"

[DubCap01]

Thanks Ajax, your last line of code fixed the problem - mucho thanks and beers mate!

if dcount("*","Episodes","[F8] ='null'")>0 then me.dirtyEP=1

cheers
Pete