Can I typecast in a Select statement?

[drmichaelw]

I have a select statement that includes [part id]
The excel table that I am linking to has "dirty data". Some [Part Id] have been assigned 0,00,000,0000,etc. [Part Id]is text not a number. I am performing a COUNT function on [Part ID] and it counts the [Part Id} with the 0,00,000,0000,etc. I want to ignore these values. Is there a way I can typecast the value to an integer and put in my where clause WHERE [Part ID]>0. Or any other ideas. I would like to code in my query as apposed to vbscript.

 

[rac2]

CInt([Part ID])

 

[scriverb]

You can use a CLng([PartID]) to convert the text field to a Long Integer and then like you said setup your WHERE statement with a criteria of [PartID]>0.

I am a little concerned about the way you described the bad data. Is the text field coming through with the comma seperated zeroes? If the above does not work on these bad records then we can discuss this situation some more.

Get back with me with your results of this suggestion.

Bob Scriver

 

[drmichaelw]

Select CLng([Part ID])
From table
WHERE [Part ID] >0

I get an error in the expression.
Problem out of the over 20000 rows
some of the part ids have letters. Would this
blow up the Clng function?

 

[scriverb]

Yes, the alpha characters would give you the errors.

Let's try leaving the data just like in comes in from the linked table and just change the WHERE statement to NOT select records with the eroneous values.

WHERE [PartID] <> &quot;0,00,000,0000&quot;

Give this a try. Bob Scriver

 

[tlbroadbent]

Try this. Use the IsNumeric Function.

WHERE IsNumeric([PartID]) = -1 Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[drmichaelw]

Bob,
I'm not sure what you are doing on the WHERE clause. It looks to me that the only value that won't be selected is the one you listed, or are you saying to make a WHERE clause like this
WHERE [part id] <> '0' AND <>'00' AND <> '000', etc.?

 

[scriverb]

In your original post you stated that some of the records had a value of &quot;0,00,000,0000&quot;. Not understanding how this is happening I took you word for it. Maybe I am not understanding you clearly. But, we are trying to eliminate these records from the query. This is the reason that I recommending trying:

Select [Part ID]
From table
WHERE [Part ID] <> &quot;0,00,000,0000&quot;

As some of the legitimate part ID's have an Alpha character which is okay we can't convert them to Long Integers in this case.. Now if you meant to say that some of the records have a different value like 0 and 00 and 000 or 0000 then we can deal with that a little different. Just explain it a little better.

Bob Scriver

 

[drmichaelw]

Sorry for the confusion!

Sample data:
0
00
000000
000000-000-00
000-000-00-000
Each month I receive new data and I won't know in advance how many part id will have data like the above, and what combination of zeros and or dashes they will have.
Any Part id that contains zeros with or without dashes I would like to eliminate from my query.

The part Id is alpha numeric containing numbers, letters and dashes. I hope this helps.

 

[scriverb]

Try this. Paste this function into a database module.
Public Function ValidPartID(CharToReplace As String, SearchString As String, ReplacementChar As String) As Boolean
Dim I As Integer, s As String, ss As Integer, vLngPartID As Long
ss = Len(SearchString)
For I = 1 To ss
If InStr(CharToReplace, Mid$(SearchString, I, 1)) = 0 Then
s = s & Mid$(SearchString, I, 1)
Else
s = s & ReplacementChar
I = I + Len(CharToReplace)
End If
Next I
vLngPartID = CLng(s)
If vLngPartID = 0 Then
ValidPartID = False
Else
ValidPartID = True
End If
End Function

Then use the following WHERE statement:

WHERE ValidProdID([PartID]) = True;

This function and WHERE statement will convert all of the hyphens(-) in the part ID to zeroes(0), then convert that string to a Long Integer. If this Long Integer equals 0 then the PartID is invalid(False). If it converts it to a True then the PartID is valid(True). This is all done in the Call to the Function and does not affect the table or the PartID field being displayed. The WHERE statement will eliminate any records with a PartID conbinations of zeroes(0) and hyphens(-).

Let me know how this works for you. Bob Scriver

 

[scriverb]

I have a few typos and problems with this still. I jumped the gun a little so just wait for the next post. Bob Scriver

 

[scriverb]

I missed the fact that there would be alpha characters and the size of the text field could be a problem with converting to long integer so I changed the function to look for something other than &quot;0&quot; and &quot;-&quot;. If it finds one character other than those then the PartID is okay and is selected.

Public Function ValidPartID(SearchString As String) As Boolean
Dim I As Integer, s As String, ss As Integer, vLngPartID As Long
ss = Len(SearchString)
For I = 1 To ss
If Mid$(SearchString, I, 1) <> &quot;0&quot; And Mid$(SearchString, I, 1) <> &quot;-&quot; Then
ValidPartID = True
I = ss
End If
Next I
End Function

Now here is the query SQL demonstrating the WHERE statement necessary to select only valid PartID's:

SELECT tblYourTable.PartID
FROM tblYourTable
WHERE (((ValidPartID([tblYourTable]![PartID]))=True));

Let me know if this works for you. You will have to modify the table designation in the query and the fields that you want selected.
Bob Scriver