TYPE CONVERSION FAILURE WITH TABLE UPDATE QUERY

Question

Hi,

I feel like I'm so close to solving a problem that has vexed me all day. My latest error when I attempt to update a table from an unbound form is a "Type Conversion Error". Here is the code:

DoCmd.RunSQL ("UPDATE [Participant Registration List] SET [Participant Registration List].[ACADEMY COHORT NUMBER]=' & Me.[ACADEMY COHORT NUMBER] & ', " & _

"[Participant Registration List].[COHORT START DATE]=' & Me.[COHORT START DATE] & ', " & _

"[Participant Registration List].[AGENCY NAMES]= '" & Me.[AGENCY NAMES] & "', " & _

"[Participant Registration List].[SEATS ALLOCATED]=' & Me.[SEATS ALLOCATED] & ', " & _

"[Participant Registration List].[EMPLOYEE ID NUMBER]= '" & Me.[EMPLOYEE ID NUMBER] & "', " & _

"[Participant Registration List].[EMPLOYEE LAST NAME]= '" & Me.[EMPLOYEE LAST NAME] & "', " & _

"[Participant Registration List].[EMPLOYEE FIRST NAME]= '" & Me.[EMPLOYEE FIRST NAME] & "', " & _

"[Participant Registration List].[EMPLOYEE HR TITLE]= '" & Me.[EMPLOYEE HR TITLE] & "', " & _

"[Participant Registration List].[EMAIL ADDRESS]= '" & Me.[EMAIL ADDRESS] & "', " & _

"[Participant Registration List].[PHONE NUMBER]= '" & Me.[PHONE NUMBER] & "', " & _

"[Participant Registration List].[LOS CITY]= '" & Me.[LOS CITY] & "', " & _

"[Participant Registration List].[TIME IN HR]= '" & Me.[TIME IN HR] & "', " & _

"[Participant Registration List].[GROUP]= '" & Me.[GROUP] & "', " & _

"[Participant Registration List].[AGENCY ABBREV]= '" & Me.[AGENCY ABBREV] & "'")

I think I understand that the type conversation error means that the code as written does not distinguish a text field from a number field from a datetime field. All three types are referred to in the above ("ACADEMY COHORT NUMBER" = NUMBER FIELD, "COHORT START DATE" = DATETIME FIELD, "SEATS ALLOCATED" = NUMBER FIELD, ALL OTHER FIELDS ARE 'SHORT TEXT' FIELDS) . . . but I don't know how to distinguish them. Please Please help.

I'd be so grateful to get some sleep tonight :)

Answer 1

Always store the SQL in a string first so you can examine it with debug.print:

Dim strSQL as String

strSQL = "UPDATE [Participant Registration List] SET [Participant Registration List].[ACADEMY COHORT NUMBER]=' & Me.[ACADEMY COHORT NUMBER] & ', " & _

"[Participant Registration List].[COHORT START DATE]=' & Me.[COHORT START DATE] & ', " & _

"[Participant Registration List].[AGENCY NAMES]= '" & Me.[AGENCY NAMES] & "', " & _

"[Participant Registration List].[SEATS ALLOCATED]=' & Me.[SEATS ALLOCATED] & ', " & _

"[Participant Registration List].[EMPLOYEE ID NUMBER]= '" & Me.[EMPLOYEE ID NUMBER] & "', " & _

"[Participant Registration List].[EMPLOYEE LAST NAME]= '" & Me.[EMPLOYEE LAST NAME] & "', " & _

"[Participant Registration List].[EMPLOYEE FIRST NAME]= '" & Me.[EMPLOYEE FIRST NAME] & "', " & _

"[Participant Registration List].[EMPLOYEE HR TITLE]= '" & Me.[EMPLOYEE HR TITLE] & "', " & _

"[Participant Registration List].[EMAIL ADDRESS]= '" & Me.[EMAIL ADDRESS] & "', " & _

"[Participant Registration List].[PHONE NUMBER]= '" & Me.[PHONE NUMBER] & "', " & _

"[Participant Registration List].[LOS CITY]= '" & Me.[LOS CITY] & "', " & _

"[Participant Registration List].[TIME IN HR]= '" & Me.[TIME IN HR] & "', " & _

"[Participant Registration List].[GROUP]= '" & Me.[GROUP] & "', " & _

"[Participant Registration List].[AGENCY ABBREV]= '" & Me.[AGENCY ABBREV] & "'"

Debug.Print strSQL

As a start

strSQL = "UPDATE [Participant Registration List] SET [Participant Registration List].[ACADEMY COHORT NUMBER]=" & Me.[ACADEMY COHORT NUMBER] & ", " & _

"[Participant Registration List].[COHORT START DATE]=#" & Me.[COHORT START DATE] & "#, " & _

Answer 2

"Now, though, when I click the button nothing happens. Before I would get a message box that told me I was about to update # records before I would get the error message. Now, nothing at all happens when I push the button. Should I replace the DoCmd RunSQL? Did I miss something in following your "As a start" guide?"

Hi toshitakae,

You should have minimally seen your SQL statement printed to the Immediate Window (open with <Ctrl><G>).

To build on Duane's suggestion, try adding a few lines of code shown below in maroon-colored font.

On Error GoTo ProcError

Dim db As DAO.Database

Dim strSQL As String

Set db = CurrentDB()

strSQL = "UPDATE [Participant Registration List] SET [Participant Registration List].[ACADEMY COHORT NUMBER]=" & Me.[ACADEMY COHORT NUMBER] & ", " & _

"[Participant Registration List].[COHORT START DATE]=#" & Me.[COHORT START DATE] & "#, " & _

"[Participant Registration List].[AGENCY NAMES]=" & Me.[AGENCY NAMES] & ", " & _

"[Participant Registration List].[SEATS ALLOCATED]=" & Me.[SEATS ALLOCATED] & ", " & _

"[Participant Registration List].[EMPLOYEE ID NUMBER]=" & Me.[EMPLOYEE ID NUMBER] & ", " & _

"[Participant Registration List].[EMPLOYEE LAST NAME]=" & Me.[EMPLOYEE LAST NAME] & ", " & _

"[Participant Registration List].[EMPLOYEE FIRST NAME]=" & Me.[EMPLOYEE FIRST NAME] & ", " & _

"[Participant Registration List].[EMPLOYEE HR TITLE]=" & Me.[EMPLOYEE HR TITLE] & ", " & _

"[Participant Registration List].[EMAIL ADDRESS]=" & Me.[EMAIL ADDRESS] & ", " & _

"[Participant Registration List].[PHONE NUMBER]=" & Me.[PHONE NUMBER] & ", " & _

"[Participant Registration List].[LOS CITY]=" & Me.[LOS CITY] & ", " & _

"[Participant Registration List].[TIME IN HR]=" & Me.[TIME IN HR] & ", " & _

"[Participant Registration List].[GROUP]=" & Me.[GROUP] & ", " & _

"[Participant Registration List].[AGENCY ABBREV]=" & Me.[AGENCY ABBREV] & ")"

Debug.Print strSQL

db.execute strSQL, dbFailOnError 'Requires a checked reference to the DAO Object Library, when you include the optional dbFailOnError parameter

MsgBox db.recordsaffected & " records updated in Participant Registration List table.", vbInformation, "Records Successfully Updated..."

ExitProc:

 Set db = Nothing

Exit Sub 

ProcError:

MsgBox "Error " & Err.Number & ": " & Err.Description, \_ 

      vbCritical, "Error in procedure *ProcedureName*..." 

Resume ExitProc 

End Sub

Substitute ProcedureName above with the actual name of your procedure.

In my opinion, the db.execute method is much preferred over DoCmd.RunSQL. It will not produce the message "You are about to update XXX records".

Answer 3

Press Ctrl+G to view the contents of the immediate/debug window. Paste the SQL into an empty SQL window of a query. If you can't figure out what's wrong, paste the results in the debug window in a reply here.

You will need to add back the line to run or execute the SQL statement.

Answer 4

Hi. I took a second look at your instructions, Duane and decided that you were trying to tell me how NUMBER fields and DATETIME fields should be identified. So, I updated the code as follows:

Private Sub Command131_Click()

Dim strSQL As String

strSQL = "UPDATE [Participant Registration List] SET [Participant Registration List].[ACADEMY COHORT NUMBER]=" & Me.[ACADEMY COHORT NUMBER] & ", " & _

"[Participant Registration List].[COHORT START DATE]= #" & Me.[COHORT START DATE] & "#, " & _

"[Participant Registration List].[AGENCY NAMES]= '" & Me.[AGENCY NAMES] & "', " & _

"[Participant Registration List].[SEATS ALLOCATED]= " & Me.[SEATS ALLOCATED] & ", " & _

"[Participant Registration List].[EMPLOYEE ID NUMBER]= '" & Me.[EMPLOYEE ID NUMBER] & "', " & _

"[Participant Registration List].[EMPLOYEE LAST NAME]= '" & Me.[EMPLOYEE LAST NAME] & "', " & _

"[Participant Registration List].[EMPLOYEE FIRST NAME]= '" & Me.[EMPLOYEE FIRST NAME] & "', " & _

"[Participant Registration List].[EMPLOYEE HR TITLE]= '" & Me.[EMPLOYEE HR TITLE] & "', " & _

"[Participant Registration List].[EMAIL ADDRESS]= '" & Me.[EMAIL ADDRESS] & "', " & _

"[Participant Registration List].[PHONE NUMBER]= '" & Me.[PHONE NUMBER] & "', " & _

"[Participant Registration List].[LOS CITY]= '" & Me.[LOS CITY] & "', " & _

"[Participant Registration List].[TIME IN HR]= '" & Me.[TIME IN HR] & "', " & _

"[Participant Registration List].[GROUP]= '" & Me.[GROUP] & "', " & _

"[Participant Registration List].[AGENCY ABBREV]= '" & Me.[AGENCY ABBREV] & "'"

Debug.Print strSQL

End Sub

Unfortunately, when I click the button to update the table, nothing at all happens. Earlier, a message box popped up to let me know how many records I was about to update - before I would get the Type Conversion message box. Now, nothing happens at all. Did I misunderstand what you wrote? Should I replace the DoCmd RunSQL? Did I at least understand what you were highlighting were issues to address? Please help if you can.