Displaying date between two dates without ( IIf and Citeria)-CodePudding

I'm new to access and coding so please bear with me. I want my [Exp] dates that fall between 1/12/2020 and 1/7/2021 to be shown/displayed in [Exp2] record and if it doesn't fall between the two mentioned dates then show/display nothing in Exp2 record. Example: |

| Exp       | Exp2       |
| 1/5/2020  |            |
| 3/8/2020  |            |
| 12/13/2020| 12.13.2020 |

see in Exp2 only 3rd record is shown/displayed while the first and second are empty.

enter image description here

CodePudding user response：

Use a query like this:

Select
    [Exp],
    IIf([Exp] Between #2020-12-01# And #2021-07-01#, [Exp], Null) As Exp2
From
    YourTable

or use a subquery (slower):

Select
    [Exp],
        (Select 
            First(T.Exp)
        From 
            YourTable As T
        Where 
            T.Exp = YourTable.Exp 
            And
            T.Exp Between #2020-12-01# And #2021-07-01#) As Exp2
From
    YourTable