Criteria datatype error???

[GraeagleBill]

Can anyone spot the datatype error in the DLookup statement (see screenshot)? "AppID" and "RosID" are numeric table autonumbers. "Attending" is a Yes/No field in table "Appendages". "RetYear" is a text field in table "Appendages". "glbRetreatYear" is a string variable.





As an aside, what's the correct syntax to replace the cumbersome use of, for example, Me.RecordsetClone![Email] when attempting to simplify code appearance using "With". VBA complained when I tried .![Email].

The whole Sub:

Private Sub Gather_EMail_Addresses(Mode As Variant)
Dim strtemp As String
Dim strToClipBoard As String
Dim LineCnt As Integer

Me.EM_Addr_Cmd.Visible = True         'Reveal the primary command button
'Me.cmdShowAll.Enabled = True          'Re-enable when choice buttons executed.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
' We need to gather the e-mail addresses of the current Roster and display them in such away that
' they can easily be copied and subsequently be pasted into an addressee pane.  When Mode equals
' "AO" (Attendees Only), then the gathering is limited accordingly.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
strToClipBoard = ""
LineCnt = 0

If Me.RecordsetClone.RecordCount > 0 Then
    With Me.RecordsetClone
        .MoveFirst
    
        Do While .EOF = False
            If IsNull(Mode) Or DLookup("Attending", "Appendages", "AppID = " & Me.RecordsetClone![RosID] & " AND RetYear = " & gblRetreatYear) = True Then
                If Len(Trim(Me.RecordsetClone![Email])) > 0 Then
                    strtemp = Chr(34) & Me.RecordsetClone![LastName] & Chr(34)
                    
                    strtemp = strtemp & "<" & RTrim(Me.RecordsetClone![Email]) & ">," & vbNewLine
                    LineCnt = LineCnt + 1
            
                    strToClipBoard = strToClipBoard & strtemp
                End If
            End If
            
        .MoveNext
        Loop
        
        strToClipBoard = Left(strToClipBoard, Len(strToClipBoard) - 1)
    End With
    ClipBoard_SetText (strToClipBoard)
Else
    MsgBox "There no individuals in this group."
End If

Me.RecordSource = "QRoster"
Me.FilterOn = True

Me.EM_Addr_Cmd.SetFocus
Me.EM_Addr_Cmd.Caption = "DONE (" & LineCnt & " Addresses)"

End Sub

[ItsMe]

Which line is throwing the error? I am going to guess this one.
If IsNull(Mode) Or DLookup("Attending", "Appendages", "AppID = " & Me.RecordsetClone![RosID] & " AND RetYear = " & gblRetreatYear) = True Then

DLookup returns a value of a field. So you would assign the value returned to a variable, not compare it to a number or a constant.

Dim MyVar as Variant

MyVar = DLookup()

If MyVar > "" then
msgbox "Something is there"
End if


AS for DAO and the recordset thing
.![FieldName] is incorrect

It is not
rs.![FieldName]

It is
rs![FieldName]

With rs
MyVar = ![FieldName]
end with

Use the dot to access members of an object/class.

Use the exclamation for fully qualified names.

[GraeagleBill]

I moved the criteria outside so I could see the evaluation. "IsAttending" is the variant into which the DLookup returns. Same datatype error. The value of strTemp looks okay.

[GraeagleBill]

Sorry, wrong screenshot posted....................... been a long day.

[ItsMe]

Do you have a field named AppID that stores numbers and a field named RetYear that has text? Looks like you are using a wildcard there.
AppID = 542 AND RetYear = 2015*



EDIT: I now see post #4 so it seems you can ignore the comment below.
take a look at how the DLookup function works
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

You are not using it correctly. You are asking if the value in the field named Attending equals the constant vbYes. Actually, you are asking if it equals 6. The constant vbYes is, literally, 6.

[GraeagleBill]

"IsAttending" is Dim'd as variant local to the current Sub. My intention is that DLookup return whatever value if finds in the Appendages table based on the criteria I set in the string strtemp. "strtemp" whose current value is shown in the most recent screenshot looks okay to me.

The tabledef for "Attending" is Yes/No, so that's why using the VBA constant vbYes seemed to be the proper comparison to use. If vbYes is inappropriate in this case, what is? 6? E.g., "If IsNull(Mode) Or IsAttending = 6 Then"? Probably the better choice would be "If IsNull(Mode) Or Not IsNull(IsAttending) Then"

All of that notwithstanding, there's still an error message issued regarding the datatype of the DLookup criteria. And yes, AppID is a number and RetYear is text.

[ItsMe]

Hmmm maybe try passing one field in the where criteria. Try one and then the other. I do not believe I have tried two fields in a DLookup, maybe that is the issue. I would expect an error in the example in post #3. The only thing I can suggest for post #4 is to get rid of AND RetYear. Try it with AppID = . Then, try it with AND RetYear (only).

As for vbYes vs. yes/no field. vbYes is a constant in the VBA language. You can see what value a constant represents by typing ?vbYes in the immediate window within the VBA editor.

Booleans typically have three states, Yes, No, and Null. In Access, this is the same as -1, 0, and Null. Having said that, they did something to the triple state Boolean with the introduction of the accdb format. So, now, it seems they have got rid of the Null. I think the null is still there, they just use some Black Box stuff to mask it.

[GraeagleBill]

Your suggestion to split the criteria more-or-less flushed out the problem. While gblRetreatYear is in fact Dim'd as a string, it's still necessary to bracket in quotes, like we commonly see in SQL syntax where strings are expressed.

The code segment below shows the final changes that process the recordset clone as desired.

Thanks for all your help,
Bill

        Do While .EOF = False
        strtemp = "AppID = " & ![RosID] & " AND RetYear = " & """" & gblRetreatYear & """"

        IsAttending = DLookup("Attending", "Appendages", strtemp)
        
        If IsNull(Mode) Or IsAttending < 0 Then

                If Len(Trim(![Email])) > 0 Then
                    strtemp = Chr(34) & ![LastName] & Chr(34)
                    
                    strtemp = strtemp & "<" & RTrim(![Email]) & ">," & vbNewLine
                    LineCnt = LineCnt + 1
            
                    strToClipBoard = strToClipBoard & strtemp
                End If
            End If
            
        .MoveNext
        Loop

[ItsMe]

Glad you were able to get it working. I was looking at what I was typing earlier and I should of noticed the = operator verses the LIKE operator. Are you able to keep the wildcard (*) using the equal operator and quotes?

[GraeagleBill]

Where are you seeing the use of wildcards? I don't have them anywhere in my code, now or previously.

[GraeagleBill]

Where are you seeing the use of wildcards? I don't have them anywhere in my code, now or previously.

[ItsMe]

I thought I did in post #3, in the hint while hovering the variable, but it must have been put there because there was not enough room for the entire hint. I never noticed an asterisk before.

[GraeagleBill]

I just see the quoted string in the hint when I look at post #3. I do click on the screenshot image to view at 100%, but I thought that's how most view images.

Again, thanks for your help.
Bill