Query Array
Jun 6, 2005
I was wondering if this was possible or not.
Can I write a function like the one below, which will accept a value and return many values?
I would like to set various columns from one score. If I have something like “Expr1: returnArray(42)” in the query, can the function return the 3 values and put them in separate columns?
If anyone has any ideas it would be greatly appreciated because I am re-writing my software to have no calculated values.
Public Function returnArray(score As Integer) As Variant
Dim arrMatrix(2) As String
'some calculations here to set the arrMatrix array.
arrMatrix(0) = "Joe"
arrMatrix(1) = "is"
arrMatrix(2) = "Stuck"
returnArray = arrMatrix()
End Function
View Replies
ADVERTISEMENT
Apr 11, 2008
Hi, I have a problem deleting a specific row and field that my query have.
What I want to do is mainly delete a specific rows in the my Query table that has : Field 1, Field 2, Field 3, Field4
what i tried to do was doing is create and array that loop through the Query table and check in field 1 for a certain value and if that certain value appears I want to delete that row: for example
Dim IntRow As Integer
Dim IntCol As Integer
Dim varData As Variant
For IntCol = 0 To numFields Step 1
For IntRow = 0 To numRows Step 1
If (rst.EOF = False) Then
'Check Field 1 for value 47-72 to delete
Else
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72) Then
varData(IntCol, IntRow).Delete
End If
End If
Next IntRow
Next IntCol
In the example Above my syntax is not correct for :
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72)
and for varData(IntCol, IntRow).Delete.
can anyone help me to find the exact syntax i need to use to execute the program above?
THANK YOU!
View 2 Replies
View Related
Dec 1, 2005
Hi,
In the query field i am putting: -
Consecutive Months: Query_Month_Consecutive([Report_2_group_data].[Availability], array([Monthly_availability_CT].[12],[Monthly_availability_CT].[11],[Monthly_availability_CT].[10],[Monthly_availability_CT].[9],[Monthly_availability_CT].[8],[Monthly_availability_CT].[7],[Monthly_availability_CT].[6],[Monthly_availability_CT].[5],[Monthly_availability_CT].[4],[Monthly_availability_CT].[3],[Monthly_availability_CT].[2],[Monthly_availability_CT].[1]))
Basicly the values of them fields are entered into the query, all fields exist and everything is ok.
Anyhow the function is not loaded, i have tested with a simple msgbox and it does not do it.
However when i simply do
Consecutive Months: Query_Month_Consecutive([Report_2_group_data].[Availability], Monthly_availability_CT].[12)
It works and the msg box appears?
I really need it to be an array for what im doing any help would be great
Thanks
k0r54
View 3 Replies
View Related
Mar 12, 2014
Basically, what's the best practice or how do we store a query's value into an array then checking what the max or min value is and how to check if let's say "4" is in the array?
View 1 Replies
View Related
Sep 16, 2014
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
View 3 Replies
View Related
Jun 4, 2007
I have a table that holds the names of locations and a count for each location. example below
LocationCount
INR100
INR16
MTU245
MTU123
INR300
RVO1234
TC45
TC188
The table is always changing and I need to create a query in code that sums the count, grouping for each location. These values will need to be stored in a dynamic array for further processing. How do I store this query value into a dynamic array.
array would need to store these values
(INR, 316)
(MTU, 368)
(RVO, 1234)
(TC, 233)
Thanks in advance
View 2 Replies
View Related
Mar 16, 2005
Hey, is there a way to get the full list of tables in the database by using VBA? I am trying to do a backup function, where I want the database to fetch all the table names and put them into an array, to export them one by one to the backup file........hope you guys understands me.....thanks in advance
View 2 Replies
View Related
May 31, 2005
Hi all,
I have a table with multiple records per individual..
I need to search through all of their results to flag up those in a query that do not match the approved list..
what is the best way to do this?
I am trying a recordset loop but am struggling a bit...
here is my code so far
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String
Dim strPscale As String
strSQL = "SELECT Eng_Speaking, Eng_speaking from qry_Sub1_crosstab_Crosstab;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Me.Text1 = ""
Do While Not rst.EOF
strPscale = rst.Fields("Eng_Speaking") & ";"
rst.MoveNext
Loop
Me.Text1 = strPscale
Set rst = Nothing
End Sub
but I need to somehow incorporate an array into this to check the score...
any ideas?
ta
John
View 4 Replies
View Related
Dec 27, 2006
I have built the first part of a table analyser into one of my projects as below:
It lists a complete list of tables except system tables for the db being assessed and writes all the table names to a table then the table properties, the fields plus there properties And relasionships and Indexes.
The Idea behind this is When I build a system I always use a frontBack Arrangement and versions within my management system.
there are always two Datafiles one the working datafile where the changes are made during design and the production model used by the users which is the one that will need to be updated by this tool.
The problem I'm having is I don't need to store all the field properties within the tables as a lot wont be used for updating the production model.
So I've been looking at ways I can generate a list of property names that I want stored so when my code loops through the properties list it can before writing the property name and value to the table check it against a list.
My question is would it be better to use an array or enum so I could check for an item and how could I best achieve this as I've not used enums before and I'm not that good at Arrays but there's more help there.
I have in the past used a list like: Prop1,prop2,prop3 which I then check using instr like instr("Prop1") which will return a start position so I know it's in the list but I think this is a bit messy so want to make the improvements
Any help would be much appreciated
mick
View 2 Replies
View Related
Jan 4, 2007
Hi, am new here and need a hand
i'm stuck on part of a program where i have a form which has two look-up lists (both looking up names of towns from the same table) and there is also a text box (called distance), which after choosing the town names from the look-up lists, should get the distance between the two towns from the array (in VBA).
thats where i have a problem, for now i have two towns (planning to have probably around 20 maybe more in the future), and something in the array isn't working
below is a link to a zip with that part of the array
i would be really grateful for any help, as i don't have a clue what went wrong
cheers
View 2 Replies
View Related
Jan 29, 2007
Hi, totally new on MS access. Is it possible to solve this following task in Access. (using it in Excel today)
Ex.
2d table?
Prod 1 Prod 2 Prod 3
Prod 1 0 10 20
Prod 2 10 0 30
Prod 3 20 30 0
Question 1: What did you just create?
Answer: Prod 1
Question 2: What will you create?
Answer: Prod 3
Then Access will show 30.
Probably explaning it bad but hope you understand and can answer me if this is possible to solve in Access....
The mission is to create a database with setup times between diffrent products, and make it possible to ask a question to get the answer.
/Martin
View 11 Replies
View Related
Feb 19, 2008
I have played around with the example from Adam, and wondered how one can make the data output avialable in a form.
I have tried the on click event on a label, but that doesn't work.
I would appreciate any pointers.
I used the 2003 version in this post :
http://www.access-programmers.co.uk/forums/showthread.php?t=142130
View 12 Replies
View Related
Oct 10, 2005
Hello,
I have a question:
1) Can I use the Array as the field name of Table? For example: Table - ABC, has 3 fields: Value(1), Value(2), Value(3).
2) If not, I define the field names as follows:
Table - ABC, has 3 fields: Value1, Value2, Value3.
Can I use the following states:
For i = 1 to 3
ABC![Value & i] = 100
Next i
Appreciate your help!
Thanks in advanced!
LiBin11011
View 2 Replies
View Related
Nov 23, 2007
Hi,
could anyone help me please, I want to populate a combobox on a form with the values retrieved froma dynamic array.
Thanks in advance.
View 1 Replies
View Related
Apr 3, 2008
Hi all
I'm using an array with the Instr function. The array (or the Instr function) doesn't seem to be case sensitive... which is a pain because its picking words within words where the case doesn't match!! I'd like the vales in the array to match exactly when using the Instr function
Any idea how I can get around this??
Thanks
Damon
View 3 Replies
View Related
Oct 13, 2006
Hello Everyone;
Here's my goal: 1) to set up a 7 column table containing a list of 147 numbers from a recordset (StNumberRS) and 2) to compare those numbers to a separate recordset containing a partial list of those numbers (completedStores). If one of the numbers in the completedStores list matches a number on the StNumberRS list show the 'icon_yes.gif' image, otherwise show the 'icon_no.gif'.
I can loop through the StNumberRS and set up the 7 column table, but I'm stuck trying to loop through the completedStores list while inside the StNumberRS loop.
Could someone look through my code and see where I've gone wrong?
Thank you!
Code:<% Dim StoreRS, Query, AltRow, ProjectQuery, ProjectRS, CompletedStores_variable, strValue, CompletedStores_ArrayProjectQuery = "SELECT * FROM Projects WHERE Id =" & Request("Id")Set ProjectRS = Connect.Execute(ProjectQuery)CompletedStores_variable = ProjectRS("CompletedStores") CompletedStores_Array = Split(CompletedStores_variable,",")Dim StNumber_variable, StoreList_ArrayQuery = "SELECT StNumber FROM Stores ORDER BY StNumber"Set StoreRS = Connect.Execute(Query)StNumber_variable = StoreRS("StNumber") StoreList_Array = Split(StNumber_variable,",")%><table width="100%" border="0" cellspacing="0" cellpadding="10"> <tr> <td> <%If Not StoreRS.EOF Then%><!-- Display store list in a table, showing 7 stores in each row: --> <TABLE width="800" cellspacing="0" cellpadding="2"><tr><td colspan="14" align="center" bgcolor="#FFFF00" class="subtitle">PROJECT STATUS</td></tr><tr class="required"> <td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td></tr><tr bgcolor="#CCCCCC"><td colspan="14"><img src="../images/spacer.gif" width="1" height="1"></td></tr> <% Dim i, theCount, remainder theCount = 0i = 0Do While Not StoreRS.EOF' The StoreList_Array contains 147 numbersFor i = LBound(StoreList_Array) TO UBound(StoreList_Array)' The CompletedStores_Array can vary from zero to 147 elements. Each strValue is one element of the array.'For Each strValue In CompletedStores_Array' As long as theCount equals 7, create an empty row separaating the table rows...remainder = theCount Mod 7If remainder = 0 thenIf theCount <> 0 thenResponse.Write "<tr><td colspan='14'><img src='../images/spacer.gif' width='1' height='1'></td></tr>"Response.Write "<TR>"End IfEnd If'... and create a table row populated with 7 elements from the StoreRS("StNumber"). If StoreRS("StNumber") = CompletedStores_Array(i) Then Response.Write "<TD align='right' valign=top>" & StoreRS("StNumber") & "</td><TD align='center' valign=top><img src='../images/icon_yes.gif' width='13' height='13'></TD>"Else' Output value of current CompletedStores_Array element for debuggingResponse.Write "<TD align='right' valign=top>" & StoreRS("StNumber") & "</td><TD align='center' valign=top><img src='../images/icon_no.gif' width='13' height='13'>" & CompletedStores_Array(i) & "</TD>"End If If remainder = 6 thenResponse.Write "</TR>" & VbCrLfEnd If theCount = theCount + 1i = i + 1StoreRS.MoveNext'next next Loop ' close out the last table row if its not alreadyIf remainder = 0 thenResponse.Write "<TD colspan='12'> </TD></TR>" & VbCrLfEnd If If remainder = 1 thenResponse.Write "<TD> </TD></TR>" & VbCrLfEnd If%> </TABLE> <%Else Response.Write "<p>There currently is no data for this project.</p>"End If%> </td> </tr></table>
View 2 Replies
View Related
May 28, 2013
I've built a query that selects certain customers based on a purchase date. I now need to assign these customers to various sales reps and display the customer information to them in another form that they can alter information in.
I was going to put my sales reps id into an array and loop it through the query updating it. Let's say i have 7 sales reps working that day, I would then enter their id's into fields and have the array assign these numbers into the query Sales Rep Id field for each row.
View 6 Replies
View Related
Aug 12, 2015
I reviewed a few ways to rank based on calculations and use them, however what I'm looking to accomplish is subjective ranking whilst limited the ranks to the total number of records in the recordset.
e.g. you have 50 records. Manually moving Record # 30 up to #3 would bump the existing records #3 to #29 down one (to #'s 4 through 30) and records >30 would stay the same.
Would deploy this to the end users within a continuous form, each row having Up/Down arrows to move each record up/down 1 rank as well as provide a combo box so they may choose any value within the array they want to set it to...
View 14 Replies
View Related
May 5, 2007
Anyone know if Access 2007 has control arrays or some thing similar?
Thanks
View 7 Replies
View Related
Jan 9, 2006
I'm populating a combo box from a query. I'm running a DCount on 2 criteria. The user selects the criteria from 2 combo boxes. If the user types * into the combo box, I want to be able to loop through each combination from the combo box values. I need to get the list of values from the combo box and put it into a string array, however it throws a type mismatch if I did something like
BRANCHES(x)=CStr(cboBranches(x)) 'cboBranches(x)=cboBranches.Value(x)
So I tried to run the sql from vb but found out that I can't return a string value from that (vb sucks). So I'm back to trying to get the values from the combo box. Any help would be appreciated.
View 3 Replies
View Related
Aug 2, 2007
Hello,
I wanted to catch a little advice if I may be so bold. I have a HTML form with a series of checkbox values.
Code:<form method=post action=form-test.asp><input type=checkbox name=drinks value='cocal-cola'>cocal-cola<input type=checkbox name=drinks value='sprite'>sprite'<input type=checkbox name=drinks value='lucozade'>lucozade<input type=checkbox name=drinks value='oasis'>oasis<input type=submit value='Submit'></form>
Normally I would gather these up into an array using ASP and email the split up comma separated values out to the client via an email.
Code:drinks=Request("drinks")drinks_split=split(drinks,",")'email drinks to client
However, this time I need to store those values in an access database. I am assuming that the client may want to run queries on the different array values, for example, how may people chose coca cola and sprite.
Is it better to store the value separately - one Ms access field for coca cola, one for sprite (which is what I thought I would have to do to create queries on this) - or can they be stored under one field heading "drinks" and queries be performed on different values within that field?
Could someone explain a quick way of doing the latter i possible in MS access and the SQL for querying?
Cheers,
trufla
View 2 Replies
View Related
Apr 28, 2015
Is it possible to send several form's OpnArg as Array ? Now I'm using a long text string including || as divider so I can split them later into Array.
View 8 Replies
View Related
Nov 14, 2013
I am having a problem with parsing the array value to Forms!<arraryvaluehere>.dirty.
Please see the code below:
Public strListOfForms(0 To 2) As String
strListOfForms(0) = "frmForm1"
strListOfForms(1) = "frmForm2"
strListOfForms(2) = "frmForm3"
For i = 0 To 2
Debug.Print CurrentProject.AllForms(strListOfForms(i)).IsLoaded
Debug.Print Forms!strListOfForms(i).Dirty
Next i
Note: CurrentProject.AllForms(strListOfForms(i)).IsLoade d works and Forms!strListOfForms(i).Dirty does not.
Is this because it is taking "strListOfForms(i)" literally? Is there a way that I can get around this?
View 1 Replies
View Related
Apr 7, 2014
I have some code that successfully gathers some items and adds these items to a listbox, one item at a time using .AddItem.And it works ok.The problem is, when there are a bunch of items to add, everytime the .AddItem runs, the form redraws.there are a bunch and this leads to a lot of flicking while the form redraws however many times .AddItem adds an item.
I've had the idea of building an array and then assigning the array to the listbox.I can build the arrray no problem but i have not been able to assign the array to the listbox all at once.how to do is add items to the listbox from the array one specific item ListArray(i) at a time.
Obviously this would work, but gains me nothing in terms of the visual effect in the form becuase it is still adding to the listbox one item at a time.Is there some way to assign an array to a list box all at once, without doing it one item at a time?
View 3 Replies
View Related
Jul 1, 2013
How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]
Here is my code:
Set rst = CurrentDb.OpenRecordset( _
"Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input
While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr")
i = i + 1
rst.MoveNext
Wend 'end of while loop
x = Arr
View 9 Replies
View Related
Aug 16, 2013
I'm trying to store all the OrderNumber + Item combinations in 2 arrays and then because the OrderNumber column really contains 2 values I'm interested in, I split it up and store that column's values in 2 arrays. So in total, I have 3 arrays. An array for Item, an array for Order, and an array for RepId (which is the one that I split up from the OrderNumber column).
Anyways, when I print the RepId array with the ' MsgBox PostValCol1(x) ' It prints 4 values like it's supposed to. But when I tested it again by looping through the values and just doing a MsgBox, It goes for a long time and that's because it has a lot of empty values in that array. How to get rid of those empty values/not store them in the first place?
Code:
Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "') AND (ActionCode = 'I')")
While rop.EOF = False
ReDim Preserve ArrRepOrder(j)
ReDim Preserve ArrItem(j)
[Code] .....
View 3 Replies
View Related
