Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    37

    Unanswered: Data Type Mismatch

    Me again

    Im trying to see if a student already exists (has a student id in tblStudents) and if so delete that student and put them in again but with changed details (namely the tutor code). Ive tried the code below. But it highlights the exist dlookup line and says data type mismatch in criteria expression; ive tried declaring exist as variant. Is there a data type that i can declare exist as which can sometimes by null and sometimes an integer?

    id = rs("STU_ID")
    exist = DLookup("STU_ID", "tblStudents", "STU_ID ='" & id & "'")

    If exist = " " Then

    'Put the assigned student into the Student Table
    strsql = "Insert into [tblStudents] ( STU_ID, STU_FORENAME, STU_SURNAME, STU_COURSE_CODE, STU_FAC_NO, STU_STANDING, STU_TU_CODE ) " _
    & "Select [tblWorking].STU_ID, [tblWorking].STU_FORENAME, [tblWorking].STU_SURNAME, [tblWorking].STU_COURSE_CODE, " & fac & ", [tblWorking].STU_STANDING, [tblWorking].STU_TU_CODE " _
    & "From [tblWorking] " _
    & "Where tblWorking.STU_TU_CODE is not null "

    db.Execute (strsql)

    Else
    strsql = "Delete from tblStudents where tblStudents.STU_ID = '" & exist & "'"
    db.Execute (strsql)
    strsql = "Insert into [tblStudents] ( STU_ID, STU_FORENAME, STU_SURNAME, STU_COURSE_CODE, STU_FAC_NO, STU_STANDING, STU_TU_CODE ) " _
    & "Select [tblWorking].STU_ID, [tblWorking].STU_FORENAME, [tblWorking].STU_SURNAME, [tblWorking].STU_COURSE_CODE, " & fac & ", [tblWorking].STU_STANDING, [tblWorking].STU_TU_CODE " _
    & "From [tblWorking] " _
    & "Where tblWorking.STU_TU_CODE is not null "
    End If

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off I'd suspect that you are trying to look for a number not a string / text value.
    Code:
    exist = DLookup("STU_ID", "tblStudents", "STU_ID ='" & id & "'")
    should read, assuming its a number
    Code:
    exist = DLookup("STU_ID", "tblStudents", "STU_ID =" & id )
    numbers in where clauses (effectively this is a where clause) should be clear, string values encapsualted in single or double quotes, however you can also use intrinsic constants eg TRUE/FALSE in boolean fields, and NULL on string columns

    incidentally unless you need the value exist you could take advantage of the true false return from dlookup to something like. To be honest I can't remember if it works with Dlookup, but its somethign to bear in mind... it can make the code easier to understand.
    Code:
    if DLookup("STU_ID", "tblStudents", "STU_ID ='" & id & "'") then
    'found student details
    else
    'haven't foudn the student details
    endif

  3. #3
    Join Date
    Jan 2008
    Posts
    37
    Hi Healdem,

    The true/false return for dlookup does work and makes the code easier to understand, thanks very much!

Posting Permissions

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