I am trying to update a text field to the following but I keep getting a type mismatch error. I had this in a macro and it ran fine. Once I put it into VBA code window I started getting the type mismatch error. Please help.

DoCmd.RunSQL "UPDATE tbl_Mas_md_temp SET tbl_Mas_md_temp.[Delete Date] = IIf(tbl_Mas_md_temp.[Delete Date]=' & 00000000 & ',"", " & _
"IIf(tbl_Mas_md_temp.[Delete Date]= "",,Format(Mid(tbl_Mas_md_temp.[Delete Date],1,2) & " / " & Mid( tbl_Mas_md_temp.[Delete Date],3,2) & " / " & Mid(tbl_Mas_md_temp.[Delete Date],5,4),mm/dd/yyyy)))"

To read the code...I am trying to update a text field to the following but I keep getting a type mismatch error. I had this in a macro and it ran fine. Once I put it into VBA code window I started getting the type mismatch error. Please help.

DoCmd.RunSQL "UPDATE tbl_Mas_md_temp SET

tbl_Mas_md_temp.[Delete Date] =

IIf(tbl_Mas_md_temp.[Delete Date]=' & 00000000 & ',"", " & _

"IIf(tbl_Mas_md_temp.[Delete Date]=

"",,Format(Mid(tbl_Mas_md_temp.[Delete Date],1,2) & " / " &

Mid( tbl_Mas_md_temp.[Delete Date],3,2) & " / " &

Mid(tbl_Mas_md_temp.[Delete Date],5,4),mm/dd/yyyy)))"

DoCmd.RunSQL "UPDATE tbl_Mas_md_temp SET

tbl_Mas_md_temp.[Delete Date] =

IIf(tbl_Mas_md_temp.[Delete Date]=' & 00000000 & ',"", " & _

"IIf(tbl_Mas_md_temp.[Delete Date]=

"",,Format(Mid(tbl_Mas_md_temp.[Delete Date],1,2) & " / " &

Mid( tbl_Mas_md_temp.[Delete Date],3,2) & " / " &

Mid(tbl_Mas_md_temp.[Delete Date],5,4),mm/dd/yyyy)))"Marked some areas in red that may be danger. Not sure though. Personally, an explanation in words of this would be better to read than the actual code...

I think your expression needs some revision with regard to the placement and use of quotes/double-quotes.

usually when you are trying to build a string you use double quotes (") to enclose actual text string sections, and if you want to put quotes inside that section you use single quotes (').
eg
strSQL = "Update mytablename Set [MyDateField]=#" & Date() & "# Where [MyCriteriaField]='" & Me.StringInATextControlOnThisForm & "';"

Notice that each double quote surrounds static text, and anytime the code is trying to retrieve dynamic information from a form or a function, or performing concatenations, these parts are outside of the sections enclosed by the pairs of double-quotes.

Now, you can reverse the convention but you do have to be consistent in your approach.

So in your case, the first part of the sql (between the first pair of double quotes) will look like

UPDATE tbl_Mas_md_temp SET tbl_Mas_md_temp.[Delete Date] = IIf(tbl_Mas_md_temp.[Delete Date]=' & 00000000 & ',

notice you have &'s inside the string?

The next bit would be ", " but there's not a & to concatenate these bits? And so on.

But beyond all that, it looks as if you're doing all this to try to input dates as text that still look like dates. The question is why? Why not just make the field a date datatype and be done with it. Simply use the CStr() and CDate() functions to convert between text and date any time you need one or the other.

I am trying to insert "/ " into a text string containing a date (ex. 01012007). The below statement is working somewhat after I removed the " and replaced them with '. But, the statement now is only returning a portion of the year (ex. 01/01/20). It won't let me use CDate until I have inserted the "/ " into the string. Any suggestions on why it is returning only a portion of the year?

I am importing (fixed width) this data from a text file, and the field containing the date also contains
"00000000" if there is no date present. This causes errors during the import, so I am importing the field as text, removing the zero's, and converting the date strings to actual dates.



DoCmd.RunSQL "UPDATE " & tbl_import & " SET " &
tbl_import & ".[Delete Date] =

IIf(" & tbl_import & ".[Delete Date]='00000000',''," & _

"Left(" & tbl_import & ".[Delete Date],2) + '/' +

Mid(" & tbl_import & ".[Delete Date],3,2) + '/' + " & _

"Right(" & tbl_import & ".[Delete Date],4))"

Try this. Add a new date/time datatype field to your import table. Call it [ConvertedDate]. Then run the following SQL

DoCmd.RunSQL "UPDATE tbl_import SET tbl_import.[ConvertedDate] = IIf(tbl_import.[Delete Date]='00000000',Null,DateSerial(CInt(Right(tbl_import.[Delete Date],4)),CInt(Mid(tbl_import.[Delete Date],3,2)),CInt(Left(tbl_import.[Delete Date],2))))"

This saves you having to re-import the data when you overwrite your text [Delete Date] field.

I found the issue. The field size was set to 8 characters on import and when modifying the format to a date it increased to 10 characters which excluded the last two numbers.