Modules & VBA :: Random Number Sequencing Based Off Of A Certain Criteria
Aug 21, 2013
I have a RN generator that looks at 9 different lanes and puts in a number based off of a certain criteria but I was wondering if in the frmLanes the actual "Go" button was an incoming number how could I get my current random number generator to look at that number and determine if the current number is within 5 of the "go" number?
Any way to have a beginning number that I would enter into a text box and have the number generator kick off based off of that number. I have included a copy of my db for viewing.
View Replies
ADVERTISEMENT
May 30, 2014
Lets say you have a table with an AutoNumber field.You populate it with 100 records.You delete the last 10 records (91-100)Now, I certainly know you can refresh the AutoNumber sequencing by compacting the database so the next record entered is 91.
My question is about NOT refreshing the AutoNumber sequencing.Lets say you DO NOT COMPACT and DO NOT refresh the AutoNumber Sequence.So, in that scenario, after deleting records 91-100 from the table, is there any programmatic way to determine that the next actual AutoNumber for that table will be 101?
View 3 Replies
View Related
Sep 11, 2014
I am trying to get the max of an integer field if the ProjectNumber field is equal to the ProjectNumber field in the current record. I have used code similar to the one below before but it was on a date field, then add one.
Me.RevisionNumber = Nz(DMax("[RevisionNumber]", "dbo_ProposalLog", "[ProjectNumber] =" & Me.ProjectNumber), 0) + 1
The ProjectNumber field is a text field.
View 6 Replies
View Related
Jun 2, 2015
I have a log in screen that my users must use before they can use my db. if they enter the wrong username/password they get the usual message boxes. I also thought it would be a nice personal touch if once they've logged in they get a little greeting, so i made a message box that says "Hello " followed by their first name
This did the trick for a while, however theyre sick of seeing "Hello" and want other greetings, i figured this wouldnt be too complicated and took me only about 10 minutes or so to implement.
Basically i created a table (tblGreetings), has two fields, an autonumber field (ID) and a short text field
I've added the following code into the login sequence:
Dim greetings As Integer
Dim greet As String
Dim randomNum
greetings = DCount("ID", "tblGreetings")
randomNum= Int((greetings - 1 + 1) * Rnd + 1)
greet = DLookup("Greeting", "tblGreetings", "[ID]=" & randomNum)
which in theory should generate a random number between 1 and "greetings" (which is an integer representing the number of greetings ive stored in my table), currently greetings=9
then it should pick a greeting from tblGreetings using the random number as an ID
This more or less works, except whenever the DB is opened and a user logs in, the "random" number is the same every time, it always seems to be 7 (which corresponds to the greeting "G'day").
Why this random number is always the same? it changes if i run the code a few times in the same session, but once the database is closed and reopened it still gives off the number 7, every time
View 3 Replies
View Related
Oct 3, 2013
I'm having trouble with my VBA module.I have a random test generator which pulls records based on a category from my table into a temp table using a make table query. I use the following code:
Private Sub Command2_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "1", acViewNormal, acEdit
DoCmd.OpenReport "WrittenExam", acViewPreview, "", "", acNormal
Reports!WrittenExam.lblTitle.Caption = "Exam Name"
DoCmd.OpenReport "WrittenExamAnswerSheet", acViewPreview, "", "", acNormal
Reports!WrittenExamAnswerSheets.lblTitle.Caption = "Exam Name - Answer Sheet"
End Sub
My querry makes the table, and then generates two reports (my exam, and the answer sheet). I'm getting an error 3211, saying the temp table is already in use by another process when trying to generate both reports. I used a Macro before, but I have a need for custom report headings, so I'm using VBA.
View 1 Replies
View Related
Sep 20, 2005
Hello all,
I'm having some difficulty obtaining the answers I want from my query. I'd have been more comfortable using filters in excel to find the answers, but my dataset is too large in this instance.
I devised a query which I thought should achieve the results, but based on a subset analysed in Excel the answers are significantly different.
My table has about thirty fields, the query I've produced is this:
SELECT AH_orphans.Product, AH_orphans.Comments, AH_orphans.ESP, AH_orphans.[ESP Parts],
<snip, all other fields in table>
FROM AH_orphans
WHERE (((AH_orphans.ESP)="n") AND ((AH_orphans.FRU)="y") AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a")) OR (((AH_orphans.ESP) Is Null) AND ((AH_orphans.FRU) Is Null) AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a"));
What I'm hoping for is the subset which match the following conditions:
- ESP field is not "Y". Possible values Y, N, null
- FRU field is not "N". Possible values Y, N, null
- Only those records from EntitlementName which include the substring 'uptime'
- Only those records from Account which start with the subtring nation
- Only those records from Type which include the substring primar
- Only those records from [Is a parent?] which equal #N/A.
Asking the same questions in Excel I get around 1.5k records. If I use the quoted query on the same dataset in Access I get precisely 11.
Thanks for reading this far! If anyone can spot the flaw/s in my query, or indeed advise that I'm going at it completely the wrong way I'd be very grateful of some advice.
Cheers,
Alex
View 4 Replies
View Related
Mar 17, 2014
I am working on a form and need it to be able to generate a random number to use as an invoice number. The only parameters i need it to meet is that it is at least 5 digits in length. I was hoping i could use a button to make the number generate in a separate box.
View 5 Replies
View Related
Jun 5, 2014
I have a report that I am trying to complete based on several queries. I am trying to count the number of records based on certain criteria and using the following DCount.
=DCount("[Calculated time]","IPG1","[Calculated Time] <= 0.04" And [Ship-to party] In ("SN00207PJZ","SN09162XXX","SN09324XXX"))
I want to count the number of IPG1 records that are under .04 and have the Ship-to party of the ones listed. I have tried everything that I can think of to get it to work but can's seem to get it to. I figure it's something easy but I don't see it.
View 4 Replies
View Related
May 12, 2014
I want to get remarks as like this i have a query which is showing the total of different fields suppose the total is 77 and I want to give a crietria field by name of net level which shall work like this ..
if the number is from 0 to 33 then then the result should be C-1
if from 33 to 40 then C-2
if from 41 to 50 then C-3
if from 51 to 60 then B-1
if from 61 to 70 then B-2
if from 71 to 80 then B-3
if from 81 to 90 then A-2
if from 91 to 100 the A-1
View 8 Replies
View Related
Jul 8, 2006
Quick question, how do I have access generate a random number between 1 and 8? I want it to generate either a 1,2,3,4,5,6,7, or 8. (no zero, no nine)
Expr1: Fix(Rnd(8-1)*10*Rnd()) is what I have been using, but this always generates the number 2? I do want the decimals to be rounded to the nearest integer, thus I have Fix.
If someone could please help me, it would be greatly appreciated! Thanks in advance.
View 3 Replies
View Related
Feb 11, 2006
How can I make random auto numbers 8 digits in length (including leading zeros) and always positive?
Thank you
View 2 Replies
View Related
Sep 25, 2006
Hello,
I have this query:
SELECT UNIT.Code, Rnd() AS Expr1
FROM UNIT;
The problem is that in the Expr1 field all the numbers are the same. I would like to have random numbers that are different for each record (generate new random number for each record)
Thanks for your help
Cheers
View 2 Replies
View Related
Feb 13, 2006
Ok, I apologize in advance for my lack of knowledge but I need Help!!
I have a database form that has a O_Num field that populates the Order table. Now the client wants the order number to be a randomly generated number. this is the primary key and I cant use autonum because there is another field in the table that is autonum.
I have tried to use the following in the OnClick field, doesnt work
=Int((1000 * Rnd()) + 1)
View 1 Replies
View Related
Mar 5, 2005
Hello i've got to make a project for school and thought I would make a rental shop like Blockbuster. But I need to create a number that will be assigned to each customer when they become a member. So I need to generate a random number that uses a certain amount of digits (I was thinking about 8) and that won't be used again can you help.
rn2j
View 2 Replies
View Related
Jan 30, 2005
Hi All,
I'm designing this database, in which, I have to give each record a unique random (or sequance) number of 5 digits, giving that the first digit must be retrived from another field.
Please Help, I need this ASAP!
Any help will be very much appreciated!
Thanks,
CS.
View 1 Replies
View Related
Oct 22, 2013
Anyway, here is what I am looking to do:
1.) Generate a random number between 1 and 100 and assign the result an identifier, like a person's name.
2.) Generate a second random number between 1 and 100 and assign the result a different identifier, like another person's name.
3.) Compare the two randomly generated numbers.
4.) Have the identifier of the LARGER random number displayed on the screen.
I would like to have this linked into a button click.I am hoping there is an easy way to accomplish this task.
View 3 Replies
View Related
Aug 3, 2012
On an Access database, I would like an ID number to be generated randomly.
This ID number needs to be within the range of 11000 - 99999.
How is this possible?
View 13 Replies
View Related
May 5, 2014
I have table with [EmployeeName] and [DateReceived] column what I am hoping to accomplish is to get 5 samples of each Employee for each Date received.
View 5 Replies
View Related
Dec 4, 2013
I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.
Here is what I have so far:
1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )
2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )
BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.
E.g.
=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')
and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.
View 6 Replies
View Related
Jan 13, 2014
I am exporting from access to excel using the code below, but I'd like to edit the code so that it exports to excel for each original value in column A. For example, if column A contains the values "Type A", "Type B" and "Type C" then I'd like to export/save three different excel files (one for "Type A", one for "Type B" and one for "Type C"). If "Type A" appears in column A seven times then I'd want to export all seven rows for columns A through E.
Code:
Private Sub ExportToExcel_Click()
'Declaration of variables for file path
Dim CurrentFolder As String
Dim FileName As String
Dim CurrentCycle As String
'Initializing
CurrentCycle = Format(Date, "yyyymm")
FileName = SVCnumber1 & "Output.xls"
[code]...
View 14 Replies
View Related
Oct 30, 2013
I have a query I am trying to build currently which creates a running total based on set criteria. I essentially have three columns (All times are in minutes). The visit ID, the duration of a visit and the time difference between the current and previous visit. e.g.
ID Duration Difference
1 30 0
2 45 10
3 15 20
4 60 10
5 15 10
What I would like to achieve is the following:
ID Duration Difference running total
1 30 0 30
2 45 10 85
3 15 20 15
4 60 10 85
5 15 10 110
So a line will have the calculation of the current time + the difference between the current and previous visit if the difference is below 20. I have the following:
Code:
Public Function Cont20(MyVal As Long, MyDif As Long) As Long
Static OldValue As Long
Dim NewValue As Long
If MyDif >= 20 Then
NewValue = MyVal
OldValue = 0
[Code]....
Which I put as Cont20([Duration],[Difference])
The problem seems to be on the return aspect, its not calculating correctly. I had the formula in Excel which worked great, but this is proving troublesome. As a note the Excel formula was such that it would add the previous running total and then add the current duration and current diff. e.g. In cell A7 the formula would be A6+B7+C7 and so on.
View 6 Replies
View Related
Mar 9, 2006
My company does daily, weekly, and monthly drawings using mountains of entry tickets and a barrel that has seen better days. So needless to say this system has to go. I hope to be able to do it digitally.
I would like to do a random number generator to pick the winners based off of a ticket number range. The ticket numbers issued to the customer would correspond to the customer account number. To complicate things I would really love to have the random numbers displayed on the screen so people feel they are watching the randomization pick in progress with the winner name popping up.
I have searched Help for Rnd function, as well as googled. I haven had any luck finding what I need. Is what I am looking to do possible in access? I had something similar in Excel a few years ago but lost it.
View 7 Replies
View Related
Mar 24, 2005
hello all
I am trying to set column values to a random number between 3 and 5
PMRatingTokenID: textH1: doubleYearID: textCode:UPDATE PMRating SET PMRating.H1 = (5+3-3)*Rnd()+3
This shows type mismatch error . What is the problem here..
please do help...Thank you
View 1 Replies
View Related
Sep 28, 2006
Hi.
I have major report due within hours.
I need to add a field to existing table that will be a 9 digit number, non duoplicating or - to take an existing field of 9 digits and allow it's values to change to random or incremental, non-duplicate numbers of 9 digits. I already have an autonumber field I must keep.
Russ
View 2 Replies
View Related
Dec 3, 2014
I have a table that contains many records of events. I've already created a multiple item form based on the table. My form contains 2 textboxes for input of a date range and a dropdown box to select "yes or no".
What I am having difficulty with is the query that will count the records by type insert it into the recordsource such that the multiple item form will appear like this:
Event Type| Count
Input | 4
Output | 2
Update | 3
How do I go about doing so? I have been testing with this simple query:
Code:
Private Sub Command8_Click()
Dim Task As String
Task = "SELECT EventType, Count(EventType) FROM Final GROUP BY EventType;"
Me.RecordSource = Task
End Sub
But the column that contains the count keeps appearing as #name?
View 12 Replies
View Related
Apr 23, 2015
I'm incrementing a counter every time I click on a combobox by using the on click event and this code:
Code:
POcount = POcount + 1
I have 5 fields on my form: GWS1, GWS2, GWS3, GWS4 and GWS5.I want to store the value of the combobox when clicked into the correct GWS field. For example: If I click the combobox 3 times it would store the value of the combobox after the 1st click in GWS1, after the 2nd click in GWS2 after the 3rd click in GWS3.I tried
Code:
me."GWS & POcount" = combobox1
but that's invalid syntax.
View 4 Replies
View Related
