Results 1 to 7 of 7
  1. #1
    Join Date
    May 2014
    Posts
    4

    Unanswered: send email each time i register bill

    i have problem " data type mismatch in criteria expression " at
    Set rest = mydb.OpenRecordset(temp, dbOpenDynaset)


    code
    Code:
    Private Sub Command2_Click()
    Dim mydb As DAO.Database
    Dim rest As Recordset
    Dim temp As String
    Dim mpayroll As String
    Dim mpage As String
    Dim sqlstring As String
    Dim qdf As QueryDef
    Dim reset As Recordset
    Dim mmail As String
    
    
    Set mydb = CurrentDb
    temp = " SELECT DISTINCT payroll, page FROM pay WHERE page = '" & scode & "'" & " ORDER BY payroll"
    Set rest = mydb.OpenRecordset(temp, dbOpenDynaset)
    rest.MoveFirst
    While Not rest.EOF
    
    mpayroll = rest("payroll")
    mpage = rest("page")
    sqlstring = " SELECT payed2.payroll, payed2.type, payed1.page, payed2.amount, personal.namee, personl.email" & _
    " FROM payed2 INNER JOIN payed1 ON payed2.page = payed1.page, INNER JOIN personal ON payed2.payroll = personal.payroll" & _
    " WHERE payed2.payroll = '" & mpayroll & "' And payed2.Page = " & mpage & "" & _
    " ORDER BY payed2.payroll "
    
    qdf.SQL = sqlstring
    qdf.Close
    
    Set reset = qdf.OpenRecordset
    reset.MoveFirst
    mmail = ("email")
    reset.Close
    DoCmd.SendObject acSendQuery, "pay", acFormatXLS, mmail, , , "here balance", " your balance here", False
    rest.MoveNext
    Wend
    MsgBox "EMAIL PROCESS COMPLETE", vbInformation
    
    mydb.Close
    
    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess would be that scode is not initialized or has an invalid value.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2014
    Posts
    4
    scode is text box I searched in it about page " number" to limit set of records

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah, then it is an invalid value. I'm surprised that VB will allow you to compile code that contains a type mismatch like that.

    Just fix your code so that it matches the working example (be sure to remove the extra puncutation too) and it ought to work fine.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2014
    Posts
    4
    the problem now " syntax error in join operation"
    Code:
    sqlstring = "SELECT payed2.payroll, payed2.type, payed1.page, payed2.amount, personal.namee, personl.email" & _
                " FROM ((payed2 INNER JOIN payed1 ON payed2.page = payed1.page), INNER JOIN personal ON payed2.payroll = personal.payroll)" & _
                " WHERE payed2.payroll = '" & mpayroll & "' And payed2.Page = " & mpage & "" & _
                " ORDER BY payed2.payroll "

    I have 3 tables named personal, payed2, payed1

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please show the CREATE TABLE statements for personal, payed1, and payed2.

    Please show the declaration and the values for your mpayroll and mpage variables.

    There appears to be a SQL syntax error in the JOIN itself, and a VB syntax error in the line that starts with: " WHERE

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    why have parens there at all? Also, all conditions for a table in an explicit join should be kept in the same ON clause, rather than some in the ON and others in the WHERE. Won't really impact you until someone changes INNER to LEFT OUTER or something similar, but will then kill performance and not give correct results.
    Dave

Posting Permissions

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