Results 1 to 3 of 3

Thread: A "Smart" query

  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    21

    Question Unanswered: A "Smart" query

    Hello All:

    I have a a set of queries built into 2 reports. They are both pretty much the same with on small exception:

    1. They both use a select query to diplay details in a report
    2. They both filter out data based on the same critera.
    The difference is that one only asks the user to put in a date range and the other asks for Date Range and Last name.

    Is their a way to incorp the two sets of param queues for the user, but disreguard if no last name is entered?
    I know this might be a little confussing. See examples below:

    Query by Date only:
    SELECT AllSRExtract.[Last Appt Date], AllSRExtract.[SR Number], AllSRExtract.Claimaint_Full_Name, AllSRExtract.[Coverage Type], AllSRExtract.show, AllSRExtract.no_show, AllSRExtract.HCP_Full_Name, AllSRExtract.[Claimant Last Name], AllSRExtract.[Claimant First Name], AllSRExtract.[HCP Last Name], AllSRExtract.[HCP First Name], AllSRExtract.[Path Name], AllSRExtract.[Path Description], AllSRExtract.signature, AllSRExtract.[Facility City], AllSRExtract.[Last Appt Status]
    FROM AllSRExtract
    WHERE (((AllSRExtract.[Last Appt Date]) Between [Enter Starting Date] And [Enter End Date]) AND ((AllSRExtract.[Coverage Type])<>"WC") AND ((AllSRExtract.[Path Name]) Like "NY*_Xam") AND ((AllSRExtract.[Last Appt Status])<>"CANCELLED" And (AllSRExtract.[Last Appt Status])<>"CANCELLED_IP_FAULT"))
    ORDER BY AllSRExtract.[Last Appt Date], AllSRExtract.[Claimant Last Name], AllSRExtract.[Facility City];

    Query by Date and Last Name:
    SELECT AllSRExtract.[Last Appt Date], AllSRExtract.[SR Number], AllSRExtract.Claimaint_Full_Name, AllSRExtract.[Coverage Type], AllSRExtract.show, AllSRExtract.no_show, AllSRExtract.HCP_Full_Name, AllSRExtract.[Claimant Last Name], AllSRExtract.[Claimant First Name], AllSRExtract.[HCP Last Name], AllSRExtract.[HCP First Name], AllSRExtract.[Path Name], AllSRExtract.[Path Description], AllSRExtract.signature, AllSRExtract.[Facility City], AllSRExtract.[Last Appt Status]
    FROM AllSRExtract
    WHERE (((AllSRExtract.[Last Appt Date]) Between [Enter Starting Date] And [Enter End Date]) AND ((AllSRExtract.[Coverage Type])<>"WC") AND ((AllSRExtract.[HCP Last Name])=[Enter Doctors Last Name]) AND ((AllSRExtract.[Path Name]) Like "NY*_Xam") AND ((AllSRExtract.[Last Appt Status])<>"CANCELLED" And (AllSRExtract.[Last Appt Status])<>"CANCELLED_IP_FAULT"))
    ORDER BY AllSRExtract.[Last Appt Date], AllSRExtract.[Claimant Last Name], AllSRExtract.[Facility City];

    Can I make this one query that will not return blanks if someone does not have a Dr's name to enter?

    Thanks

    Lakota

  2. #2
    Join Date
    Jan 2004
    Posts
    75
    try

    like [enter doctors last name] & "*"

    That should filter by both, but will return all records if no name is entered.

  3. #3
    Join Date
    Dec 2003
    Location
    USA
    Posts
    21

    Smile

    Originally posted by sublimsos
    try

    like [enter doctors last name] & "*"

    That should filter by both, but will return all records if no name is entered.
    Thank you for the tip. It seems to be working very well.

    Lakota2k

Posting Permissions

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