Pull data from open form for report

[oxicottin]

Hello, on my open form I have a max list of 10 employees BUT there is usually 8 employees and the last few are usually blank. I need to just pull the list of the employees from that open form to a single text box in my report that gets printed from that form. How can I just print the names from the combo boxes that have a name and not the ones that dont so I have no gaps in my list...

=[Forms]![frm_WeeklySafetyHuddle]![cboEmployee1].[column](2) & ", " & =[Forms]![frm_WeeklySafetyHuddle]![cboEmployee2].[column](2)) & ", " & [Forms]![frm_WeeklySafetyHuddle]![cboEmployee3].[column](2)) & ", " & =[Forms]![frm_WeeklySafetyHuddle]![cboEmployee4].[column](2)) & ", " & [Forms]![frm_WeeklySafetyHuddle]![cboEmployee5].[column](2)) & ", " & =[Forms]![frm_WeeklySafetyHuddle]![cboEmployee6].[column](2)) & ", " & [Forms]![frm_WeeklySafetyHuddle]![cboEmployee7].[column](2)) & ", " & =[Forms]![frm_WeeklySafetyHuddle]![cboEmployee8].[column](2)) & ", " & [Forms]![frm_WeeklySafetyHuddle]![cboEmployee9].[column](2)) & ", " & =[Forms]![frm_WeeklySafetyHuddle]![cboEmployee10].[column](2))

The attachment is how its coming out. See the end how there is a few commas? I don't want them there if there is no employees after the last one.

 

[June7]

Why do you have a combobox for each employee?

Are these combobox selections not saved to a record?

 

[oxicottin]

Why do you have a combobox for each employee?

Are these combobox selections not saved to a record?

No they are not saved but they are pulled from a table. This database is just to print papers I have to do every week and this is to make it easier for me.

Acually I take that back I have a table that does save the selections on the main open form so I dont have to keep entering/selecting names and dates etc

 

[The_Doc_Man]

Usually, it is easier to pull from the underlying source rather than trying to fiddle with going through a form.

 

[June7]

If you want to concatenate fields and some might be Null, can utilize + character for concatenation. Example with 3 fields:

a + "," & b + "," & c

If a or b is Null, concatenation using + will return Null, not the comma. Whereas with &, the comma would display. Now another issue will encounter is when a and b are not Null but c is. There will be a trailing comma with nothing following.

 

[oxicottin]

Usually, it is easier to pull from the underlying source rather than trying to fiddle with going through a form.

So what woud the query look like it pull namex

 

[oxicottin]

Ok lets do it over.... I created a query and a table to make this right... In the query I have each employee field from my table. I need to only show the fields that show a name or data AND I need to only show the initials of the employee and not their name.

To get their initials I would use? but how in the query? and display only fields that have data... @The_Doc_Man and @June7 I hope that's better...

The tables data displays their name and not their ID because there hardly is data in this DB so it would show an employee as Mike Myers

SELECT tbl_WeeklySafetyHuddle.Employee1, tbl_WeeklySafetyHuddle.Employee2, tbl_WeeklySafetyHuddle.Employee3, tbl_WeeklySafetyHuddle.Employee4, tbl_WeeklySafetyHuddle.Employee5, tbl_WeeklySafetyHuddle.Employee6, tbl_WeeklySafetyHuddle.Employee7, tbl_WeeklySafetyHuddle.Employee8, tbl_WeeklySafetyHuddle.Employee9, tbl_WeeklySafetyHuddle.Employee10
FROM tbl_WeeklySafetyHuddle;

I have even tried the image and I get all the null results still and the ones that have values.

 

[June7]

Again, why do you have a field for each employee? Your db design appears to not be normalized.

To get each employee's initials from Employees table, would have to include Employees table in query 10 times, each instance joined to one employee field.

Then see example expression in post 5.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[SHANEMAC51]

on my open form I have a max list of 10 employees BUT there is usually 8 employees

I hope I didn't make a mistake in the brackets

=[Forms]![frm_WeeklySafetyHuddle]![cboEmployee1].[column](2) 
&(", " +[Forms]![frm_WeeklySafetyHuddle]![cboEmployee2].[column](2)) 
&(", " +[Forms]![frm_WeeklySafetyHuddle]![cboEmployee3].[column](2)) 
&(", " +[Forms]![frm_WeeklySafetyHuddle]![cboEmployee4].[column](2)) 
&(", " +[Forms]![frm_WeeklySafetyHuddle]![cboEmployee5].[column](2)) 
&(", " +[Forms]![frm_WeeklySafetyHuddle]![cboEmployee6].[column](2)) 
&(", " +[Forms]![frm_WeeklySafetyHuddle]![cboEmployee7].[column](2)) 
&(", " +[Forms]![frm_WeeklySafetyHuddle]![cboEmployee8].[column](2)) 
&(", " +[Forms]![frm_WeeklySafetyHuddle]![cboEmployee9].[column](2)) 
&(", " +[Forms]![frm_WeeklySafetyHuddle]![cboEmployee10].[column](2))

 

[oxicottin]

Again, why do you have a field for each employee? Your db design appears to not be normalized.

To get each employee's initials from Employees table, would have to include Employees table in query 10 times, each instance joined to one employee field.

Then see example expression in post 5.

If you want to provide db for analysis, follow instructions at bottom of my post.

@June7 I followed your instructions and attached a DB. I have a initials query that this is what I'm trying to do this with. I needed to display the first and last letters of the name and not show data that is null. As for the employees I'm not sure what to do to normalize it.

 

[Eugene-LS]

I have a initials query that this is what I'm trying to do this with. I needed to display the first and last letters of the name and not show data that is null. As for the employees I'm not sure what to do to normalize it.

Take a look to attached DB please

 

[June7]

Okay, so not saving initials in Employees table but calculating them (I should have expected that). Expression to extract initials:

Emp1: (Left([Employee1],1)) & Mid([Employee1],InStr(Nz([Employee1],"")," ")+1,1)

Now applying expression suggested earlier: =[Emp1] + ", " & [Emp2] + ", " & [Emp3]

However, as I said, database is not normalized. What you have can be managed but some things might be simpler with normalization. That means instead of 10 employee fields, have a dependent table where each employee selected is a record.

It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."

 

[oxicottin]

Thank you @Eugene-LS and @June7