Expression in form (Left function)

[jimbo100]

Hi there,

I can use the left function to strip characters from different fields and concatenate them into one field. This is easily done using a query however when I try to do this using an entry form, I seem to be getting error messages such as: "You must enter a value in the tblCustomer.CustomerID field. I have used the following expression in the control source "=Left([Customer_Surname],3) & Left([Customer_Forename],3)".

This works in the query, it also works in the form however it does not add the data to the table.

------------

Second question.

The image shows an example of data given. The primary key has been created using the first name, two characters of the last name as well as age. I personally don't think this is a good example of a primary key. Would a better approach be to use Auto Number for the primary key?

There is also the issue of getting a username the same as a previous one and there are numbers being repeated.

Thank you.

 

[jdraw]

Would a better approach be to use Auto Number for the primary key? Yes.

See Autonumber

I have used the following expression in the control source "=Left([Customer_Surname],3) & Left([Customer_Forename],3)" Why? One fact in one field. If you must display some code/pattern/substring, you can always create same from existing fields.

 

[MarkK]

I concur with jdraw. Use an Autonumber. There is nothing to gain by slicing up data into smaller bits, and then mashing those bits together to make a key. It's extra effort, and the resulting string key will take up more space, and perform less efficiently.

 

[jimbo100]

Thank you, I appreciate both of your replies. I will go with the Autonumber approach, based on your suggestion and what many websites are recommending as well.

Is there a link to show how I might be able to approach the Left function within the form? I keep coming across resources but they all relate to queries.

 

[MarkK]

...how I might be able to approach the Left function within the form?

What exactly do you need to do with the Left function?

 

[The_Doc_Man]

The reason your "hash" of the names and age won't work as a PK (and autonumber would be preferred) is in this possible case:

Robert K Smith, age 45
Roland G Smith, age 45

Two people who, using your hash, would cause what we call a "key collision." There are ways to "force" a natural key, but the SIMPLEST way is to use autonumber and thereby avoid the need to consider collisions.

In general, a natural key is often the best candidate. However, in specific, that key is potentially not unique and thus is disqualified as a candidate key.

As to using the Left function, WHERE do you want to use it? (In a form...) WHERE in the form do you want to use it?

 

[jimbo100]

Thanks, yeah I did realise I would encounter such issues. The idea of using the Left function was to see how it would work within a form. The Access file I have uploaded shows the form that has an ID, getting characters from other fields such as firs tname and last name. This works on the form however it doesn't allow the data to be added to the table, even when the data itself is unique.

 

[jdraw]

jimbo,

You seem to have some affinity/preoccupation for using the Left function. Can you explain what your specific interest in Left if about/for?
Left, Right, Mid, InStr all have their uses and applicability.

Here is a list of Access String functions showing syntax and examples.

Here is some info related to Primary Key.

 

[richard bro-42]

I am also trying to add a "Left" Expression for the following scenario.
(Get all text string left of dash)
Fields TagNumber contains a device tag such as PSV- 2478
Field: Tag Prefix Should auto fill "PSV"
The tag number isn't consistence some are 2 characters, some 3 and some 4.
I can do this really simple in excel with =LEFT(A1,FIND("-",A1)-1).
(Problem is I have data clerks inputting the data and they aren't used to doing any type of functions.

 

[MarkK]

I would use the VBA.Split() function, which splits a string, at a character or characters you specify, into a variant array. Then you can refer to the chunk you want by using array subscripts, so to get everything left of a hyphen, consider...

   dim vArray as variant

   vArray = Split("PSV-2478", "-") [COLOR="green"]'split the string at the hyphen, assign to array[/COLOR]
   debug.print vArray(0)           [COLOR="Green"]'print the first array element[/COLOR]

That way you aren't mucking around with InStr() to find the position of the hyphen, and then using Left() to return the x leftmost characters.
hth
Mark

 

[isladogs]

Duplicate of post: https://www.access-programmers.co.uk/forums/showthread.php?t=298938

I had already added an answer there using Left & Instr

=Left(TagNumber,InStr(TagNumber,"-")-1)

Use whichever method you prefer

 

[pbaldy]

Duplicate of post: https://www.access-programmers.co.uk/forums/showthread.php?t=298938

Duplicate thread deleted.

 

[Pat Hartman]

This works in the query, it also works in the form however it does not add the data to the table.

That is because the control is not bound. When a control contains an expression rather than a field from the Form's RecordSource, it is NOT BOUND. How would Access know where to store this:

=fldA + fldB

Does it go into fldA or does it go into fldB?

If you want to store a calculated field - and we will all tell you not to do this, you need to use VBA

In the AfterUpdate event of fldA AND fldB OR in the BeforeUpdate event of the Form:

Me.fldC = Me.fldA + Me.fldB