Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparison Operators with Dates in Queries (2000 (9.0.6926 SP-3))

    Hi!

    I have a Form with a combo box that allows a user to select a supplier and then a subform which lists the invoices for that chosen Supplier. On the main form is a frame with toggle buttons which filters the list by All Invoices, Invoices with Purchase Orders or Invoices without Purchase Orders. This all operates from a query which links the Supplier ref to the form's combo box and the form frame codes to a Purchase Order status (1,2 or 3).

    I wish to add to the form two date fields, From and To, which will filter the form further, by Invoice Date, between the two dates entered and when a button is clicked to refresh the screen. However, if these fields aren't completed or a new Supplier is chosen, then the fields should subsequently be ignored or cleared.

    I tried to add to the query under the Invoice_Date field:-

    IIf([Forms]![frmInvoice_Enquiry]![TxtFrom_Date] Is Null,>=#01/01/1980#, between [Forms]![frmInvoice_Enquiry]![TxtFrom_Date] and [Forms]![frmInvoice_Enquiry]![TxtTo_Date])

    but if the [TxtFrom_Date] is Null, the the query returns no records. If I remove the >= operators and change #01/01/80# to a known date, then I get an output of records. Further, if I complete the [TxtFrom_Date] and [TxtTo_Date] fields then I get no records returned.

    The individual components of the IIf statement work fine by themselves.

    I may not actually be going the right way about what I'm trying to achieve anyway, but I'm somewhat puzzled about what is going on re the IIf statement and the comparison operators.

    Can anyone shed any light on what is going on here, please? And also how to fix?

    Cheers,

    Niven

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

    Re: Comparison Operators with Dates in Queries (2000 (9.0.6926 SP-3))

    Try

    Between Nz(([Forms]![frmInvoice_Enquiry]![TxtFrom_Date],#01/01/1980#) And Nz([Forms]![frmInvoice_Enquiry]![TxtTo_Date],#01/01/2050#)

    Alternatively, remove the criteria from Invoice_Date, and add a column

    [Forms]![frmInvoice_Enquiry]![TxtFrom_Date]

    Clear its Show check box and set Criteria to

    <=[Invoice_Date] Or Is Null

    Also add a column

    [Forms]![frmInvoice_Enquiry]![TxtTo_Date]

    Clear its Show check box and set Criteria to

    >=[Invoice_Date] Or Is Null

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparison Operators with Dates in Queries (2000 (9.0.6926 SP-3))

    Brilliant!

    Works better than I'd hoped. I have seen the NZ function in the past but never had occasion to use it - until now of course!

    Many thanks

    Niven <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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