Hopefully an easy one for you. I have a varchar(7) field in which the majority of the values are 7 digit numbers ('#######').
I'm trying to count the number of records that have a "valid" 7 digit number value in them.
SELECT COUNT(myField) FROM myTable HAVING MAX(LEN(myField)) ='7'
That returned the count of all records in my table. I know there are some blanks and commas and things in there so I know that's not right - Maybe I should use rtrim/ltrim?
Then after a little research I found "DATALENGTH" and tried:
SELECT COUNT(myField) FROM myTable WHERE DATALENGTH(myField) ='7'
That returned a count smaller than the total record count (more than half). This seems realistic but I'm not sure if I'm getting what I'm asking for.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs).
Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…