Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    74

    Unanswered: retrieve non primary key

    hi,

    i have a search function where in my sql, i have both search by name (non primary key)and NRIC (primaryk key). how can find the exact match (whether is capital letter or non capital letter etc)???

    eg. how can i have a sql statement that will retieve the records of each individually??? the below are 2 different rercords.
    1) nono
    2) Nono

    in my scenario od search function, when the user key in 'Nono', it will retrieve the records for 'nono'. how can i solve this???
    can i retrieve them individually ???

    pls advice
    nono

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Look up the function Ucase which converts 2 UpperCase

  3. #3
    Join Date
    Nov 2003
    Posts
    74
    why do i need to convert to uppercase???

    what i mean is that, in my table there are 2 different records. they are different records but with same name.
    - nono
    - Nono

    in my sql, how can i differentiate these 2 records??
    eg. the user wants to search for records that comes under the name 'Nono', what should be my sql???

    i have try this:

    SELECT *FROM Table1 WHERE name=Nono;

    however it retrieves the record of 'nono', how can i retrieve the record of Nono????

    **both are different records

    pls advice
    nono

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Ok, I can understand your question now. In this case you have to join another field NRIC which is uniqu. i.e searching through both fields name and NRIC OR only NRIC

    SELECT urTable.name, urTable.Nric
    FROM urTable
    WHERE (((urTable.name)="nonno") AND ((urTable.Nric)=215));


    for example

  5. #5
    Join Date
    Nov 2003
    Posts
    74
    hi

    i have try the method u taught me but it still cannot work.

    this is what i have written for my code.

    s = "SELECT Sum([Balance]+[Quantity]) AS Total FROM Customer_Table INNER JOIN Customer_Account_Table ON Customer_Table.NRIC = Customer_Account_Table.NRIC GROUP BY Customer_Table.NRIC HAVING (((Customer_Table.NRIC)='" & [Forms]![Form1]![txtNRIC] & "') AND ((Customer_Table.Name)='" & [Forms]![Form1]![txtName] & "'));"

    is my code correct??

    nono

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    nono,

    You are trying to do something that can't be done ... string searching in access is not case sensitive so your search would retrieve NONO NOno noNO NoNo nOnO and all the other permutations ... That is by design. What you can do is retrieve the records and apply string comparisons to reject those that do not exactly match ... Try looking at the InStr function and specifically the vbBinaryCompare option.

  7. #7
    Join Date
    Nov 2003
    Posts
    74
    do u mean that if i use the InStr() function, it can solve my problem by differentiating the names???

    eg. Nono, nono, n0no

    i need guidelines on this function.
    eg.
    Dim SearchString, SearchChar, MyPos
    SearchString = me.txtMyTextField
    SearchChar = "P"

    MyPos = Instr(1, SearchString, SearchChar, 1)

    if i assign the SearchString as my input for user, then what should i assign value to SearchChar??

    how can i apply this function into my scenario??


    pls advice
    nono
    Last edited by damnnono_86; 11-06-03 at 22:49.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •