Convert Number to String

[jamesWP]

Hi there, on my form I'm using a drop down filter kind of thing, where you choose a field, then in the next combo box you choose a value, click filter and it then filters it all for you, the problem is, I have different data types, where as

"Name = '" &  combo21 & "'" 'Combo 21 will return a value such as "/dsfads/"

will work

"ID = '" &  combo21 & "'" 'In this instance it will hold a number such as 3

will not. Is there any way to force combo21 to always act as if its holding plain old text?

Thanks,
James Prince

 

[KenHigg]

Can you use the cstr() function?

 

[jamesWP]

Private Sub Label33_Click()
On Error Resume Next
Me.FilterOn = True
Combo27.SetFocus
Me.Filter = Combo25.Value & " = '" & CStr(Combo27.Text) & "'"
'Combo25.Value would be "Part No"
'Combo27.Value would be "001"
End Sub

Doesn't seem to work :S

 

[jamesWP]

Debug.print returns

Part No = '001'

to the immediate window, I just don't see what's wrong with it :S

Size = '12.7/22.0/M6'

Works just fine

 

[KenHigg]

To start with, I think you need to tell your code where these combo boxes are when you refer to them:

Me.Filter = me!Combo25.Value & " = '" & CStr(me!Combo27.Text) & "'"

 

[jamesWP]

When I try applying the filter using mine or your code, it takes absolutely no effect :S

 

[KenHigg]

Maybe:

Private Sub Label33_Click()
On Error Resume Next
Me.FilterOn = True
Combo27.SetFocus
Me.Filter = "'" & me!Combo25.Value & " = '" & CStr(Combo27.Text) & "''"
'Combo25.Value would be "Part No"
'Combo27.Value would be "001"
End Sub

 

[jamesWP]

Mmmm, that one lost me the little functuallity I had to begin with , what characters do you wrap numbers with? I know you wrap strings with ' '.

 

[KenHigg]

Here's where it seems you need to be (as a sample):

me.filter = "myfldName = 'SomeValue'"

So I was trying to put quotes around everthing...

 

[jamesWP]

mmmm, this has stumped me =/

 

[KenHigg]

Ok. don't despair

Does it work when you do this:

Me.Filter = "[Part No] = '001'"

???

 

[jamesWP]

Yup , Surpised I forgot to try that in the first place :$.

 

[KenHigg]

OK, now I'm a little confused. I was just trying to start from scratch to help fix your problem but after your last response it appears as though the problem has been resolved. True or false?

 

[jamesWP]

Nope, only works if I hard code the values as you suggested, trying to grab them from the combobox doesn't work if it's a number .

 

[Bat17]

so what does
debug.print Me.Filter = "'" & me!Combo25.Value & " = '" & CStr(Combo27.Text) & "''" return?

Peter

 

[KenHigg]

Ok - Try something like:

dim MyFilterString as string
MyFilterString = me!combo25 & "'" & me!combo27 & "'"
me.filter = MyFilterString

???

 

[jamesWP]

debug.print Me.Filter = "'" & me!Combo25.Value & " = '" & CStr(Combo27.Text) & "''"
returns
False

 

[jamesWP]

Private Sub Label33_Click()
On Error goto err_trap
Me.FilterOn = True
Dim MyFilterString As String
Combo27.SetFocus
MyFilterString = Combo25.Value & " = '" & CStr(Combo27.Text) & "'"
Me.Filter = MyFilterString
err_trap:
debug.print err.description
End Sub

After adding the error trapping, I was able to discover what the error is when the combo box shows a number,

Syntax error (missing operator) in query expression 'Part No = '012''.

 

[jamesWP]

Yay fixed it!

Thanks for the help Ken, if anyone else is interested the correct code was

Private Sub Label33_Click()
On Error GoTo err_trap
Me.FilterOn = True
Dim MyFilterString As String
Combo27.SetFocus
MyFilterString = "[" & Combo25.Value & "] = '" & CStr(Combo27.Text) & "'"
Me.Filter = MyFilterString
err_trap:
Debug.Print Err.Description
End Sub

 

[KenHigg]

Cool... Another reason not to use spaces in fld or object names