Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Unanswered: date and time issues

    I have a database designed as follows:

    Events (
    ID AutoNumber
    Location Text
    Event Text
    Category Number -> Category.Description
    )

    Category (
    ID Autonumber
    Description Text
    )

    Dates (
    ID Autonumber
    EventID Number ->Events.ID
    Date DateTime
    TimeStart DateTime
    TimeEnd DateTime
    )


    My problem is that when i'm wanting to program with this data, i can't find a good way to do the times. I would like to have a field that will say "4:00PM-6:00PM" where those 2 are coming from TimeStart + '-' + TimeEnd basically. However, not all events have an ending date, so you get an error if you concatenate the 2 fields together, since one is null.

    Is there any way in a query, to do a check beforehand that the field is not null, so that if they're both there they'll concatenate, if not, it'll just be the TimeStart field alone?

    If this was in SQL Server, i would think i could do a Stored Procedure, but it's just Access.

  2. #2
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50

    Re: date and time issues

    run a query on the table and include this field.
    Time: IIf([timestart] Is Not Null,IIf([timeend] Is Not Null,[timestart] & "-" & [timeend],[timestart]))


    Originally posted by mandreko
    I have a database designed as follows:

    Events (
    ID AutoNumber
    Location Text
    Event Text
    Category Number -> Category.Description
    )

    Category (
    ID Autonumber
    Description Text
    )

    Dates (
    ID Autonumber
    EventID Number ->Events.ID
    Date DateTime
    TimeStart DateTime
    TimeEnd DateTime
    )


    My problem is that when i'm wanting to program with this data, i can't find a good way to do the times. I would like to have a field that will say "4:00PM-6:00PM" where those 2 are coming from TimeStart + '-' + TimeEnd basically. However, not all events have an ending date, so you get an error if you concatenate the 2 fields together, since one is null.

    Is there any way in a query, to do a check beforehand that the field is not null, so that if they're both there they'll concatenate, if not, it'll just be the TimeStart field alone?

    If this was in SQL Server, i would think i could do a Stored Procedure, but it's just Access.

  3. #3
    Join Date
    Mar 2003
    Posts
    7
    i hate to be retarded, but will that work in Microsoft SQL as well? I'm planning on migrating it over in probably 8 months. Or will i need some new code?

Posting Permissions

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