Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Red face Unanswered: SQL with Date Problem

    I would like to design a query that will pull all records where the “Expiration Date” is expiring 3 months from today. I am at a loss for how to do this other than prompt for user-entered dates.
    So far I have this SQL statement, which returns all Expiration Date from today’s date:


    SELECT tblIRBReviews.ApprovalDate, tblIRBReviews.ExpirationDate, FROM tblStudyInformation WHERE (((tblIRBReviews.ExpirationDate)>=Date()));
    Skharva

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: SQL with Date Problem

    Originally posted by Skharva
    I would like to design a query that will pull all records where the “Expiration Date” is expiring 3 months from today. I am at a loss for how to do this other than prompt for user-entered dates.
    So far I have this SQL statement, which returns all Expiration Date from today’s date:


    SELECT tblIRBReviews.ApprovalDate, tblIRBReviews.ExpirationDate, FROM tblStudyInformation WHERE (((tblIRBReviews.ExpirationDate)>=Date()));
    Do you want the records where the Expiration Date is within 3 months from today... or after 3 months from today... or exacty 3 months from today?... You can use the DateAdd() function...

    Within 3 months:
    WHERE [tblIRBReviews].[ExpirationDate] Between Date() and DateAdd("m",3,Date());

    After 3 months:
    WHERE [tblIRBReviews].[ExpirationDate] >= DateAdd("m",3,Date());

    Exactly 3 months:
    WHERE [tblIRBReviews].[ExpirationDate] = DateAdd("m",3,Date());

    HTH

  3. #3
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Cool Re: SQL with Date Problem

    Thanks a lot.

    I would also like to aleart user when expiration data comes near. Any idea how to accomplish this ?




    Originally posted by Trudi
    Do you want the records where the Expiration Date is within 3 months from today... or after 3 months from today... or exacty 3 months from today?... You can use the DateAdd() function...

    Within 3 months:
    WHERE [tblIRBReviews].[ExpirationDate] Between Date() and DateAdd("m",3,Date());

    After 3 months:
    WHERE [tblIRBReviews].[ExpirationDate] >= DateAdd("m",3,Date());

    Exactly 3 months:
    WHERE [tblIRBReviews].[ExpirationDate] = DateAdd("m",3,Date());

    HTH
    Skharva

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: SQL with Date Problem

    Originally posted by Skharva
    Thanks a lot.

    I would also like to aleart user when expiration data comes near. Any idea how to accomplish this ?
    There are many ways to do this... Were you thinking of prompting them from a form?.. or?

  5. #5
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Red face Re: SQL with Date Problem

    I can use the form and maybe send user email reminder..................Also, i have form with more then 5 optionbutton (RadioButton) and one cmdbutton "cmdSearch" i have wrote two if then statement but for some reason 2nd if statement never get execute ? Any idea why ? Thanks in advance

    Private Sub cmdSearch_Click()
    On Error GoTo ErrorHandler

    Dim strReport As String
    Dim strField As String
    Dim strWhere As String

    If Not (IsNull(Me.cboSAENo)) Then
    strReport = "frmSearchBySAENo"
    strField = "SAENo"
    strWhere = "[SAENo]=" & "'" & Me![cboSAENo] & "'"
    DoCmd.OpenForm strReport, , , strWhere
    ElseIf Not (IsNull(Me.CboStudyNo)) Then
    strReport = "frmSearchByStudyNo"
    strField = "StudyNo"
    strWhere = "[StudyNo]=" & "'" & Me![CboStudyNo] & "'"
    DoCmd.OpenForm strReport, , , strWhere
    End If


    ErrorHandler:
    If Err.Number > 0 Then
    MsgBox Error
    End If
    End Sub



    Originally posted by Trudi



    There are many ways to do this... Were you thinking of prompting them from a form?.. or?
    Skharva

  6. #6
    Join Date
    Feb 2004
    Posts
    199
    IsNull(Me.cboSAENo) - ?!
    what do you mean, is the "cboSAENo" Option Button? or what?
    did you group Option Buttons?

  7. #7
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Red face

    cboSAENO is the combo box and i have option button called optSAENo...whenever the OptSAENo is click cboSAENo is enable set to true. I have attached the form documents



    Originally posted by Kakha
    IsNull(Me.cboSAENo) - ?!
    what do you mean, is the "cboSAENo" Option Button? or what?
    did you group Option Buttons?
    Attached Files Attached Files
    Skharva

  8. #8
    Join Date
    Feb 2004
    Posts
    199
    I can't figure out your task by the image you posted, but I can tell you this:
    IsNull works on controls this way
    IsNull(ControlName)=True when Control has NULL value, that means - you erase or hasn't filled it.

    what is the row source of the ComboBoxes?

  9. #9
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    Originally posted by Kakha
    I can't figure out your task by the image you posted, but I can tell you this:
    IsNull works on controls this way
    IsNull(ControlName)=True when Control has NULL value, that means - you erase or hasn't filled it.

    what is the row source of the ComboBoxes?
    RowSource of ComboBoxes is based on Query

    I have tried this SQL statmen and it seems to be working

    If Not IsNull(Me.cboSAENo) Then
    strReport = "frmSearchBySAENo"
    strField = "SAENo"
    strWhere = "[SAENo]=" & "'" & Me![cboSAENo] & "'"
    DoCmd.OpenForm strReport, , , strWhere
    End If

    If Not IsNull(Me.CboStudyNo) Then
    strReport = "frmSearchByStudyNo"
    strField = "StudyNo"
    strWhere = "[StudyNo]=" & "'" & Me![CboStudyNo] & "'"
    DoCmd.OpenForm strReport, , , strWhere
    End If
    Skharva

Posting Permissions

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