Results 1 to 8 of 8

Thread: Query Help

  1. #1
    Join Date
    May 2013
    Posts
    29

    Unanswered: Query Help

    Hello,
    I'm having trouble writing a query to show me that a person hasn't returned his survey within 30 days. My fields are Date Mailed and Date Received. If I haven't recieved it back within 30 days I want it to show me the person's name with the dates.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a query comes with several parts
    first off is the list of columnbs you want from the table(s) you want
    then thereis a filter, the 'where' clause

    so you need a where clause that returns the rows you want

    eg

    where isnull(datemailed) = false and isnull(datereceived) = true and dateadd('d',-30,date()) <= datemailed

    or in psuedo English
    return all rows who have a non null datemailed AND a null datereceived and whose date mailed is greater than or equal to 30 days from today

    or in English
    i want all rows which have a date mailed but don't have a date recieved where the datemailed is equal to or more than 30 days from today
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2013
    Posts
    29
    Sorry I am really new to access and SQL. I put

    Select [Date Received], [Date Mailed]
    From Sheet1
    Where [Date Received] - [Date Mailed] < 30;

    And it doesnt want to work

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that doesn't surprise me
    there are 3 conditions that must be met in order for a row to be returned
    you must have mailed a survey out (so the mailed date cannot be null)
    you must have not received a survey back (so the datereceived must be null)
    the date mailed must be equal to or more than 30 days

    Where [Date Received] - [Date Mailed] < 30;
    will return rows whose date of mailing is greater than 30 days. BUT it won't 'find' rows whose date received is null, and it will return any rows whose surveyrs came back over 30 days ago
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2013
    Posts
    29
    Okay, So how would I write my query?

  6. #6
    Join Date
    May 2013
    Posts
    29
    I got it to return the dates that were more than 30 days
    SELECT Sheet1.[Date Mailed], Sheet1.[Date Received], Sheet1.[Date Answer]
    FROM Sheet1
    WHERE [Date Received] - [Date Mailed] >30

    But Some poeple haven't returned their results back in yet. And I need to run a query to show the poeple who haven't returned their survey yet(Date Received), and it's taken them 30 days or more.

  7. #7
    Join Date
    May 2013
    Posts
    29

    Query Help Part 2

    Hey guys,

    I have 3 fields I want to display. The name of the person[Name], Date Mailed[Date Mailed], and Date Received[Date Received]. I want to display the records that don't have information in them and only if it's been 30 days or more. I wrote this code here:

    Select * from Sheet1
    Where [Date Received] is NULL

    But that only returns all the blank fields. I want to display the ones that are blank and have been 30 days..I want to keep the blank records within the 30 day period.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so replace the column and table names in the where clause as in post #2
    datemailed is [date mailed]
    date received is [date received]
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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