Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DLookUp with multiple criteria VBA

Posted on 2009-05-20
8
Medium Priority
?
439 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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