I am trying to add from 1 to 12 fields to an output table used for a report. I only want to show the fields that heve values and not all the empty ones. If input fields 1, 5, and 10 have values i dont want to show the emty field for 2,3, and 4 or 6 thru 9. Incomminy data for field 1 will be in position 1(task1), field 5 in position 2(task2), and field 10 in position3 (task3). I have fields in the output table labeled task1, task2, task3,,,task12. I can't seem to concatenate onto the field name for my output recordset. I want to write input field 1 to wrk!task1, input field 5 to wrk!task2, and input field10 to wrk!task3. Help...
Brad Davis

Brad

"I have fields in the output table" ... WARNING! Access is NOT a spreadsheet. If you're creating an "output table" so you can get the data you want in a report, you're not taking advantage of the power/features that Access offers.

In a relational database (like Access), it all starts with the data. Unless/until you get the data right, your queries/forms/reports will be a struggle.

Please describe your underlying data/table structure a bit more...

I am trying to produce a report the will show from 1 to 12 available tasks. I do not want to show blank spaces across the report where there is not data. The recordset I am working from has an entry for each of 12 fields wether there is data in them or not. I am trying to determine if data is present so I can move it into sequential output records so when they appear on the report it only shows fields that contained data. my output table has fields for 12 input fields. If I only have data for inut fields 1, 5, and 10, I want thet to show in output fields 1,2, and 3. Instead of moving input 10 to output 10 I want to fill the next sequential output field so I only show data in fields 1,2, and 3 rather than data,blank, blank,blank, data, blank, blank etc. I imagine there is a better way to do this but I have not been doing much code for several years and I am probably making this more difficult than I need to. I hope this makes it a bit more clear as to what I want to do.

So if your underlying raw data was something like:

Data1, blank, blank, blank, blank, Data6, blank, blank, blank, blank, blank, blank, Data12

you'd want to see:

Data1, Data6, Data12

What about the idea of using concatenation and only showing what's there?

(*by the way, if those are "tasks" in columns one through 12, there's a very good chance your data needs further normalization! Any chance you migrated this over from a spreadsheeet?*)

... and in case that doesn't make sense, I'm proposing to use a query against the underlying table, with a new field something like:

NewField: [Field1] & ", " & [Field2] & ", " & ...

Note that this will put a lot of commas in for null fields. If you don't want those, you can use an IIF() statement to 'skip' the empties.

NOTE2: make sure your "empties" are nulls, not blanks or zero-length strings

NOTE3: I still suspect that you're committing spreadsheet on Access.

Yes that's how I want it to work. No, not from a spreadsheet just an input screen where the user checks one or more radio buttons.

Well, if you're using Access correctly, you'll be working from data in Tables, not in Forms. Forms don't contain data; they're just tools, windows to let you manage data in Tables.

That said, you can generate one string without any of the nulls using a sneaky trick: both the & and the , operators concatenate strings, but they handle NULLS differently. The expression
[FieldA] & [FieldB]

treats a NULL as an empty string and will just give you whichever field is not NULL;

[FieldA] + [FieldB]

will be NULL if either field is NULL. So you could use

([Field1] + ", ") & ([Field2] + ", ") & ([Field3] + ", ")

etc. to only insert a comma if there is a field value.

Thanks for all the great responses. It turns out my biggest problem is being able to concatenate sequencial numbers onto a recordset label. I have 12 incomong fields that could all contain data or could all be null. I have 12 output fields from task1 , task2... task12. I want to only output the fields with data. If input fields 1, 5, and 10 contain data, I want to output them as task1, task2 and task3. My problem is concatenating a numerical value onto the label of my output recordset to reflect this.

dim x as integer
x = 1

if input field1 = true then
wrk!task1 = inputfield1
x = x + 1
else
end if

'''' input fields 2 thru 4 are null *******************

if input field5 = true then
wrk!("task" & x) = input field5 trying to put the next field with data into the next sequential output field (wrk!task2) *** DOES NOT COMPUTE ****

What synatax do I need to make this concatenation work for my recordset entry???

It still sounds like your output table is incorrectly normalized! You should never have a table with fieldnames like Task1, Task2, Task3 and so on.

That said... if the fieldnames in fact do end in numbers 1 through 10, you could use code like this (assumng two recordsets named rsIn and rsOut):



Basically you're constructing the fieldname as a string and using the Field() collection of the recordset object to read or write the appropriate field.

I'm with John on this.

If your table has columns (fields) named "Task1", "Task2", "Task3", ..., you dont' have a table, you have a spreadsheet!