Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report field logic (2k)

    I gotta make this annoying report field that calculates dwell time from todays date. so basically i need to do date() - [startdate]. ok easy enough... problem is, i have to find the start date field, there are probably 30 of them that exist, based off of a status code. the status code says where the item is, so the start date for that status is [StatusCodePrefix_startdate]. since i am displaying this info in a report, i think my best bet is to use the query the report is based off of to find this out. the query has a field that contains the field [status], and the different possibilites of location would be in there. im thinking along the lines of using something that does locaction: "[" & [status] & "_startdate]" to get the field, but that just lists the field name in a column in the query. i wouldn't know how to get data from there, any suggetions on how to get the data from that field?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Report field logic (2k)

    As far as I know, you can't refer to a field indirectly. I would write a custom function in the report module using a Select Case ... End Select block to return the correct field.

    Private Function SelectStartDate(strStatus)
    Select Case strStatus
    Case "nnn"
    SelectStartDate = Me.[nnn_StartDate]
    ...
    ...
    End Select
    End Function

    and use it in the Control Source of a text box on the report:

    =SelectStartDate([Status])

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report field logic (2k)

    hmmm yeah i was hoping to avoid that, wouldn't that require 30 cases then (maybe more now that i think about other fields i could have)? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    what i was hoping to do was have the query make that column (which it now does successfully), and then from there have a text box bound to it (inivisible), and then have another textbox use that value to do the lookup to the table for the start date (something along those lines anyway). if you don't think that will work though, I will just end up using the cases, i was just hoping to avoid typing that all out <img src=/S/sneaky.gif border=0 alt=sneaky width=15 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Report field logic (2k)

    From your original description, I got the impression that it couldn't be done in a query, but I don't know enough about the design of your tables to know for sure.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report field logic (2k)

    i'm basically taking data from 1 table that uses the status code prefix + "startdate" as the field titles. so i thought if i could build the field names, that would be sufficient to finding out the info that i want to know by using a 2nd text box to look it up. any additional thoughts?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Report field logic (2k)

    I don't think that will work. You get the field name as a string, but there is no way to use that string (except by writing yet another VBA function). It is a disadvantage of the design of the database; it would have been better to use a separate table linked to the main table with one record for each date, instead of 30 date fields in the main table.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report field logic (2k)

    alright, i guess i'll get started on the case statement then <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> , thanks hans.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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