how to filter using date range but show all records if text box's are null

[sspreyer]

hi,
all
I m trying to make form which filters my records and generates a report


here's where I am

Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"

but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null

this part of code works perfect but below but I'm struggling to get the between in with the code

Like "*" & [Forms]![Form1]![Text6] & "*"

the code is in report record source

thanks in advance

shane

 

[David R]

So you're filtering from fields on the report itself? Because I thought you said you were using a form...

Anyway, try looking into IsNull(). You can drop that criteria entirely if it's a blank field.

 

[sspreyer]

hi thanks for quick reply yes I'm filtering the report on open depend on which value are in the textbox's on the form eg. textbox6 is start date and textbox8 is finish date but if these are empty would like to show all records on the report which my understand is done by the "like" but would also like to add between too




cheers


shane

 

[David R]

Are you using VBA behind the button to open the report? Drop the criteria in your query, it's not flexible enough for this.

This is aircode, but something like this should work:

IF (IsNull(Me.[Text6]) OR IsNull(Me.[Text8])) Then
    DoCmd.OpenReport "ReportName",acViewPreview
Else
    DoCmd.OpenReport "ReportName",acViewPreview,,"Between #" & Me![Text6] & "# And #" & Me.[Text8] & "#"
End If

Also your brain will thank you later if you will rename those fields to something meaningful like fldSearchStartDate.

 

[CJ_London]

or have a look at post #12 form this thread

http://www.access-programmers.co.uk/forums/showthread.php?p=1322420#post1322420

 

[sspreyer]

Thanks David I'll give a try and report back .


thanks CJ_London i'll have look into that thread


thanks again to you both

shane

 

[sspreyer]

hi all

have some that I need to adjust which I think will do the job I'm asking the code is

Private Sub Command4_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

'DO set the values in the next 3 lines.
strReport = "report1" 'Put your report name in these quotes.
strDateField = "[Date start]" 'Put your field name in the square brackets in these quotes.
lngView = acViewReport 'Use acViewNormal to print instead of preview.

'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub

but what I would like is if the txtstartdate and txtenddate have no value enter and are null to only show records that do not have value in
Date start field


thanks for your help everyone

I m getting there

shane

 

[CJ_London]

What are you asking?

 

[sspreyer]

What are you asking?

sorry if haven't explain well


if run the code above it opens myreport call "report1" and filters "date start" field depending on date range I put in txtstartdate and txtenddate but if these textbox's are empty I would like it to only show the records that are null (no date inputted in field) in "date start" field

as of the moment if I leave these textboxs empty, it shows all records


thanks again for your help

shane

 

[CJ_London]

You need to construct a filter which looks like this

[Date Start] is null