Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Unanswered: Need help with Date expression

    I need help to build an expression as part of a query criteria.

    1/ I have a table of dates referred to by the query [Fieldname]. I will also be freetexting in a date I wish to use when running the query [EnterDate ../../..]. I need to be able to ask the query to look at the [Fieldname] and if the date is before or equal to the [EnterDate ../../..] then use the [EnterDate ../../..]. But if the [Fieldname] date is after the [EnterDate ../../..] then use the date in the [Fieldname].
    Every time I try the expression gets very ugly and always fails. Can anyone help PLEASE! ....

    and

    2/ is there a way that the [EnterDate ../../..]part is only used once in a query like this so it does not need to be typed in multiple times when running the query?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use a form to input the date value and build your query dynamically after testing the condition you specify. Long and complex queries are hard to read and to debug.

    Once you have the correct SQL sentence you can always re-create a permanent query from it.

    Have a nice day!

  3. #3
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    How can I join two queries

    Thanks for the tip. I have broken my query into two segments and I now have both of them returning the correct information. I now need to find a way of merging those two queries named [PS2] and [PS3].
    At this point the two queries return two columns of dates. On any matching horizontal field there is only one date shown. (Like the example in the attachment)


    Creating a new field which I named [PS4] I then tried to merge the two fields
    using this expression as the Fieldname expression
    PS4: [PS2] And ("IIf IsNull"=[PS3])

    This only resulted in a 0 value being the returned.
    I replaced And with "Or" and only got a -1 value being returned

    What is the correct way to merge the results of these two queries?
    Attached Files Attached Files

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    PS4: Nz([PS2],[PS3])
    Paul

  5. #5
    Join Date
    Aug 2009
    Location
    Waikato, New Zealand
    Posts
    28

    Wonderfull

    In my eyes You are an absolute legend my friend. This works like a charm. Very much appreciated. I had no knowledge of the Nz function at all.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem. You could also use IIf() and IsNull(), but Nz() is simpler in this case. The correct syntax for them would be:

    IIf(IsNull([PS2]), [PS3], [PS2])
    Paul

Posting Permissions

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