Query Criteria

[CarlettoFed]

Look at the file I attached.

[data808]

It takes a minimum of data to be able to verify the operation.

I figured it out. Its the records that do not have values for the criteria that we put into the query. So for example, if I put this into the query criteria for the clerk initials:

Like "*" & [Forms]![Counter Partial Criteria].[txtClerkInitial] & "*"

Then when I go to the form to fill out the clerk initials text box it won't show any records that are blank for the clerk initials. It won't even show if I leave that field blank then click the button that opens the split form results. So basically I need an sql that will show records that have blank fields especially for the ones that we set criterias for in the query.

Do you know how to do that?

[data808]

Look at the file I attached.

I can't. Looks like my comp is locked up for downloading files off the internet. But did you see my last post? I figured out what the problem was. Do you know how to rewrite the sql for the query criteria so that it shows blank field records too?

[CarlettoFed]

In the "Counter Partial Criteria_query" query the Sql predicate must be replaced by the following:

SELECT tbl_Counter_Log.[Ref Num], tbl_Counter_Log.[Date Of Service], tbl_Counter_Log.[Time Of Service], tbl_Counter_Log.[Customer Num], tbl_Counter_Log.[Customer Name], tbl_Counter_Log.[DL Num], tbl_Counter_Log.[Plate Num], tbl_Counter_Log.[Citation Num], tbl_Counter_Log.[Num Of BWs], tbl_Counter_Log.Clerk, tbl_Counter_Log.[Amt Due], tbl_Counter_Log.CHK, tbl_Counter_Log.CHRG, tbl_Counter_Log.[Cash Given]
FROM tbl_Counter_Log
WHERE tbl_Counter_Log.[Date Of Service]=Nz([Forms]![Counter Partial Criteria].[txtDate],[Date Of Service]) AND tbl_Counter_Log.Clerk Like "*" & Nz([Forms]![Counter Partial Criteria].[txtClerkInitial],"") & "*";

[data808]

In the "Counter Partial Criteria_query" query the Sql predicate must be replaced by the following:

SELECT tbl_Counter_Log.[Ref Num], tbl_Counter_Log.[Date Of Service], tbl_Counter_Log.[Time Of Service], tbl_Counter_Log.[Customer Num], tbl_Counter_Log.[Customer Name], tbl_Counter_Log.[DL Num], tbl_Counter_Log.[Plate Num], tbl_Counter_Log.[Citation Num], tbl_Counter_Log.[Num Of BWs], tbl_Counter_Log.Clerk, tbl_Counter_Log.[Amt Due], tbl_Counter_Log.CHK, tbl_Counter_Log.CHRG, tbl_Counter_Log.[Cash Given]
FROM tbl_Counter_Log
WHERE tbl_Counter_Log.[Date Of Service]=Nz([Forms]![Counter Partial Criteria].[txtDate],[Date Of Service]) AND tbl_Counter_Log.Clerk Like "*" & Nz([Forms]![Counter Partial Criteria].[txtClerkInitial],"") & "*";

Where do I replace this code in the query? Sorry not sure what the sql predicate is?

[data808]

In the "Counter Partial Criteria_query" query the Sql predicate must be replaced by the following:

SELECT tbl_Counter_Log.[Ref Num], tbl_Counter_Log.[Date Of Service], tbl_Counter_Log.[Time Of Service], tbl_Counter_Log.[Customer Num], tbl_Counter_Log.[Customer Name], tbl_Counter_Log.[DL Num], tbl_Counter_Log.[Plate Num], tbl_Counter_Log.[Citation Num], tbl_Counter_Log.[Num Of BWs], tbl_Counter_Log.Clerk, tbl_Counter_Log.[Amt Due], tbl_Counter_Log.CHK, tbl_Counter_Log.CHRG, tbl_Counter_Log.[Cash Given]
FROM tbl_Counter_Log
WHERE tbl_Counter_Log.[Date Of Service]=Nz([Forms]![Counter Partial Criteria].[txtDate],[Date Of Service]) AND tbl_Counter_Log.Clerk Like "*" & Nz([Forms]![Counter Partial Criteria].[txtClerkInitial],"") & "*";

I think I found what you were talking about. I pasted this into the SQL View window. I tested it and it still doesn't show records that have blank clerk initial values.

[CarlettoFed]

You have to attach the file with the data because nothing is understood from what you say.
To attach the file you must first compress it in .rar or .zip format.

[data808]

You have to attach the file with the data because nothing is understood from what you say.
To attach the file you must first compress it in .rar or .zip format.

Ok zipped and attached with 4 records. The second record does not have a clerk initial typed in on purpose to show that this is the record that will be hidden once you use the forms. Again, my theory is that it is hidden based on the SQL expression in the query criteria that causes this to happen. If I remove that criteria in the query for the clerk field, it will show all 4 records when doing a filter with the Counter Partial Criteria form if all fields are blank when clicking the filter button to open the split form.

[data808]

If it's not possible to do what I want by rewriting this for the query criteria:

Like "*" & [Forms]![Counter Partial Criteria].[txtClerkInitial] & "*"

then another idea I have is to do some VBA in the split form which is called Counter Partial Criteria Results. I could do an if statement based on if there is or isn't a value in the previous Counter Partial Criteria form then open this split form to show these records? Something like that. I would just need to figure out how to check on a text box that is on another form. Would you know how to do something like that?

[CJ_London]

really isn't clear what you are trying to achieve since my original suggestions in post #2 work - copy and paste this code into a new query

SELECT tbl_Counter_Log.[Ref Num], tbl_Counter_Log.[Date Of Service], tbl_Counter_Log.[Time Of Service], tbl_Counter_Log.Clerk, tbl_Counter_Log.[Num Of BWs], tbl_Counter_Log.[Amt Due], tbl_Counter_Log.CHK, tbl_Counter_Log.CHRG, tbl_Counter_Log.[Cash Given], tbl_Counter_Log.[Customer Num], tbl_Counter_Log.[Customer Name], tbl_Counter_Log.[DL Num], tbl_Counter_Log.[Plate Num], tbl_Counter_Log.Subject, tbl_Counter_Log.[Citation Num]
FROM tbl_Counter_Log
WHERE (((tbl_Counter_Log.[Date Of Service]) Like "*" & [Forms]![Counter Partial Criteria].[txtDate] & "*" Or [Forms]![Counter Partial Criteria].[txtDate] Is Null) AND ((tbl_Counter_Log.Clerk) Like "*" & [Forms]![Counter Partial Criteria].[txtClerkInitial] & "*" Or [Forms]![Counter Partial Criteria].[txtClerkInitial] Is Null));

run it and clearly explain why it doesn't meet your requirements using your example data to illustrate

We are now up to post 25 for something that should only take a response or two

[data808]

really isn't clear what you are trying to achieve since my original suggestions in post #2 work - copy and paste this code into a new query

SELECT tbl_Counter_Log.[Ref Num], tbl_Counter_Log.[Date Of Service], tbl_Counter_Log.[Time Of Service], tbl_Counter_Log.Clerk, tbl_Counter_Log.[Num Of BWs], tbl_Counter_Log.[Amt Due], tbl_Counter_Log.CHK, tbl_Counter_Log.CHRG, tbl_Counter_Log.[Cash Given], tbl_Counter_Log.[Customer Num], tbl_Counter_Log.[Customer Name], tbl_Counter_Log.[DL Num], tbl_Counter_Log.[Plate Num], tbl_Counter_Log.Subject, tbl_Counter_Log.[Citation Num]
FROM tbl_Counter_Log
WHERE (((tbl_Counter_Log.[Date Of Service]) Like "*" & [Forms]![Counter Partial Criteria].[txtDate] & "*" Or [Forms]![Counter Partial Criteria].[txtDate] Is Null) AND ((tbl_Counter_Log.Clerk) Like "*" & [Forms]![Counter Partial Criteria].[txtClerkInitial] & "*" Or [Forms]![Counter Partial Criteria].[txtClerkInitial] Is Null));

run it and clearly explain why it doesn't meet your requirements using your example data to illustrate

We are now up to post 25 for something that should only take a response or two

Thank you so much! You solved it. Something must have been wrong with the query. I created a brand new one and pasted that code in the SQL View window and now it works for clerk initials and date.

Would you know how to hook up the other 3 fields?

Nevermind I think I can figure it out from your code. Let me try it out and let you know. Again thank you very much for your patience.

[CarlettoFed]

This is the example with all possible searches.

[data808]

This is the example with all possible searches.

I see you have come up with a VBA solution. Is this method better to use? I was able to do your SQL way with all the fields, however, I did notice some slow down in the filtering process. When I click the button it takes about a second or two to load. Would this VBA be a lot faster?

[data808]

This is the example with all possible searches.

I went ahead and used your VBA instead of the SQL method and it seems to be loading a lot faster now so thank you very much for that. You have been extremely helpful and patient through this process. I will be sure to give you the reputation you deserve. Thanks again.