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

ashah08

New member
Local time
Yesterday, 19:21
Joined
May 1, 2020
Messages
13
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
 

Attachments

theDBguy

I’m here to help
Local time
Yesterday, 16:21
Joined
Oct 29, 2018
Messages
10,005
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

You've got your good things, and you've got mine.
Local time
Yesterday, 19:21
Joined
May 21, 2018
Messages
3,103
This may help
 

ashah08

New member
Local time
Yesterday, 19:21
Joined
May 1, 2020
Messages
13
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

New member
Local time
Yesterday, 19:21
Joined
May 1, 2020
Messages
13
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

AWF VIP
Local time
Yesterday, 18:21
Joined
Apr 3, 2020
Messages
469
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

AWF VIP
Local time
Today, 00:21
Joined
Nov 28, 2005
Messages
1,784
You could Have a look at my Task manager template There are a number of search forms maybe they will help you
 

ashah08

New member
Local time
Yesterday, 19:21
Joined
May 1, 2020
Messages
13
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



 

Attachments

June7

AWF VIP
Local time
Yesterday, 15:21
Joined
Mar 9, 2014
Messages
2,924
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
 
Last edited:

June7

AWF VIP
Local time
Yesterday, 15:21
Joined
Mar 9, 2014
Messages
2,924
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

New member
Local time
Yesterday, 19:21
Joined
May 1, 2020
Messages
13
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

New member
Local time
Yesterday, 19:21
Joined
May 1, 2020
Messages
13
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

AWF VIP
Local time
Today, 00:21
Joined
Nov 28, 2005
Messages
1,784
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

New member
Local time
Yesterday, 19:21
Joined
May 1, 2020
Messages
13
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

AWF VIP
Local time
Today, 00:21
Joined
Nov 28, 2005
Messages
1,784
This is the code behind one of the Templates search Engines The IsNothing Is A Function I use I can post it if needed
Code:
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

AWF VIP
Local time
Yesterday, 15:21
Joined
Mar 9, 2014
Messages
2,924
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

AWF VIP
Local time
Today, 00:21
Joined
Nov 28, 2005
Messages
1,784
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

AWF VIP
Local time
Today, 00:21
Joined
Nov 28, 2005
Messages
1,784
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

New member
Local time
Yesterday, 19:21
Joined
May 1, 2020
Messages
13
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

New member
Local time
Yesterday, 19:21
Joined
May 1, 2020
Messages
13
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom