Find A Specific Record To Return A Value
Aug 15, 2007
Having 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
View Replies
ADVERTISEMENT
Feb 6, 2014
I have a database which has a main form and subform built in linked by parent/child customerid, what i would like to do is search all the subform records from the whole DB and return its parent record on the main form?
Can this be done? because if i use find it will only search the filtered form i have onload of the form?
My onload event is based on fosusername()
View 3 Replies
View Related
Oct 10, 2005
Hi,
I have a table with various customer items, a current price and the date the price changed.
EXAMPLE DATA
CustID/ItemCode/DateChanged/Value
AAA 21 01/01/2004 £3.00
AAA 21 01/06/2005 £5.00
AAA 21 01/03/2005 £4.00
AAA 22 01/01/2005 £6.00
AAA 22 01/06/2005 £7.00
BBB 25 01/01/2005 £8.00
BBB 25 03/07/2005 £9.00
BBB 26 01/06/2005 £1.00
BBB 26 01/09/2005 £2.00
Note: Dates are in "dd/mm/yyyy" format. Also, not all data is in order in the table. It’s not how I would have designed it but that’s what I need to work with!
I would like to run a query that groups by custID & itemcode and also the most recent date for any price change. I would then like the query to display the relating price for the record it pulls out. (EG for custID 'AAA' and item 21, it would display the price of £5 as that is the value for the most recent price as of 01/06/2005)
I then want to link the custID & itemcode to another table that has sales in, to check that we are using the most recent price.
Is there a way I can do this via a query or do I need to put some code together?
Cheers
Red [CODE]
View 2 Replies
View Related
Oct 10, 2013
Table Name: Admin
Field Name: userid, admin (Y/N), Password, ConPw, PasswordReset (Y/n), Createdby
Trying to run a vba to find and delete records that was "createdby" the current user. Enviorn("username")
View 2 Replies
View Related
Jul 8, 2015
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
View 3 Replies
View Related
Apr 25, 2005
I've been using the ctrl+F search in order to search my db for a string or phrase (from a form). Now, after I put it on the Intranet and accesss it through there, I use ctrl+F and if I search for something that is not there, it doesn't return a "did not find" box. It's like it just keeps searching.
Is using the find feature like this just bad form and I should write queries for users to search the db?
Comments/critiques/criticisms welcome. Thanks.
View 1 Replies
View Related
Aug 8, 2014
I'm currently busy with something for my thesis as a student and I need to use Access for this. I'm not too new at access, I know how to do the very basics, let's say on the level of [if field contains *"text*", return x].
However I am struggling right now on something that shouldn't be too hard... I could do it immediately in Excel if there werent millions of rows..I have 2 tables. Table 1 regards a list of patent publication numbers (eg. WO2012024604A3) and additional data (publication date, title, etc), only the publication number matters for me now.
Code:
Table 1
publication numberWO2011085209A2
WO2011100754A1
WO2011112983A3
EP2342192A4
EP2342192A2
EP2205725A2
EP2205725A4
WO2012006540A3
WO2010008486A3
WO2012083136A1
Table 2 contains another list of patents that might cite/refer to Table 1's patents and additional data such as publication date.
Code:
Table 2
Publication Number Citing PatentsPublication Date Cited Refs - Patent
AU2001287375B2 1998-12-01 US5178882A | US4225581A | WO1998001161A2
AU2001288365B2 1990-02-24 US5967154A | WO1996039117A2 | US3699979A | US3943949A | US3838702A
AU2001288437B2 1999-03-09 US6087157A
[code]....
Now what I'd like to do is to create a third table which has for each of [Table 1].[Publication number]:
Column 2) A count of how many times the [Table 1].[Publication number] is found in [Table 2].[Cited Refs - Patent] ...
Column 3) In case a patent is cited more than once, return the [table 2].[publication Number Citing Patents] value of the earliest citing patent (so with the lowest Publication Date value).
For Column 2 I had expected it to be an easy count(iif( [Table 2].[Cited Refs - Patent] = "*"&[Table 1].[publication number]&"*")) command but apparently it's harder than that..
View 2 Replies
View Related
Jul 6, 2015
I 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 Related
Feb 10, 2008
OK 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
View 1 Replies
View Related
Jul 26, 2013
what 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?
View 5 Replies
View Related
Dec 31, 2012
I have a Query of dates and I need to have those dates return onto a from horizontally?
View 1 Replies
View Related
Feb 25, 2014
I 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?
View 1 Replies
View Related
Nov 3, 2014
I 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"
View 10 Replies
View Related
Jul 23, 2013
I 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 Related
May 3, 2005
I am wondering if there is a quick way to find a database object (table, query, form, report) other than having to scroll through them in the database window.
E.g. I have many many queries and when I need to find one to make changes to it, it often takes me a while to find it when scrolling thru the queries in the database window.
Has anyone ever programmed anything whereby you can type in the object type and name and then it will be highlighted in the database window?
This would be a great time saver.
Thanks,
BJS
View 8 Replies
View Related
Aug 26, 2007
I have many queries for separate types of income that when criteria is entered on the switchboard, a report is created for either a specific month or range of dates. This works fine. Now I need to create one report that shows all 7 types of income in one spot, but once again based on a specific month or range of dates. I have a query that produces the entire income for all dates, but can't get it narrowed down to the specific criteria.
View 8 Replies
View Related
Mar 30, 2006
Hi well as the title says i have a database with a LOT of tables, and i need to find the tables that contain a certain heading, eg reference 6, is there an easy way of doing this?
View 1 Replies
View Related
Dec 14, 2006
I have a table with 5 fields:
Build ID Process Product Product_De Operation_
What I am trying to accomplish is as follows:
Each Product can be in the table mulitiple times depending on how many Build ID's it has. So if Product 123456 has Build ID's G004, E818, N005, F813, D024, C879 it will show up one time each for each Build ID. What I want to query is unique Products that are for Build ID's D024 & C879 only and not for G004, E818, N005, & F813.
How can I accomplish this with a query. I am sure I am making this harder than it is but I sure need help.
View 1 Replies
View Related
Nov 20, 2013
This query returns a running asset balance for 60 months. Each month is 1 column.
1) I need to create a query based on those results that can cycle through each column and determine if it is NEGATIVE. Then if it is negative, I need to perform some math on it to determine how to get it back up to being positive.
2) I need to loop through X columns based on the assets lead time. Each lead time varies and I have a master table that contains it. So if the assets lead time is 3 months, I need it to move over 3 columns and return that value. If it is 6 months then I need it to return the value in the 6th column.
View 9 Replies
View Related
May 5, 2013
I am working on a school project called employee skills. I have a table of skills, each has a check box, which if checked is true. I am trying to write a query that will find who has a specific skill.
View 6 Replies
View Related
Mar 18, 2015
I need to find whether a memo field contains a specific word. I know how to find whether it contains a specific string.
Let's say I am looking for the word "run." I would not want a positive result when searching "I don't like gerunds," but I WOULD want to find the records with "I know how to 'run' a search."
So I am looking for WORDS, not matching strings.
I am perfectly willing to use a user-defined function, to put involved processing into the query, such as
Code:
...
WHERE FieldHasWord("run", memoField) = True
AND ...
View 3 Replies
View Related
Oct 3, 2005
Hello, I have just spent ages doing searches and reading everything I can on locking. But, I have yet to find an answer as to how I can lock a specific field in a specific record.
e.g. Staff enter customer details, then at the end of the day the admin (me) checks it over and presses a big old button that stops them from locking certain fields in the current record only - they must still have access to the unlocked fields of the current record, and it must not lock any other records.
I'm guessing there's some VB code in the form of fieldname.lock = true, but then it locks the field throughout the whole table!
Can anyone tell me how to do this please?
View 11 Replies
View Related
Apr 17, 2013
I have a custom form setup for 1up 1.5inch labels on the print server. We are printing these on Okidata Printers using an Epson driver.
I want to know how you can find the specific printersize number value for a form called "1_5upLabels" which is a custom form.
I am not looking to do this manually in the page setup for the report but to programmatically so that it's dynamic.
Is there a way I can loop through the forms for this printer and check for the form name?
View 1 Replies
View Related
Sep 1, 2014
I'm using a function to find all files/folders in a specific directory, then copy each one to a specific destination folder. I'm going to use this on a weekly basis to backup files on a computer. I would like to use the vbarchive in GetAttr/SetAttr so I only need to copy these if they have changed since last backup.
Something like:
If GetAttr(strFile) And vbArchive = 32 then
filecopy xxxx, xxxx
else
rs.movenext
As I understand it, a value of 32 indicates that the file has been modified since it was last backuped up (i.e. since the file attribute was "reset"). How do I "reset" the file attributes to clear the vbArchive?? I've read some google searches and the only thing I could find was the files were set to vbnormal however I'm concerned that this will erase program files that are vbReadOnly or VbHidden.
View 5 Replies
View Related
Feb 16, 2014
I'm new to Access but comfortable with Excel and VBA.I'm trying to transform a VBA-heavy form from Excel into an Access database, as multiple accessing is required, as well as data analysis. The situation is I have a form where users enter data (name, account number, date, etc). The account number is unique, and I have a table of approximately 14000 account numbers which contains the company name, address, and other details for each account number. I've set the account number as the primary ID on the accounts table.
What I would dearly love to be able to do is: when the account number is entered on the form, it is searched for (e.g. by the user clicking a button) from the accounts table. If it is located, the 7 fields in the relevant row are displayed in 7 textboxes on the form. I do not want to record the address, just display it on the form so the user can choose whether or not to use it.
So far, I have found GetRows, which seems to be able to convert the fields into a 2D array... this could be usable. But how can I do the first bit - find the correct row in the accounts table based on the account number entered into the textbox on the form? Is it possible to do this behind the scenes? In other words, Access finds the right row and displays each field in its textbox on the form, without producing a report form first.
View 5 Replies
View Related
Sep 17, 2014
I am relatively new to Access and i am wondering why i cannot find a specific field from a table on the control source when i am and attached it to a check box.
I have created the field on the table, and now i want to attached it to the check box on a form however when i go down the list of control sources i cannot see it.
View 3 Replies
View Related
