Trim spaces in SQL

[Samantha]

I need to trim two text fields, concatenate them with a slace ("/") in the
middle and then update it back to the table. But I'm getting a Run-time error
'13': Type mismatch,
on the third SQL1 assignment.

Dim db As Database
Dim SQL1 As String
Set db = CurrentDb

SQL1 = "INSERT INTO tblTemp ( Invoice, PO, PartNumber, ShipDate, ShipQty,
UnitPrice ) "
SQL1 = SQL1 & " SELECT Import.Invoice, Import.PO, "
SQL1 = SQL1 & "IIf(IsNull([Import]![Rev]),
Trim([Import]![PartNumber]),Trim([Import]![PartNumber]) & " / " &
Trim([Import]![Rev])) AS PartNumber, "
'error in previous line
SQL1 = SQL1 & "Import.ShipDate, Import.ShipQty, Import.UnitPrice "
SQL1 = SQL1 & " FROM Import"
db.Execute SQL1
db.Close

I'm suspecting there's something wrong with the IIF... statement.
I'm working too late to get this resolved! Any help is very much
appreciated. Thanks in advance

 

[Andreas]

Try the following:

IIf(IsNull([Import]![Rev]),
Trim([Import]![PartNumber]),
Trim([Import]![PartNumber]) & " / " & Trim([Import]![Rev] & ""))
AS PartNumber

Regards,
Andreas

 

[Graham Mandeno]

Hi Samantha

The problem is this bit:
... & " / " & ...
VBA thinks the first " is ending the string that started just before the
"IIf", and is seeing the "/" not as part of the string, but as a division
operator.

To embed a double quote in a VBA string, you must use two in a row, so you
should have:
... & "" / "" & ...

Another trick you can use here to avoid the IIf is that the two operators, &
and +, behave differently between strings and Nulls

Null & "Some string" gives "Some string"
while
Null + "Some string" gives Null

So, your IIf part can be replaced with:

SQL1 = SQL1 & "Trim([Import]![PartNumber]) & ("" / "" +
Trim([Import]![Rev])) AS PartNumber, "