Help with Query that is attached to a form (1 Viewer)

abette

Member
Local time
Today, 17:51
Joined
Feb 27, 2021
Messages
85
Hi - I have a query that is attached to a 2nd form that open when the user inputs information in a 1st form and then clicks the Search button.
1633982407046.png

So the Search button opens a 2nd form whose record source is based on a query. The 2nd form displays the correct records when using Fund Year and/or Fund Type, however, when searching by the Received Start Date Range and Received End Date Range OR Processed Start Date Range and Processed End Date Range it displays all my records.
Here's what I have for the date ranges in my query
1633982465306.png

And here's he Fund Year and Fund Type criteria
1633982542732.png

Can you tell me what I am doing wrong? Here's the SQL script code
SELECT tblClaimsWorkingTable.ID, tblClaimsWorkingTable.ClaimNumber, tblClaimsWorkingTable.AuthNumber, tblClaimsWorkingTable.DateOfServiceFrom, tblClaimsWorkingTable.DateOfServiceTo, tblClaimsWorkingTable.ProviderCIMID, tblClaimsWorkingTable.PayeeName, tblClaimsWorkingTable.FEIN_SSN, tblClaimsWorkingTable.DateReceived, tblClaimsWorkingTable.DateProcessed, 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.ClaimsFundYear, tblClaimsWorkingTable.ClaimsFundType, tblClaimsWorkingTable.AttestationCheck
FROM tblClaimsWorkingTable
WHERE (((tblClaimsWorkingTable.DateReceived) Between [Forms]![frmAnomalyClaimsReviewQueueMAIN]![ReceivedStartDateRange] And [Forms]![frmAnomalyClaimsReviewQueueMAIN]![ReceivedEndDateRange]) AND ((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo])=[tblClaimsWorkingTable]![ClaimsFundYear] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo]) Is Null) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=[tblClaimsWorkingTable]![ClaimsFundType] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo]) Is Null)) OR (((tblClaimsWorkingTable.DateProcessed) Between [Forms]![frmAnomalyClaimsReviewQueueMAIN]![ProcessedStartDateRange] And [Forms]![frmClaimsAnomalyReviewQueueMAIN]![ProcessedEndDateRange]) AND ((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo])=[tblClaimsWorkingTable]![ClaimsFundYear] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo]) Is Null) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=[tblClaimsWorkingTable]![ClaimsFundType] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo]) Is Null)) OR (((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo])=[tblClaimsWorkingTable]![ClaimsFundYear] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo]) Is Null) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=[tblClaimsWorkingTable]![ClaimsFundType] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo]) Is Null))
ORDER BY tblClaimsWorkingTable.ClaimsFundYear, tblClaimsWorkingTable.ClaimsFundType;

Thank you so much for your help. Ann Marie
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:51
Joined
Oct 29, 2018
Messages
17,137
Hi Ann Marie. Please pardon me for saying this, but that looks a bit messy. When you use criteria in a query for a search form, I know it's necessary to cover all possible user options, which could result in a mess like that. A better approach is to use VBA to construct the query dynamically by applying only the criteria selected by the user.

You should be able to look for "search forms" demo on how to make a search form a lot simpler than using a form reference criteria like that.

If I find a link for you, I'll post it here.
 

abette

Member
Local time
Today, 17:51
Joined
Feb 27, 2021
Messages
85
Hi Ann Marie. Please pardon me for saying this, but that looks a bit messy. When you use criteria in a query for a search form, I know it's necessary to cover all possible user options, which could result in a mess like that. A better approach is to use VBA to construct the query dynamically by applying only the criteria selected by the user.

You should be able to look for "search forms" demo on how to make a search form a lot simpler than using a form reference criteria like that.

If I find a link for you, I'll post it here.
I am not a VBA programmer so I think that would be difficult. Maybe someone else on here can assist. Thanks again!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:51
Joined
Oct 29, 2018
Messages
17,137
I am not a VBA programmer so I think that would be difficult. Maybe someone else on here can assist. Thanks again!
Ouch, you made it sound like I couldn't assist you. I was just saying what might be an easier approach. I didn't say I don't know how to make that mess work. Maybe I don't, I just didn't say it. :)
 

abette

Member
Local time
Today, 17:51
Joined
Feb 27, 2021
Messages
85
Ouch, you made it sound like I couldn't assist you. I was just saying what might be an easier approach. I didn't say I don't know how to make that mess work. Maybe I don't, I just didn't say it. :)
By no means was a implying that you couldn't help me. I am not a VBA programmer and I have average Access skills. I joined this group for support since I do not have anyone I can go to on my job. Please do not insult me by referring to the query as 'that mess'. I am trying the best I can.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:51
Joined
Oct 29, 2018
Messages
17,137
By no means was a implying that you couldn't help me. I am not a VBA programmer and I have average Access skills. I joined this group for support since I do not have anyone I can go to on my job. Please do not insult me by referring to the query as 'that mess'. I am trying the best I can.
Hi. My apologies. I wasn't really calling your query a "mess." It is what it is, and it can never be anything else since it was created to handle multiple situations. If I created that query, it would look messy as well. I was just commenting it looked "messy" - not that it is a mess, or that it can be made less messy.

So, let me ask you a different question. Are you opposed to learning some VBA? If so, then we can forget any suggestions involving it and simply concentrate on how to make your query work as is.
 

plog

Banishment Pending
Local time
Today, 16:51
Joined
May 11, 2011
Messages
10,652
So the Search button opens a 2nd form whose record source is based on a query.

I agree there is a better way to accomplish this than your--to be more semantically thoughtful, let's use--"complicated" query. Inteady you should base that second form on tblClaimsWorkingTable and use DoCmd.OpenForm to open it:


When the user clicks the Search button it runs code that builds a criteria string and DoCmd.OpenForm uses it to open your 2nd form and applies that criteria at the same time. That way, you only add criteria the user wants to add.

Give it a shot and post back here if it doesn't work like you want and we can help walk you through getting it there.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:51
Joined
Feb 19, 2002
Messages
33,711
Access sort of goes crazy with parentheses and repetition when you have complex criteria. The result is SQL that is virtually impossible to parse.

Start by saving a copy of the query. Then we're going to test each "line" separately. The criteria in each row is AND'd, meaning ALL criteria must be true. The Rows are all OR'd so row 1 can be true or row 2 can be true or row 3 can be true. If any of the three rows has ALL true reuslts, then the record is selected.
Test 1 - remove rows 2 and 3. Do you get what you expect?
Test 2 - start with the original SQL and remove rows 1 and 3. Do you get what you expect?
Test 3 - start with the original SQL and remove rows 1 and 2. Do you get what you expect?

If each of the three works indpendently, the query should work in total so I would rebuild it from scratch.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:51
Joined
Oct 29, 2018
Messages
17,137
Hi guy and gal. Thanks for the assist. I was quickly making a "mess" of things (unintentionally, of course). Cheers!
 

abette

Member
Local time
Today, 17:51
Joined
Feb 27, 2021
Messages
85
I agree there is a better way to accomplish this than your--to be more semantically thoughtful, let's use--"complicated" query. Inteady you should base that second form on tblClaimsWorkingTable and use DoCmd.OpenForm to open it:


When the user clicks the Search button it runs code that builds a criteria string and DoCmd.OpenForm uses it to open your 2nd form and applies that criteria at the same time. That way, you only add criteria the user wants to add.

Give it a shot and post back here if it doesn't work like you want and we can help walk you through getting it there.
So if I use the DoCmd to open the form based on the values entered in the first form, how does it work for a date range value? What would the code look like?
 

abette

Member
Local time
Today, 17:51
Joined
Feb 27, 2021
Messages
85
Access sort of goes crazy with parentheses and repetition when you have complex criteria. The result is SQL that is virtually impossible to parse.

Start by saving a copy of the query. Then we're going to test each "line" separately. The criteria in each row is AND'd, meaning ALL criteria must be true. The Rows are all OR'd so row 1 can be true or row 2 can be true or row 3 can be true. If any of the three rows has ALL true reuslts, then the record is selected.
Test 1 - remove rows 2 and 3. Do you get what you expect?
Test 2 - start with the original SQL and remove rows 1 and 3. Do you get what you expect?
Test 3 - start with the original SQL and remove rows 1 and 2. Do you get what you expect?

If each of the three works indpendently, the query should work in total so I would rebuild it from scratch.
Access sort of goes crazy with parentheses and repetition when you have complex criteria. The result is SQL that is virtually impossible to parse.

Start by saving a copy of the query. Then we're going to test each "line" separately. The criteria in each row is AND'd, meaning ALL criteria must be true. The Rows are all OR'd so row 1 can be true or row 2 can be true or row 3 can be true. If any of the three rows has ALL true reuslts, then the record is selected.
Test 1 - remove rows 2 and 3. Do you get what you expect?
Test 2 - start with the original SQL and remove rows 1 and 3. Do you get what you expect?
Test 3 - start with the original SQL and remove rows 1 and 2. Do you get what you expect?

If each of the three works indpendently, the query should work in total so I would rebuild it from scratch.
Hi. My apologies. I wasn't really calling your query a "mess." It is what it is, and it can never be anything else since it was created to handle multiple situations. If I created that query, it would look messy as well. I was just commenting it looked "messy" - not that it is a mess, or that it can be made less messy.

So, let me ask you a different question. Are you opposed to learning some VBA? If so, then we can forget any suggestions involving it and simply concentrate on how to make your query work as is.
I am willing to learn VBA if I have to. How do I code the date range entries?
 

plog

Banishment Pending
Local time
Today, 16:51
Joined
May 11, 2011
Messages
10,652
Here's a good example

 

abette

Member
Local time
Today, 17:51
Joined
Feb 27, 2021
Messages
85
Here's a good example

Using the sample you provided me I have an Event Procedure attached to the Search Button On Click Property

Private Sub Command82_Click()
DoCmd.OpenForm "frmAnomalyClaimsReviewQueueSUB", , , "dtDateReceived between #" & Format([ReceivedStartDateRange], "dd-mmm-yyyy") & "# And #" & Format([ReceivedEndDateRange], "dd-mmm-yyyy") & "# " Or "dtDateProcessed between #" & Format([ProcessedStartDateRange], "dd-mmm-yyyy") & "# And #" & Format([ProcessedEndDateRange], "dd-mmm-yyyy") & "# " Or " &[FundYearCombo] = '" & Me!ClaimsFundYear & "'" Or " &[FundTypeCombo] = '" & Me!ClaimsFundType & "'"
End Sub

When I run this I am getting a type mismatch error.
 

abette

Member
Local time
Today, 17:51
Joined
Feb 27, 2021
Messages
85
I am willing to learn VBA if I have to. How do I code the date range entries?
Using the sample you provided me I have an Event Procedure attached to the Search Button On Click Property

Private Sub Command82_Click()
DoCmd.OpenForm "frmAnomalyClaimsReviewQueueSUB", , , "dtDateReceived between #" & Format([ReceivedStartDateRange], "dd-mmm-yyyy") & "# And #" & Format([ReceivedEndDateRange], "dd-mmm-yyyy") & "# " Or "dtDateProcessed between #" & Format([ProcessedStartDateRange], "dd-mmm-yyyy") & "# And #" & Format([ProcessedEndDateRange], "dd-mmm-yyyy") & "# " Or " &[FundYearCombo] = '" & Me!ClaimsFundYear & "'" Or " &[FundTypeCombo] = '" & Me!ClaimsFundType & "'"
End Sub

When I run this I am getting a type mismatch error.
 

plog

Banishment Pending
Local time
Today, 16:51
Joined
May 11, 2011
Messages
10,652
You are not going to hard code your criteria into the DoCmd.OpenForm. You are going to build a string and add stuff to it only if it is needed:

Code:
Private Sub Command82_Click()

Dim str_Criteria As String        ' will hold criteria string to open form

str_Criteria="(1=1)"
' default value of string, adding true condition so easier to add other criteria

if (IsNull([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=False) Then
  ' user input ClaimsFundType Value into form, will add it to criteria
    str_Criteria = str_Criteria & " AND ([ClaimsFundType]='" & [Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo] & "'"
End If

' More conditional statements for each criterion will be added here just like the one above for ClaimsFundType

DoCmd.OpenForm "frmAnomalyClaimsReviewQueueSUB", , , str_Criteria
  ' opens form with criteria

End Sub


That will open your form and use ClaimsFundType as a criteria. Add it to your form and make sure it works. When it does you add more conditional statements like the one I did for ClaimsFundType. Do it one by one to make sure you are doing it right, don't try and make it work for everything all at once.
 

Users who are viewing this thread

Top Bottom