yet another dlookup question

[scoob8254]

hello and heres yet another dlookup question, its annoying me and ive tried a few different variations.

its a dlookup with 2 criteria, i cant seem to resolve why i keep getting data type mismatch error,

here my bit of code

If Counter = DCount("[Alias]", "[tblAlias]", "[Alias]='" & sNick & "' And [UserID]='" & sID & "'") > 0 Then

no doubt some1 will solve it within 2secs , im positive the problem is to do with the sID which holds a userid, i know its prefixed with a s but its actualy a double not a string

any help much appreciated

scoobs

 

[DCrake]

If userID is a number you need to drop the single quotes. Not needed.

 

[scoob8254]

chirst, thanks a lot, i swear down i'd tried that lol. thanks anyway, feel daft now

scoobs

 

[JamesMcS]

Did that work? You've got 2 conditions in your if there - one to say counter=dlookup..whatever and a >0 at the end. I thought, if you had 2 conditions in an if, you'd have to put something like

If [Counter] = DCount("[Alias]", "[tblAlias]", "[Alias]='" & sNick & "' And [UserID]='" & sID & "'") OR [counter]>0

DC, am I wrong there?

 

[gemma-the-husky]

the other thing is

what data type is counter?

as you are equating counter to the result of a boolean test

ie dcount(something)>0

 

[JamesMcS]

Isn't boolean true or false?

 

[gemma-the-husky]

yes

the original dcount resulted in a boolean test

the expression was

counter = expression>0

this will only result in true or false (-1 or 0) . I dont think it would necessarily give a type mismatch, but i suspect its not what was required.

 

[scoob8254]

was merely a test to see if a record already excists with that alias and id, because people with different id's can have the same alias i needed to test for both, basicaly its part of some code which records which alias's users have been using

 

[JamesMcS]

I've never seen an expression like that before, = and > in one line would surely produce an error?

 

[DCrake]

Ok Lets turn it into a function for easier user

Public Function IsDuplicate(AnyAlias As String, AnyID as Long) As Boolean
'/Checks to se if a record exists for both alias and id

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset("Select * From tblAlias Where Alias ='" & AnyAlias & "' And UserID =" & AnyID)

If Not Rs.EOF and Not Rs.BOF Then
   IsDuplicate = True
   Rs.Close
Else
   IsDuplicate = False
End IF

Set Rs = Nothing

End Function

Then in your form you would employ as such

If IsDuplicate(sNick, sID) Then
   MsgBox "Duplicate"
End If

 

[scoob8254]

your right doesnt look right, seems to work tho for some reason, their are some if arguments just prior to that so will check them see if that bit of code is being bypassed, only wierd thing is the code is doing what i want it to :/ seems to be working fine