Results 1 to 9 of 9
  1. #1
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98

    Data type mismatch when run through form, but not through sql?

    I have a form that runs an append query with the specific date range the user chooses. When this append query is run through the form, I get a data type mismatch error, but when I run the append query directly through sql, the information appends just fine with no errors? I have done all the research I can to find where the issue is, but after checking all values and data types, I cannot find any good reason why I am getting this error.

  2. #2
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    UPDATE: So I ran my query in the form through
    Code:
    docmd.runsql
    rather than
    Code:
    docmd.runquery
    and I still get the same error? It only works if I run the query separate from the form.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Show the SQL statement.

    Or provide db for analysis. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Quote Originally Posted by June7 View Post
    Show the SQL statement.

    Or provide db for analysis. Follow instructions at bottom of my post.
    Here is what I have in VBA:

    Code:
    Private Sub Command46_Click()    DoCmd.SetWarnings False
        SQL = "INSERT INTO PriorSales_Append_Table ( SITE, F_SALESCR, OBJID, STATUS, LOGDATE, CC, TOTAL, ACCTNUM )" & _
                "SELECT unSCRsales.SITE, unSCRsales.F_SALESCR, unSCRsales.OBJID, unSCRsales.STATUS, unSCRsales.LOGDATE, unSCRsales.CC, unSCRsales.TOTAL, unSCRsales.ACCTNUM" & " " & _
                "FROM unSCRsales" & " " & _
                "WHERE (((unSCRsales.LOGDATE)>=[Forms]![Invoices]![fromdate] And (unSCRsales.LOGDATE)<=[Forms]![Invoices]![todate]))"
        DoCmd.RunSQL SQL
    End Sub
    Here is my sql for the query (not run through VBA):

    Code:
    INSERT INTO PriorSales_Append_Table ( SITE, F_SALESCR, OBJID, STATUS, LOGDATE, CC, TOTAL, ACCTNUM )SELECT unSCRsales.SITE, unSCRsales.F_SALESCR, unSCRsales.OBJID, unSCRsales.STATUS, unSCRsales.LOGDATE, unSCRsales.CC, unSCRsales.TOTAL, unSCRsales.ACCTNUM
    FROM unSCRsales
    WHERE (((unSCRsales.LOGDATE)>=[Forms]![Invoices]![fromdate] And (unSCRsales.LOGDATE)<=[Forms]![Invoices]![todate]));
    Run from SQL/Directly:

    Click image for larger version. 

Name:	cap.png 
Views:	16 
Size:	42.6 KB 
ID:	33241
    Run through the form:
    Click image for larger version. 

Name:	cap2.jpg 
Views:	18 
Size:	136.8 KB 
ID:	33239
    Attached Thumbnails Attached Thumbnails cap.png  

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    When you use SQL strings in VBA, you need to add appropriate delimiters - in this case for date fields in WHERE clause

    I've added additional brackets using standard Access bracketing & switched warnings back on - IMPORTANT!

    The easiest way to check for SQL error is to use Debug.Print before running the SQL.
    You can then paste the output back into the query designer & test it

    Code:
    Private Sub Command46_Click()    
        DoCmd.SetWarnings False
        SQL = "INSERT INTO PriorSales_Append_Table ( SITE, F_SALESCR, OBJID, STATUS, LOGDATE, CC, TOTAL, ACCTNUM )" & _
                "SELECT unSCRsales.SITE, unSCRsales.F_SALESCR, unSCRsales.OBJID, unSCRsales.STATUS, unSCRsales.LOGDATE, unSCRsales.CC, unSCRsales.TOTAL, unSCRsales.ACCTNUM" & " " & _
                "FROM unSCRsales" & " " & _
                "WHERE (((unSCRsales.LOGDATE)>= #" & [Forms]![Invoices]![fromdate]) & "# And  ((unSCRsales.LOGDATE)<= #" & [Forms]![Invoices]![todate] & "#))"
        Debug.Print SQL
        DoCmd.RunSQL SQL
        DoCmd.SetWarnings True
    End Sub
    or this simpler version:
    Code:
    Private Sub Command46_Click()    
        DoCmd.SetWarnings False
        SQL = "INSERT INTO PriorSales_Append_Table ( SITE, F_SALESCR, OBJID, STATUS, LOGDATE, CC, TOTAL, ACCTNUM )" & _
                "SELECT unSCRsales.SITE, unSCRsales.F_SALESCR, unSCRsales.OBJID, unSCRsales.STATUS, unSCRsales.LOGDATE, unSCRsales.CC, unSCRsales.TOTAL, unSCRsales.ACCTNUM" & " " & _
                "FROM unSCRsales" & " " & _
                "WHERE (((unSCRsales.LOGDATE) Between #" & [Forms]![Invoices]![fromdate] & "# And  #" & [Forms]![Invoices]![todate] & "#))"
        Debug.Print SQL
        DoCmd.RunSQL SQL
        DoCmd.SetWarnings True
    End Sub
    I recommend you read up on the different types of delimiter: text, number, date and how/when to use them

    P.S. I think you are overdoing your screenshots - just show the essential items, cropping where necessary
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you cannot run sql which uses paramaters references from vba, you have to assing them by direct reference

    try

    Code:
    Private Sub Command46_Click()    DoCmd.SetWarnings False
        SQL = "INSERT INTO PriorSales_Append_Table ( SITE, F_SALESCR, OBJID, STATUS, LOGDATE, CC, TOTAL, ACCTNUM )" & _
                "SELECT unSCRsales.SITE, unSCRsales.F_SALESCR, unSCRsales.OBJID, unSCRsales.STATUS, unSCRsales.LOGDATE, unSCRsales.CC, unSCRsales.TOTAL, unSCRsales.ACCTNUM" & " " & _
                "FROM unSCRsales" & " " & _
                "WHERE (((unSCRsales.LOGDATE)>=" & [Forms]![Invoices]![fromdate] & " And (unSCRsales.LOGDATE)<= " & [Forms]![Invoices]![todate] & "))"
        DoCmd.RunSQL SQL
    End Sub
    if the code is being run from your invoices form them you refer to the controls directly, not via the forms collection

    Code:
    Private Sub Command46_Click()    DoCmd.SetWarnings False
        SQL = "INSERT INTO PriorSales_Append_Table ( SITE, F_SALESCR, OBJID, STATUS, LOGDATE, CC, TOTAL, ACCTNUM )" & _
                "SELECT unSCRsales.SITE, unSCRsales.F_SALESCR, unSCRsales.OBJID, unSCRsales.STATUS, unSCRsales.LOGDATE, unSCRsales.CC, unSCRsales.TOTAL, unSCRsales.ACCTNUM" & " " & _
                "FROM unSCRsales" & " " & _
                "WHERE (((unSCRsales.LOGDATE)>=" & [fromdate] & " And (unSCRsales.LOGDATE)<= " & [todate] & "))"
        DoCmd.RunSQL SQL
    End Sub
    I note you are not using # to tell sql that the string between the # chars is to be treated as a date, and also assuming you are in the US since this is the format required by SQL

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "SQL" is a reserved word in Access and shouldn't be used for object names.
    See http://allenbrowne.com/AppIssueBadWord.html#S


    I would use
    Code:
    CurrentDb.Execute sSQL, dbFailOnError
    instead of
    Code:
        DoCmd.SetWarnings False
        DoCmd.RunSQL SQL
        DoCmd.SetWarnings True

  8. #8
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    I apologize to everyone for such a late reply. I really appreciate everyone's response. I went ahead and did some more debugging based on the examples and info provided. I want to apologize again for such an inconvenience, but I realized that my value for fromdate and todate were the wrong textboxes being used for the query. The correct textboxes were titled sfromdate and stodate. So when I was running the query through sql or the form, it was trying to populate an empty textbox (fromdate and todate: not sfromdate and stodate). The good news is I will be starting to use debug.print more often in my sql code if I use VBA because I would have caught that long ago if I had begun using debug.print. So thank you much for all the help everyone. NOTE: I was really lazy with the screenshots because I was getting out of work and I rushed to get those screenshots in. As for the "sql" object, I just used that a quick reference towards posting my sql code, but still good to know not to use SQL directly as an object/variable. In regards to proper use of delimiters, that will help me out A LOT. I never thought to use delimiters differently when using VBA instead of SQL. So even though I solved my own problem due to my own mistake, I still learned more than I hoped. Thanks a bunch again! P.S. I will be marking this thread as solved.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Consider using camel case for naming convention - easier to read sToDate than stodate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. data type mismatch
    By ottoc in forum Queries
    Replies: 1
    Last Post: 12-02-2014, 09:42 AM
  2. Data Type Mismatch
    By Mtyetti2 in forum Queries
    Replies: 3
    Last Post: 10-23-2013, 11:48 AM
  3. Data Type Mismatch in SQL
    By Phred in forum Queries
    Replies: 2
    Last Post: 01-04-2012, 03:40 PM
  4. Data Type Mismatch
    By timmy in forum Programming
    Replies: 9
    Last Post: 04-12-2011, 03:48 AM
  5. data type mismatch
    By jgelpi16 in forum Programming
    Replies: 5
    Last Post: 08-02-2010, 04:15 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