Date Range with one Null and one Not

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Scenario: The code below allows me to enter a date range and return only the
records that fit within the range. This works fine with both dates entered.

Problem: If either the txtAddDateBegin or txtAddDateEnd is left blank while
the other is filled in, I get all records.

What I want: Instead of all records I’d like code that will determine if
either of the fields is filled in and the other is not, then a message box to
appear. I’ve searched the knowledge base and tried a variety of if/then
combinations and can’t figure this out. Please HELP!

*************CODE *********

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #"
& txtAddDateEnd & "#"
End If

*************END CODE *********
 
Shel said:
Scenario: The code below allows me to enter a date range and return only the
records that fit within the range. This works fine with both dates entered.

Problem: If either the txtAddDateBegin or txtAddDateEnd is left blank while
the other is filled in, I get all records.

What I want: Instead of all records I’d like code that will determine if
either of the fields is filled in and the other is not, then a message box to
appear. I’ve searched the knowledge base and tried a variety of if/then
combinations and can’t figure this out. Please HELP!

*************CODE *********

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #"
& txtAddDateEnd & "#"
End If

*************END CODE *********
Click to expand...


Wellll, you could use Else to do that ...

If Not IsNull(txtAddDateBegin . . . Then
strWhere = strWhere & " . . .
Else
MsgBox "Missing start or end date
End If"
 
Marshall, thanks for the suggestion. It partially works. I get my message box
but the secondary form still opens because my open form code is separate. I
can see the problem but I can't think how to fix it. Here's what I have now:
Any clues?

'Build the wherecondition to use in the Open Form method.
If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation,
"No Search Criteria"
End If

'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL
 
Shel,

You may want to add something like this to not only check for Nul values,
but also for start dates that are greater than the end date....

--------------------
If [txtAddDateBegin] > [txtAddDateEnd] Then

MsgBox "You entered a Start Date that is later than the end date." &
vbCrLf & " " & vbCrLf & "Please select an earlier Start Date.",
vbExclamation, "Error..."

Exit Sub

ElseIf Not IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter an End Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And Not IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start and End Date to begin search",
vbExclamation, "Error..."

Exit Sub

End If
 
Paul,

Thanks that's a great suggestion. Do you have any idea as to how I can keep
my secondary form from opening if the criteria is not met? Right now the
secondary form opens anyway because my Open Form code is separate from my
criteria code. This is because I have multiple fields included in my search
form. See the code below for what I mean.

*****CODE******
'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'*" & txtSSN & "*'"
End If

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
End If

'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

*****END CODE*****

Paul B. said:
Shel,

You may want to add something like this to not only check for Nul values,
but also for start dates that are greater than the end date....

--------------------
If [txtAddDateBegin] > [txtAddDateEnd] Then

MsgBox "You entered a Start Date that is later than the end date." &
vbCrLf & " " & vbCrLf & "Please select an earlier Start Date.",
vbExclamation, "Error..."

Exit Sub

ElseIf Not IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter an End Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And Not IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start and End Date to begin search",
vbExclamation, "Error..."

Exit Sub

End If

--------------------------

Cheers


Shel said:
Scenario: The code below allows me to enter a date range and return only the
records that fit within the range. This works fine with both dates entered.

Problem: If either the txtAddDateBegin or txtAddDateEnd is left blank while
the other is filled in, I get all records.

What I want: Instead of all records I’d like code that will determine if
either of the fields is filled in and the other is not, then a message box to
appear. I’ve searched the knowledge base and tried a variety of if/then
combinations and can’t figure this out. Please HELP!

*************CODE *********

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #"
& txtAddDateEnd & "#"
End If

*************END CODE *********
Click to expand...
Click to expand...
 
Shel said:
Marshall, thanks for the suggestion. It partially works. I get my message box
but the secondary form still opens because my open form code is separate. I
can see the problem but I can't think how to fix it. Here's what I have now:
Any clues?

'Build the wherecondition to use in the Open Form method.
If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation,
"No Search Criteria"
End If

'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL
Click to expand...


As Paul demonstrates, just add an Exit Sub statement after
the MsgBox.
 
It looks to me as though you want to run the first two parts of code
regardless of the date part. Is this right? If so, simply insert an 'Exit
Sub' line in your code...
-----
If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
Exit Sub 'This exits the function and your openform code does not
run
End If



Cheers



Shel said:
Paul,

Thanks that's a great suggestion. Do you have any idea as to how I can keep
my secondary form from opening if the criteria is not met? Right now the
secondary form opens anyway because my Open Form code is separate from my
criteria code. This is because I have multiple fields included in my search
form. See the code below for what I mean.

*****CODE******
'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'*" & txtSSN & "*'"
End If

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
End If

'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

*****END CODE*****

Paul B. said:
Shel,

You may want to add something like this to not only check for Nul values,
but also for start dates that are greater than the end date....

--------------------
If [txtAddDateBegin] > [txtAddDateEnd] Then

MsgBox "You entered a Start Date that is later than the end date." &
vbCrLf & " " & vbCrLf & "Please select an earlier Start Date.",
vbExclamation, "Error..."

Exit Sub

ElseIf Not IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter an End Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And Not IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start and End Date to begin search",
vbExclamation, "Error..."

Exit Sub

End If

--------------------------

Cheers


Shel said:
Scenario: The code below allows me to enter a date range and return only the
records that fit within the range. This works fine with both dates entered.

Problem: If either the txtAddDateBegin or txtAddDateEnd is left blank while
the other is filled in, I get all records.

What I want: Instead of all records I’d like code that will determine if
either of the fields is filled in and the other is not, then a message box to
appear. I’ve searched the knowledge base and tried a variety of if/then
combinations and can’t figure this out. Please HELP!

*************CODE *********

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #"
& txtAddDateEnd & "#"
End If

*************END CODE *********
Click to expand...
Click to expand...
Click to expand...
 
Paul, yes you are correct, and I did what you suggested, and it worked. If I
enter one date without the other my message appears and the secondary form
does not open. However, now the rest of my form does not work properly. If I
enter criteria into any other field on the form the secondary form will not
open. Instead I get my message box. :( Please help! Anyone... if you can.

Paul B. said:
It looks to me as though you want to run the first two parts of code
regardless of the date part. Is this right? If so, simply insert an 'Exit
Sub' line in your code...
-----
If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
Exit Sub 'This exits the function and your openform code does not
run
End If



Cheers



Shel said:
Paul,

Thanks that's a great suggestion. Do you have any idea as to how I can keep
my secondary form from opening if the criteria is not met? Right now the
secondary form opens anyway because my Open Form code is separate from my
criteria code. This is because I have multiple fields included in my search
form. See the code below for what I mean.

*****CODE******
'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'*" & txtSSN & "*'"
End If

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
End If

'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

*****END CODE*****

Paul B. said:
Shel,

You may want to add something like this to not only check for Nul values,
but also for start dates that are greater than the end date....

--------------------
If [txtAddDateBegin] > [txtAddDateEnd] Then

MsgBox "You entered a Start Date that is later than the end date." &
vbCrLf & " " & vbCrLf & "Please select an earlier Start Date.",
vbExclamation, "Error..."

Exit Sub

ElseIf Not IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter an End Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And Not IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start and End Date to begin search",
vbExclamation, "Error..."

Exit Sub

End If

--------------------------

Cheers


:

Scenario: The code below allows me to enter a date range and return only the
records that fit within the range. This works fine with both dates entered.

Problem: If either the txtAddDateBegin or txtAddDateEnd is left blank while
the other is filled in, I get all records.

What I want: Instead of all records I’d like code that will determine if
either of the fields is filled in and the other is not, then a message box to
appear. I’ve searched the knowledge base and tried a variety of if/then
combinations and can’t figure this out. Please HELP!

*************CODE *********

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #"
& txtAddDateEnd & "#"
End If

*************END CODE *********
Click to expand...
Click to expand...
Click to expand...
 
Shel,

Can you post the entire code....and I'll have another look at it.

Cheers


Shel said:
Paul, yes you are correct, and I did what you suggested, and it worked. If I
enter one date without the other my message appears and the secondary form
does not open. However, now the rest of my form does not work properly. If I
enter criteria into any other field on the form the secondary form will not
open. Instead I get my message box. :( Please help! Anyone... if you can.

Paul B. said:
It looks to me as though you want to run the first two parts of code
regardless of the date part. Is this right? If so, simply insert an 'Exit
Sub' line in your code...
-----
If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
Exit Sub 'This exits the function and your openform code does not
run
End If



Cheers



Shel said:
Paul,

Thanks that's a great suggestion. Do you have any idea as to how I can keep
my secondary form from opening if the criteria is not met? Right now the
secondary form opens anyway because my Open Form code is separate from my
criteria code. This is because I have multiple fields included in my search
form. See the code below for what I mean.

*****CODE******
'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'*" & txtSSN & "*'"
End If

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
End If

'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

*****END CODE*****

:

Shel,

You may want to add something like this to not only check for Nul values,
but also for start dates that are greater than the end date....

--------------------
If [txtAddDateBegin] > [txtAddDateEnd] Then

MsgBox "You entered a Start Date that is later than the end date." &
vbCrLf & " " & vbCrLf & "Please select an earlier Start Date.",
vbExclamation, "Error..."

Exit Sub

ElseIf Not IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter an End Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And Not IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start and End Date to begin search",
vbExclamation, "Error..."

Exit Sub

End If

--------------------------

Cheers


:

Scenario: The code below allows me to enter a date range and return only the
records that fit within the range. This works fine with both dates entered.

Problem: If either the txtAddDateBegin or txtAddDateEnd is left blank while
the other is filled in, I get all records.

What I want: Instead of all records I’d like code that will determine if
either of the fields is filled in and the other is not, then a message box to
appear. I’ve searched the knowledge base and tried a variety of if/then
combinations and can’t figure this out. Please HELP!

*************CODE *********

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #"
& txtAddDateEnd & "#"
End If

*************END CODE *********
Click to expand...
Click to expand...
Click to expand...
 
Sure, Here is the code. Thanks Again!

Private Sub btnOK_Click()

Dim ctl As Access.Control
Dim fGotOne As Boolean

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox _
Or ctl.ControlType = acCheckBox _
Or ctl.ControlType = acComboBox _
Or ctl.ControlType = acListBox _
Then
If Not IsNull(ctl.Value) Then
fGotOne = True
Exit For
End If
End If

Next ctl
If fGotOne Then


Dim strSQL As String
Dim strWhere As String

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'*" & txtSSN & "*'"
End If

If Not IsNull(txtFName) Then
strWhere = strWhere & " AND TxPrFName Like " & "'*" & txtFName & "*'"
End If

If Not IsNull(txtMidInit) Then
strWhere = strWhere & " AND TxPrMI Like " & "'" & txtMidInit & "'"
End If

If Not IsNull(txtLName) Then
strWhere = strWhere & " AND TxPrLName Like " & "'*" & txtLName & "*'"
End If

If Not IsNull(txtAddress) Then
strWhere = strWhere & " AND Address Like " & "'*" & txtAddress & "*'"
End If

If Not IsNull(txtCity) Then
strWhere = strWhere & " AND City Like " & "'*" & txtCity & "*'"
End If

If Not IsNull(txtState) Then
strWhere = strWhere & " AND State Like " & "'" & txtState & "'"
End If

If Not IsNull(txtZip) Then
strWhere = strWhere & " AND Zip Like " & "'*" & txtZip & "*'"
End If

If Not IsNull(txtEmployerID) Then
strWhere = strWhere & " AND EmployerID Like " & "'*" & txtEmployerID &
"*'"
End If

If Not IsNull(txtRoutingNo) Then
strWhere = strWhere & " AND BankRoutNo Like " & "'*" & txtRoutingNo & "*'"
End If

If Not IsNull(txtAccountNo) Then
strWhere = strWhere & " AND BankAcctNo Like " & "'*" & txtAccountNo & "*'"
End If

If Not IsNull(chkProtester) Then
strWhere = strWhere & " AND TaxProtester = " & chkProtester
End If

If Not IsNull(chkDeceased) Then
strWhere = strWhere & " AND Deceased = " & chkDeceased
End If

If Not IsNull(txtComments) Then
strWhere = strWhere & " AND TxPrComments Like " & "'*" & txtComments &
"*'"
End If

If Not IsNull(txtAddUser) Then
strWhere = strWhere & " AND AddUser Like " & "'" & txtAddUser & "'"
End If

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
Exit Sub
End If

If Not IsNull(txtUpdateUser) Then
strWhere = strWhere & " AND UpUser Like " & "'" & txtUpdateUser & "'"
End If


'Trim the leading "AND" strWhere and store the new string in strSQL.
If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If


'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

'Set the text boxes back to null.
Clear_Controls:
txtTxPrID = Null
txtSSN = Null
txtFName = Null
txtMidInit = Null
txtLName = Null
txtAddress = Null
txtCity = Null
txtState = Null
txtZip = Null
txtEmployerID = Null
txtRoutingNo = Null
txtAccountNo = Null
chkProtester = Null
chkDeceased = Null
txtComments = Null
txtAddUser = Null
txtAddDateBegin = Null
txtAddDateEnd = Null
txtUpdateUser = Null
txtUpdateDateBegin = Null
txtUpdateDateEnd = Null
Else
DoCmd.Beep
MsgBox "Please enter search criteria or click Cancel. ",
vbInformation, "No Search Criteria"
DoCmd.CancelEvent
End If
End Sub


Paul B. said:
Shel,

Can you post the entire code....and I'll have another look at it.

Cheers


Shel said:
Paul, yes you are correct, and I did what you suggested, and it worked. If I
enter one date without the other my message appears and the secondary form
does not open. However, now the rest of my form does not work properly. If I
enter criteria into any other field on the form the secondary form will not
open. Instead I get my message box. :( Please help! Anyone... if you can.

Paul B. said:
It looks to me as though you want to run the first two parts of code
regardless of the date part. Is this right? If so, simply insert an 'Exit
Sub' line in your code...
-----
If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
Exit Sub 'This exits the function and your openform code does not
run
End If



Cheers



:

Paul,

Thanks that's a great suggestion. Do you have any idea as to how I can keep
my secondary form from opening if the criteria is not met? Right now the
secondary form opens anyway because my Open Form code is separate from my
criteria code. This is because I have multiple fields included in my search
form. See the code below for what I mean.

*****CODE******
'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'*" & txtSSN & "*'"
End If

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
End If

'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

*****END CODE*****

:

Shel,

You may want to add something like this to not only check for Nul values,
but also for start dates that are greater than the end date....

--------------------
If [txtAddDateBegin] > [txtAddDateEnd] Then

MsgBox "You entered a Start Date that is later than the end date." &
vbCrLf & " " & vbCrLf & "Please select an earlier Start Date.",
vbExclamation, "Error..."

Exit Sub

ElseIf Not IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter an End Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And Not IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start and End Date to begin search",
vbExclamation, "Error..."

Exit Sub

End If

--------------------------

Cheers


:

Scenario: The code below allows me to enter a date range and return only the
records that fit within the range. This works fine with both dates entered.

Problem: If either the txtAddDateBegin or txtAddDateEnd is left blank while
the other is filled in, I get all records.

What I want: Instead of all records I’d like code that will determine if
either of the fields is filled in and the other is not, then a message box to
appear. I’ve searched the knowledge base and tried a variety of if/then
combinations and can’t figure this out. Please HELP!

*************CODE *********

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #"
& txtAddDateEnd & "#"
End If

*************END CODE *********
Click to expand...
Click to expand...
Click to expand...
 
So it looks like you need to put the date 'IF' statement at the begining of
your code. I think what you want is to validate the dates first, then run the
remainder of the code. This way, if the user doesn't select the right dates,
the code exits right away. If the dates are ok, then it should carry on as
normal. Other than that, I don't see any other reason this shouldn't work
(assuming it worked before putting the Exit Sub statement in).



:

------------------------------
Private Sub btnOK_Click()

Dim ctl As Access.Control
Dim fGotOne As Boolean

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox _
Or ctl.ControlType = acCheckBox _
Or ctl.ControlType = acComboBox _
Or ctl.ControlType = acListBox _
Then
If Not IsNull(ctl.Value) Then
fGotOne = True
Exit For
End If
End If

Next ctl
If fGotOne Then

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"

Else

Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"

Exit Sub

End If

Dim strSQL As String
Dim strWhere As String

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If
-----------------------------------------

Does this help?

Cheers



Paul B. said:
Shel,

Can you post the entire code....and I'll have another look at it.

Cheers


Shel said:
Paul, yes you are correct, and I did what you suggested, and it worked. If I
enter one date without the other my message appears and the secondary form
does not open. However, now the rest of my form does not work properly. If I
enter criteria into any other field on the form the secondary form will not
open. Instead I get my message box. :( Please help! Anyone... if you can.

:

It looks to me as though you want to run the first two parts of code
regardless of the date part. Is this right? If so, simply insert an 'Exit
Sub' line in your code...
-----
If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
Exit Sub 'This exits the function and your openform code does not
run
End If



Cheers



:

Paul,

Thanks that's a great suggestion. Do you have any idea as to how I can keep
my secondary form from opening if the criteria is not met? Right now the
secondary form opens anyway because my Open Form code is separate from my
criteria code. This is because I have multiple fields included in my search
form. See the code below for what I mean.

*****CODE******
'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'*" & txtSSN & "*'"
End If

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "#
and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search
Criteria"
End If

'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

*****END CODE*****

:

Shel,

You may want to add something like this to not only check for Nul values,
but also for start dates that are greater than the end date....

--------------------
If [txtAddDateBegin] > [txtAddDateEnd] Then

MsgBox "You entered a Start Date that is later than the end date." &
vbCrLf & " " & vbCrLf & "Please select an earlier Start Date.",
vbExclamation, "Error..."

Exit Sub

ElseIf Not IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter an End Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And Not IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start Date to begin search", vbExclamation,
"Error..."

Exit Sub

ElseIf IsNull([txtAddDateBegin]) And IsNull([txtAddDateEnd]) Then

MsgBox "You must enter a Start and End Date to begin search",
vbExclamation, "Error..."

Exit Sub

End If

--------------------------

Cheers


:

Scenario: The code below allows me to enter a date range and return only the
records that fit within the range. This works fine with both dates entered.

Problem: If either the txtAddDateBegin or txtAddDateEnd is left blank while
the other is filled in, I get all records.

What I want: Instead of all records I’d like code that will determine if
either of the fields is filled in and the other is not, then a message box to
appear. I’ve searched the knowledge base and tried a variety of if/then
combinations and can’t figure this out. Please HELP!

*************CODE *********

If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #"
& txtAddDateEnd & "#"
End If

*************END CODE *********
Click to expand...
Click to expand...
Click to expand...
 
Back
Top