Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forcing Values in a Field (2000)

    I have two fields, Observation and Date. I would like to have the response in Observation (0-7) force a specific date in the Date field (0=01/01/01, 1=02/02/02, etc.). Can I accomplish this by using a query and creating a field with an expression in it?

    Also, I was wondering if anyone had any pointers about using the search function in this forum? I ask simple questions, which I am sure that have been asked before, but I can never find anything through the Searches with any efficiency and I get frustrated and give up and post anyway.

    Thanks! Sarah

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

    Re: Forcing Values in a Field (2000)

    If the dates strictly follow the pattern you indicated, the expression could be

    DateField: DateValue(([Observation]+1) & "/" & ([Observation]+1) & "/" & ([Observation]+1))

    (I wouldn't call a field Date, since this is also the name of a built-in function). Otherwise, you'd have to use nested IIf's or the Switch function.

    When searching, try to enter search terms that are specific enough to describe your problem (otherwise, half the posts in the Access forum will match), yet general enough (otherwise, nothing will match). For the question you ask here, that would be difficult.

  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Values in a Field (2000)

    Hi thanks,
    Actually the dates and observation values are like this:
    0 - 03/05/05
    1 - 03/09/05
    2 - 03/12/05
    3 - 03/16/05
    4 - 03/19/05
    5 - 03/23/05
    6 - 03/26/05
    7 - 03/30/05
    8 - 04/02/05

    Sorry those dates i put in earlier were just examples. Thanks for the tip about renaming the date field also!

    Sarah

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

    Re: Forcing Values in a Field (2000)

    You could use this:

    DateField: Switch([Observation]=0,#03/05/05#,[Observation]=1,#03/09/05#,[Observation]=2,#03/12/05#,[Observation]=3,#03/16/05#,[Observation]=4,#03/19/05#,[Observation]=5,#03/23/05#,[Observation]=6,#03/26/05#,[Observation]=7,#03/30/05#,[Observation]=8,#04/02/05#)

    or

    DateField: #03/05/05#+([Observation]*7+1)2

    The first expression is flexible - you can enter any date you need; the latter one is rigid, but much shorter.

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

    Re: Forcing Values in a Field (2000)

    Yet another one:

    DateField: Choose([Observation]+1,#03/05/05#,#03/09/05#,#03/12/05#,#03/16/05#,#03/19/05#,#03/23/05#,#03/26/05#,#03/30/05#,#04/02/05#)

  6. #6
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Values in a Field (2000)

    Thanks Hans,
    I'm eager to try them out tomorrow.

    Sarah

  7. #7
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Values in a Field (2000)

    Thanks Hans,
    I ended up using the "Switch" version and it's working like a charm <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

Posting Permissions

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