Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    41

    Unanswered: please help fix this statement

    im using an access project file now and no longer a mdb file.

    i used to prompt a user for the first and last name of the patient they were looking for using this statement like [forms]![main]![text0] & "*"
    and like [forms]![main]![text2] & "*" then it would promt them to enter in a last name and a first name but if they only entered a last name and left the first name prompt empty it would give them all the last name they had entered .


    now im on sql as my backend and i got my query working except
    if i dont type anything in on the second prompt i get no records.

    how can i fix this because the user doesnt always no the first name and would like to sort through the records by just the last.


    my current statement that works as long as you fill in both prompts is a follows.

    SELECT tblpatientinfo.*
    FROM tblpatientinfo
    WHERE (lname LIKE @LastName + '%') AND (fname LIKE @firstname + '%')
    ORDER BY chartnumber

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    SELECT tblpatientinfo.*
    FROM tblpatientinfo
    WHERE (lname LIKE isnull(@LastName,'') + '%') AND (fname LIKE isnull(@firstname,'') + '%')
    ORDER BY chartnumber
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Sep 2003
    Posts
    41
    Originally posted by Enigma
    SELECT tblpatientinfo.*
    FROM tblpatientinfo
    WHERE (lname LIKE isnull(@LastName,'') + '%') AND (fname LIKE isnull(@firstname,'') + '%')
    ORDER BY chartnumber



    thank you very much worked great , im just having a hard time with the new syntax at least the vb codes i wrote still work.

Posting Permissions

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