Query when date is Blank

[nmapeso]

Hello, People,

I have a form which user enter date to query certain date fielname "EnterDate, with format/mask short date.
WHich is reference to Queries name" QryDateSent with a criteria
[Forms]![frmQuerysent]![EnterDate] on thefield name datesent. Somehow works okay when user enter ceratin date, but not sure how to code to query when dates are blank.

Private Sub Open_Click()
On Error GoTo Err_Open_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "maintblBankruptcy"
Me.Visible = False
stLinkCriteria = "[DateESent]=" & "#" & Me![EnterDate] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close , "frmQueryrecieved"
Exit_Open_Click:
Exit Sub

Err_Open_Click:
MsgBox Err.Description
Resume Exit_Open_Click

End Sub

 

[Remou]

You can use IsNull.

If IsNull(Me![EnterDate]) Then
     'What to do for no date
Else
    stDocName = "maintblBankruptcy"
    Me.Visible = False
    stLinkCriteria = "[DateESent]=" & "#" & Me![EnterDate] & "#"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close , "frmQueryrecieved"
End If

 

[Crowley16]

have a check to see if the date entry textbox is blank, if so, do not include any filtering in your query, i.e. pull up all records in that query...

--------------------
Procrastinate Now!

 

[CosmoKramer]

If the goal is to return all records (all dates) if the [EnterDate] textbox is left blank, then try something like this as the criteria in your query:

[Forms]![frmQuerysent]![EnterDate] OR [Forms]![frmQuerysent]![EnterDate] Is Null

Si hoc legere scis, nimis eruditionis habes

 

[nmapeso]

Thanks everyone for the response, but the goal is to query on only dates that are blank.

 

[Remou]

Perhaps

If IsNull(Me![EnterDate]) Then
    stLinkCriteria = "[DateESent]=Null" 
Else
    stLinkCriteria = "[DateESent]=" & "#" & Me![EnterDate] & "#"
End If 
    stDocName = "maintblBankruptcy"
    Me.Visible = False
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close , "frmQueryrecieved"

 

[nmapeso]

Thanks Remou...but still not working, it will show a blank form with blank field, should have contain at least 5 record with empty DateESent.

 

[PHV]

Replace this:
"[DateESent]=Null"
with this:
"[DateESent] Is Null"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

How about:
[tt]If IsNull(Me![EnterDate]) Then
stLinkCriteria = "IsNull([DateESent])"
... [/tt]

 

[nmapeso]

Thanks All, PHV that works...thanks