Access Query to allow user to enter criteria for anyone or more values on a form (1 Viewer)

abette

Member
Local time
Today, 09:10
Joined
Feb 27, 2021
Messages
85
Greetings,
I have a Form in my Access database that is based on a query.
My form consists of multiple textbox controls and the goal is to allow the user to enter either the date range start and end values, and/or Fund Year and/or Fund Name Listing.
1621890871388.png


My query selects records from my table with the following criteria for the controls listed on my form. When the user clicks the Search button it opens a form whose record source is based on the query.
1621890839372.png


My query works if I enter values for the Date Processed fields or values for everything, however, if I enter just the Fund Year and/or Fund Name Listing it's not running. I modified my query and removed the criteria for the Date Processed field and it worked for the Fund year and Fund Name Listing. When I return the criteria for the Date Processed field it's does not work.
What does Access see when nothing is entered in a date field? I need to use Between/And because I want to search within the range of date values entered. Any ideas on how I can get this to work? I am dumbfounded by this. Obviously, the value of null or maybe zeros (if nothing entered for the date) is being interpreted as criteria when the query runs. So it's looking for blanks or zeros.
I am so frustrated with this and appreciate any assistance.
Thank you for your time and help,
Ann Marie
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:10
Joined
Feb 19, 2013
Messages
16,553
Providing the sql to your query would help but the general answer would be along the lines of

where (myfield =forms!myform!mycontrol or
forms!myform!mycontrol is null)
 

abette

Member
Local time
Today, 09:10
Joined
Feb 27, 2021
Messages
85
Hi there - Thank you for your quick reply!
Here's the SQL version of my query for your review.

SELECT tblClaimsWorkingTable.ID, tblClaimsWorkingTable.ClaimNumber, tblClaimsWorkingTable.AuthNumber, tblClaimsWorkingTable.DateOfServiceFrom, tblClaimsWorkingTable.DateOfServiceTo, tblClaimsWorkingTable.ProviderCIMID, tblClaimsWorkingTable.PayeeName, tblClaimsWorkingTable.FEIN_SSN, tblClaimsWorkingTable.DateReceived, tblClaimsWorkingTable.MemberID, tblClaimsWorkingTable.YouthName, tblClaimsWorkingTable.DxCode, tblClaimsWorkingTable.DxCode2, tblClaimsWorkingTable.DxCode3, tblClaimsWorkingTable.DxCode4, tblClaimsWorkingTable.ServiceCode, tblClaimsWorkingTable.LineNumber, tblClaimsWorkingTable.UnitsRequested, tblClaimsWorkingTable.AmountBilled, tblClaimsWorkingTable.ClaimStatus, tblClaimsWorkingTable.DenialReasonComments, tblClaimsWorkingTable.ProcessedBy, tblClaimsWorkingTable.DateProcessed, Nz([Forms]![frmClaimsReviewQueueMAIN]![FundYearCombo],[tblClaimsWorkingTable].[ClaimsFundYear]) AS FundYear, Nz([Forms]![frmClaimsReviewQueueMAIN]![FundNameCombo],[tblClaimsWorkingTable].[ClaimsFundType]) AS FundType, tblClaimsWorkingTable.AttestationCheck
FROM tblClaimsWorkingTable
WHERE (((tblClaimsWorkingTable.DateProcessed) Between [Forms]![frmClaimsReviewQueueMAIN]![StartDateRange] And [Forms]![frmClaimsReviewQueueMAIN]![EndDateRange]) AND ((Nz([Forms]![frmClaimsReviewQueueMAIN]![FundYearCombo],[tblClaimsWorkingTable].[ClaimsFundYear]))=[tblClaimsWorkingTable]![ClaimsFundYear]) AND ((Nz([Forms]![frmClaimsReviewQueueMAIN]![FundNameCombo],[tblClaimsWorkingTable].[ClaimsFundType]))=[tblClaimsWorkingTable]![ClaimsFundType]) AND ((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0));

Thank you again,
Ann Marie
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:10
Joined
Feb 19, 2013
Messages
16,553
Ok so you need to decide what you want to happen if for example only one field is completed for your between range

I’m on my phone so will paraphrase to cut down the typing

dateprocessed between nz(formstart,#01/01/1900#) and nz(formend,date())

might be a solution
 

abette

Member
Local time
Today, 09:10
Joined
Feb 27, 2021
Messages
85
Hi there -
I entered the following criteria for the Date Processed field of my query
Between Nz([Forms]![frmClaimsReviewQueueMAIN]![StartDateRange],#1/1/1900#) And Nz([Forms]![frmClaimsReviewQueueMAIN]![EndDateRange],#1/1/1900#)
Then I entered the following on my form and clicked Search but no records were generated. I should have 4 records returned so that didn't work.
I cleared my search criteria and tried entering Fund Year = FY21 expecting 9 records but nothing came back. Ugh...
1621895475394.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:10
Joined
Feb 19, 2002
Messages
42,970
Your form should ensure that if EITHER start or end date is entered, then both start and end dates have values. Use whatever logic makes sense for populating the empty field. Then the criteria will be something like:

Where (SomeDate Between Forms!YourForm!StartDT AND Forms!YourForm!EndDT OR Forms!YourForm!StartDT Is Null)
AND (FundYear = Forms!YourForm!cboFundYear OR Forms!YourForm!cboFundYear Is Null)
AND (FundName = Forms!YourForm!cboFundID OR Forms!YourForm!cboFundID Is Null)

Notice that each compound condition is enclosed in parenthesis because both AND and OR operators are used in the complex expression and like all boolean logic parenthesis are used to enforce the order of operation. By including an "OR something Is Null) in each expression, we allow the item to be optional. I.e. the user can enter one, two, or three of the values to filter the search. Your code in the button click needs to ensure that at least one of the three options is provided unless you want to allow the user to return ALL records (not recommended).

If you don't want to provide start/end dates to fill in the missing values the expression becomes more complex because you have two additional options to check for and the logic of the Between needs to change to ignore the items missing one date or the other.
 
Last edited:

abette

Member
Local time
Today, 09:10
Joined
Feb 27, 2021
Messages
85
Hi Pat - I am not sure I understand this syntax. Is there a parenthesis missing?
Where (SomeDate Between Forms!YourForm!StartDT) AND Forms!YourForm!EndDT OR Forms!YourForm!StartDT Is Null)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:10
Joined
May 7, 2009
Messages
19,169
Code:
SELECT tblClaimsWorkingTable.ID, tblClaimsWorkingTable.ClaimNumber,
tblClaimsWorkingTable.AuthNumber, tblClaimsWorkingTable.DateOfServiceFrom,
tblClaimsWorkingTable.DateOfServiceTo, tblClaimsWorkingTable.ProviderCIMID,
tblClaimsWorkingTable.PayeeName, tblClaimsWorkingTable.FEIN_SSN,
tblClaimsWorkingTable.DateReceived, tblClaimsWorkingTable.MemberID,
tblClaimsWorkingTable.YouthName, tblClaimsWorkingTable.DxCode,
tblClaimsWorkingTable.DxCode2, tblClaimsWorkingTable.DxCode3,
tblClaimsWorkingTable.DxCode4, tblClaimsWorkingTable.ServiceCode,
tblClaimsWorkingTable.LineNumber, tblClaimsWorkingTable.UnitsRequested,
tblClaimsWorkingTable.AmountBilled, tblClaimsWorkingTable.ClaimStatus,
tblClaimsWorkingTable.DenialReasonComments, tblClaimsWorkingTable.ProcessedBy,
tblClaimsWorkingTable.DateProcessed,
Nz([Forms]![frmClaimsReviewQueueMAIN]![FundYearCombo],[tblClaimsWorkingTable].[ClaimsFundYear]) AS FundYear,
Nz([Forms]![frmClaimsReviewQueueMAIN]![FundNameCombo],[tblClaimsWorkingTable].[ClaimsFundType]) AS FundType,
tblClaimsWorkingTable.AttestationCheck
FROM tblClaimsWorkingTable
WHERE ((tblClaimsWorkingTable.DateProcessed) Between Nz([Forms]![frmClaimsReviewQueueMAIN]![StartDateRange],#1/1/1900#) And
Nz([Forms]![frmClaimsReviewQueueMAIN]![EndDateRange], #12/31/2999#)) AND
[tblClaimsWorkingTable].[ClaimsFundYear] = iif(Isnull([Forms]![frmClaimsReviewQueueMAIN]![FundYearCombo]),[tblClaimsWorkingTable].[ClaimsFundYear]), [Forms]![frmClaimsReviewQueueMAIN]![FundYearCombo])
 AND [tblClaimsWorkingTable].[ClaimsFundType] = Iif(IsNull([Forms]![frmClaimsReviewQueueMAIN]![FundNameCombo]),[tblClaimsWorkingTable].[ClaimsFundType]), [tblClaimsWorkingTable]![ClaimsFundType]
 , [Forms]![frmClaimsReviewQueueMAIN]![FundNameCombo]) AND
 tblClaimsWorkingTable.AttestationCheck = False;
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:10
Joined
Jul 9, 2003
Messages
16,244
The SQL Simplified:-

Code:
SELECT ID, ClaimNumber,
AuthNumber, DateOfServiceFrom,
DateOfServiceTo, ProviderCIMID,
PayeeName, FEIN_SSN,
DateReceived, MemberID,
YouthName, DxCode,
DxCode2, DxCode3,
DxCode4, ServiceCode,
LineNumber, UnitsRequested,
AmountBilled, ClaimStatus,
DenialReasonComments, ProcessedBy,
DateProcessed,
Nz([Forms]![frmClaimsReviewQueueMAIN]![FundYearCombo],[tblClaimsWorkingTable].[ClaimsFundYear]) AS FundYear,
Nz([Forms]![frmClaimsReviewQueueMAIN]![FundNameCombo],[tblClaimsWorkingTable].[ClaimsFundType]) AS FundType,
AttestationCheck
FROM tblClaimsWorkingTable
WHERE ((DateProcessed) Between Nz([Forms]![frmClaimsReviewQueueMAIN]![StartDateRange],#1/1/1900#) And
Nz([Forms]![frmClaimsReviewQueueMAIN]![EndDateRange], #12/31/2999#)) AND
[tblClaimsWorkingTable].[ClaimsFundYear] = iif(Isnull([Forms]![frmClaimsReviewQueueMAIN]![FundYearCombo]),[tblClaimsWorkingTable].[ClaimsFundYear]), [Forms]![frmClaimsReviewQueueMAIN]![FundYearCombo])
AND [tblClaimsWorkingTable].[ClaimsFundType] = Iif(IsNull([Forms]![frmClaimsReviewQueueMAIN]![FundNameCombo]),[tblClaimsWorkingTable].[ClaimsFundType]), [tblClaimsWorkingTable]![ClaimsFundType]
, [Forms]![frmClaimsReviewQueueMAIN]![FundNameCombo]) AND
AttestationCheck = False;


And you do it like this in a Module:-

Take Out the Tables - Nifty Access​

 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:10
Joined
Jul 9, 2003
Messages
16,244
I'd also put the WHERE Clause in a separate function and add it back in afterwards.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:10
Joined
Feb 28, 2001
Messages
26,996
Actually, I might do something different. I would build the SQL to return the data elements you want with proper FROM clause, and if you need it sorted, a proper ORDER BY clause. But I would have no WHERE clause.

Instead, I would set up a FILTER clause - which is a WHERE clause minus the word "WHERE" and would then have four sub-clauses.

in the _Current routine:

Code:
If Nz( [FundYearControl], "" ) <> "" Then
    FundYearString = " ( 1 = 1 ) "
Else
    FundYearString = "[FundYear] = " & [FundYearControl] & " "
End If

If Nz( [FundNameControl], "" ) <> "" Then
    FundNameString = " (1 = 1 )"
Else
    FundNameString = "[FundName] = '" & [FundNameControl] & "' AND "
End If

If IsDate( [StartDateControl] ) Then
    StartDateString = "[DateProcessed] >= #" & [StartDateControl] & "# "
Else
    StartDateString = " ( 1 = 1 ) "
End If

If IsDate( [EndDateControl] ) Then
    EndDateString = "[DateProcessed] <= #" & [EndDateControl] & "# "
Else
    EndDateString = " ( 1 = 1 ) "
End if

Then you would repeat one part of the above code in the LostFocus routine - the part where you did the test for the [FundYearControl] would be in the FundYearControl_LostFocus routine. After that, you rebuild your filter string:

Code:
FilterString = FundYearString & " AND " & FundNameString & " AND " & StartDateString & " AND " EndDateString
Me.Filter = FilterString
Me.FilterOn = TRUE
Me.Requery

Now, in English: Build components for the four controls you wanted to test. If the control is empty then use " (1=1) " - which is a common trick to assure that for the given component of the filter, you don't filter anything. But if the control is NOT empty, build the appropriate single-value test. For starters, everything should be emptied out. Take out ONE of the four things I showed and associate it with the LostFocus routine for the control that it goes with. Then I would rebuild the four-part filter string (which would contain the (1=1) "dummy" sequence for the blank controls. Load that to the form's filter, turn on the filter, and do a .Requery.

I will leave it to you to decide where these things would best go. Further note: I don't recommend using the CHANGE event for these controls, though you COULD do so. The form will work harder when using a _Change event because it fires for every keystroke. LostFocus waits until you exit the field.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:10
Joined
Sep 21, 2011
Messages
14,041
Hi Pat - I am not sure I understand this syntax. Is there a parenthesis missing?
Where (SomeDate Between Forms!YourForm!StartDT) AND Forms!YourForm!EndDT OR Forms!YourForm!StartDT Is Null)
Yes. I'd say the closing bracket should be after the end date?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:10
Joined
Feb 19, 2002
Messages
42,970
Hi Pat - I am not sure I understand this syntax. Is there a parenthesis missing?
Sorry, there was an extra one in the first row. I'll fix it.

Where (SomeDate Between Forms!YourForm!StartDT AND Forms!YourForm!EndDT OR Forms!YourForm!StartDT Is Null)
AND (FundYear = Forms!YourForm!cboFundYear OR Forms!YourForm!cboFundYear Is Null)
AND (FundName = Forms!YourForm!cboFundID OR Forms!YourForm!cboFundID Is Null)
 

Users who are viewing this thread

Top Bottom