Random Number in Query

Hi.  I am trying to get a Random number between 0 and 5 to appear on each row 
of a query.  I can get the same number on each row (it changes every time I 
run the query) but cannot get a different random number for each row.

Basically, I want to create some Test data by adding the Random number to a 
Received Date and then saving this new date as a Completed Date.

I have created a Module to generate a Random number:

Function Random_Number() As Integer

    Randomize
    Random_Number = Int(Rnd * 6)

End Function


Then, I call the function from a query:

Field = Number: Random_Number()

Can anyone tell me what I am doing wrong?

Cheers,
Steve.

0
Utf
1/3/2008 10:51:01 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
2571 Views

Similar Articles

[PageSpeed] 3

Sorry, Ive just answered my own question!!

I used my Unique ID field in the Rnd command:

RandomNumber : Int(Rnd([Workflow_Ref])*6)

And that gives me a value between 0 and 5 for adding onto my Received date.

Thanks Me :)



"FBxiii" wrote:

> Hi.  I am trying to get a Random number between 0 and 5 to appear on each row 
> of a query.  I can get the same number on each row (it changes every time I 
> run the query) but cannot get a different random number for each row.
> 
> Basically, I want to create some Test data by adding the Random number to a 
> Received Date and then saving this new date as a Completed Date.
> 
> I have created a Module to generate a Random number:
> 
> Function Random_Number() As Integer
> 
>     Randomize
>     Random_Number = Int(Rnd * 6)
> 
> End Function
> 
> 
> Then, I call the function from a query:
> 
> Field = Number: Random_Number()
> 
> Can anyone tell me what I am doing wrong?
> 
> Cheers,
> Steve.
> 
0
Utf
1/3/2008 11:00:01 AM
hi Steve,

FBxiii wrote:
> Hi.  I am trying to get a Random number between 0 and 5 to appear on each row 
> of a query.  I can get the same number on each row (it changes every time I 
> run the query) but cannot get a different random number for each row.
The query optimizer handles all functions as deterministic. So a 
function is only called again when its parameters are different.

> Function Random_Number() As Integer
Make it

   Public Function Random_Number(ADummy As Long) As Integer

> Then, I call the function from a query:
> Field = Number: Random_Number()
And use it

   Number: Random_Number([ID])

where [ID] must be an auto-increment field.


mfG
--> stefan <--
0
Stefan
1/3/2008 11:04:24 AM
Reply:

Similar Artilces:

XPath Query into Datagrid
Hello, I have a XML File that looks something like this. <ItemList> <ProductType>Google</ProductType> <EmployeeName>John,X,Doe,john.doe@domain.com</EmployeeName> <EmployeeName>Jane,X,Doe,jane.doe@domain.com</EmployeeName> <EmployeeName>Larry,X,Doe,larry.doe@domain.com</EmployeeName> </ItemList> What I am trying to accomplish is do an XPath Query that finds all employees where the ProductType is swTitle.Text(Product Name that a user enters into a form). But I want to display the result into a datagrid with 4 columns ( FN, MI, LN, EMAI...

SQL query with Many-To-Many relationship???
I am trying to write an SQL statement that is giving me some trouble, and I was hoping that somebody here could lend me a hand. CONTEXT I am working in Access 2007. The tables concerned are the Students, Classes and StudentsAndClasses. The relationship of Students to Classes is many-to-many, i.e. each student can attend several classes and each class can have several students. StudentsAndClasses table is used to help define this relationship and works something like a class roster. The Students table has StudentID as its primary key. The Classes table has ClassID as its primary key. The St...

Complex query, do I use union?
Hi I've posted about something similar some time ago and got a response telling me to use a union query, but I've never got it to work.... Anyone got any more advice? I've got 2 tables with a 1-many relationship as follows: Table1 - ReadSignRecords, fields RSRecordID (pk), DocNo, DocName etc Table2 - ReadSignDistribution, fields DistID (pk), RSRecordID (fk), SentToName Table 1 stores a list of documents and table 2 lists the people that each document was sent to, a distribution list. I want to run a query that shows the following: RSRecordID, DocNo, DocName, List of SentT...

Disapearing Query Results!
Hello Dear I have some Combo Box's on a form that extract data from queries When queries run,the results show to the user that is not necessary! Is it possible to hide query results from user? Thanks Best Regards The question is unclear. Do you want to show the results of a query in the form? Are you seeing query results in data sheet view and don't want to? If this is the case, then you must be executing the query in some manner and opening the query. We cannot see your application and cannot see what you are doing. John Spencer Access MVP 2002-2005, 2007-2...

Make a change to an Update Query
I need to make changes to an update query but when I open the desing view it only shows the field that being updated. How do I view the hidden fields? Answered in your prior post John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County JLUTHER wrote: > I need to make changes to an update query but when I open the desing view it > only shows the field that being updated. How do I view the hidden fields? ...

performance issue with query
Hi, I have 40 million rows table, simplified: create table t (id int, type char(1), cost decimal(19,5)) Type column can have 3 values, 'A', 'B', and 'X'. If type has only 'X' for certain ID, its cost should be included into calculation. Otherwise no. All others are included into calculation. So select id into #temp from t where type<>'X'. select id, type, sum(cost) cost from t where type<>'X' group by id, type union all select id, type,sum(cost) from t as t1 where not exists(select 1 from #temp where id=t1.id)...

Query Filter using Not In
I want to filter a query to not show a list of values. I know to use Not Iin('something', 'something'), but what if I want to use a wildcard? How do I do a not in query using a wildcard? Thanks! Use a temp table, one field, CompareTo, to hold all the values, in different rows, one value you want to compare to, per row. Without wild card, use an equality: SELECT whatever FROM table1 LEFT JOIN tempTable ON table1.fieldName = tempTable.CompareTo WHERE tempTable.CompareTo IS NULL that is, indeed, something similar that the query wizard about finding unmatched reco...

multi-field query
Hi all,I have a select query that consist alot of tables and fields,thatissue is making the proccess heavieris it posible to make it faster then its current stage?otherwise ineed to run a make-table query that will build a table but thisproccess will blow the file sizeany ideas? thread wrote:> Hi all,> I have a select query that consist alot of tables and fields,that> issue is making the proccess heavier> is it posible to make it faster then its current stage?otherwise i> need to run a make-table query that will build a table but this> proccess will blow the file size> any...

Number notation
I having trouble plotting a column of number that was save in scientifi notation like 6.2F-8, 7.1F-6 etc. I couldn't reformat this as 6.2E- using the "Format" button. It seems Excel doesn't recognize 6.2F-8 as number notation. I was able to plot number in 6.2E-8 notation. Is ther a function to convert this into recognized number notation? Wil conditional formatting work here -- Message posted from http://www.ExcelForum.com Hi A Find/Replace F for E seemed to work for me. -- Andy. "Fifi >" <<Fifi.1a7sf6@excelforum-nospam.com> wrote in message ne...

Mulit Function Query
I am working on this query. I want to run it, so it will show what my users are missing in their files. But only what they are missing. What would the criteria be in order to do that? On Tue, 6 Apr 2010 09:56:21 -0700, LewisDUA <LewisDUA@discussions.microsoft.com> wrote: >I am working on this query. I want to run it, so it will show what my users >are missing in their files. But only what they are missing. What would the >criteria be in order to do that? You'll have to give us some more explanation. I know that one thing that's missing from my files i...

Access 2003 treats embedded functions in nested queries as a reference rather than a value
I've run into a curious behavior with Access 2003. I would like to know if Access 2007 handles things differently. I have to provide a bit of background before I can describe the problem. In this context I'm using Access 2003 as a tool to analyze and manipulate string sets. BACKGROUND: Microsoft Access 2003 allows one to embed "expressions" in the query design form. I believe these "expressions" are drawn from Access Basic (VBA Access). I often make use of the "Built-In Functions" "Program Flow" "IIf" and "Switch" functions....

WEB QUERY #7
Hi , I am scrapping a website i want to maintain the query in webquery is that possible ? pls give some example and links. Thanks, Dasbaba You need to give a better explanation and the url involved. >>maintain the query in webquery<< ??? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Souravmay Das" <souravmd@gmail.com> wrote in message news:2c91f6f2-c1c4-4ce2-a417-e0acaee5ca09@l33g2000pri.googlegroups.com... > Hi , > > I am scrapping a website i want to maintain the query in webquery is > that possible ? pls give so...

Limit number of Recipients
Under SMTP, in Exchange 2003, there is an option to limit the number of recipients. It splits the outgoing mail into multiples of 100 (default). However, most Service Providers seem to block any email that have more than 25 recipients. This option is not allowed. Is there a way to make this option accept Limit Number of Recipients to 25 instead of 100? On Sun, 25 Feb 2007 23:54:05 -0800, Paul <Paul@discussions.microsoft.com> wrote: >Under SMTP, in Exchange 2003, there is an option to limit the number of >recipients. It splits the outgoing mail into multiples of 100 (defau...

Week numbers
Hi, I have a problem with my query which shows week numbers on a column. Week numbers derived from date field of table. Column has below string: WEEK: Format$([Table1].[Date1];'ww') Above equation gives next week number for Sundays. On my computer date system is normally adjusted to start the new week number on Mondays. At the other hand I have a string calculating week number for my form like: WEEK.Value = Format(Date1.Value, "ww", vbMonday, vbFirstFourDays) Above formula gives correct result. For example: 24th. February 2008 ==> My form gives 8 and my query give...

Count number of cells with "CAC"
I have a spreadsheet. One column has cells that have either A, CAC, or Other. How can I have Excel enter the number of each type of cell in ONE cell? -- Janet A. =COUNTIF(A:A,"CAC") or =COUNTIF(A:A,B1) where B1 houses your text. -- Regards Dave Hawley www.ozgrid.com "Janet A. Thompson" <JanetA@discussions.microsoft.com> wrote in message news:EC5E3F85-DAE7-4174-9182-FAD9DE244E1B@microsoft.com... >I have a spreadsheet. One column has cells that have either A, CAC, or >Other. > > How can I have Excel enter the number of each typ...

how to bind an query to a gridview programmatically?
Hi, i want to bind programmatically the records of a select query to an existing gridview. I can get the records via dtreader, but i don't know how to get them into the gridview (which is created in the aspx file) Thanks Luc My attempt: connectionstr = ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString() connection = New SqlConnection(connectionstr) comd = New SqlCommand() comd.Connection = connection comd.CommandText = "select field1 from table1" connection.Open() dtreader = comd.ExecuteReader If dtreader.HasRows Then...

counting the number of occurences
I have a list that shows the worker ID and the result of each work item. The number of work items differs for each worker ID--the results of each item is either "A" or "D". This is an example of a portion of the list: Worker Status 008Q A 008Q D 008Q A 098Q D 098Q A 098Q D I am trying to set up a new list that counts for each worker ID the total number of work items with status "A". How can I go about doing this? Thanks. Let me know if you need more information on this. Assume your data...

Mr. Scott McPhillips' answer on query
hi,Mr. Scott so am i corerct to say that CMySocket* ps = new CMySocket(...);//creat a new socket- socket1 m_SocketList.AddTail(ps);//add the socket1 to the tail of new created socketlist m_sListenSocket.Accept(*ps);//accept the connection with socket 1 then how do i accept the next connection?? do i put your codes in a while loop??? are u saying that i dont need a thread for this?? would you pls care to elaborate further? also..do i need to configure the new socket??because i am using AsyncSockets tks a lot! God Bless. hi Mr. Scott, i have done this... if (m_bConnected) { // Create...

select query with aggregate?
I need to select all rows from table1 for a date range but only when 1 or more rows exist in table2 for the primary key in table1 for the same date range. What is the best way to achieve this in SQL 2005 sp4? TIA Matt Assuming you are returning the primary key in your select list. SELECT DISTINCT Table1.* FROM Table1 JOIN Table2 ON Table1.Table1ID = Table2.Table1ID WHERE Table1.MyDate BETWEEN @date1 AND @date2 -- Not sure if this is needed -- AND Table2.MyDate BETWEEN @date1 AND @date2 -- or -- SELECT Table1.* FROM Table1 WHERE Table1.MyDate BET...

Query of Office Spreadsheet (Web Components) in a Access Form
I put the Office Spreadsheet in a form; I accessed the 'Command and Options' button and went to the 'Datasource' tab; I put the database and the query in the fields at the normal process in this tab...; I can manipulate this datasource fields of the spreadsheet using the propery 'connectionstring' on VBA Two questions about it: 1)I cannot put the same database as I'm using, because it says that the database is already opened by another user (in this case, me). I can do it only putting another database. Can I put the active database on the datasource tab of t...

Query Left Command Help
Hi All, I want to find the first 2 or 3 letter in the field. The data is the users initial followed by a number. Sometimes they enter there 2 or 3 letter initials. What can I do to find the first 2 or 3 letters and omit the numbers? Thanks Matt -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1 Matt One approach might be to build a procedure that starts with the first character, tests to see if it's numeric, then moves over one and re-tests until the test fails because the nth cha...

Adding Sequential numbers to a new feld in access table.
I am working with a table which is actually a merge of 14 other tables, therefore there is no real index key. Table 1 Index was 1-5000 Table 2 index was 1-5000 In the merged database there are about 52,000 records, but no index key. I added a field to the table named Index and would like to populate that field with a unique index number. Does anyone have any ideas on how I could create an update query which would add 1-52,000 to this field. Thank you for your help. rjcmi wrote: >I am working with a table which is actually a merge of 14 other >tables, therefore t...

Update Query and Timestamp
I need to update a main table with data from other tables. However, I need to keep track of the changes to the main table. I thought of adding a field to the main table with a datestamp. When the records get updated a datestamp would be automatically entered. How do I set this up? Is there an easier or better way of doing this? Thank you in advance for any help provided. If the modifications occur through a form, in the form before update event procedure, update the said field: Me.TimeStampFieldNameHere = Now() (That assumes your form is bound to your table. In fact, Me.T...

Passing an expression from a table field to a query
The quick question (Access 2003): If I store an expression in a text field in a table, can a query convert it from text and use it? The expression would contain fields from another table that is joined when the query runs. The longer explanation: I have a table which is a list of "activities" to track the progress of manuscript submissions to a journal. The table (tblActivities) only contains two fields - ActivityID and ActivityDesc. There are about 25 activities. The data tables involved are a main manuscript table and a sub-table of the activities for each manuscript. Each ...

Keeping A Derived Column Of Numbers ?
Hello, Using Excel 2007 Let's say I have a column of numbers in Column A that were derived from some calculation done on numbers in Column B. e.g. in A6: =B6/1000 etc. I wish to delete Column B, but keep the derived Column A numbers. How do I define the Column A numbers, now, such that they are not dependent on Column B, which will no longer exist ? Thanks, Bob You can convert the formulas to values (before you delete column B). Select column A Rightclick anywhere in that selection. Chose Copy. Rightclick a second time and choose Paste Special. Check Values. Then delete colu...