Random Numbers Revisited

T

TCD

After being out a while, we are still working on a
process to generate approval codes that would appear to
be random to our staff. We have got it to work for
inputting the individuals name but can't figure out how
to be able to input the date as well. This is the part
that we have that works:

Private Sub cmdInsert_Click()

Dim db As DAO.Database
Dim strSQL As String

Randomize

Set db = CurrentDb

strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(Code, [Name])"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ",""" & Me!Name & """);"



Debug.Print strSQL

db.Execute strSQL

db.Close

End Sub


Thanks
 
G

Guest

Use DateValue() function, which convert string to date.

strSQL = "INSERT INTO Table1 " & _
"(Code, [Name])" & _
" VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ", DateValue(""" & Me!Name
& """));"
 
T

TCD

Unfortunately that produces the same error message we've
been getting: 'Syntax Error in INSERT INTO statement. Any
other ideas?

Thanks

-----Original Message-----
Use DateValue() function, which convert string to date.

strSQL = "INSERT INTO Table1 " & _
"(Code, [Name])" & _
" VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ", DateValue(""" & Me!Name
& """));"



-----Original Message-----
After being out a while, we are still working on a
process to generate approval codes that would appear to
be random to our staff. We have got it to work for
inputting the individuals name but can't figure out how
to be able to input the date as well. This is the part
that we have that works:

Private Sub cmdInsert_Click()

Dim db As DAO.Database
Dim strSQL As String

Randomize

Set db = CurrentDb

strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(Code, [Name])"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ",""" & Me!Name & """);"



Debug.Print strSQL

db.Execute strSQL

db.Close

End Sub


Thanks
.
Click to expand...
.
Click to expand...
 
T

Tim Ferguson

Use DateValue() function, which convert string to date.

strSQL = "INSERT INTO Table1 (Code, [Name]) " & _
"VALUES (" & (Int((99999 - 10000 + 1) * Rnd() + 10000)) & _
", DateValue(""" & Me!Name & """));"
Click to expand...
Unfortunately that produces the same error message we've
been getting: 'Syntax Error in INSERT INTO statement. Any
other ideas?
Click to expand...

Hmm: this seems to parse into

INSERT INTO Table1 (Code, [Name]) VALUES (12345, DateValue("Eric"));

If you really mean to pass a function like

DateValue("Eric")

then I have no idea what you expect the Jet engine to do with it... The
most helpful thing would be to inspect (or post here) the result from the
Debug.Pring strSQL line -- the whole point is to be able to see what is
actually being passed.

By the way, I am sure that someone has already suggested that using
reserved words for object names like [Name] and
Code: 
 is a Really Bad
Idea.

By the way (2), how are you going to avoid giving the same code number out
twice?

B Wishes


Tim F
 
T

TCD

Both Code and Name will likely have different field names
when rolled out; we just went with those for simplicity
sake right now. Indexing and/or primary keys should
prevent duplicates. Anyway, we don't anticipate any
duplicates of the name, date and approval code since each
employee will get at most one per day anyway.

What we are struggling with is having a data entry field
for the date along with the existing data entry employee
name field.

It seems line 14 should be: strSQL = strSQL & "(AppCode,
EmpName, Date)"

It is line 17 and beyond that we have trouble with trying
to add date. (Example below)
Thanks

Private Sub cmdInsert_Click()

Dim db As DAO.Database
Dim strSQL As String

Randomize

Set db = CurrentDb

strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(AppCode, EmpName)"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ",""" & Me!EmpName & """);"



Debug.Print strSQL

db.Execute strSQL

db.Close


-----Original Message-----
Use DateValue() function, which convert string to date.

strSQL = "INSERT INTO Table1 (Code, [Name]) " & _
"VALUES (" & (Int((99999 - 10000 + 1) * Rnd() + 10000)) & _
", DateValue(""" & Me!Name & """));"
Click to expand...
Unfortunately that produces the same error message we've
been getting: 'Syntax Error in INSERT INTO statement. Any
other ideas?
Click to expand...

Hmm: this seems to parse into

INSERT INTO Table1 (Code, [Name]) VALUES (12345, DateValue("Eric"));

If you really mean to pass a function like

DateValue("Eric")

then I have no idea what you expect the Jet engine to do with it... The
most helpful thing would be to inspect (or post here) the result from the
Debug.Pring strSQL line -- the whole point is to be able to see what is
actually being passed.

By the way, I am sure that someone has already suggested that using
reserved words for object names like [Name] and
Code: 
 is a Really Bad
Idea.

By the way (2), how are you going to avoid giving the same code number out
twice?

B Wishes


Tim F

.
[/QUOTE]
Click to expand...
 
T

Tim Ferguson

It is line 17 and beyond that we have trouble with trying
to add date. (Example below)
Thanks
Click to expand...

So (a) what is the SQL you are trying to aim at and (b) what is the SQL
that you are actually generating..?

Tim F
 
T

TCD

So (a) what is the SQL you are trying to aim at and (b)
what is the SQL
that you are actually generating..?
Click to expand...

That is the problem unfortunately. We are unsure how to
do jsut that. Basically we want the form to have data
entry fields for both name and date. The date part is
what kills us.
 
T

TC

Post:

- the exact field names that you pan to use, and

- one example of an actual value that you might want to put in each field.

Then someone can easily tell you what is the right SQL.

TC
 
G

Guest

Here is the example from a prior post. Thanks

Private Sub cmdInsert_Click()

Dim db As DAO.Database
Dim strSQL As String

Randomize

Set db = CurrentDb

strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(AppCode, EmpName, Date)"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ",""" & Me!EmpName & """);"



Debug.Print strSQL

db.Execute strSQL

db.Close
 
T

Tim Ferguson

Here is the example from a prior post. Thanks
Click to expand...

Arrggh - we all already know this is wrong: TC and I are trying to get at
something that might be right!

In this original code, you have a line that says;

Debug.Print strSQL

which prints the actual query to the immediate window -- open the VBA
editor, select View | Immediate Window, and copy-and-paste the entire SQL
string into a message.

Next we need to identify what you actually _do_ want to do. We need (a) the
fields in the table you are trying to append to, and (b) examples and
nature of the data you are trying to put into them. For example:

AppCode......... 1093934784 This is a random number
EmpName......... Eric This comes from..?
Date............ 2003-12-05 Today's date, or someone's birthday.

By the way, Date is still a stupid name for a field: please change it to
something else.

Hope that helps


Tim F
 
T

TCD

Yes, AppCode is the so called random number that we are
seeking. Date is the date of the request, MM/DD/YYYY. Our
Microsoft Press books use the name Date, so that is news
to us. But ReqDate will work, if you like. EmpName is the
name of the employee requesting the code acquired via
drop down in the format: Smith, John. This data is
entered into a simple data-entry form that obviously
feeds Table1. This data will be entered by a Supervisor
for an employee requesting approval. And as mentioned
prior to, approvals are almost never done more than once
a day for an employee, so the re-occurrence of a random
number for that person on that date won't be an issue.

By the way, the last post was not done by myself.
 
T

Tim Ferguson

Yes, AppCode is the so called random number that we are
seeking. Date is the date of the request, MM/DD/YYYY. Our
Microsoft Press books use the name Date, so that is news
to us. But ReqDate will work, if you like. EmpName is the
name of the employee requesting the code acquired via
drop down in the format: Smith, John. This data is
entered into a simple data-entry form that obviously
feeds Table1. This data will be entered by a Supervisor
for an employee requesting approval. And as mentioned
prior to, approvals are almost never done more than once
a day for an employee, so the re-occurrence of a random
number for that person on that date won't be an issue.
Click to expand...

Right: so it sounds like you need a command like this:

INSERT INTO Table1 (AppCode, ReqDate, EmpName)
VALUES( 12345, #2003-06-12#, "Eric");

and notice how all the different data types are delimited properly[1]. Now,
to make up this string, it's just a question of taking things bit by bit.
First of all get the values -- I don't know where these come from, but I
really don't need to, as long as you do.

Dim dwAppCode as long
Dim dtReqDate as date
Dim strEmpName as String

dwAppCode = GetNewRandomFiveDigitNumberFromSomewhere()
dtReqDate = LookUpRequestDateFromRequestLog()
strEmpName = SecurityObject.CurrentEmpName

and put the actual command together. Once again, make sure that the
different data types are formatted and delimited properly

Dim strSQL as string
strSQL = "INSERT INTO Table1 (AppCode, ReqDate, EmpName) & _
vbNewLine

' numbers don't need anything special, but this will catch
' an illegally big number > five digits
strSQL = strSQL & "VALUES (" & Format(dwAppCode,"00000") & ", "

' dates have to have # chars round and must be in a jet-
' compatible format. I prefer the ISO standard
strSQL = strSQL & Format(dtReqDate, "\#yyyy\-mm\-dd\#) & ", "

' strings have double quotes - if you are using SQL Server then
' they must be single quotes instead. Make sure you don't have
' any quote marks inside the EmpName. Anything like O'Neill or
' Gerry "the keyboard" Marks will cause problems.
strsQL = strSQL & Chr$(34) & strEmpName & Chr$(34) & ");"

and then make sure it looks right: remove this line once it is all working
properly

MsgBox strSQL

and if everything looks okay you are set to go:

Dim db as DAO.Database
Set db = CurrentDB()
db.Execute strSQL, dbFailOnError
By the way, the last post was not done by myself.
Click to expand...

Apols, in that case. I don't know where all these anonymous postings have
come from but I do wish they would go away as it is completely rupturing
any kind of continuity in these threads :-(

Hope that helps


Tim F
 
S

SteveS

In this line

strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))

why use (99999 - 10000 + 1) instead of 90000?? Yep, even
checked it using a calculator.


The above strSQL line always evaluates to:

strSQL = strSQL & Int(90000 * Rnd() + 10000)

Maybe it won't save clock cycles, but it is easier to read
and understand.

Just wondering if I am missing something....(be nice <g>)

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
-----Original Message-----
Both Code and Name will likely have different field names
when rolled out; we just went with those for simplicity
sake right now. Indexing and/or primary keys should
prevent duplicates. Anyway, we don't anticipate any
duplicates of the name, date and approval code since each
employee will get at most one per day anyway.

What we are struggling with is having a data entry field
for the date along with the existing data entry employee
name field.

It seems line 14 should be: strSQL = strSQL & "(AppCode,
EmpName, Date)"

It is line 17 and beyond that we have trouble with trying
to add date. (Example below)
Thanks

Private Sub cmdInsert_Click()

Dim db As DAO.Database
Dim strSQL As String

Randomize

Set db = CurrentDb

strSQL = "INSERT INTO Table1 "
strSQL = strSQL & "(AppCode, EmpName)"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & (Int((99999 - 10000 + 1) * Rnd() +
10000))
strSQL = strSQL & ",""" & Me!EmpName & """);"



Debug.Print strSQL

db.Execute strSQL

db.Close


-----Original Message-----
Use DateValue() function, which convert string to date.

strSQL = "INSERT INTO Table1 (Code, [Name]) " & _
"VALUES (" & (Int((99999 - 10000 + 1) * Rnd() + 10000)) & _
", DateValue(""" & Me!Name & """));"



Unfortunately that produces the same error message we've
been getting: 'Syntax Error in INSERT INTO statement. Any
other ideas?
Click to expand...

Hmm: this seems to parse into

INSERT INTO Table1 (Code, [Name]) VALUES (12345, DateValue("Eric"));

If you really mean to pass a function like

DateValue("Eric")

then I have no idea what you expect the Jet engine to do with it... The
most helpful thing would be to inspect (or post here) the result from the
Debug.Pring strSQL line -- the whole point is to be able to see what is
actually being passed.

By the way, I am sure that someone has already suggested that using
reserved words for object names like [Name] and
Code: 
 is a Really Bad
Idea.

By the way (2), how are you going to avoid giving the same code number out
twice?

B Wishes


Tim F

.
[/QUOTE]
.
[/QUOTE]
Click to expand...
Click to expand...
 
S

SteveS

Thanks, Tim

I'm in the process of learning SQL and I wanted to make sure I wasn't
missing something in the syntax.

Steve
 
T

Tim Ferguson

I'm in the process of learning SQL and I wanted to make sure I wasn't
missing something in the syntax.
Click to expand...

In my view, what you may be missing is a much better way to do it. I would
have a table of unique, validated and checksummed values and pick one out
of it and completely avoid all this messing about with random numbers.

IIRC, the original problem was to prevent users from inventing their own
task numbers and I have no idea how this method achieves that. Still, it's
not my database or my problem... :)


B Wishes


Tim F
 
T

TCD

Correct me if I am wrong, but I don't see where this will
create a random number for us.


-----Original Message-----
Yes, AppCode is the so called random number that we are
seeking. Date is the date of the request, MM/DD/YYYY. Our
Microsoft Press books use the name Date, so that is news
to us. But ReqDate will work, if you like. EmpName is the
name of the employee requesting the code acquired via
drop down in the format: Smith, John. This data is
entered into a simple data-entry form that obviously
feeds Table1. This data will be entered by a Supervisor
for an employee requesting approval. And as mentioned
prior to, approvals are almost never done more than once
a day for an employee, so the re-occurrence of a random
number for that person on that date won't be an issue.
Click to expand...

Right: so it sounds like you need a command like this:

INSERT INTO Table1 (AppCode, ReqDate, EmpName)
VALUES( 12345, #2003-06-12#, "Eric");

and notice how all the different data types are delimited properly[1]. Now,
to make up this string, it's just a question of taking things bit by bit.
First of all get the values -- I don't know where these come from, but I
really don't need to, as long as you do.

Dim dwAppCode as long
Dim dtReqDate as date
Dim strEmpName as String

dwAppCode = GetNewRandomFiveDigitNumberFromSomewhere()
dtReqDate = LookUpRequestDateFromRequestLog()
strEmpName = SecurityObject.CurrentEmpName

and put the actual command together. Once again, make sure that the
different data types are formatted and delimited properly

Dim strSQL as string
strSQL = "INSERT INTO Table1 (AppCode, ReqDate, EmpName) & _
vbNewLine

' numbers don't need anything special, but this will catch
' an illegally big number > five digits
strSQL = strSQL & "VALUES (" & Format (dwAppCode,"00000") & ", "

' dates have to have # chars round and must be in a jet-
' compatible format. I prefer the ISO standard
strSQL = strSQL & Format(dtReqDate, "\#yyyy\-mm\-dd\#) & ", "

' strings have double quotes - if you are using SQL Server then
' they must be single quotes instead. Make sure you don't have
' any quote marks inside the EmpName. Anything like O'Neill or
' Gerry "the keyboard" Marks will cause problems.
strsQL = strSQL & Chr$(34) & strEmpName & Chr$(34) & ");"

and then make sure it looks right: remove this line once it is all working
properly

MsgBox strSQL

and if everything looks okay you are set to go:

Dim db as DAO.Database
Set db = CurrentDB()
db.Execute strSQL, dbFailOnError
By the way, the last post was not done by myself.
Click to expand...

Apols, in that case. I don't know where all these anonymous postings have
come from but I do wish they would go away as it is completely rupturing
any kind of continuity in these threads :-(

Hope that helps


Tim F

.
Click to expand...
 
T

Tim Ferguson

Correct me if I am wrong, but I don't see where this will
create a random number for us.
Click to expand...


You can get your random number from wherever you like -- this post was
about getting the SQL command right.

Incidentally, I still don't believe that random is what you want, but that
of course is up to you.

B Wishes


Tim F
 
T

TCD

Actually our whole goal was to create and store random
numbers to be used as approval codes. We can get it to
work for the code and agent name but not date. That was
what I was hoping to accomplish in my original post.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Export Filtered Form Data To Excel 0
Error 3141 Cannot resolve 3
Update query issue 0
Error 3061 - too few parameters 2
Error 3075 Missing Operator 7
Broken Module 1
Update table from another table 4
Create new table in backend through VBA 2

Top