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! ....
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?
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?