Searching for partial primary key (1 Viewer)

[FreonIceMan]

I am trying to search for a partial number in the primary key field. I have based my search off Allen Browne's search form http://allenbrowne.com/ser-62.html

I have posted my full code below. I am having trouble with the portion searching the Member_ID field.

My first version:
I would get a Run-time error '3464':
Data type mismatch in criteria expression.

If Not IsNull(Me.txtMember_ID) Then
        strWhere = strWhere & "([Member_ID] = """ & Me.txtMember_ID & """) AND "
    End If

My second Version: (I was searching for 54)
I would get a Run-time error '3075'
Syntax error (missing operator) in query expression '([Member_ID] = Like "*54*")'

If Not IsNull(Me.txtMember_ID) Then
        strWhere = strWhere & "([Member_ID] = Like ""*" & Me.txtMember_ID & "*"") AND "
    End If

Version 3:
If I use this the search works when the Member_ID matches exactly

If Not IsNull(Me.txtMember_ID) Then
        strWhere = strWhere & "([Member_ID] = " & Me.txtMember_ID & ") AND "
    End If

Does anyone see what I am doing wrong?

Thanks,
Freon

Full Code:

Private Sub Member_Search_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtMember_ID) Then
        strWhere = strWhere & "([Member_ID] = """ & Me.txtMember_ID & """) AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtLast_Name) Then
        strWhere = strWhere & "([Last_Name] Like ""*" & Me.txtLast_Name & "*"") AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFirst_Name) Then
        strWhere = strWhere & "([First_Name] Like ""*" & Me.txtFirst_Name & "*"") AND "
    End If
       
         'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtDate_Of_Birth) Then
        strWhere = strWhere & "([Date_Of_Birth] Like ""*" & Me.txtDate_Of_Birth & "*"") AND "
    End If
    
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

 

[CJ_London]

You are building a string and cannot mix = and like so this

([Member_ID] = Like "*54*")

should be

([Member_ID] Like "*54*")

Also, if your are building a string within a larger string the string should be surrounded with single quotes so

([Member_ID] Like '*54*')

so your code should be

If Not IsNull(Me.txtMember_ID) Then
        strWhere = strWhere & "([Member_ID] Like '*" & Me.txtMember_ID & "*'") AND "
    End If

 

[namliam]

CJ I believe the problem here is not a coding problem per-se... it is a field type problem

Member_ID obviously is a number field and you cant do "LIKE" on a number field...
You have to convert the ID to a text first using format or some simular function if you feel you need the LIKE

Otherwize use a more number-ish where id > 5400 and id < 5500 for example...

 

[FreonIceMan]

Hi CJ,

When I try your code I am receiving a Compile error: Expected: end of statement


namlaim,
I want to be able to search with in the member id. so if I have a partial i can still find it.

i.e. 7895463; 5469812; 3681254 if you search these for 54 they will al come back as results
How would I convert it to so i can search it using the like statement?

 

[mdlueck]

CJ I believe the problem here is not a coding problem per-se... it is a field type problem

Member_ID obviously is a number field and you cant do "LIKE" on a number field...
You have to convert the ID to a text first using format or some simular function if you feel you need the LIKE

I solve this type of challenge in real SQL RDBMS's via casting the numeric data as a VARCHAR, then performing the LIKE comparison. Example:

[FONT=Courier New][COLOR=#1F497D]WHERE VARCHAR(UDISPUNTCST) LIKE '%.___%'[/COLOR][/FONT]

To select rows with greater than two decimal places of precision.

 

[CJ_London]

Member_ID obviously is a number field and you cant do "LIKE" on a number field

I've just done it! You can

When I try your code I am receiving a Compile error: Expected: end of statement

Sorry, my mistake, had an extra quotation mark in the bit in red

If Not IsNull(Me.txtMember_ID) Then
        strWhere = strWhere & "([Member_ID] Like '*" & Me.txtMember_ID & [COLOR=red]"*')[/COLOR] AND "
    End If

 

[namliam]

I've just done it! You can

No you basicaly cannot, the number implicitly gets converted to a text.... doing so with a proper format is by far prefered.

Either way implicitly or explicitly.... it is a text that you do a like on, not a number

 

[FreonIceMan]

Hi CJ,

Thank you so much that works perfectly.

-Freon

Final code:

Private Sub Member_Search_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtMember_ID) Then
        strWhere = strWhere & "([Member_ID] Like '*" & Me.txtMember_ID& "*') AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtLast_Name) Then
        strWhere = strWhere & "([Last_Name] Like ""*" & Me.txtLast_Name & "*"") AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFirst_Name) Then
        strWhere = strWhere & "([First_Name] Like ""*" & Me.txtFirst_Name & "*"") AND "
    End If
       
         'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtDate_Of_Birth) Then
        strWhere = strWhere & "([Date_Of_Birth] Like ""*" & Me.txtDate_Of_Birth & "*"") AND "
    End If
    
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

 

[Galaxiom]

doing so with a proper format is by far prefered.

By whom?

Implicit type casting is part and parcel of the way Access works. Many developers use it all the time and don't even realise it.

 

[namliam]

Because you are relying on Access to make the "right guess", which a lot of the time it does... However I dont want to buy everyone coffee who has had these implicit conversions and "randomly" ran into trouble because of it.... For example number conversions depend on your regional settings by default, same for dates. It can make a HUGE difference to have US or Euro settings there and that can cause a lot of grief.

Try searching for a period on a date that is stored as a Text, 11-1-2014 thru 19-1-2014, stored as text will reap havoc on your results, it will seem to work because its only januari right now, and because its a certain given period.
Then go to 14-1 thru 24-1 or even just add Feb/Mar and you run into problems, *but it works* IF YOU ARE LUCKY and access desides to convert your string into dates instead of your dates into strings.... But a date is a date, should be stored as a date and treated as a date, that way you make sure you dont run into trouble.

 

[RainLover]

Because you are relying on Access to make the "right guess", which a lot of the time it does...

Did you really mean to make that statement, or where you trying to over simplify things.

Access never makes a guess because it is incapable of doing so.

If you feel that I am wrong simply post a Data Base (in 2003 please) where each time the code is run it comes up with a different answer or guess.

 

[namliam]

Random errors are random, code running properly or not isnt the issue, and true it is largely about semantics.... how many times (on this forum alone) have we seen a question like:
I have a query : Select number from mytable where number < "11"
The strange thing is I am seeing 1, but not 2,3,4,5
Plus I am also seeing 100, 1000, 1001 , 10000001, what the hell is wrong with this stupid database?

We all know the answer right? All because you quoted the 11, access guessed/chooses/desides/assumes to convert the number to text and cause this "mess", instead store the data the way its supposed to be stored and work with the data the way that you are supposed to work with it.

 

[RainLover]

It would appear that it is the user that has made the mistake rather than Access has made a guess.

 

[namliam]

True user error, semantics, programs dont "Guess" perse... its the reason I say "guess" with quotes... on the whole though... just work with your data the way its intended...

 

[nanscombe]

Access interprets what you have written, not what you meant to write, in other words Access behaves correctly for the input it is given.

< "11" means treat the left hand side of the equation as a string of characters
< 11 means treat the left hand side of the equation as a number.

Unlike the English language being near enough, and still being understood, is not good enough you have to write it correctly.

 

[Galaxiom]

You need to understand the direction of implicit castings. Yes string to date is affected by the regional settings and some "helpful" Windows functions that try to fit any scheme if it is invalid in the regional setting.

If you feed it something ambiguous or locally invalid then don't expect consistency.

However there are very clear situations where the cast is highly predictable. Put an arithmetic operator between two numeric strings and they will be cast to numbers if they can.

Indeed I have no doubt you have used automatic casting many times. An unbound textbox is a string. If it contains a numeric value then it will be cast to a number if it is used as a number. Do you apply CLng() every time you use an unbound textbox in an arithmetic operation?

Moreover the case in point is the use of the Like operator. Since it is a string comparator Access will convert both sides to string. String is the lowest in the hierarchy of types. Everything will cast to a string. Like doesn't fail. It even works with dates so long as you are prepared for the date to be treated as a string. All hail ISO dates which do work as strings.

What you said is "you cant do "LIKE" on a number field..." which is wrong, no two ways about it. Everything you have posted since that mistake has simply dug you into a deeper hole.

 

[namliam]

Depends on your POV, you just proved my point, if you do like it IS always on a text.... implicit or explicit, text it is

I definatly second "All hail ISO dates which do work as strings.", though they have their own set of problems when trying to work with them.

 

[Galaxiom]

Depends on your POV, you just proved my point, if you do like it IS always on a text.... implicit or explicit, text it is

Which one?

You original point was "you cant do "LIKE" on a number field..."

Your second point was that implicit casting is unreliable.

All evidence shows otherwise for both these statements.

 

[namliam]

Like forces a string comparison, therefor you will never do like on a proper number field because the number is first converted to string.

Implicit cast is "unreliable" in the sence that it may be persieved to work, while failing at other times... Yes this may not be the result of access "freely choosing a or b" or something alike... but the result is the same

 

[Galaxiom]

CJ I believe the problem here is not a coding problem per-se... it is a field type problem

Member_ID obviously is a number field and you cant do "LIKE" on a number field...
You have to convert the ID to a text first using format or some simular function if you feel you need the LIKE

Here was can see your claim was that the original problem was caused by the field being a number. That is obviously is incorrect and you are simply resorting to semantics to disguise the gap in your experience.

Implicit casting is equally as reliable as explicit casting. CLng("abc") will fail exactly the same as an implicit cast on the same data and has zilch to do with being explicit or implicit.

Conversion to String is one hundred percent reliable.