Maximum Number of Characters in Query Parameter (1 Viewer)

Weekleyba

Registered User.
Local time
Yesterday, 20:28
Joined
Oct 10, 2013
Messages
445
I have a query with an expression for one of the fields.
The expression looks like this:

Code:
Balance: CCur(DLookUp("Nz([MajorM&IFund],0)+Nz([BEMARFund],0)+Nz([SmallClinicFund],0)","FY_Funding_T","FY=" & [FY])-DSum("CostEstimate","AllScoreSheet_Q","Format([GrandTotal],""0000"") & Format([PROJECTID],""00000"")>='" & Format([GrandTotal],"0000") & Format([PROJECTID],"00000") & "'"))

1598409311294.png


The field "Balance" has 279 characters with spaces.
I believe the limit is 255.

How to I reduce this and still make it work?
 

plog

Banishment Pending
Local time
Yesterday, 20:28
Joined
May 11, 2011
Messages
10,576
This is not the correct way to accomplish what you want. You need to properly structure your tables. Read up on normalization:

.

You have used values that should be in fields as field names. "MajorM&IFund" should be a value in a field called [FundType] not the name of a field itself. Then you should have another field for the numerical value. When you need to add data for SmallClicnicFund, you add a new record with that as the FundType and its value. Then, when you want to all all those types up you run a simple aggregate query to do that.
 

Weekleyba

Registered User.
Local time
Yesterday, 20:28
Joined
Oct 10, 2013
Messages
445
Sorry guys... I figured it out.
It was giving me the following message:

1598409743538.png


But....the query that this query was relying on had a criteria that required me to select a Year first.
If the year was not first selected, it would give me this message above.
But if I first select the Year, then all is well with the world again.
I write a little If statement in VBA to first check if the Year has been selected.
 

Weekleyba

Registered User.
Local time
Yesterday, 20:28
Joined
Oct 10, 2013
Messages
445
Plog,
I'm interested in what you're try to teach me.
Currently I have a table, FY_Funding_T shown below.

1598410224607.png


If I create a field [FundType] what is the data type for that?
I guess I'm envisioning a combo box for FundTypes and store the FundType ID in the table?
Then have field for values for these.

But...for this specific instance, I'm only entering these values once a year, I don't think this gains me anything.
Or am I off base here?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:28
Joined
Feb 28, 2001
Messages
20,660
@Weekleyba, the issue plog mentions with normalization is that you are treating your data as an Excel spreadsheet. There is nothing wrong with that if you are actually using Excel because that's what it does. But it can become difficult to do certain types of data aggregation when all you have is a spreadsheet. That is why when you want more advanced analysis, you switch to Access.

I'm going to add my voice to plog's and say you need to understand normalization. There is a proper way to store data and that has nothing to do (well, ... very little to do) with how you present it. In Access those are two totally different factors.

If you search THIS forum ("Search" is upper right, near your forum name) for "Normalization" you will see some examples and discussion on this totally crucial topic. The forum's search ability is at least decent if not elegant.

If you choose to search the web for this topic, you must look for "Database Normalization" because it happens that there are at least four or five other uses of the word "normalization." Also, if you choose to search the web, start your reading from sites in the .EDU domain at least for the first couple of articles. You can get really good discussions from .COM sites too, but they frequently have something to sell you that might be proprietary rather than of general interest.

You would realize once you have studied normalization that you have too many columns in your table. And you would understand why. What you do next will depend on your imagination, of course, but you can always come back here for more ideas.
 

plog

Banishment Pending
Local time
Yesterday, 20:28
Joined
May 11, 2011
Messages
10,576
Normalization is the process of properly setting up your tables--which is the fundamental element of building a database. Get that wrong and everything else you do is putting band aids and duct tape on a broken bone.

The table you posted should be structured as such:

FY_FUNDING_T
funding_ID, Auto number, primary key
funding_FY, number, holds value in FY field now
funding_Fund, text, this will hold 'Major M & I', 'BEMAR', etc
funding_Amount, number, this will hold the amount

Then, instead of 1 row per FY you will have 1 row per funding type per FY. So FY 2021 would have 3 records in the above table (1 per type).

How does [MajorM&IFundTotal] fit in? Why two fields prefixed with 'MajorM&I'? Why isn't there a BEMARFundTotal field?
 

Weekleyba

Registered User.
Local time
Yesterday, 20:28
Joined
Oct 10, 2013
Messages
445
Plog,
Well...you'll probably really smack me now, but let me explain and then smack away. (-:
We received Major M&I funding and from that, we set aside 15% for Small Clinics.
So, the [MajorM&IFundTotal] is currency field but, the [MajorM&IFund] and [SmallClinicFund] are calculated fields.
The BEMARFund is another funding so it is separate currency field. See table below.

1598451170834.png


So with your suggestion I would have a table like this:

1598451935144.png


Would I then have another table for Funding_Fund so I can select which one I want, tied together with an ID or are you suggesting to type it in?

Thanks for all the help. I really do want to learn to set these databases up correctly so, I value very much your help.
 

plog

Banishment Pending
Local time
Yesterday, 20:28
Joined
May 11, 2011
Messages
10,576
Wow, that makes things really interesting actually. First, you shouldn't use calculated fields in tables. Instead, when you need that data you should build a query and do the calculation there and reference the query when you need the calculated value.

Second, while Funding_T is most likely correct; I think I need more info. Can you write 1 simple (no database jargon) paragraph explanation of what your organization does and then another paragraph (with minimal database jargon) that explains what your database does for your organization?
 

Weekleyba

Registered User.
Local time
Yesterday, 20:28
Joined
Oct 10, 2013
Messages
445
Sure can.
My organization assists native american tribes in maintaining their healthcare facilities. We receive federal funds for the maintenance and repair of the facilities and then through the Maintenance & Improvement Resource Allocation Committee (MIRAC) scoring process, we distribute the funds to the tribes. We assist the tribes to whatever extent they require in design and construction of these facilities.
The database I'm trying to develop, will help the committee score the submitted projects by each of the tribal locations. Each project is inputted into the database with its description, cost estimate, project type, & score and then the committee convenes to review each project. The database is also used to allow each committee member a chance to comment on each project prior to the committee convening and eventually, agreeing on the final scoring. The top scoring projects are funded on down to the cutoff line where the funding runs out. The plan is for the database to be utilized year after year and to store the results of the prior years results for historical purposes.
I've attached the drafted database if you'd like to view it. It's a work in progress....as are my database skills.
Thanks for the help!
 

Attachments

  • MIRAC ver6.zip
    119.7 KB · Views: 98

plog

Banishment Pending
Local time
Yesterday, 20:28
Joined
May 11, 2011
Messages
10,576
That helps thanks. For your database, you've made the same mistake in Project_T as you do in FY_FUNDING_T--creating too many fields and naming them with values. We you start naming a group of fields after terms specific to your organization it is most likely time for a new table (e.g. SmallClinic, Urgency, GenSafety, etc.). You probably need all those values in a new table with a field to hold what is currently in the field name as a type field.

Read up on normalization and give it a shot with Project_T and FY_Funding_T. Then, complete the Relationship Tool in Access, expand all the tables so I can see all the fields, take a screen shot and post it back here so we can walk through it.
 

Weekleyba

Registered User.
Local time
Yesterday, 20:28
Joined
Oct 10, 2013
Messages
445
I'm lost on this.
How would your method work with my form below?
Below is my form and a start of new table.
The form is built per project so each record is a project with one ProjectID.
I'm afraid I'm not seeing how to do this the way your explaining.
Unless I have to scrap the way the form is built and start from scratch.??
Even then my brain gears are stuck...
Can you elaborate or show me an example?


1598483500587.png
 

plog

Banishment Pending
Local time
Yesterday, 20:28
Joined
May 11, 2011
Messages
10,576
Forms have nothing to do with structuring your tables. In fact they are the very last portion of building a database (building reports comes after setting up tables).
 

Users who are viewing this thread

Top Bottom