Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blank Criteria (A2K SP3)

    I have a query, qrySecurityDeposit that has a LastName field and a FirstName field whose
    Criteria are set to [Forms]![frmCheckOut]![LastName] and [Forms]![frmCheckOut]![FirstName]

    If both the FirstName field and the LastName field are populated in tblCustomerName, the correct record is returned.
    If the First Name field is blank, no record is returned. If a period is placed in the First Name field (i.e. populating the FirstName field), the correct record is returned. If the FirstName field is deleted from the query, the correct
    record is returned.

    Is there a way to have the correct record returned, if the FirstName field is blank?

    Any suggestions whould be appreciated.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blank Criteria (A2K SP3)

    I'm not clear on what you're trying to do, work with a blank first name on the form or work with a blank first name in the query. Why don't you post the entire SQL instead of just the criteria.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Criteria (A2K SP3)

    SELECT [tblPropertyName]![PropertyID] & [OrderID] AS ReceiptNumber, tblRental.PropertyID, tblRental.RoomNumber, qryCustomerSorted.LastName, qryCustomerSorted.FirstName, [FirstName] & " " & [LastName] AS CombinedName, tblRental.RentBegDate, DateAdd("y",-1,[CheckoutDate3]) AS RentEndDate, DateAdd('y',[NumberofDays],[RentBegDate]) AS CheckOutDate3, tblRental.SecurityDeposit, qryCustomerSorted.BillingAddress, qryCustomerSorted.CCity, qryCustomerSorted.StateOrProvince, qryCustomerSorted.PostalCode, [CCity] & ", " & [StateOrProvince] & " " & [PostalCode] AS CombinedAddress, qryCustomerSorted.PhoneNumber, tblRental.DepositRefundAmt, tblRental.CheckOutDate, tblPropertyName.PCity, tblRental.DepositTransferred, qryCustomerSorted.CustomerStatusID
    FROM (tblRental INNER JOIN qryCustomerSorted ON tblRental.CustomerID = qryCustomerSorted.CustomerID) INNER JOIN tblPropertyName ON qryCustomerSorted.PropertyID = tblPropertyName.PropertyID
    WHERE (((qryCustomerSorted.LastName)=[Forms]![frmCheckOut]![LastName]) AND ((qryCustomerSorted.FirstName)=[Forms]![frmCheckOut]![FirstName]) AND ((tblRental.SecurityDeposit)>0) AND ((tblRental.DepositRefundAmt) Is Null)) OR (((qryCustomerSorted.LastName)=[Forms]![frmCheckOut]![LastName]) AND ((qryCustomerSorted.FirstName)=[Forms]![frmCheckOut]![FirstName]) AND ((tblRental.DepositRefundAmt) Is Null) AND ((tblRental.DepositTransferred)>0))
    ORDER BY [tblPropertyName]![PropertyID] & [OrderID] DESC , DateAdd('y',[NumberofDays],[RentBegDate]) DESC;

    What I am trying to do is to get qrySecurityDeposit to return a single record when the FirstName field is blank.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Blank Criteria (A2K SP3)

    Try replacing:
    qryCustomerSorted.FirstName)=[Forms]![frmCheckOut]![FirstName]

    with:
    (qryCustomerSorted.FirstName=[Forms]![frmCheckOut]![FirstName] or =[Forms]![frmCheckOut]![FirstName] Is Null)

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Criteria (A2K SP3)

    Your suggestion was most helpful in getting me on the correct path. I had to modify your code slightly:

    (qryCustomerSorted.FirstName=[Forms]![frmCheckOut]![FirstName] or =[Forms]![frmCheckOut]![FirstName] Is Null)

    To

    (qryCustomerSorted.FirstName=[Forms]![frmCheckOut]![FirstName] or qryCustomerSorted.FirstName=[Forms]![frmCheckOut]![FirstName] Is Null)

    Many thanks,

    Tom

Posting Permissions

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