Results 1 to 5 of 5
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Error data type mismatch in criteria expression

    I'm running the following function to



    check whether student data has been updated todayand if it hasn't


    create a temporary table,
    upload data from excel file (all text fields)
    change the StudentYeargroup field to integer type
    fill the studentyeargroup field with integers

    Everything works fine until the line indicated and I cant work out why

    Any ideas????


    Code:
    Function tempReview(tblname As String, filename As String)
    On Error GoTo Tempreview_err
    
    Dim mysql As String
    Dim db As Database
    Set db = CurrentDb
    Dim mystring As String
    Dim varX As Variant
    varX = DLookup("[update_date]", "tbl_update", _
        "[update_date] = #" & Date & "#")
    Debug.Print varX
    If IsNull(varX) Then
    'complete download
    'delete the old temporary table,import data and change the yeargroup to a integer field
    
    db.TableDefs.Delete tblname
    DoCmd.TransferSpreadsheet acImport, 10, tblname, filename, True, ""
    mysql = "ALTER TABLE " & tblname & " ALTER COLUMN [StudentYearGroup] int"
    
    db.Execute mysql, dbFailOnError ' fails on this line. The MySQL line works here if run as a query
    
    
    'delete the data in tbl_students
    mysql = "DELETE  * FROM tbl_students "
    db.Execute mysql, dbFailOnError
    
    'insert data from tbl_students_tmp into tbl_students altering "year 8" to "8" as a number
    mysql = "INSERT INTO tbl_students ( LastName, FirstName, UPN, Mathematics_Group, StudentYearGroup )" _
     & " SELECT tbl_students_tmp.LastName, tbl_students_tmp.FirstName, tbl_students_tmp.UPN, tbl_students_tmp.Mathematics_Group, Right([yrgroup],1) AS StudentYearGroup" _
     & " FROM tbl_students_tmp;"
    'Debug.Print mysql
    
    db.Execute mysql, dbFailOnError
    db.Execute "UPDATE tbl_update SET tbl_update.update_date = Date();"
    mystring = "updated"
    Else
    'leave as it is
    mystring = "not updated"
    End If
     
    Debug.Print mystring
     
     
     
     
     
     
     
    tempreview_Exit:
        Exit Function
        
    Tempreview_err:
    MsgBox Error$
    Resume tempreview_Exit
    End Function

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    all of this can be done without programming.
    you can put all this in a macro with queries, thus saving coding time.

    but, i dont see the indicated line.

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    I've hightlighted the line now

    Any advice on an easier approach would be taken

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    To add data to a table from Excel:
    - TransferSpreadsheet into temp table
    - delete records from tbl_students
    - append data to tbl_students, the StudentYearGroup is already defined as an integer on the table, the query can change data types as it runs, such as CInt(Right([yrgroup],1))

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Quote Originally Posted by aytee111 View Post
    To add data to a table from Excel:
    - TransferSpreadsheet into temp table
    - delete records from tbl_students
    - append data to tbl_students, the StudentYearGroup is already defined as an integer on the table, the query can change data types as it runs, such as CInt(Right([yrgroup],1))
    Thanks for the feedback so far

    Aytee that simple change sorted it straight away

    Ranman, when I get some more free time on the project, I'll look into doing it without code (presumably by having the queries set up already and just running them from a macro)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-21-2016, 09:43 PM
  2. Replies: 2
    Last Post: 08-24-2015, 09:14 PM
  3. Replies: 3
    Last Post: 07-21-2014, 09:33 AM
  4. Replies: 1
    Last Post: 05-02-2013, 01:29 AM
  5. Replies: 1
    Last Post: 05-11-2012, 10:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums