Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Finding date in query (2002)

    Hi,

    I'm enclosing a stripped down version of a database. It opens to a form where a date range is entered and the a query is run filtering the records based on the date range. This all works fine. What I need to have the query or a report (or whatever you suggest) show is whether a activity was conducted by the [next visit date]. The query shows the [visit date] and then the [next visit date] which is calculated in the query. The user wants to be able to see if the sales person did their next visit by the [next visit date]. I have no clue as to how to trap this information or if it is even possible. So before I proceed further, I'm uploading how far I've gotten.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding date in query (2002)

    See if the attached version does what you want. I added two calculated fields to the query: the first uses DMin to look up the first visit date after the current one, and the second compares it to the next visit date as calculated by you.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding date in query (2002)

    Hans that is perfect! I've never used DMin and never knew it was an option. I was thinking of something with DLookup but that was as far as I got. Were you reading my mind (or my bosses) when you gave me the second field? I was going to put in somthing similar once I knew if it was even possible to achieve my goal! I usually use these long if/than statements which take forever to type and test. I love how you did the yes/no format piece. I've never seen it that way. Learned two things for the price of one request!

    Thanks again,
    Leesha

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding date in query (2002)

    Hi Hans,

    I need your help again. In instances where there has been no [Next Done] visit, there is a #Error. I need this to be null so that there isn't an the [in time] field doesn't have #Error and/or is null. I tried doing an if/than statement without any luck.

    Thanks!

    Leesha

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding date in query (2002)

    Try this:

    Next Done: IIf(IsNull(DMin("[Visit Date]","tblSalesActivityBackup","[Referral Source] =" & [Referral Source] & " AND [Visit Date] > #" & [Visit Date] & "#")),Null,CDate(DMin("[Visit Date]","tblSalesActivityBackup","[Referral Source] =" & [Referral Source] & " AND [Visit Date] > #" & [Visit Date] & "#")))

    (It'll be on two or more lines in the browser, but it's a single expression)

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding date in query (2002)

    Perfect! I was on the right track with the if/than approach but didn't think to try using null because there was a vaule (#error) in the field.

    Thanks!

    Alicia

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding date in query (2002)

    Hi Hans!

    Back yet again. First, I experimented with DMax using the formula you gave me to get the last visit done (to be used in a different report) and it worked! I'm tickled with this new area of learning. The problem I'm having now is that I need to provide a report that is used as a "nag" report to show the sales person only the visits that have not been completed. The previous report that this was used for was to show whether the visits are timely or not or not done at all. The report can get quite long depending on the number of accounts and she would like to be able to only see the accounts that she hasn't visited. I figured it would be simple enough to just put in "is Null" for the criteria in the [next done field] but when I do that I get a syntax error.

    Thanks,
    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding date in query (2002)

    Not sure if the attached does what you want.

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding date in query (2002)

    Hi Hans,

    It appears to do what I want but I'm not sure. When I went to put the query in design view from SQL I get an error pertaining to tblSalesActivitiy_1 and not being able to restore links, so I can't see the design view to visualize how you set it up so I can duplicate it.

    Leesha

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding date in query (2002)

    Hi Leesha,

    The query cannot be viewed in design view because it contains a non-standard join. It can only be viewed in SQL view or in datasheet view. To get an idea, switch to SQL view and change < to = in

    ... (tblSalesActivityBackup.[Visit Date]< tblSalesActivityBackup_1.[Visit Date]) ...

    You can then switch to design view. But you mustn't save the query!

    The query contains two instances of tblSalesActivityBackup (the second one has an alias tblSalesActivityBackup_1), joined on Referral Source and Visit Date, but the join on Visit Date specifies that the date in the first instance of the table is earlier than the date in the second instance. The criteria specify that the first date is not null and the second date is null.

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding date in query (2002)

    Thanks! Now I can visualize it to understand it and reproduce it!

    Leesha

Posting Permissions

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