Creating an SQL Query

[paultri]

I have two questions.

1. I need to query a field in a table and only show records that contain numbers....Is this possible? if so how would i go about it?

2. I need to query an EMAIL field, but i want to cleanup email accounts that are not formatted properly, no @ symbol or .com, .net etc etc... Anyone have any suggestions on the best way to do this? This is a database of over 300,000 customers.

Any help with this is GREATLY appreciated!!!!!

Thanks
Paul

 

[dcx693]

I haven't done the exact things that you're asking about, but I can at least offer some suggestions.

1- There is an IsNumeric() function in Access. You can use that in your query critieria to only show results that are numeric. In the QBE grid, place this in the field row: IsNumeric([name_of_field]) and place this in the criteria row: True. I assume that you mean you're looking for results where entire field looks like a number, not just where a number exists (and can be in the middle of text).

2- Use the Instr function to look for @ symbols in your addresses, then check that there are characters in front of the @ symbol, then look for a period and characters before the period and after. You can use the Left(), Right() and Mid() string functions for these tests. Sorry I don't have a pre-formatted IsEmail function. I imagine someone out there has written one.