Quick questions

[just2cool]

I'm writing a field where it shows a combination of many fields.
For example, when a user types in "abc" in field 1, "def" in field 2, "ghi" in field 3, I will have to combine the three fields into "abc/def/ghi" with slashes in between them. However I don't want to have any slashes at the front or at the back the field.
There are unlimited fields of "abc" "def" "ghi" "jkl" "mno" etc. Any idea how I can write the code? I'm thinking using if statements. Is there other ways I can do this?
Thanks!!

 

[Rusty]

So do you want to do this in a query or a form?

I'm assuming query seing as this is the Queries board, but thought I'd check first.

Rusty

 

[neileg]

If there is an unlimited number of three letter codes, are you storing these as additional records (the right way) or as repeating fields in a record (the wrong way)?

 

[just2cool]

Rusty: This will be in a query. Thanks.

neileg: Actually there will only be at most 10 codes, and they could be three or four letters each. The user will input the codes and I need to combine all of them and show it to the user in a query. Im not sure if this answers your second question. Thanks again!!

 

[Rusty]

If it's a query then the best way is to concatenate (or combine) the three fields into a single field.

It's quite straightforward and I have attached an example using the information you supplied in the original post, with the three bits of data combined with slashes in between in a new fourth field.

If you open the query in design view, you will see the 'code' for how this is done, i.e.
TypeFieldNameHere: [Field1] & "/" & [Field2] & "/" & [Field3]

If you play around with it in datasheet view, you will see that as soon as you type data into Field1 the combination field updates itself. Neat huh?

Hope that all makes sense.

Cheers,

Rusty

 

[just2cool]

Thanks Rusty. But the problem is if there are two fields only, an extra slash appears at the end of the combined field. Sometimes the number of fields can go up to 10 fields. So if I do it the way you did it, there needs to be 10 slashes but the user might end up inputing two fields only, and there will be an extra 9 slashes.
Can anyone help with the problem?

 

[stopher]

Doesn't seem right to store your data in separate fields but without knowing your full requirements it's difficult to suggests a better structure.

Anyway, you can solve your current problem with:

TypeFieldNameHere: [Field1] & ("/"+[Field2]) & ("/"+[Field3]) etc

You can repeat the & ("/"+[Field3]) bit for as many fields as you need.

Both & and + are concatenators but + returns a null if either side is null, whereas & returns a value (expect only when both sides are null).

hth
Stopher

 

[just2cool]

Thanks for the tips! I will try it out later.

 

[neileg]

Doesn't seem right to store your data in separate fields but without knowing your full requirements it's difficult to suggests a better structure.

Anyway, you can solve your current problem with:

TypeFieldNameHere: [Field1] & ("/"+[Field2]) & ("/"+[Field3]) etc

You can repeat the & ("/"+[Field3]) bit for as many fields as you need.

Both & and + are concatenators but + returns a null if either side is null, whereas & returns a value (expect only when both sides are null).

hth
Stopher

Clever! You have just reduced my ignorance by a tiny amount!

 

[stopher]

Clever! You have just reduced my ignorance by a tiny amount!

Thanks Neil
But it is a small price to pay for the experience and knowledge I have gained from you and many other regular contributors.

I read this tip in column by Mark Whitehorn in Personal Computer World.

The inspirational point of the article was how it gets rid of blank lines in addresses by combining the + and & in the right place with Cr Lf i.e. don't do a CrLf if the address line is null. One of those "light on" moments.

Chris