My Date Search isnt showing me null values for my date fields (1 Viewer)

[ashah08]

Hi All,

I am creating my log database and in that i want to perform date search for my task.
my date search functionality works fine, but i want to be able to display my complete and in-complete task for particular search please see the attached copy of my database and let me know where am i going wrong.

please help!!!

Thank You

 

[theDBguy]

Hi. Welcome to AWF!

Please correct me if I am wrong, but the way I see it in your code, you are requiring the user to enter a From and To dates for the search, is that correct? If so, what exactly was your question about? Are you asking how to make either From or To input dates as "optional?"

 

[MajP]

This may help

Work Smarter Not Harder; SQL and Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[ashah08]

In my tasksearchformbycombobox i have kept search by date to and from and search by task category and %complete.

all the functionalities are working fine in my form but when i search by date it only shows me complete task and it doesnt show me incomplete task when i try to generate report using command button60.


Note: try search using command button 60.

i am tired trying to resolve this issue. in my query which generates report i have even included is null field but that doesnt work. please give me a workaround for this.

Hi. Welcome to AWF!

Please correct me if I am wrong, but the way I see it in your code, you are requiring the user to enter a From and To dates for the search, is that correct? If so, what exactly was your question about? Are you asking how to make either From or To input dates as "optional?"

 

[ashah08]

In my tasksearchformbycombobox i have kept search by date to and from and search by task category and %complete.

all the functionalities are working fine in my form but when i search by date it only shows me complete task and it doesnt show me incomplete task when i try to generate report using command button60.


Note: try search using command button 60.

i am tired trying to resolve this issue. in my query which generates report i have even included is null field but that doesnt work. please give me a workaround for this.

 

[strive4peace]

hi ashah08 (what is your name?

I'm not sure what to look at ... currently viewing DateSearch but you didn't specify a form. By the way, "Null" means the ABSENCE of a value ... can't show what isn't there. If you want to find Null, as well as values, that needs to be in the criteria

 

[MickJav]

You could Have a look at my Task manager template There are a number of search forms maybe they will help you

Task Management Template

[center][url=https://databasedreams.createaforum.com/task-management/]Task Management Forum[/url][/center] I have created this like the microsoft template it has beed created as simple as possible to make it easier for you to edit it to th away you wish. NOTE: This project as will all...

databasedreams.createaforum.com

 

[ashah08]

Hi there,

Thank You for your prompt response.
My name is Akash

By null i mean i want to show fields as per my category.
my category consist of complete and in-complete task.

i am using parameterized query in which input for date search is through textboxes.
textboxes are dateFrom and DateTo

what i am trying to achieve using my report is to display both the categories complete and in-complete, when i generate a report through date search.

Form through which i am trying to achieve this functionality is named as tasksearchformbycombobox

My report is generated using Command button 60, this command button opens my report which based on query named " Copy Of TaskQuery22"

in my query i have included the functionality for date search using textboxes from from tasksearchformbycombobox but when i generate my report it only gives me values for completed task and it doesnt show me in-complete task.

in-complete task are those task where completed date is not mentioned.

Moral of the story - my date search works but it shows value where my datefrom and dateTo are enetered but if any of them are null or no values or field is empty then it doesn't show me on my report.

For Reference i am attaching my database please check


Please help me thank you

hi ashah08 (what is your name?

I'm not sure what to look at ... currently viewing DateSearch but you didn't specify a form. By the way, "Null" means the ABSENCE of a value ... can't show what isn't there. If you want to find Null, as well as values, that needs to be in the criteria

 

[June7]

My 2 cents - I never use dynamic parameterized query because of complication of handling parameters when no input is provided. I prefer VBA to build criteria and apply to form or report. Perhaps MickJav template has examples of that but here is another source to review http://allenbrowne.com/ser-62.html

 

[June7]

If you must use dynamic parameterized query, consider providing alternate values if user does not do input:

Between Nz([Forms]![TaskSearchbycombobox]![OrderDateFrom], #1/1/1900#) And Nz([Forms]![TaskSearchbycombobox]![OrderDateTo], #12/31/2199#)

 

[ashah08]

Hithere,

Thank You so much for your prompt reply.
I have used vba only to display my records on form.

but frankly i am new to ms access and i am using it because i am asked to do so or else i would prefer something else.

I dont know how to create report using Vba?

I have my vba code to display all records on my sub-form and it works fine but when i had to create report i couldn't find any alternative other than using Queries

can you guide me on how can i create report using Vba?


Thanks once again !!!

My 2 cents - I never use dynamic parameterized query because of complication of handling parameters when no input is provided. I prefer VBA to build criteria and apply to form or report. Perhaps MickJav template has examples of that but here is another source to review

Consider providing alternate values if user does not do input:

Between Nz([Forms]![TaskSearchbycombobox]![OrderDateFrom], #1/1/1900#) And Nz([Forms]![TaskSearchbycombobox]![OrderDateTo], #12/31/2199#)

 

[ashah08]

I tried using this but output still stays the same.

Thank You

If you must use dynamic parameterized query, consider providing alternate values if user does not do input:

Between Nz([Forms]![TaskSearchbycombobox]![OrderDateFrom], #1/1/1900#) And Nz([Forms]![TaskSearchbycombobox]![OrderDateTo], #12/31/2199#)

 

[MickJav]

My 2 cents - I never use dynamic parameterized query because of complication of handling parameters when no input is provided. I prefer VBA to build criteria and apply to form or report. Perhaps MickJav template has examples of that but here is another source to review http://allenbrowne.com/ser-62.html

Yes @June7 It builds a where string which is used to limit a queries output.

 

[ashah08]

Can Someone Please help and guide me on what should i do to create my report ?
I need to complete this by day end or else my manager will eat my head by asking me trillion questions on why its not done.

Please help me

 

[MickJav]

This is the code behind one of the Templates search Engines The IsNothing Is A Function I use I can post it if needed

On Error GoTo HandleErr

W = ""
    If Not IsNothing(Me![txt_Campaign]) Then W = " AND [CampaignName] like """ & "*" & Me![txt_Campaign] & "*" & """"
    If Not IsNothing(Me![Cbo_Status]) Then W = W & " AND [StatusID]=" & Me![Cbo_Status]
    'Launch Date Options
        If Not IsNothing(Me![txt_From]) Then
            If IsNothing(Me![txt_To]) Then 'Single Date
                W = W & " And [LaunchDate]=" & Format(Me![txt_From], "\#mm/dd/yyyy\#")
            Else   'Limit Between Dates
                W = W & " And int([LaunchDate]) BETWEEN " & Format(Me![txt_From], "\#mm/dd/yyyy\#") & " AND " & Format(Me![txt_To], "\#mm/dd/yyyy\#")
            End If
        End If
     'Deadline Date Options
        If Not IsNothing(Me![Txt_DeadlineFrom]) Then
            If IsNothing(Me![Txt_DeadlineTo]) Then 'Single Date
                W = W & " And [DeadlineDate]=" & Format(Me![Txt_DeadlineFrom], "\#mm/dd/yyyy\#")
            Else   'Limit Between Dates
                W = W & " And int([DeadlineDate]) BETWEEN " & Format(Me![Txt_DeadlineFrom], "\#mm/dd/yyyy\#") & " AND " & Format(Me![Txt_DeadlineTo], "\#mm/dd/yyyy\#")
            End If
        End If
    
    
        'Now Remove The First " And "
    If W <> "" Then
        W = Right(W, Len(W) - 5)
    End If
    Me![lst_Results].RowSource = "SELECT * FROM QryCampaignSearchResults WHERE " & W
    Me![lst_Results].Requery
    
HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case 2501 'Cancel = True
            Exit Sub
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select

 

[June7]

I dont know how to create report using Vba?
...
can you guide me on how can i create report using Vba?

VBA does not build report, it builds filter criteria and applies it to report when report is opened. You have been provided references with examples of that.

 

[MickJav]

Can Someone Please help and guide me on what should i do to create my report ?
I need to complete this by day end or else my manager will eat my head by asking me trillion questions on why its not done.

Please help me

The report is a different question I thought we were dealing with the search problem?

 

[MickJav]

I would suggest you download that template as It has a print screen as well the templates cover everything needed to get you going.

 

[ashah08]

Hi @June7 ,

i tried this to display records based on filter on my report but it displays only first record from the database.

DoCmd.OpenReport "DemoReportSubform", acViewPreview, , "ID = " & ID.

Can you tell me whats wrong ?
i want to display all the records based on my search criteria from the form.

VBA does not build report, it builds filter criteria and applies it to report when report is opened. You have been provided references with examples of that.

 

[ashah08]

it allows to download only 1 template per day and its not allowing me to download the template that i actually need.

I would suggest you download that template as It has a print screen as well the templates cover everything needed to get you going.