Update Blank Field with Combination of Data from 2 Other Fields

[Paul]

This seems so simple but I can't get it. I've read the many posts on
this subject but I still don't get it. Worse, I know I've done it
before.

tblAttendees has fields FirstName, LastName, and Username. Username is
not populated.

I'd like to create a simple Username for each person from first three
letters of first name plus first three letters of last name. Don't
worry, this is not a high security situation (or they wouln't let me
touch it).

The SQL that results from my query worksheet (didn't they used to call
this qbe?) is

UPDATE tblAttendees SET tblAttendees.Username =
(Left$([tblAttendees].[FirstName],3)) &
(Left$([tblAttendees].[LastName],3));

When I go from design mode into datasheet mode (to see the results) I
get a single column with not data.

I've tried it using Left as well as Left$

I've checked that all three fields are text fields.

Once I do this, I'd like to add a new field Password. I'd like to
creat a simple password that is the first letter of LastName plus five
random numbers. I'm happy to use a single kernal or whatever you call
it, maybe something off the computer clock.

Thanks,

Paul

 

[John Spencer]

Part one:
Simply switching for the query grid to the datasheet view does not perform
the update. What you are seeing is what will be updated. Since what will
be updated is blank, you see blanks.

To actually do the update you can click on the Red exclamation mark in the
tool bar or select Query: Run from the Menu bar.

Part Two: See quote and function below from John Vinson

Add a field to your query.
UPDATE tblAttendees
SET tblAttendees.Username = (Left$([tblAttendees].[FirstName],3)) &
(Left$([tblAttendees].[LastName],3))
, Password = Left(LastName,1) & Format(RndNum(Len(LastName)) *
100000),"00000")

============================================
Quote from John Vinson
Copy and paste this little function into a module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);

Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces
Access
to give you a new random number for every row. If you don't have a numeric
field available then you can use RndNum(Len([SomeField])) to force a number
to be generated.

Source: John Vinson