moving field values 1

[brownfox]

Consider this:
|-----------------------------------------|
|Field1|Field2|Field3|Field4|Field5|Field6|
|-----------------------------------------|
|value1|value2|value3|value4|value5|value6|
|-----------------------------------------|

If the table is updated and, let's say, Field2 is set to '' is there a easy way of moving all the values across 1 place so there are no spaces? I.E in the above example: the value of field3 becomes that of field2,field 4 becomes field3 etc...

 

[sleipnir214]

No, there's not, using the columns of a table.

For this kind of operation (storing a set of optional attributes for an item), one generally uses a second related table. Instead of removing one column's value, you remove the entire record from the related table.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[BNPMike]

This is not easy. If one field is blank, you could move all subsequent fields left one space. However if the next one is blank you've got to do it again to get rid of the space that you've just copied. Equally if the 3rd field is blank and the 6th, then some fields will move one space and some two spaces.

As a result you may be effectively forced to use VBA or run a simple procedure n times.

 

[brownfox]

No need to worry if the next field is blank if the procedure is run every time a field is made empty. You say: "If one field is blank, you could move all subsequent fields left one space" - could you tell me how? Could you expand on "VBA" and "run a simple procedure n times"?
Thanks in advance...

 

[sleipnir214]

Again, you can do this with a lot less effort by using a related table.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[BNPMike]

By VBA I mean Visual Basic for Applications that comes with Access - the programming language of modules etc.

By run a procedure n times I mean you can write an SQL Update query that copies the next field to the right if the field concerned is empty. As I described above, you need to make moves of more than one field to cover all possible gaps. Therefore you run the query n times where n is the maximum number of left shifts you know will ever be needed. The maximum value of n is something like the number of fields involved minus one.