Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Troubleshooting (VB6/Access 2K)

    I am trying to set up a query in conjunction with a front end application that will determine if a password has been picked by a user within the last three times. I figured the outer query would pick the top three using the date field in decending order, then a sub query would be used to determine if the password exists in the top three in the outer query. This doesn't seem to be working out for me and have posted the SQL below in hope someone might be able to tell me what I'm doing wrong.

    SELECT TOP 3 tblPasswordHist.UID, tblPasswordHist.UserID, tblPasswordHist.Password, tblPasswordHist.PWDDate
    FROM tblPasswordHist
    GROUP BY tblPasswordHist.UID, tblPasswordHist.UserID, tblPasswordHist.Password, tblPasswordHist.PWDDate
    HAVING (((tblPasswordHist.Password) In (select Password from tblPasswordHist "hist1" Where Password = 'Test')))
    ORDER BY tblPasswordHist.PWDDate DESC;

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Troubleshooting (VB6/Access 2K)

    Do you want to test for the last three passwords picked by any user or by the user who is setting the password?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Troubleshooting (VB6/Access 2K)

    No, just the last three password picked by a specific user.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Troubleshooting (VB6/Access 2K)

    So, let's assume that you have a form frmPassword with controls txtUserID and txtPassword that contain the UserID and the potential new password.

    The following query will return the last three passwords picke by this user:

    SELECT TOP 3 Password
    FROM tblPasswordHist
    WHERE UserID=[Forms]![frmPassword]![txtUserID]
    ORDER BY PWDDate DESC;

    Save this as (for example) qryLast3. Create a new query based on qryLast3:

    SELECT *
    FROM qryLast3
    WHERE Password=[Forms]![frmPassword]![txtPassword];

    Save this query as (for example) qryMatch. If this query returns a record, there is a match, if it returns no records, there is no match. So you can test if DCount("*", "qryMatch") is 1 or 0.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Troubleshooting (VB6/Access 2K)

    Thanks, Hans. I was trying to figure out how to do it all with one SQL statement in case I had to move it to Oracle. What you propose will work just fine though for my purposes right now and will use this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •