This expression is typed incorrectly or it is too complex to be evaluated....

[watdahek]

Good Afternoon,
I am trying to create a subform that pulls data from a query that uses fields in the main form as its criteria. Everything works fine if I use 5 criteria fields but I need to use 7 and as soon as add the sixth and try to run I get the error mentioned in the title of this thread. Here is a brief synopsis of the database:

7 data tables: Account, Year, Location, Workgroup, Scenario, Version, Event (each table includes all possible values for the respective field plus an ID field)
1 Main Data table: includes a field for each table named above and one for each month (Jan, Feb, Mar...)

1 Form that includes 5 combo boxes for 5 of the categories (Location, Workgroup, Scenario, Version, Year) and 2 text boxes that include Account and Event values.

When I try to pull the values for each month field into the form I get an error, if I only use 5 criteria (any combination) it works but gives me too many results as my unique identifier contains 7 fields.

Does anyone know what I am doing wrong or if there is a better approach to accomplish this?

All help is greatly appreciated,
Steve

 

[Gideon1973]

How many characters are in the final SQL command? I think this tends to happen if the SQL statement is over 1,024 or something like that.

If that's the cause of the problem, you may consider breaking up the process into multiple steps and/or have subqueries to do some parts of the joins and then join to the query to get your final dataset.

 

[watdahek]

Thanks....I'll try splitting it up...The SQL is not that long...Here it is:

SELECT tbl_Data_12Mo.Account, tbl_Data_12Mo.Event, tbl_Data_12Mo.Jan, tbl_Data_12Mo.Feb, tbl_Data_12Mo.Mar, tbl_Data_12Mo.Apr, tbl_Data_12Mo.May, tbl_Data_12Mo.Jun, tbl_Data_12Mo.Jul, tbl_Data_12Mo.Aug, tbl_Data_12Mo.Sep, tbl_Data_12Mct, tbl_Data_12Mo.Nov, tbl_Data_12Mo.Dec
FROM tbl_Data_12Mo
WHERE (((tbl_Data_12Mo.WorkGroup)=[Forms]![Data Entry_12Mo]![cmb_WG]) AND ((tbl_Data_12Mo.Location)=[Forms]![Data Entry_12Mo]![cmb_loc]) AND ((tbl_Data_12Mo.Year)=[Forms]![Data Entry_12Mo]![cmb_yr]) AND ((tbl_Data_12Mo.Scenario)=[Forms]![Data Entry_12Mo]![cmb_scen]) AND ((tbl_Data_12Mo.Version)=[Forms]![Data Entry_12Mo]![cmb_ver]));

 

[Gideon1973]

I see there are references to values from three or four different controls...

I wonder if there are empty object/null values being passed and it's making the SQL statement throw up.

You could try putting the SQL in VBA and concatenate those values together and then examine the SQL statement being generated to see if it returns something unexpected. Maybe putting NZ([Forms]![Data Entry_12Mo]![....]) could help.

Or, you can put the three or four different control values as expressions in the SELECT, and see what values they return -- but get rid of them from the WHERE clause. That way you can see the values that are coming across to the SQL statement.

 

[strive4peace]

I formatted your SQL to make it easier to read

SELECT tbl_Data_12Mo.Account
, tbl_Data_12Mo.Event
, tbl_Data_12Mo.Jan
, tbl_Data_12Mo.Feb
, tbl_Data_12Mo.Mar
, tbl_Data_12Mo.Apr
, tbl_Data_12Mo.May
, tbl_Data_12Mo.Jun
, tbl_Data_12Mo.Jul
, tbl_Data_12Mo.Aug
, tbl_Data_12Mo.Sep
, tbl_Data_12Mct
, tbl_Data_12Mo.Nov
, tbl_Data_12Mo.Dec
FROM tbl_Data_12Mo
WHERE (((tbl_Data_12Mo.WorkGroup)=[Forms]![Data Entry_12Mo]![cmb_WG])
AND ((tbl_Data_12Mo.Location)=[Forms]![Data Entry_12Mo]![cmb_loc])
AND ((tbl_Data_12Mo.Year)=[Forms]![Data Entry_12Mo]![cmb_yr])
AND ((tbl_Data_12Mo.Scenario)=[Forms]![Data Entry_12Mo]![cmb_scen])
AND ((tbl_Data_12Mo.Version)=[Forms]![Data Entry_12Mo]![cmb_ver]));

If ANY of the referenced controls are not filled out, this statement will fail. Wrap control references with NZ and, ideally, provide your own default value for the second (optional) argument

ie:

if this is text:
nz( [Forms]![Data Entry_12Mo]![cmb_ver], "")

if this is a number:
nz( [Forms]![Data Entry_12Mo]![cmb_yr] ,0)

I can't help but notice that your database is not normalized ... I am assuming you are using it for temporary manipulation, not an ongoing project

optionally, if you want the user to be able to filter for whatever (maybe leave some blank and specify others), you may wish the subform to show everything and apply a filter to the subform as criteria is specified

 

[watdahek]

Thank you for your reply...I was having issues with normalization...could you point me in the right direction? I set up a table for each "dimension" (except month) and then I created a main data table which has a lookup field for each of the 7 tables plus 12 fields for each month (to store data values)...I am thinking I should create a month table instead of the 12 fields, I tried that and encountered grief when trying to create a form that would allow a user to enter data values for the same 7 field combination for each of the 12 months on the same form. What is the correct way to accomplish this or am I completely off the rails? Any guidance would be greatly appreciated.

Thank You,
Steve

 

[strive4peace]

Hi Steve,

you're welcome

> "I was having issues with normalization...could you point me in the right direction?"

I'd be happy to.

Watch the videos in this short playlist:
Learn Access By Crystal - YouTube

then read my free Access Basics* book, paying close attention to the chapter on Normalization.
Access Basics

* only 100 pages with lots of screen shots

> "enter data values for the same 7 field combination for each of the 12 months on the same form"

first get the structure right (or closer) then come back with more questions ...

 

[watdahek]

Thanks Crystal. I watched your videos and read the book...I found them very helpful. For some reason I am still having some difficulty correlating what I am doing to the normalization process as I understand it. When I am done I have 9 tables, 8 of which contain "items" that the ninth table uses. The ninth table stores an ID, and eight IDs (each of which relates to a different table) and then a value. I call this last table tbl_Master and I would equate it to the "People" table in your examples. After the ID in the Master table the next eight fields make up a unique combination of Group, Location, Year, Month, Scenario, Version, Account, and Event (this combination can only exist once in my table, I have set this combo as the primary key) and the last field is a value. So a sample record would look like this:

Master_ID, GrpID, LocID, YrID, MnID, ScID, VerID, AccID, EvID, dataVal
1, 2, 2, 1, 3, 1, 1, 2, 1, 10
Which Represents:
Record1, Group2, NY, 2012, March, Budget, Final, Internal, NewHire, 10
In Words Means:
Group 2 in New York had 10 Internal New Hires in March 2012 as entered in the Final Version of the Budget

Is this correct?

If it is, I have a few questions about doing what I would like to do while data is stored in this format.

Thank You,
Steve

 

[strive4peace]

Hi Steve,

thank you and you're welcome

> "9 tables, 8 of which contain "items" that the ninth table uses."

why do you have 8 different tables with items? Cannot you create some kind of category field and combine them into one table?

Run my analyzer on your database. Look at all the reports. Print the Table Summary, and maybe the Deep Analysis report too. Use the Deep Analysis and Field List reports to identify your repeating data structures.

Analyzer By Crystal

Setting up a database like you set up an Excel workbook is a big mistake that many people make.

 

[watdahek]

Thanks Again...I will absolutely run your Analyzer. I don't know why I am not getting this...

>"why do you have 8 different tables with items? Cannot you create some kind of category field and combine them into one table? "

Here is an example of 2 of the tables:

Scenario:
Actual
Budget
Forecast

Version:
Current
Final

If I put them into one table I would have 6 records:
Actual Current
Actual Final
Budget Current
Budget Final
Forecast Current
Forecast Final

Now if I add another Version (Prelim) I would add 3 more records (Actual Prelim, Budget Prelim, Forecast Prelim), that is why I put them in seperate tables, I see lots of redundant data if it were one table.

If I add a new field, say month, to the mix the number of records goes from 9 to 108 because each scenario/version combination would exist for each month. If I keep going I quickly have 700K records for 1 account. Plus, I wouldn't have an easy way to add a new version if the need arises.

Thanks...I look forward to the results of the analyzer.
Steve