# Thread: SQL with Date Problem

1. Registered User
Join Date
Sep 2003
Location
Raleigh, NC
Posts
183

## 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()));

2. Registered User
Join Date
Dec 2003
Location
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:

Exactly 3 months:

HTH

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

## 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:

Exactly 3 months:

HTH

4. Registered User
Join Date
Dec 2003
Location
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. Registered User
Join Date
Sep 2003
Location
Raleigh, NC
Posts
183

## 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?

6. Registered User
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. Registered User
Join Date
Sep 2003
Location
Raleigh, NC
Posts
183
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?

8. Registered User
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. Registered User
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

#### Posting Permissions

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