Combine entries in three fields in a fourth field

[Rich_B]

Hi

I have three input fields: Q_1, Q_2, Q_3. If the values entered are 001, 002, 003 respectively I would like Q_4 to display a combination of the first three fields i.e. "001-002-003".

This is incredibly easy in Excel, is it possible in Access 2007?

Thank you

 

[cyanidem]

Incredibly easy too:
Me.Q_4=Me.Q_1 & "-" & Me.Q_2 & "-" & Me.Q_3

 

[jdraw]

Possible --sure.
Logical and sensible --- probably not.

It's a little like the person using a crescent wrench to hammer nails --possible, but not the tool designed for the job.

You would not store the values from 3 fields in a fourth field.

 

[Peter Reid]

Are those freaking American messing with our language again so that display means store?

 

[jdraw]

Could be M$oft ---fields are in tables, controls are on forms and reports.

Concatenating values from Controls into another control is possible.

The key point is that Excel and Access are separate products and built on different object models with different concepts.

 

[Rich_B]

Could be M$oft ---fields are in tables, controls are on forms and reports.

Concatenating values from Controls into another control is possible.

The key point is that Excel and Access are separate products and built on different object models with different concepts.

Sorry yes I am referring to controls on a form, not fields in a table.

I'm very new to Access, trying to get up to speed.

Concatenating is the term I was looking for.

 

[Rich_B]

Possible --sure.
Logical and sensible --- probably not.

It's a little like the person using a crescent wrench to hammer nails --possible, but not the tool designed for the job.

You would not store the values from 3 fields in a fourth field.

Could you please educated me as to why this is not logical and sensible?

The only reason I have it structured this way is because it worked for my Excel version. The first part of the three part code represents a client, the second part a site and the third part a segment of that particular contract. My users usually enter the first part, say 001, which in Excel would VLOOKUP from a list of details and auto input the client details into the form saving the need to type in every time. The final concatenated code acts as the primary key for each order.

If there is a better way I would appreciate it if you could point me in the right direction.

Thank you

 

[Rich_B]

Incredibly easy too:
Me.Q_4=Me.Q_1 & "-" & Me.Q_2 & "-" & Me.Q_3

Thank you for your input.

Does this also work for form controls?

I can't seem to get it to work?

 

[cyanidem]

That bit is for form controls. "Me" is reference to current form. Me.Q_1 means control named Q_1 on current form.
But to make it work you need to hook this code to some event (like button click, or After Update of those text boxes. All depends when and how would you like it to work.

 

[jdraw]

Rich,

My comment on not logical was referring to fields in a table where values are stored. You do not store data redundantly. You design your table(s) according to some rules (normalization) which organizes your tables in an efficient manner for processing with relational database.

If you can do something very easily in Excel, why try to do it in Access? Use the tool suited to the job.

If you're intent on using Access for something, then do some research on Database and Access. Do not assume that Excel and Access are similar or that Access is a bigger Excel etc. They are different tools.

If you are interested in learning more about Database and Access, then you could watch these youtube video series starting:
Database
Access Programming

 

[Rich_B]

Rich,

My comment on not logical was referring to fields in a table where values are stored. You do not store data redundantly. You design your table(s) according to some rules (normalization) which organizes your tables in an efficient manner for processing with relational database.

If you can do something very easily in Excel, why try to do it in Access? Use the tool suited to the job.

If you're intent on using Access for something, then do some research on Database and Access. Do not assume that Excel and Access are similar or that Access is a bigger Excel etc. They are different tools.

If you are interested in learning more about Database and Access, then you could watch these youtube video series starting:
Database
Access Programming

Thanks Jdraw

The only reason I'm switching to Access is because I need multiple users to be able to input data simultaneously which Excel won't allow without overwriting someone else's input.

I realise they are different tools, I have watched a few tutorials to see if Access will meet my needs and I think it will, but it is only once I start to design my specific system that I start to run into problems not covered by the generic tutorials. Hence my (probably very newbie-sounding) questions on here.

 

[Rich_B]

That bit is for form controls. "Me" is reference to current form. Me.Q_1 means control named Q_1 on current form.
But to make it work you need to hook this code to some event (like button click, or After Update of those text boxes. All depends when and how would you like it to work.

Ah, I see. I would like it to work in real time if possible, not after a button click.

From the sound of it 'after update' might be the right one. How to I hook the code to this event?

Thank you

 

[cyanidem]

In design mode, select Q_1 textbox control and look at "Preferences" window (if not visible - right click on this textbox and select preferences) and select "Event" tab. Find "After Update" and click on three dots, and if asked select "Event procedure", then paste this code there in VBA editor. Repeat for Q_2 and Q_3.

Or, more recommended, google how to create event procedures and follow it