Concatenate 2 numbers without Access Calculating it

[davidb88]

Hi - I am looking to concatenate two variables, both are numbers, with a hyphen in the middle. Right now I have variables x1 and z1 and I want to create a new variable, b1. I have tried all different approaches and each time Access is taking the difference of the two variables, whereas I just want the text output of the two variables. This is what I have now:

Dim b1 As String
b1 = [z1] & " - " & [x1]

In the end I want it to look like b1=z1-x1

Is there an easy way to get this to work? I have already tried to convert z1 and x1 to strings instead of integers but that does not work. I also have tried to insert text such as "to" in place of the hyphen but then I get an error that it is missing an operator.

Thanks!

 

[Joe4]

I take it that x1 and z1 are actually numeric values. Do they have decimals?

How about something like (for no decimals):

Dim b1 As String
b1 = FORMAT([z1],"0") & " - " & FORMAT([x1],"0")

or (for two decimals)

Dim b1 As String
b1 = FORMAT([z1],"0.00") & " - " & FORMAT([x1],"0.00")

 

[davidb88]

Thanks for your response, Joe. Yes, the two variables are numeric values. I tried your first option because they do not have decimals and it still is outputting the difference between the two variables as if I am subtracting them. I should also note that in my troubleshooting I have displayed the result of the concatenate in a message box and it displays fine but when I output the value of b1 to a table it is subtracting. In terms of the table I am outputting into, I am using a select into statement and the field is a text field. Do you have any other ideas?

Thanks again!

 

[xenou]

How do you set the data type with a select into statement? I didn't think you could do that. I'd prefer to append to an existing table so I can set the data type in advance of the append query. This only means you have to truncate the table first (clear it of any existing records so you "start fresh", as you would with a make-table query).

 

[davidb88]

Sorry, I meant to say I have tried both an insert into statement and a select into statement with the insert into statement inserting the value into a table with a field that has been set to text. The select into statement is inserting the value into a number field. Either way though, it is still computing the difference between the two variables.

 

[Joe4]

Can you post your code that is assigning the value?
It DEFINITELY needs to write it to a Text field. You cannot store an expression in a numeric field.

 

[xenou]

Probably you need to put the string literal in single quotes:'

Sub Foo()
Dim s As String
    s = Format(2, "0") & " - " & Format(1, "0")
    s = "INSERT INTO Table1 (Field1) Values ([B][COLOR="#FF0000"]'[/COLOR][/B]" & s & "[B][COLOR="#FF0000"]'[/COLOR][/B])"
    CurrentDb.Execute s
End Sub

 

[davidb88]

Wow! Thank you, Xenou! The single quotes worked perfectly! This is the final code I used in case anyone else needs something similar in the future:

Dim b1 as string
b1 = Format([z1], "0") & " - " & Format([x1], "0")
strSQL = "INSERT INTO tblFICObandfinal ([FICO band]) VALUES ('" & b1 & "');"
DoCmd.Run SQL (strSQL)