How To Return Specific Rows In A Query To A Form
Dec 31, 2012I have a Query of dates and I need to have those dates return onto a from horizontally?
View Replies
ADVERTISEMENT
Modules & VBA :: Return Rows In Query From Variant Array Return From UDF
Sep 16, 2014I 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.
Locking Specific Rows Within A Query
Oct 12, 2007Hey guys,
I have a query on a form in which I want to lock the first two rows to prevent the user from manipulating the data. The user will input information in the third column.
How would I go about this
Thanks
Set Colors On Specific Rows In Continuous Form
Sep 5, 2006Can we set different colors on specific rows in a continuous form?
Suppose the following data entry form (continuous) is to fill in spouse name, while name and marital status is already prepared in a different table. If the marital status is Married, then the spouse name textbox's background color will be Yellow. The standard background color is white.
Name Marital Status Spouse Name
-------------------------------------
xxxx Married (Yellow)
xxxx Unknown (White)
xxxx Not married (White)
xxxx Married (Yellow)
Return All Rows
Aug 16, 2006I hope this is a simple query becuase my brain has drawn a blank on it.
I have a query with a parameter which a form textbox. If the text box has X value I want to use one list of parameters and if has Y I want to list all rows in the query.
Any help appreciated!
Dave
Forms :: How To Hyperlink From Query To Specific Record In A Specific Form
Jul 23, 2013I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
View 3 Replies View RelatedQueries :: Return Entire Rows With Min Date Only
Apr 15, 2015I am looking to return one row from groups of the same EpisodeID whereby the row with the minimum date is selected each time. This includes returning all other fields in the row such as EventID below and ideally others as well if that will be possible.
To illustrate I include the following. What Access 2003 query would I need to return all the rows with the earliest dates? EventID will be unique in the intial table.
Code:
EventsTable
```````````
EpisodeID | EventID | EventDate
-------------+---------------+-------------
1 | 001 | 01/02/2010
1 | 023 | 05/10/2009
1 | 103 | 12/02/2010
2 | 004 | 02/03/2013
2 | 102 | 12/10/2014
3 | 546 | 04/05/2012
3 | 100 | 08/08/2013
3 | 034 | 10/10/2012
3 | 066 | 02/03/2013
4 | 777 | 05/07/2014
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011
Code:
ExpectedOutput
``````````````
EpisodeID | EventID | EventDate
-------------+-------------------+---------------
1 | 023 | 05/10/2009
2 | 004 | 02/03/2013
3 | 546 | 04/05/2012
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011
Queries :: Return Multiple Rows From A Single Record In Database
Jul 16, 2014For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows
1st Obs Start
1st Obs End
2nd Obs Start
2nd Obs End
3rd Obs Start
3rd Obs End.
I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:
ID......Obs Start.............Obs End........
1....[1st Obs Start].....[1st Obs End]....
1....[2nd Obs Start]....[2nd Obs End]....
2....[1st Obs Start].....[1st Obs End]....
2....[2nd Obs Start]....[2nd Obs End]....
2....[3rd Obs Start].....[3rd Obs End]....
3....[1st Obs Start].....[1st Obs End]....
4....[1st Obs Start].....[1st Obs End]....
etc.
I could then quickly count how many obs periods started within the desired month.
Queries :: Return A List Of Rows Sorted By Service Type Ascending
May 20, 2014In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL:
SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes
ORDER BY 1 DESC;
Find A Specific Record To Return A Value
Aug 15, 2007Having problems with the following function to goto a specific record and return a value from a field in that record.
Code:'Public Function intFieldZConv(strICAO As String) As IntegerPrivate Function intFieldZConv() As Integer 'TEST PURPOSE ONLYDim strICAO As String 'TEST PURPOSE ONLYstrICAO = "KTCM" 'TEST PURPOSE ONLYDim cnCurrent As ADODB.ConnectionDim rsFieldInfo As ADODB.RecordsetSet cnCurrent = CurrentProject.ConnectionSet rsFieldInfo = New ADODB.RecordsetrsFieldInfo.Open "tblFieldInfo", cnCurrent, , , adCmdTable'rsFieldInfo.Index = "FieldICAO"'rsFieldInfo.Seek "=", strICAOintFieldZConv = rsFieldInfo!FieldZConvSTMsgBox intFieldZConv 'TEST PURPOSE ONLYrsFieldInfo.ClosecnCurrent.CloseSet rsFieldInfo = NothingSet cnCurrent = NothingEnd Function
strICAO is received from another function which is to be used to determine the specific record in the table "tblFieldInfo". Once that record is referenced, the value from the field "FieldZConVST" is returned.
My problem area is highlighed (index and seek) which I receive an error stating "method or data member not found". Any help would be appreciated on how to finding a specific record.
Thanks
Jeff
Queries :: Return All Entries For Specific Day
Jul 6, 2015I have a large table with millions of entries. I want to run a query to return all entries that are on a Saturday. The date stored in the table though is just typical date format eg 15/11/2015.
View 1 Replies View RelatedReports :: How To Get Sum Of Values For Specific Rows
Nov 5, 2013I have a report being generated that pulls in a bunch of data. The processed column is a boolean value.
I want to be able to set the text of a label to be equal to the "material cost" for all the rows where is processed is set to false.
Picture of the report: [URL] ....
Trying To Return Specific Fields From Multiple Tables
Feb 10, 2008OK so here is a working query:
SELECT Assets.*
FROM Assets
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));
It works great returns the correct results. But I don't need everything out of Assets. I just need a few things from there and a few things from 2 other tables.
I tried this but it now gives back over 220 repeating results.
SELECT Assets.BarcodeNumber ,
Employees.UserID ,
Building_names.BuildingName,
Assets.Floor ,
Assets.BuildingLocation ,
Assets.DeskLocation ,
Employees.FirstName ,
Employees.LastName ,
Employees.SSO
FROM Assets ,
Employees,
Building_Names
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));
I am sure it something simple but I am a novice at this so please help me. :D
Modules & VBA :: Return Next Primary Key Value Which Is In Specific Format?
Jul 26, 2013what is the best/cleanest way to return the next primary key value which is in the format yyyy-###.These are case files for the current year, ### being 3 digit number incremented by one for each new case.
I could use a function that finds the last one, checks the date, increments by one and returns it, but maybe I could use a public property or a class? I'm keen to use classes more for code maintenance.With a class then: would i create a PrimaryKey variable of the class type/object, so when I need a new key I would just type PrimaryKey.NewKey, for example?
Modules & VBA :: DLookup To Return A Value Starting With Specific Letter
Feb 25, 2014I am trying to use a DLookup to search to find what block goes into the production of an item.
In a table called "dbo_vw_MCE_job_with_materials", each item contains a list of the parts which go into its production in a column titled "Expr1". However there are always more than one parts to this, with varying names.
What I am trying to do is to use a Dlookup to only return the first value which starts with B (as the block number is always the only part on the list which begins with B).
At the moment my DLookup would look ike this:
Code:
MixTypeTxt = DLookup("Expr1", "dbo_vw_MCE_job_with_materials", "[item] ='" & Forms![Theta Input]![ItemNumbertxt] & "'")
However I think as the item column contains the same item number in 8 columns, with different values in the Expr1 column, Dlookup only returns the top value "A-CF0057" (which is irrelevant as far as I am concered) - whereas I am hoping it will be able to pull the "B1499" value from the column as it is the only one starting with a B
Is there anyway to specify which value the dlookup function would return, or is there any way to apply some sort of permanent filter on my table?
Forms :: Count Only Rows In Specific Field With Date
Dec 12, 2014I have an access forum that I need to have a textbox count how many rows have dates in my "Ship_To_Date_HP_" field
View 6 Replies View RelatedQueries :: Select Statement To Return Specific Data From Another Column?
Nov 3, 2014I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
Reports :: Conditional Formatting - Rows To Be Of Specific Color Based On Value In 1 Field
Jul 24, 2014I have a report that I would like to have the rows be a specific color based on the value in 1 field on the row.
I have attempted to use conditional formatting but it will not work (IE, I can get font color to change, but not field boxes to have color).
The field is labeled [text144] based on the value in the field (1,2, or 3) I would like the field to be a different color.
i.e.
3 = green
2 = yellow
1 = red
Return The Query Results On The Form
Sep 13, 2005Hi,
I am not much familiar with VB Coding.Can any body please help me with this Please:When i click on submit button,it has to show the query results on the Form.Please find the below code:
Private Sub Command_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Set db = CurrentDb
strSql = "SELECT Interface.[Interface ID], Interface.[Interface Name] from [Interface]"
Set rs = db.OpenRecordset(strSql)
Do While Not rs.EOF
Debug.Print ("Interface ID: " & rs![Interface ID] & "Interface Name: " & rs![Interface Name])
rs.MoveNext
Loop
rs.Close
db.Close
End Sub
#######################
It is not retrieving any thing.IF my code is wrong,Please advice me with the necessary steps to view the results on the form,when we clicl on the submit button.
Thanks,
Kanuri
Form And Query Return Different Recordsets
Dec 11, 2012Is there a common answer or design mistake that would cause a form to return a different (much higher) record set than that if the query is ran by itself. The query is the control source for the form.
View 3 Replies View RelatedForms :: Query From Form To Return All Results
Jan 6, 2014I have a form that displays multiple records in datasheet view. I want to query off of that form and return all results for all records being displayed. However, I'm not sure how to tell Access to do this. Access instead wants to return records for only the first displayed record in the form or the record that is highlighted by the cursor.
In the attached image you can see a list of AccountingUnits (AU) going down. My query using AU from the form as criteria will only return records for AU 114510 since it is the first record, but I would like to see records for all AU's being displayed by the form. How can I do this?
Trying To Use A Form To Return Multiple Fields From A Query
Nov 3, 2014I have already made a query with all the information needed. What I am trying to do is create a form where a user can open the database and enter a unique number and have information from 10 fields associated with that number show.
View 5 Replies View RelatedForm And Query To Return Records Between Certain Date Range
Oct 15, 2013First, I am trying to get a query to return records between a certain date range. In the form I have DateFrom and DateTo unbound text boxes from which faculty select the date range. I know I have done this before, but I cant figure out how to create a field name and write the criteria for the source query in design mode!
I tried [Form]![FormName]![DateFrom]<[Form]![FormName]![DateTo] in the criteria but I can't seem to write a valid field name that doesn't alter the criteria in some way...
Second, in the same form, faculty enter the StudentID for the student they wish to get records for. How do I write the code to show a msgbox when there is no such ID in the event they enter an StudentID incorrectly.
Query To Return Default Set Of Values In A Subform When Main Form Opens
Apr 23, 2007Here is my sample DBA
I'd like to solve this issue
when I open my Quotations form I'd like a set of services to be selected by default, ex: Brush and Pen. So the user only has to input the quote amount
is there anyway to have the form open with a script like:
"on open (or on load). in subform SELECT refproduct where product from tblproducts = 1 and also SELECT refproduct where product from tblproducts = 4"
Therefore when my main form opens, in the subform I have already Brush on the 1st line selected and pen on the 2nd. My user only has to type the price.
Thanks for your much appreciated help
Modules & VBA :: Return A Value From A Separate Query / Have To Show Up In A Field On Main Form
Jun 28, 2013I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.This is the code I placed in the Control Source property for the field I want to be displayed on the main form:
=DLookUp("[Six Month Date]","DT6Monthqry","CustomerID=" & [CustomerID])
The result of this displays a flashing "Error" in the field, even when I go to a Customer that I know has a result in the "DT6Monthqry".
Reports :: Send Email Based On Query From A Specific Form
Jun 27, 2013I'm trying to send at report based on a Query from a specific form.
Everything is (almost) working fine, except the fact that I can't get a criteria into DoCmd.SendObject method.
My code is:
Private Sub Kommandoknap212_Click()
On Error GoTo MailTilGodkendelse_Err
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Dim MailAtt As String
Dim intSearch As String
[Code] ....