UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Criteria Expression Issue, Access 2016    
 
   
PickPride
post Jul 21 2019, 10:12 AM
Post#1



Posts: 22
Joined: 21-July 19



Hi, first post to this forum. Have used Access for a home business for 20 yrs. Totally self-taught with respect to Access, I have within the last five years developed tables, queries, reports that have increased efficiencies allowing us to grow our business eight-fold. The last item on my bucket list is to speed up the printing process of a certain report. The following screen shot shows the way we enter criteria into the query the report is based on.

Attached File  PrintCurrent.jpg ( 175.57K )Number of downloads: 10


(I understand that the "date" format used is not good. We purchase data sets from a state agency and that's the way it comes from them and that's the way it has to appear on the printed report going back to them. It works fine for our purpose.)

To print our work product, the process is this:
Open "Query1" in design view
Enter "City_Code" and "AudOnBehalfTaxDist" criteria. (I don't want to "automate" that process because it's a subjective choice as to which city codes and taxing district numbers are entered.)
Enter the "RegDate" criteria. The default is "Between 20181201 And 20181231". We have a report which tells us which months will have data to print for any given city code. If the first month with data is October, we just change the "12"s to "10"s.
Close the query saving the changes
Click the report name "BMV2" and then click the printer icon at the top left of the Access window.
Repeat process above until all months with data for the specified city code are printed.
Repeat entire process above using new "City_Code" and "AudOnBehalfTaxDist" criteria, ad nauseum.

NOTE: Let's say a city code has data for all months except May. If "Between 20180501 And 20180531" is inadvertently entered in the "RegDate" criteria, nothing happens when the query is saved, the report is highlighted, and the print icon is clicked. No error message, no blank sheet of paper coming off the printer, etc. That is good.

After research, I discovered the method of setting up a form with a list box (or combo box) displaying the 12 possible print date ranges and then using an expression in the "RegDate" criteria to use the selected range from the list box. The next attachment shows the list box in the form "PrintDateRanges"

Attached File  FormListBox.jpg ( 136.43K )Number of downloads: 5


After creating the form, I used the following expression in the "RegDate" criteria box:
Forms![PrintDateRanges]![List0]

The next attachment shows the query criteria expression builder:

Attached File  CriteriaExpressionBuilder.jpg ( 62.1K )Number of downloads: 1


The SQL for "Query1" is now:

SQL
SELECT FinalTable.RegDate, FinalTable.City_Code, Others.AudOnBehalfTaxDist, FinalTable.IRP, FinalTable.Address, FinalTable.Zip, FinalTable.Name, FinalTable.App_No, [FinalTable]![License_Tax]*0.01 AS License_Dec, [FinalTable]![Permissive_Tax]*0.01 AS Permiss_Dec, FinalTable.Type_Reg, FinalTable.Tax_District
FROM FinalTable, Others
WHERE (((FinalTable.RegDate)=[Forms]![PrintDateRanges]![List0]) AND ((FinalTable.City_Code)="co") AND ((Others.AudOnBehalfTaxDist)=4270) AND ((FinalTable.IRP) Is Null))
ORDER BY FinalTable.RegDate;


After selecting one print range from the list box in the form that should have printable data, when I run "Query1", the following attachment shows the resulting "error" message:

Attached File  Query1Error.jpg ( 191.07K )Number of downloads: 6


As a test to insure that I was creating a workable form and list box, I created a different form and list box using several "City_Code"s, entered the appropriate expression in the criteria box (Forms![CityCodes]![List0]) and manually entered a "RegDate" criteria and it worked fine.

Any guidance, help, suggestions would be greatly appreciated. Thanks!



Go to the top of the page
 
DanielPineault
post Jul 21 2019, 10:23 AM
Post#2


UtterAccess VIP
Posts: 6,774
Joined: 30-June 11



Instead of a listbox, I'd use 2 textboxes and then you can directly use them in your query criteria

BETWEEN Forms![FormName]![ControlName1] AND Forms![FormName]![ControlName2]

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
theDBguy
post Jul 21 2019, 10:46 AM
Post#3


Access Wiki and Forums Moderator
Posts: 76,081
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

The problem with your approach is you are passing a Text value to the query and expect the query to interpret it as a logical expression, which is not going to work. You must construct the query to either have the logical parts already built-in or reconstruct the query, during execution, to convert the Text value into a logical expression.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Jul 21 2019, 11:01 AM
Post#4


UtterAccess VIP
Posts: 9,943
Joined: 25-October 10
From: Gulf South USA


Hi: I agree with Daniel and theDBguy (as usual!). The string you chose will not do what you want. And I was constructing a demo of what Daniel suggested as they were posting. There are several ways to go about this, and this is just one - but see if this makes sense...

When you choose a From date, the To date is automatically loaded into the second textbox, given the month ranges you posted as your standard.

HTH
Joe
Attached File(s)
Attached File  DateRangeCriteria.zip ( 23.5K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Jul 21 2019, 12:49 PM
Post#5


UA Admin
Posts: 35,535
Joined: 20-June 02
From: Newcastle, WA


One of the primary differences between computers and people is that WE are able to do an excellent job of disambiguation, whereas computers tend to be very literal.

The value you are passing to the criteria is a string of digits--text as theDBGuy points out. You see that particular sequence and "know" that it represents a date. That's not something a computer can do so well, not without help at any rate.

You need to take the value as it is FORMATTED FOR DISPLAY and convert it to a "real" date value that Access can work with.

I did a series of YouTube videos recently which all focus on the difference between dates as they are stored in Access, and the many ways you can FORMAT those dates for DISPLAY.

Good luck with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
PickPride
post Jul 21 2019, 02:58 PM
Post#6



Posts: 22
Joined: 21-July 19



Thanks to all of you for responding so quickly. I have incorporated your suggestions, tested them, and all is perfect...magnifico. Maybe with the time saved printing, we can increase the business another eight-fold. Kudos to all!!
Go to the top of the page
 
RJD
post Jul 21 2019, 03:25 PM
Post#7


UtterAccess VIP
Posts: 9,943
Joined: 25-October 10
From: Gulf South USA


Hi again: Perhaps you could share what you actually did to solve this. Others may be following this (or might look at this in the future), and may have similar needs, and would like to see your solution.

Thanks,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
PickPride
post Jul 22 2019, 11:36 AM
Post#8



Posts: 22
Joined: 21-July 19



Daniel and you (RJD) provided the solution. Create one new simple table shown here:
Attached File  NewTable.jpg ( 103.23K )Number of downloads: 1


Create one new simple form shown here in design view along with the property sheet:
Attached File  NewForm.jpg ( 443.15K )Number of downloads: 2


The new Query1 "RegDate" criteria is: Between [Forms]![frmDateRanges]![cboDateRange1] And [Forms]![frmDateRanges]![txtDateTo]

Now, to print we just open Query1 in design view, enter the appropriate criteria for "City_Code" and "AudOnBehalfTaxDist" save and close the query. Open the new form (and leave it open), select the appropriate date range, click the report name, click the print icon. Select the next print range on the form, click the report name, click the print icon. Repeat until all needed print ranges have printed.

All much more efficient and accurate (no chance for mistyping a print range). Thanks again to you and all who responded.


Go to the top of the page
 
RJD
post Jul 22 2019, 01:42 PM
Post#9


UtterAccess VIP
Posts: 9,943
Joined: 25-October 10
From: Gulf South USA


Good. Thanks.

You could go further with this by supplying the city code and tax dist as controls on the form as well. This way, you will not have to open the query in design view at all. Just reference these form controls in the query. Should make this even easier.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
PickPride
post Jul 22 2019, 09:37 PM
Post#10



Posts: 22
Joined: 21-July 19



I know I could but, as noted in my original post, I don’t want to “automate” the entry of those two for the reason stated (and others).
Go to the top of the page
 
RJD
post Jul 23 2019, 10:22 AM
Post#11


UtterAccess VIP
Posts: 9,943
Joined: 25-October 10
From: Gulf South USA


Hi: In your first post you stated ...

QUOTE
I don't want to "automate" that process because it's a subjective choice as to which city codes and taxing district numbers are entered.

Well, using controls on a form to enter these values is also a "subjective choice" as well. And, if you wish, on a form you can use comboboxes to choose the selection from an approved list, or you can use an event procedure to check a table to verify that the control entry is acceptable. Or, as a less desirable choice, you could prompt for the values to enter by appropriate design in the query. In any case, you will have better control over the entered values without having to open the query in Design View and making changes, and possibly making an error in the SQL. These approaches are also more efficient, data entry-wise.

Just some thoughts while we are still on the subject.

Of course, how you do this is entirely up to you, but repeatedly opening the query design to make changes before running it is a very unusual approach ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
PickPride
post Jul 23 2019, 01:47 PM
Post#12



Posts: 22
Joined: 21-July 19



....and in the post above your latest response, I wrote "I don’t want to “automate” the entry of those two for the reason stated (and others)." It's the "and others" that is the most relevant. Without getting into proprietary details of our work, any given city code may have hundreds of different "AudOnBehalfTaxDist" numbers associated with it. We create a new database for each project and we do hundreds of projects each year. The city code "CO" may mean "Columbia" in one project but "Comstock" in another. Within any project database, each city code is associated with a unique "AudOnBehalfTaxDist" number. Anyway, there could be thousands, millions of permutations if I tried covering all current projects in one table that could be used in all projects. Instead of trying to account for all of them, it's very quick and easy to open the query design, double click the existing city code, change it (only 2 or 3 letters) tab to the next column, change the "AudOnBehalfTaxDist" number (4 digits), close it and move on. I know you are trying to be helpful and I appreciate it.
Go to the top of the page
 
tina t
post Jul 23 2019, 02:17 PM
Post#13



Posts: 6,034
Joined: 11-November 10
From: SoCal, USA


QUOTE
Open "Query1" in design view
Enter "City_Code" and "AudOnBehalfTaxDist" criteria. (I don't want to "automate" that process because it's a subjective choice as to which city codes and taxing district numbers are entered.)
Enter the "RegDate" criteria. The default is "Between 20181201 And 20181231". We have a report which tells us which months will have data to print for any given city code. If the first month with data is October, we just change the "12"s to "10"s.
Close the query saving the changes
Click the report name "BMV2" and then click the printer icon at the top left of the Access window.
Repeat process above until all months with data for the specified city code are printed.
Repeat entire process above using new "City_Code" and "AudOnBehalfTaxDist" criteria, ad nauseum.

NOTE: Let's say a city code has data for all months except May. If "Between 20180501 And 20180531" is inadvertently entered in the "RegDate" criteria, nothing happens when the query is saved, the report is highlighted, and the print icon is clicked. No error message, no blank sheet of paper coming off the printer, etc. That is good.

i agree with Joe's last post: repeatedly opening, editing, saving a query is somewhat hazardous, and slow, too. if data validation in a form is not practical for your process, just skip that part.

given the info you've posted, you could fairly easily reduce the process you described above to simply opening your form, entering all city code and tax dist combinations that you wanted to print during the current session - in a subform - then clicking a button on the mainform and letting the printer get to work.

if you're interested, i'll post details. if you're happy to stick with the process you've developed to this point, no harm/no foul. maybe it's something you'll be interested in looking into sometime down the road. :)

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
PickPride
post Jul 23 2019, 03:26 PM
Post#14



Posts: 22
Joined: 21-July 19



Tina,

Your suggestion of entering all possible combinations onto a subform would take longer than it takes to print everything using my OLD method. There could literally be hundreds to enter. Joe provided the help a couple of days ago that cut printing time significantly and we're happy with that. As I hinted at in my original post, we've been using Access for 20 years and the original database "consultant" we used did the best they could. I've made vast improvements on my own, but don't pretend to be anything more than a beginner. We're nearing retirement and very content with getting marginal improvement in our processes without reinventing the wheel. My wife understands ZERO about any of this. She can perform the work, but the underlying programming/design, etc. is totally lost on her. It took me 20 years of coaching and I think she finally remembers control c for copy and control v for paste. iconfused.gif

Anywhoooo, thanks again to all for your help/opinion. I consider this thread closed.
Go to the top of the page
 
tina t
post Jul 23 2019, 08:01 PM
Post#15



Posts: 6,034
Joined: 11-November 10
From: SoCal, USA


QUOTE
Your suggestion of entering all possible combinations onto a subform would take longer than it takes to print everything using my OLD method.

i know you consider this topic closed, and that's fine, but i can't help posting a clarification of your misquote. i didn't suggest entering all possible combinations anywhere.

QUOTE
...entering all city code and tax dist combinations that you wanted to print during the current session

typing those specific values into a form would certainly be faster than what you're doing now:

QUOTE
open the query design, double click the existing city code, change it (only 2 or 3 letters) tab to the next column, change the "AudOnBehalfTaxDist" number (4 digits), close it

this won't help you, as you're satisfied with your current process, but it may help others who read this thread down the road. :) tina
This post has been edited by tina t: Jul 23 2019, 08:01 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th August 2019 - 08:55 AM