Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query problem (2000)

    Hi,
    I have this parameter in a query:
    Expr1: (SELECT * Where date()>([DueDate] - [Reminder]) and [ConfirmedCourtCPO] Is Null)
    But its incorrect. Being completely useless (as I don't fully understand them) I'm not too sure how to set it correctly.
    I'm trying to get records where the date is greater than the field DueDate minus Reminder (Due date being date field, Reminder being numerical field), so effectively inbetween DueDate-Reminder and DueDate, and where ConfirmedCourtCPO is Null.
    From this I'm hoping to make a report that is colour coded, ie five days before the Due date, the record is displayed with a red background, 5-10 days before, orange, and so on..........

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

    Re: Query problem (2000)

    Since your expression starts with Expr1:, I assume that you entered this in the Field: row of the query design grid. That is not the correct place for selection criteria. you must enter seleciton criteria in the Criteria row.
    - Make youre that DueDate is one of the columns.
    - Set the criteria for this column to < Date()+[Reminder]
    - Make sure that ConfirmedCourtCPO is one of the columns.
    - Set the criteria for this column to Is Null
    - If you don't want to see these columns, clear their Show check box.

    In the report, you must create an On Format event procedure for the Detail section, for example

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim lngDiff As Long
    lngDiff = Me.DueDate - Date
    Select Case lngDiff
    Case 0 To 4
    Me.Detail.BackColor = vbRed
    Case 5 To 9
    Me.Detail.BackColor = RGB(255, 128, 0) ' orange
    Case 10 To 19
    Me.Detail.BackColor = vbYellow
    Case Else
    Me.Detail.BackColor = vbWhite
    End Select
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    Oops, I get the error:
    Run-time Error '2427'
    You entered an expression that has no value, and then it highlights:
    lngDiff = Me.DueDate - Date

    Sorry I have no idea as to what IngDiff is. I presume it is deducting todays date from DateDue, and the following criterias set the colour.

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

    Re: Query problem (2000)

    Is DueDate a field in the record source of the report?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    Yep, DueDate refers to the query as detailed earlier in the post. This is based on 3 tables. TblMain,TblHearing,TblDirections. Within TblDirections is DueDate.

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

    Re: Query problem (2000)

    Please post a stripped down copy of your database. You know the procedure.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    Attached database..........

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

    Re: Query problem (2000)

    The query returns no records, so DueDate is empty. To avoid problems when there are no records, use the On No Data event of the report:

    Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There are no data to display.", vbInformation
    Cancel = True
    End Sub

    If you open the report from a form using DoCmd.OpenReport, canceling the report will cause error 2501. You can suppress the error message in the cmdReport_Click event procedure:

    ...
    Err_CmdReport_Click:
    If Not (Err = 2501) Then MsgBox Err.Description
    Resume Exit_CmdReport_Click
    End Sub

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    Thanks Hans, I didn't even realise this event was available.
    One other minor problem I'm having is that I have a calendar control on the form. Within the single click event of the field I enter:
    =opencalendar("") if its on the main form
    =opencalendar("FrmMainSub","Datefield") if it is on a sub form
    The problem is that I have a further subform that I'd like the calendar to pop up on.
    I've tried various variations but can seem to get nothing to work.
    The coding in the calendar is quite vast, hence I'm not posting it. But its in this <!post=post,387192>post<!/post>.

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

    Re: Query problem (2000)

    Please explain what you mean by "I have a further subform that I'd like the calendar to pop up on". Is this a second subform on thr main form, or a subform of a subform

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    Within FrmMain, is FrmHearingSub. Within FrmHearingSub is FrmDirectionsSub1. Its the FrmDirectionsSub1 that I'm interested in. The pop-up calendar works okay in both the previous forms.
    I presume the problem is because it is a subform within a subform, displayed on a main form.

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

    Re: Query problem (2000)

    The code for the calendar form in the post you referred to does not provide for subsubforms. You'd have to rewrite the code to get it to handle subsubforms.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    Thanks Hans, I've managed to adapt a calendar to work in all forms.
    My final question, I promise <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> , is can I apply the same principle referred to earlier with regards to changing the reports back color dependant on the date, in a form.
    What I wish to do is when the database starts up, the user is taken to a form that is based on the same query as the report. Thus displaying the same records. Do I place coding on the On Load event of the form to change the Text box background and do I have to refer to them on an individual basis, i.e. Me.URN, Me.DefSurname, etc.

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

    Re: Query problem (2000)

    You can't have different colours in a datasheet form. In a continuous form, there are several techniques to do so, if you do a search in this forum for continuous background color or something similar, you'll find threads discussing this, some with demos attached.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    Thanks, I'll do a search for this.
    Your help as always is appreciated!

Page 1 of 2 12 LastLast

Posting Permissions

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