strcompair and sql

[Trevor2007]

I have an Sql statment I need to do a case sensitive compair on and I don't know how to do the case sensitive compair I Have seen examples here but I didn't have a luck getting an answer I could work with , could some one make the appropiate changes to my sql statment for the case sensitive compair so I can see how it works :

 vba:
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PsWrdTbl] WHERE" _
& " [password]= '" & Me.[Password] & "' AND [Name]= '" & Me.[Tech. Name] & "';")
 

Thanks for helping.

[ADezii]

I have an Sql statment I need to do a case sensitive compair on and I don't know how to do the case sensitive compair I Have seen examples here but I didn't have a luck getting an answer I could work with , could some one make the appropiate changes to my sql statment for the case sensitive compair so I can see how it works :

 vba:
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PsWrdTbl] WHERE" _
& " [password]= '" & Me.[Password] & "' AND [Name]= '" & Me.[Tech. Name] & "';")
 

Thanks for helping.

To perform an exact, Binary Comparison ("Philadelphia" <> "philadelphia"), you would have to incorporate something similar to that below into the Where Clause of the SQL String:

'Assuming [Tech Name] is a String
Where StrComp([Password], Me![Password], vbBinaryCompare) = 0 And [Name] = '" & Me![Tech Name] & "';"

[Trevor2007]

Thanks I am in the process of trying your post reply out, but I forgot how do I set a password complexity sting , so that the user can change their password
Ie password must 7 - 10 charicture long and must contain 2 special characters (special chacters being: !,@,#,$,%,^,&,*, 1 captital leter. but they can appear in any order in the password
thanks for helping.

[Trevor2007]

Dim qryDB As DAO.Database
Dim loRst As DAO.Recordset
With loRst
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PsWrdTbl] WHERE strComp(" _
& " [password], Me.[Password], vbBinaryCompare) = 0 AND [Name]= '" & Me.[Tech. Name] & "';")
tstr = .Fields("password")
 
If loRst.RecordCount > 0 Then MsgBox "we have a match"
If loRst.RecordCount = 0 Then MsgBox "no match"
End With
 Set loRst = Nothing
    loRst.Close
 

This doesn't seem to be working , I have a text box set up for testing to return the password from the db and also generate a msg if recordcount is greater that 0 and nother message if recordcount is less than 0
and niether mesgbox's will generate and the textbox stays blank
FYI: tech. Name is a string

[ADezii]

I have an Sql statment I need to do a case sensitive compair on and I don't know how to do the case sensitive compair I Have seen examples here but I didn't have a luck getting an answer I could work with , could some one make the appropiate changes to my sql statment for the case sensitive compair so I can see how it works :

 vba:
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PsWrdTbl] WHERE" _
& " [password]= '" & Me.[Password] & "' AND [Name]= '" & Me.[Tech. Name] & "';")
 

Thanks for helping.

I've changed my trend of thought on this Password Thread since it must meet different criteria levels. The code will actually act as Validations for saving Passwords to PsWrdTbl:

1. Not only must the Password Match the value in the Text Box, it must be a Binary Match (case sensitive).
2. Its length must be >= 2 and <= 10.
3. It must contain at least 2 Special, pre-defined characters

Dim strPassword As Variant
Dim intCounter As Integer
Dim intNumOfSpecChars
Const strSpecialChars As String = "!@#%^&*"
 
intNumOfSpecChars = 0       'Initialize
 
If IsNull(Me![Password]) Then Exit Sub
 
strPassword = Nz(DLookup("[Password]", "PsWrdTbL", "[Password] = '" & Me![Password] & "'"), "")
 
If Len(strPassword) > 0 Then        'Test #1, a Password does exist
  If StrComp(strPassword, Me![Password], vbBinaryCompare) = 0 Then    'Test #2, is Password an "exect" Match
    If Len(strPassword) >= 2 And Len(strPassword) <= 10 Then      'Test #3, length of Password >=2 and <=10
      For intCounter = 1 To Len(strSpecialChars)
        'Test #4, at least 2 special characters as defined by Const strSpecialChars
        If InStr(strPassword, Mid$(strSpecialChars, intCounter, 1)) > 0 Then
          intNumOfSpecChars = intNumOfSpecChars + 1
        End If
      Next
      If intNumOfSpecChars < 2 Then
        MsgBox "Password is missing some special characters!"
      Else
        'If you ever get here, you have a valid Password meeting all 4 conditions
        MsgBox "Bravo"
      End If
    Else
      MsgBox "Password is not the proper length!"
    End If
  Else
    MsgBox "Your Password is no an exact Match!"
  End If
Else
  MsgBox "Invalid Password!"
End If

[Trevor2007]

WOW, thanks for the code,I was looking an IntStr and for somreason never crossed my mind to use a counter on predefined special characture.
I hav't tried the code yet, I'm still putting together the login screen, where if they login correctly then they can change there password (I've made it not so easy to login though I did a binary compare on both the username in the field against the table as well as the password
Thanks for your help agpin.
Side note: I plan to allow the user to change password like on windows logonscreen , where user must enter the "old" password, then the new password twice

[ADezii]

WOW, thanks for the code,I was looking an IntStr and for somreason never crossed my mind to use a counter on predefined special characture.
I hav't tried the code yet, I'm still putting together the login screen, where if they login correctly then they can change there password (I've made it not so easy to login though I did a binary compare on both the username in the field against the table as well as the password
Thanks for your help agpin.
Side note: I plan to allow the user to change password like on windows logonscreen , where user must enter the "old" password, then the new password twice

Glad to help you Trevor. Just keep in mind that all the preceeding code is useless if the same Validations are not applied when the User atempts to Save a Password. It should pass similar Tests prior to be saved in the Table. Take care.