Solved

DLookUp with multiple criteria VBA

Posted on 2009-05-20
8
431 Views
Last Modified: 2013-11-28
Hello,

I'm trying to if a user name exists in a table over the past 4 weeks...
I'm creating this database to update our contact information and in order to make more engaging we are developing a point system which can be used to trade for other things.
The first step of the code is to check the user name if the user name has a manager name then the code stops
Then the code checks if today is Monday, Tuesday or Wednesday if it is not then the code stops
Then it checks if the current date is checked against the tblWinners_ResOps table to make sure that there is no winner this week
Then it checks whether the user has won over past 4 weeks (this is not working), but if it is true then the code stops
Finally if all the above come back false then a random number is generated and compared against 17 (lucky number) if they both equal each other then the user wins.

Can you tell me if my DLookUP is wrong and what am I doing wrong...

Thank you,
Private Sub cmdUpdateContact_Click()

Dim strDayName As String

Dim strUserName As String

Dim intRandomNumber As String

Dim strFName As String

Dim strsql As String

Dim dateExists As Integer

Dim dateExists4wks As Integer

Dim dtStart As Date

Dim dtEnd As Date

Dim dt4Weeks As Date
 
 

'Here we'll set up some task for the contact update
 
 

'Next we'll process the check winning process

strDayName = Format(Date, "dddd")

strUserName = fOSUserName()

strFName = DLookup("UserFirstName", "tblHSGUsers_ResOps", " [UserNetworkName] =  '" & fOSUserName() & "'")

intRandomNumber = Int((Round(((1 * Nz(DCount("User_ID", "tblHSGUsers_ResOps"))) * 5) * 2.5, 0) - 1 + 1) * Rnd() + 1)

dtStart = Format(Date, "mm/dd/yyyy")
 

Select Case Weekday(dtStart)

    Case "2"

        dtEnd = Format(Date, "mm/dd/yyyy")

    Case "3"

        dtEnd = Format(DateDiff("d", 1, Date), "mm/dd/yyyy")

    Case "4"

        dtEnd = Format(DateDiff("d", 2, Date), "mm/dd/yyyy")

    Case "5"

        dtEnd = Format(DateDiff("d", 3, Date), "mm/dd/yyyy")

    Case "6"

        dtEnd = Format(DateDiff("d", 4, Date), "mm/dd/yyyy")

End Select
 

dt4Weeks = Format(DateDiff("d", 28, Date), "mm/dd/yyyy")
 

dateExists = DCount("Winner_Date", "tblWinners_ResOps", "[Winner_Date] BETWEEN #" & dtStart & "#  AND  #" & dtEnd & "#")

dateExists4wks = DCount("Winner_Name", "tblWinners_ResOps", "Winner_Name = " & strUserName & "" And "[Winner_Date] BETWEEN #" & dtStart & "#  AND  #" & dt4Weeks & "#")
 

'check if it is ResOps Managers

If LCase(strUserName) <> "Manager1" Or LCase(strUserName) <> "Manager2" Then

    'Check if it is the first 3 days of the week

    If strDayName = "Monday" Or strDayName = "Tuesday" Or strDayName = "Wednesday" Then

        'Check if there is a winner this date

        If dateExists = 0 Then

 

	   '     THIS SECTION IS NOT WORKING CORRECTLY AND NEED TO FIGURE OUT WHY

           'Check if date exists 4 weeks ago

            If dateExists4wks = 0 Then

                'Check if random # generated is equal to 17

                If intRandomNumber = 17 Then

                    

                    MsgBox strFName & " You WON!!!"

                    strsql = "INSERT INTO tblWinners_ResOps ( Winner_Name, Winner_Date ) " & _

                                "SELECT fOSUserName() AS Winner_Name, Date() AS Winner_Date;"

                            Debug.Print

                            DoCmd.SetWarnings False

                            DoCmd.RunSQL strsql

                            DoCmd.SetWarnings True

                    

                End If

            End If

        End If

    End If

End If
 
 
 

End Sub
 
 

'Using this function to get the UserName

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" _

(ByVal lpBuffer As String, nSize As Long) As Long
 

'-----------------------------------------------------------------------------------------

' This code is used to retrieve the network user name by accessing the API apiGetUserName.

' Created by: Unknown (Found on Dev Ashish web site http://home.att.net/~dashish/api)

'-----------------------------------------------------------------------------------------
 

Public Function fOSUserName() As String

On Error GoTo fOSUserName_Err
 

    Dim lngLen As Long, lngX As Long

    Dim strUserName As String

    

    strUserName = String$(254, 0)

    lngLen = 255

    lngX = apiGetUserName(strUserName, lngLen)

    

    If lngX <> 0 Then

        fOSUserName = Left$(strUserName, lngLen - 1)

    Else

        fOSUserName = ""

    End If
 
 

fOSUserName_Exit:

    Exit Function
 

fOSUserName_Err:

    MsgBox Error$

    Resume fOSUserName_Exit

End Function

Open in new window

0
Comment
Question by:AIdoHSG
  • 4
  • 4
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24433764
it looks good..

strFName = DLookup("UserFirstName", "tblHSGUsers_ResOps", " [UserNetworkName] =  '" & fOSUserName() & "'")

'ADD this line to verify you got the correct name, remove later

msgbox strFName
0
 

Author Comment

by:AIdoHSG
ID: 24434339
Hello Capricorn1,

I noticed it and tried it out, it is capturing the correct username, but everytime i run the code i get a Run-time error '13' type mismatch and it highlightes the

dateExists4wks = DCount("Winner_Name", "tblWinners_ResOps", "Winner_Name = '" & strUserName & "'" And "[Winner_Date] BETWEEN #" & dtStart & "#  AND  #" & dt4Weeks & "#")

When I hover over it I get a value of 0... I know there is data in the table because I put it myself and I should be getting 2 instead.

Any suggestions???
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24434464
change this

dateExists4wks = DCount("Winner_Name", "tblWinners_ResOps", "Winner_Name = '" & strUserName & "'" And "[Winner_Date] BETWEEN #" & dtStart & "#  AND  #" & dt4Weeks & "#")

to

dateExists4wks = DCount("Winner_Name", "tblWinners_ResOps", "Winner_Name = '" & strUserName & "' And [Winner_Date] BETWEEN #" & dtStart & "#  AND  #" & dt4Weeks & "#")
0
 

Author Comment

by:AIdoHSG
ID: 24434517
Hello capricorn1,

I don't get the error anymore, but i still get 0 for the Dcount... there are two records that are dated within the last 4 weeks.

Any suggestions

Thank you for your help
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24434674
<there are two records that are dated within the last 4 weeks.>

does the "Winner_name" from table tblwinners the same as the name represented by the variable  strUserName ?
0
 

Author Comment

by:AIdoHSG
ID: 24434844
Yes the name matches what i have in the table...
I ran the DCount expression in a query and it gave me 2, but in VBA it is 0
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 24435039
what is  the format of  [Winner_Date] ? is this a DateTime datatype?

change this

dtStart = Format(Date, "mm/dd/yyyy")
to

dtStart = cdate(Format(Date, "mm/dd/yyyy"))

and this
dt4Weeks = Format(DateDiff("d", 28, Date), "mm/dd/yyyy")

to

dt4Weeks = cdate(Format(DateDiff("d", 28, Date), "mm/dd/yyyy"))



0
 

Author Closing Comment

by:AIdoHSG
ID: 31583558
Sorry for the delay... I forgot about this question.

Thank you, I managed to figure out the issue. Your suggestions also helped the process go much smoother.

Thank you again,
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now