Filter ID by "."

[philgarside]

Hi all,

Any help with this would be appreciated. I have a field in my query call Id; it’s our reference id for jobs being worked on. However we have a number of duplicates that have been imported in error. These error id’s contain a decimal place in the code and I wanted to know if it would be possible to filter these out in my query.

ID
12494245
714023
692159
707177
691188
708161
694876
694876
10817399
12469813
1.2469
12501075
12495731
1.2369
12369493
12501898
12399664
11979428
712805
720276


Thanks for your help,

Phil

 

[dk]

Hi

Assuming that your ID field is text then you could use something like this:

SELECT MYTABLE.ID
FROM MYTABLE
WHERE Clng(InStr(1,[MYTABLE]![ID],'.'))=0

Obviously you'll need to change the table and field names. It will basically pull any records where there is no full stop/period in the ID field.

HTH
DK

 

[boblarson]

I would go with:

SELECT ID
FROM MYTABLE
WHERE InStr(1,[ID],'.') = 0

 

[philgarside]

That works great DK thanks for your help.

 

[dk]

Glad to hear it. Just to say, you would probably be better off using the slightly changed version that Bob suggested - using the Clng was an oversight on my part and will actually result in the query running slower (approximately half the speed based on testing I did on 600,000 records). If your table is small (e.g. few thousand rows) then the speed difference may be negligible although it's always best to use the quickest query possible as that will allow for further growth and ensure that your application is as responsive as possible.

Cheers
DK