Format as Text in Append Query

[Rats]

I have some code that creates a dynamic "Append To" query. The problem is that when the code is run it creates the query but generates a Data Type Mismatch error. I have tracked this down to a [clientnumber] field which is formatted as text in both the main database and the one the Append to query is updating.

The client number appears as a criteria and this works for finding the client's record but it will not append it unless I enclose it in quotes. When it's in quotes it works fine.

Can anybody give me a clue as to how I can include, in the code, a formatting command that makes the information inserted into the criteria field appear enclosed in quotes. Or-

Perhaps explain why the problem exists when both fields are formatted the same in both the main table and the Appended To table.

thanks

 

[QTNM]

Post SQL statement here ...

Can you paste your sql statement here .... I'll fix it for you .... I have experienced similar issues.

What data type is your client# in the main table you are appending the new record to ??

QT

 

[mresann]

Look up CStr in Access help

 

[Rats]

Code attached

Here is the code. The portion that is causing the problem is the ClientNo field which is appearing as a number. The relevant fields in the two tables are formatted as text. I think Mresann is on the right track as well but I don't know how I would include Cstr in the SQL. Thanks for everyones help.

Dim qdf As QueryDef
Dim strsql As String
Dim ServerPath As Variant
ServerPath = DLookup("[Link]", "links", "[linkname] = 'PAActivity'")
If IsNull(varserver) Then
MsgBox ("Unknown Path.")
Else
strsql = "INSERT INTO Customers (Advisor, ClientID, C1surname, " & _
" C1firstname, C2Surname, C2firstname, " & _
" stNo, StName, Locality, postcode) " & _
"IN '" & ServerPath & "' " & _
"SELECT advisors, ClientNo, Client1Name1, Client1Name2, Client2Name1, Client2Name2, StreetNo, StreetName, Locality, Postcode " & _
"FROM [Client action]" & _
"WHERE [Client action].ClientNo = " & [Forms]![client action amended]![ClientNo] & ";"

Set qdf = CurrentDb.CreateQueryDef("PA1Link")
qdf.sql = strsql
qdf.Close

 

[Rats]

Any chance of some help with this question please?

 

[mresann]

You need to change the WHERE clause to show the client number as a text field, as you indicated earlier. When you use VBA code, it's sometimes easy to forget to add the text field indicators, which are single or double quotes. As VBA uses double quotes to denote strings, it is much easier to use single quotes.

For clarity, I used the code windows. Change the Where clause from:

"WHERE [Client action].ClientNo = " & [Forms]![client action amended]![ClientNo] & ";"

to

"WHERE [Client action].ClientNo = '" & [Forms]![client action amended]![ClientNo] & "';"