SQL Question

[ruff]

I'm sure this must be do-able but i just cannot get it to work.

I'm using Jet to connect to an Access 2002 db. All the sql requests
I've tried so far have worked so far but I've now hit a brick wall.

I have a table with a column DSCD which contains identifiers in the
format 'A00000' where A is either upper case or lower case letter
followed by a 5 digit number.

The problem I'm having is that i want to count the number of records
which start with a capital letter. I've tried so many different ways
but always get back a count including the lowercase records.

Somebody please put me out of my misery!!!

Private Sub Form_Load()

m_sConnStr = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source='" &
App.Path & "\Indices.mdb'; "
Set oConnection1 = New ADODB.Connectio n
oConnection1.Cu rsorLocation = adUseClient
oConnection1.Op en m_sConnStr

sSQL = "SELECT NAME FROM TblTotal WHERE NAME LIKE '%MSCI%' AND
DSCD LIKE 'return records starting with capital only'"
Set m_oRecordset = New ADODB.Recordset
m_oRecordset.Op en sSQL, oConnection1, adOpenStatic,
adLockReadOnly, adCmdText
Label7.Caption = m_oRecordset.Re cordCount

[Jeffrey R. Bailey]

Re: SQL Question

The criteria part of your SQL string should look something like this:
WHERE Asc([DSCD])='97' AND NAME Like '%MSCI%';"

Asc function returns the ASCII character code of the first character of any
string that is submitted to it and Jet supports the Asc function. The
character code for the lower case "a" is 97 and the upper case "A" is "65"
exactly 32 less than the lower case.

By the by, it is bad practice to name a field with a common property like
"Name". Generally, for clarity's sake something specific like
"CompanyNam e", "CustomerNa me", or "FirstName" works better.

--
Jeffrey R. Bailey
"ruff" <spurs1961@tisc ali.co.uk> wrote in message
news:ed21a43f.0 401290725.3bde0 9d5@posting.goo gle.com...[color=blue]
> I'm sure this must be do-able but i just cannot get it to work.
>
> I'm using Jet to connect to an Access 2002 db. All the sql requests
> I've tried so far have worked so far but I've now hit a brick wall.
>
> I have a table with a column DSCD which contains identifiers in the
> format 'A00000' where A is either upper case or lower case letter
> followed by a 5 digit number.
>
> The problem I'm having is that i want to count the number of records
> which start with a capital letter. I've tried so many different ways
> but always get back a count including the lowercase records.
>
> Somebody please put me out of my misery!!!
>
> Private Sub Form_Load()
>
> m_sConnStr = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source='" &
> App.Path & "\Indices.mdb'; "
> Set oConnection1 = New ADODB.Connectio n
> oConnection1.Cu rsorLocation = adUseClient
> oConnection1.Op en m_sConnStr
>
> sSQL = "SELECT NAME FROM TblTotal WHERE NAME LIKE '%MSCI%' AND
> DSCD LIKE 'return records starting with capital only'"
> Set m_oRecordset = New ADODB.Recordset
> m_oRecordset.Op en sSQL, oConnection1, adOpenStatic,
> adLockReadOnly, adCmdText
> Label7.Caption = m_oRecordset.Re cordCount
>[/color]

[ruff]

Re: SQL Question

Thanks very much for your reply Jeffrey. A great help.