2

I'm working in Access and I need to create a random 4 digit-number for a list of users separated into records. My query keeps returning the same random number for each record.

SELECT 1000 + (1999-1000)*RND() as [PassCodes], 
    stafflistEMAIL.Name, stafflistEMAIL.Description 
FROM stafflistEMAIL;

How can I make this work, the other answered I've viewed don't provide a solution?

Smandoli
  • 6,919
  • 3
  • 49
  • 83
swagger937
  • 49
  • 1
  • 5

2 Answers2

2

Just as a thought, the following will give 4 random characters:

Mid(CreateObject("Scripting.FileSystemObject").GetTempName,4,4)

For example:

906F
0818
25A5
F200
918F
A166
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
1

Repeating random variables in VBA

Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer.

Depending a lot on your goal, you can do something fun like this:

Right(Cstr(CInt(Len(stafflistEMAIL.Name)*Len(stafflistEMAIL.Description)/1000)),4)
Community
  • 1
  • 1
Smandoli
  • 6,919
  • 3
  • 49
  • 83
  • If the system timer is on 1-second resolution, and queries come faster than this, this won't work. You need to make sure the timer is ticking faster than queries are received. – djconnel Aug 16 '12 at 17:13
  • If you are running your query apart from VBA, I'm not sure how to do what you want. I would suggest you move it to VBA for good control -- presumably this is a fairly critical function. I'm thinking, for example, that you want these numbers to be unique; so you need a way to check that, and VBA offers the control you need. – Smandoli Aug 16 '12 at 17:13
  • djconnel -- that is a great point. Again, this argues for VBA; increment number +1 if timer is unchanged (of course that isn't random, but I assume it meets the need). – Smandoli Aug 16 '12 at 17:17