Set field Criteria to “Is Not Null” based on localvars

[DavidCBzz]

Aloha,

I have a form (with 30 accounts), were user selects check boxes; one of each day of week, then clicks a button to start the event.

The event will print a report(s) for each day of the week; it is a schedule for a specific day of week, containing 5 fields from the query. Assignment, Time In, Time Out, Hours, Day[?]. Where there are 7 day fields for each day of week, and the fields contain the employee name. The needed field is the Day Field (field names are SPS_day1, SPS_Day2….) field contains the employee name, which are sometimes blank, and would prefer not to print blank records in the report.

Ok that got a little wordy …

I have made 7 different queries, and 7 different reports, one for each day of the week, and it worksJ

Although, I know there is a better way, has to be, and its VB, but I cann't get my head wrapped around all the code at this point. The VB would solve both issues, though.

The current Idea is (was) to use the IIF statement somehow in the criteria, in each day field. The IIf result would be based on a variable with in the reports click event, and if true place ‘Is Not Null’ in the criteria for the field

The next step would be to do the same (but without the variable part) in the reports day condole, and set it to a value on the form.

I’ve attempted numerous, numerous, combinations without success, I’m a newbie, been going at it for a while, right now the only positive is I’ve learned a bit.:banghead:

I think I’m close; I’ve placed the following code in a blank field heading in the query and set the criteria to True.

IIf([tempvars]![varbleday1]="enableday1",[sps_day1]="Is Not Null",True)

Do I need to reference the expression somehow in the field’s criteria?

It runs on the event, but all the data is blank. The event runs as follows via the Macro Builder
1.) Clear all TempVars
2.) Set TempVars
3.) Open Report

So am I attempting to do the impossible, and should I move on doing it with VBA code?

Using 2010

 

[plog]

Your post set off my 'improper table structure' alarm. I think this issue that you are posting about is a symptom of a larger problem.

Can you post the structure of your table(s). Perhaps a screen shot of the relationship window. Then also post a sample of a report you would like to generate.

 

[DavidCBzz]

Alarms...Hmmmm, I figured it may be more serious

Wanted to get back to you sooner, but then I started to rethink the design.

The original data for the dbase is linked to an existing Excel file; it does a lot of math, but has always had issues with the report side, hence the access part. I have a feeling this is going to morph in to something larger, already getting requests from a small number of beta users, if I need to scrap the current design, better now than later.

I’ve enclosed what I think you were asking for, should provide a general idea.

Any direction would be appreciative

 

[plog]

Yes, your design is improper. A big issue I see is numerated field names. When you start prefixing/suffixing field names (e.g. Day1SIS, Day2SIS, etc.) with numbers it means you have the wrong structure. Instead of 7 fields (one for each day), you should have 7 records (one for each day). Looks like this is the case for ExcelSchData as well.

Without sample data or a brief description of what this data represents in real life, that's the only glaring issue I see. If you could provide some sample data and a brief, plain-english (that means no database jargon) explanation of what the data represents, I could help more.