Results 1 to 8 of 8
  1. #1
    coach32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    47

    RunSQL has faltered on me

    I was given some assistance by 'ItsMe' to help get my code running correctly.
    I have a code attached to a check box that when pressed will run a create table query and then export
    that table to an excel spread sheet.


    All was working sweetly until I added another line to the query SQL. (ORDER BY).
    I receive a run-time error '3464': Data type mismatch in criteria expression.
    Code:
    Private Sub Check153_Click()
    Dim strSQL As String
    strSQL = "SELECT tbl_Scores.RAN, tbl_Scores.TEAM, tbl_Scores.DVN, tbl_Scores.FOR, tbl_Scores.RLT, tbl_Scores.AGT INTO 1974 " & _
                   "FROM tbl_Scores " & _
                   "WHERE (((tbl_Scores.RAN)<19) AND ((tbl_Scores.YEAR)=1974) AND ((tbl_Scores.AGE)=10)) " & _
                   "ORDER BY tbl_Scores.RAN, tbl_Scores.TEAM;"
    Debug.Print strSQL
    DoCmd.RunSQL strSQL
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "1974", "C:\Documents\U10_1974-1979.xlsm", True, "_1974"
    End Sub
    When I press Debug the line
    Code:
    DoCmd.RunSQL strSQL
    is highlighted.
    What have I done wrong this time?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    cut and paste the SQL into a query window and see what error you get the errors out of VBA when running code aren't terribly helpful.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Where is the part that creates the table? I believe DoCmd.RunSQL needs an Action type query?
    As rpeare said, put that in the Query Designer SQL window and see what it says. Switch to Design view to see where it is putting things. You could maybe even leave it as a query and in your TransferSpreadsheet change 1974 to this query name and remove the need for a temp table.

  4. #4
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    As stated by the OP, the SQL is an action query (INTO 1974).

    My best guess is that your data type mismatch is an untrapped null somewhere in your where clause.

    Try
    Code:
    WHERE 
        ( 
        ((Nz(tbl_Scores.RAN,0))<19)
        AND 
        ((Nz(tbl_Scores.YEAR,0))=1974) 
        AND 
        ((Nz(tbl_Scores.AGE,0))=10)
        )
    If that allows the query to run then there's a null in your record source causing the issue.

    If not, then I'd double check your data types in the source table to make sure that you're not trying to perform mathematical comparisons on a number stored as text.

    Otherwise, I'd recommend copying the SQL from your immediate window and creating a new query, and pasting the sql into the SQL view of the new query. Run it and see what errors you might get.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can you post the fields in tbl_Scores and their datatypes?

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Deleted - I can't count!
    Last edited by John_G; 08-19-2016 at 01:19 PM. Reason: Delete - content!

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    My guess would be that one or more of tbl_Scores.RAN, tbl_Scores.YEAR, or tbl_Scores.AGE is not numeric data type in the table.

    This is only a hunch, but I suspect that Access is smart enough to make the required conversions if there is no ORDER BY, but as soon as you put the ORDER BY in, it insists that the data types be correct.

  8. #8
    coach32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    47
    Thank-you everyone. It was because I changed a Number datatype to a text datatype.
    How stupid. The error comes up and pretty much says that. I changed it back and all works well again.

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

Similar Threads

  1. help with runsql where statement
    By vicsaccess in forum Programming
    Replies: 13
    Last Post: 11-28-2015, 03:37 PM
  2. DoCmd.RunSQL help!
    By stephenchan433 in forum Access
    Replies: 7
    Last Post: 11-14-2015, 06:35 PM
  3. DoCmd.RunSQL
    By Access_Blaster in forum Programming
    Replies: 6
    Last Post: 07-23-2013, 09:27 PM
  4. RunSQL issue
    By SemiAuto40 in forum Programming
    Replies: 3
    Last Post: 10-21-2011, 09:11 AM
  5. my first DoCmd.RunSQL
    By broecher in forum Programming
    Replies: 4
    Last Post: 11-05-2010, 09:35 PM

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