Data Type Mismatch

[MHamid]

Hello,

I am getting a data type mismatch in my code below.

CurrentDb.Execute "INSERT INTO QAMaster([Cycle Month],[Report Type],[Date Reviewed],[Reviewer Type],[Reviewer],[Reviewer Report Area],[Main Section],[Topic Section],[Ownership],[Count],[Priority],[QA Update],[L1],[L2],[L3],[Exception],[Notes],[Outliers])" & _
    "VALUES ('" & Me.CycleMonth & "', '" & Me.ReportType & "', '" & Me.DateReviewed & "', '" & Me.ReviewerType & "', '" & Me.Reviewer & "', '" & Me.ReviewerReportArea & "', '" & Me.MainSection & "', '" & Me.TopicSection & "', '" & Me.Ownership & "', '" & Me.Count & "', '" & Me.Priority & "', '" & Me.QAUpdate & "', '" & Me.L1 & "', '" & Me.L2 & "', '" & Me.L3 & "', '" & Me.Exceptions & "', '" & Me.Notes & "', '" & Me.Outliers & "');", dbFailOnError

All values except for Date Reviewed, Count, Notes, QA Update and Exceptions are combo boxes. Date Reviewed is a date option ... count and notes is a text box, and QA Update and Exceptions are check boxes.

What am I missing in my code?

Thank you

 

[xenou]

You really have to look at your SQL to debug something like this, then try to think about data types for the field you are inserting (what should they be, and do they match what your SQL does):

SQL_Text = "INSERT INTO QAMaster([Cycle Month],[Report Type],[Date Reviewed],[Reviewer Type],[Reviewer],[Reviewer Report Area],[Main Section],[Topic Section],[Ownership],[Count],[Priority],[QA Update],[L1],[L2],[L3],[Exception],[Notes],[Outliers])" & _
    "VALUES ('" & Me.CycleMonth & "', '" & Me.ReportType & "', '" & Me.DateReviewed & "', '" & Me.ReviewerType & "', '" & Me.Reviewer & "', '" & Me.ReviewerReportArea & "', '" & Me.MainSection & "', '" & Me.TopicSection & "', '" & Me.Ownership & "', '" & Me.Count & "', '" & Me.Priority & "', '" & Me.QAUpdate & "', '" & Me.L1 & "', '" & Me.L2 & "', '" & Me.L3 & "', '" & Me.Exceptions & "', '" & Me.Notes & "', '" & Me.Outliers & "');"

debug.print SQL_Text

What is the SQL you get?

 

[MHamid]

Hello,

I am not too familiar with the SQL View.
Can you please explain what I need to do?

Thank you

 

[Norie]

If you run the code xenou posted then you should see the SQL statement being generated in the Immediate Window (CTRL+G).

You could then copy that statement, open a new query in the database, goto SQL View, paste the query and then try and execute it.

 

[Micron]

When you do as suggested, sometimes when you run that temporary query while in sql view, the offending part gets highlighted. Maybe not in this case, as I suspect there's more than one field that will generate the error. Everything is wrapped in single quotes yet there appears to be dates and numbers involved. Although I agree that these fields could have been designed as text and not dates/numbers, they look suspect.
'" & Me.DateReviewed & "
'" & Me.Count & "'
Then there's a bunch that don't provide any clue at all.

 

[MHamid]

Hello,

I tried using xenou's code and I did not see the SQL code generated in the immediate window. Am I supposed to be inserting this code somewhere else instead of On Click (Add Record button)?

Private Sub Add_Record_Click()
SQL_Text = "INSERT INTO QAMaster([Cycle Month],[Report Type],[Date Reviewed],[Reviewer Type],[Reviewer],[Reviewer Report Area],[Main Section],[Topic Section],[Ownership],[Count],[Priority],[QA Update],[L1],[L2],[L3],[Exception],[Notes],[Outliers])" & _
    "VALUES (" & Me.CycleMonth & ", " & Me.ReportType & ", " & Me.DateReviewed & ", " & Me.ReviewerType & ", " & Me.Reviewer & ", " & Me.ReviewerReportArea & ", " & Me.MainSection & ", " & Me.TopicSection & ", " & Me.Ownership & ", " & Me.Count & ", " & Me.PriorityLevel & ", '" & Me.QAUpdate & "', " & Me.L1 & ", " & Me.L2 & ", " & Me.L3 & ", '" & Me.Exceptions & "', '" & Me.Notes & "', " & Me.Outliers & ");"
Debug.Print SQL_Text

End Sub

In the QAMaster table, the Data Type for Date Reviewed is Date/Time, Count is Number, and Notes is Memo. All other columns have Data Types of Text.
When I looked at the Format in the form for each combo box I have, I see that in properties, the Format is blank but the options are for numbers for all combo boxes I have listed as Text in the Table. Is the format for the combo boxes supposed to be in text?


Thank you

 

[Micron]

You know where to look in the vb editor, right? I would post a picture, but it seems this site doesn't allow it.
If you're in the editor and don't see it at the bottom, try Ctrl+G or go to the View menu.

 

[xenou]

This page has a visual explanation of the same technique for using debug.print:
https://www.techrepublic.com/blog/f...bugging-a-routine-in-the-visual-basic-editor/

Your sql construction is too long. You really have to view the actual sql command.

 

[MHamid]

Hello,

I am in the vb editor and I did press Ctrl+G. The Immediate window did pop up at the bottom but it's blank.
When I run the code, I still get data type mismatch error message. However, the cursor goes to the ".L3" is highlighted with the blue cursor ('" & Me.L3 & "'). I believe that this might be the error, but I'm still trying to figure out why this would be causing the error.

 

[MHamid]

Hello,

I tested the SQL code one piece at a time and the below items appeared in the Immediate window.

INSERT INTO QAMaster(Cycle Month)VALUES (February);
INSERT INTO QAMaster([Report Type)VALUES (CBNA);
INSERT INTO QAMaster([Date Reviewed])VALUES (11/28/2017);
INSERT INTO QAMaster( [Reviewer Type])VALUES (Mgr Feedback);
INSERT INTO QAMaster( [Reviewer)VALUES (Nick Woody);
INSERT INTO QAMaster( [Reviewer Report Area])VALUES Internal Audit,);
INSERT INTO QAMaster([Main Section])VALUES (Front_SOX_MOR);
INSERT INTO QAMaster([Topic Section])VALUES (Front - Reg - Exam Summary);
INSERT INTO QAMaster([Ownership])VALUES (ACM-IA);
INSERT INTO QAMaster([Count])VALUES (50);
INSERT INTO QAMaster([Priority])VALUES (P3);
INSERT INTO QAMaster([QA Update])VALUES ('-1');
INSERT INTO QAMaster([L1])VALUES (Consistency);
INSERT INTO QAMaster([L2])VALUES (Prior_Current_Comparison);
INSERT INTO QAMaster([L3])VALUES (Data Source Change);
INSERT INTO QAMaster([Exception])VALUES ('-1');
INSERT INTO QAMaster([Notes])VALUES ('Testing');
INSERT INTO QAMaster( [Outliers])VALUES (Pending QA);