Cause number to act as a string?

[chris-uk-lad]

Hi,

I have a form that searches a table and retrieves all the values in a column, based on an SQL query. At the moment it is only retrieving values that are all character related, not integers.

Such as in my column i have
ATHERTON
201
It wont retrieve 201.

My search button contains the code:

    If IsNull(Name) Then
        strName = "NAME LIKE '*'"
    Else
        strName = "NAME LIKE '" & Name & "*'"
    End If
 
    strSQL = "SELECT * FROM tblMember WHERE " & strName & " ORDER BY NAME"
 
    Form_frmSearchResults.SetSearchResults strSQL
    Form_frmSearchResults.Refresh

 

[KenHigg]

Is it safe to assume in your example that it fails when the null condition is met?

 

[DCrake]

The first task you should do is to rename the field NAME to something else. This is a reserved word and can lead to all sorts of issues later on.

 If IsNull(Name) Then
        strName = "NAME LIKE '*'"
    Else
        strName = "NAME LIKE '" & Name & "*'"
    End If
 
    strSQL = "SELECT * FROM tblMember WHERE " & strName & " ORDER BY NAME"
 
    Form_frmSearchResults.SetSearchResults strSQL
    Form_frmSearchResults.Refresh

Also what version of Access are you using? Never seen .SetSearchResults is this 2007 option?

David

 

[chris-uk-lad]

Im in 97, Form_frmSearchResults.SetSearchResults strSQL is relating to the following procedure

Public Sub SetSearchResults(strSQL As String)
    Me.RecordSource = strSQL
End Sub

Im aware the CSTR command to convert an integer into a string but im not sure how i can first convert the incoming text into a string as its not specifically an integer.

 

[DCrake]

Create a new query and bring down the field NAME (if you have not renamed it yet) Then in the condition line type in Like 'ATHERTON 201*' and run the query.

Does it work?

If it does look at the sql and see how Access has constructed it and replicate the sql in vba.

David

 

[KenHigg]

Create a new query and bring down the field NAME (if you have not renamed it yet) Then in the condition line type in Like 'ATHERTON 201*' and run the query.

Does it work?

If it does look at the sql and see how Access has constructed it and replicate the sql in vba.

David

It looked like ATHERTON and 201 were in two seperate records to me, or was ATHERTON 201 in one field in one record?

 

[chris-uk-lad]

It looked like ATHERTON and 201 were in two seperate records to me, or was ATHERTON 201 in one field in one record?

yes thats correct, i was just giving an example of what could be found in the many rows of that column such as

NAME
-----------
ATHERTON
LINKS
OTHER
SUBCAP
201
G&N

 

[KenHigg]

I'm thinking something like the following may work:

strName = "CStr(NAME) LIKE '*'"

???

 

[DCrake]

The like operator examines the data in each record for the text qualifier In your example you stated "ATHERTON 201" as though it was in one field. When it transpires They are actually in different fields.

You actually need the In() operator Such as In("ATHERTON","201")

The fact that number are in text fields states that they will be treated as if they were text.

Hope that answers your question

 

[chris-uk-lad]

i actually figured that to be the case, that the numbers would be treated as text/strings initially but then i wouldnt need t owork out why they arent being retrieved

 

[KenHigg]

The like operator examines the data in each record for the text qualifier In your example you stated "ATHERTON 201" as though it was in one field.

Just curious, how did you interpret this:

Such as in my column i have
ATHERTON
201

as ATHERTON 201 being in one field?

Then, here:

When it transpires They are actually in different fields.

I think it's the same field, but different records.

Finally, how exactly would you work the In() function into his problem??

You actually need the In() operator Such as In("ATHERTON","201")

??

 

[DCrake]

Sorry Ken, I did mean ATHERTON and 201 were in the same field but in different records.

The original senario suggested that they were both contained in the field in the same record.

Anyway I think I have exhausted this line of questioning and will move on to something different.

David

 

[KenHigg]

Ok. I wonder if Chris figured out how to use your In() suggestion in his situation? Maybe I can learn a new trick

 

[DCrake]

The problem with In() it compares the arguments against the whole contents of the field. So if record 1 had "ATHERTON" and Record 3 had "201" then In("ATHERTON","201") would return both rows.

But if Record 1 had "ATHERTON 201" In("ATHERTON","201") would return no records.

The only way to get what he wants is to parse the individual words/values using the Like Operator

Like '*ATHERTON*' Or Like '*201*' would work

David

 

[gemma-the-husky]

i don't see why it wouldn't retrieve 201

except that using a textbox called name? to search a dbs field called NAME can't help.

Possibly if you enter 201 in the text box, access treats it as a number rather than a string.

in which case always use cstr(namefield) to coerce to a string before using it in the SQL query

 

[KenHigg]

i don't see why it wouldn't retrieve 201

except that using a textbox called name? to search a dbs field called NAME can't help.

Possibly if you enter 201 in the text box, access treats it as a number rather than a string.

in which case always use cstr(namefield) to coerce to a string before using it in the SQL query

Kinda where I'm at with it all. Seeing as how I still have no clue how to implement the In() thing and seeing as how the original poster is long gone, we appear to be beating a dead horse... Cheers.

 

[chris-uk-lad]

sorry for confusion, when i mentioned that this was on a form, i really should have mentioned that a search box is used, where this code resides. I understand this likely caused confusion as to the number of words being searched for.

Ill elaborate on the previous. Theres a textbox, enter search name (be that 201 or ATHERTON) and it will search that 1 table (tblMember) in the Name column for that result and return it.

Private Sub cmdSearch_Click()
    Dim strSearch As String
    Dim strName As String
    Dim strWhere As String
    Dim strSQL As String
        
    If IsNull(Name) Then
        strName = "NAME LIKE '*'"
    Else
        strName = "NAME LIKE '" & Name & "*'"
    End If
    
    strSQL = "SELECT * FROM tblMember " WHERE " & strName"
    Form_frmSearchResults.SetSearchResults strSQL
    Form_frmSearchResults.Refresh
End Sub

?strsql
SELECT * FROM tblMember WHERE NAME LIKE '201*'

I hope that clears things a little, thanks for your suggestions so far but i believe i took you down an inaccurate path. Again, using the above should return the value 201 but it returns blank, only working for values in the column that are text strings such as ATHERTON.

 

[DCrake]

As a final interjection here is a sample mdb detailing the difference between Like and In

Posted before reading #17

Syntax should be

SELECT * FROM tblMember WHERE [NAME] Like '201*';

I told you to rename that field. Access is miss-interpreting the word NAME. If you insist on not changing it at least encompass it in square brackets, then Access will at least think it is a field.

 

[chris-uk-lad]

oh, and i tried using Name = CStr(Name) before hitting 'If ISNull' but returns an error (Invalid use of Null) if the textbox is empty.

If its 201 it still didnt work, but ATHERTON did.

 

[KenHigg]

What data type is the field 'Name' in the table?