General :: Search A Table Using Part Of A Field
Jul 24, 2014
We have a form with a field Drawing No from the Table Job Register
I want to be able to take the first part of the drawing number and display all matching items from the quote details table.
Aesthetics I can sort later.
I've created a query Item History.
My intention is to have a command button on the form Job Register that runs the query qryQuoteHistory displaying matching results.
My issues are as follows:
drawing numbers are sporadic in length and information ie:
kk80
a2-19520
a2-19520 rev a
e8
1128215 issue d
Retrospectively I would have had issue or revision number in a separate field, or better yet have a products table and link the info there. Unfortunately this isn't the case.
View Replies
ADVERTISEMENT
May 13, 2015
I want to take the first 15 characters from a field in my table and place those 15 characters into a new field in the same table , assuming i can do this with a query.
View 3 Replies
View Related
Jul 23, 2005
Hi Guys!
I'm working on this database in which I have 2 forms:
one form has personal information (PersonalInfo)
and the other one is a search form (Search)
In the search form I have a field in which I want to type any part of the Name field in PersonalInfo form and I want to retrieve all the records that have this part of the name.
Here is what I tried:
In the PersonalInfo form, I want to the criteria of the NameField and typed the following:
Like %[Forms]![Search]![SearchField]%
but it didn't work.
Any help will be very much appreciated.
Regards,
CS.
View 4 Replies
View Related
Jul 28, 2005
I want to ba able to search for only part of the field in a query. I want to run a reort from a query where the user only needs to enter part of the information from a product list ie the items listed may be as lisetd as make model in the same field. the user won't be able to get to the query to alter the criteria. i have tried:
Like "*" & [Enter Product Name] & Like "*" Which I found on a previous post but this returns a syntax error, and highlights the second Like
View 3 Replies
View Related
Feb 12, 2015
I have a table with about 29,000 postcode values
I want to extraxct the first part of the field (usually 3 or 4 characters)
How can I automate this?
View 2 Replies
View Related
Jun 13, 2013
I'm trying to split up the filename into four separate fields. Here's an example of the filename:
123112 427900 55261 1156833.jpg
A 12312
B 427900
C 55261
D 1156833
The values A, B and C are always the same number of characters. The last value, D, changes in size. It can be anywhere from four characters to twenty. But it always starts at position "20" as with "1" in the case of the 1156833. The extension, jpg, is of no value.I might also add my programming ability is close to nonexistent.
View 2 Replies
View Related
Apr 6, 2015
I have two tables (A) and (B).
Table (A) contains key-field name "position1".
table (B) contains key-filed name "position2".
How to make a link between key-field "position1" and part of key-field "position2".
View 5 Replies
View Related
Jan 16, 2006
I have a data base with the field named "PartNumber" this is a 13 diget number. How can I search on the last 7 digets only
View 2 Replies
View Related
Jul 23, 2013
I have a table called BID with the following fields
bidder, seller, iid, starttime, bidtime, and bidprice
bidder is an id number that is reference to uid of table USER
the USER table have the following fields,
uid, uname, city, and state
I created a BID form for the user to enter data into the BID table. What I am trying to do but is having trouble with is have the user search for their name that may already be store in the uname field in the user table, and what ever they choose base on their search, the uid of this uname gets store into the bidder field of the bid table.
I still have a second step where the user in the bid form can search the iid but shows the itemname from the item table, and the value of seller, iid, and starttime from the auction table gets populated into the bid table.
iid from the auction table is reference to the itemtype and the following fields exist in the itemtype table.
iid, itemname, description
View 14 Replies
View Related
Feb 7, 2013
I'm developing a program to search several fields. One of which is a memo field with large characters. Using wildcard in the search form I made, it only returns the record if the first word of the field is typed in the search box. I want to type any word in any part of the field to return the record.
This is my code
' Check for LIKE Subject
If Me.txtsubject > "" Then
varWhere = varWhere & "[subject] LIKE """ & Me.txtsubject & "*"" AND "
End If
subject is a memo field.
View 5 Replies
View Related
Jan 14, 2013
Using an attachment field - can we specify the location to search?
We have an Access 2007 DB and need to attach files from only 1 location - we hope to attach a copy of these files into the database efficiently.
I have a few sample DB's that use VB but I am hoping there is a quick and easy solution for this.
View 2 Replies
View Related
Apr 7, 2014
I have a master data table containing 4 columns and ~ 500K rows. I have a list of keyterms from column1, column2 and column3. How do I use Access to search the masta data table and return the corresponding column4 value for each row on the list.
View 6 Replies
View Related
Jun 11, 2013
Access 2007. I have the start of a family history database containing two tables with very similar data.
First table:
FILTERED Births 1837-2005
IDGiven_Name Location County Town Year
1 Eliza Ann England Yorkshire Leeds 1837
2 Elizabeth England Norfolk Aylsham 1837
3 Isabella England Lancashire Preston 1837
and
Second Table:
FILTERED Deaths 1837-2006
ID SURNAME GIVENAMES LOCATION COUNTY TOWN YEAR
1 FRANKLAND Alice England Lancashire Clitheroe 1846-1855
2 FRANKLAND Ann England Lancashire Clitheroe 1837
3 FRANKLAND Barbara England Yorkshire Whitby 1837
My problem; from the Given_Name in first table I want to be able to search for the same name in the second table probably using the Town field to narrow the search down. As an example;
FILTERED Births 1837-2005
ID Given_Name Location County Town Year
16 Birtwistle England Lancashire Haslingden 1838
FILTERED Deaths 1837-2006
ID SURNAME GIVEN NAMES LOCATION COUNTY TOWN YEAR
18 FRANKLAND Birtwistle England Lancashire Haslingden 1838
This show that Birtwistle died as a child, but it is also an easy match to spot due to the unusual given name.
View 2 Replies
View Related
Jan 25, 2007
Hi everybody,
How I can set a query to search in all table fields. In my table I have around 48 fields and I want to search for particular value from combo box if this value exists in any of those fields.
View 2 Replies
View Related
Dec 16, 2004
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
View 1 Replies
View Related
Jan 2, 2015
I'm using a very simple unbound textbox on a very simple form, with the following Expression to find me data in a query and sum the last 30 days, based on a combo-box on my form...
Only thing is, it's showing me only the data from the last 30 days...regardless of the value in the combo.
=DSum("Credit","qryIncVsExp","TransDate>=#" & Date()-30 & "#" And "AccountID_FK="""&[cboaccount].[column](1)&"")
I'm sure it's something to do with the number of "'s I have, but I'm probably more than likely ever so wrong.
View 1 Replies
View Related
Nov 3, 2006
Hi everyone,
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
Many thanks,
View 6 Replies
View Related
Sep 21, 2012
I'm having Table with some universities name and i want web link address for all universities. Take university from table1 in column1 and search on google page and return first link of the search page and save into column2...
View 1 Replies
View Related
Jul 23, 2005
On a report I have a field that has a value that can be anywhere from 15 to 25 char. Can I set up the report field to only display the 1st 10 char. ?
jon
View 9 Replies
View Related
Apr 7, 2008
Hi,
Apologies, I have no idea if this should go in reports, queries, macros or modules and VBA as I'm a bit stuck but hopefully it's fairly simple.
I've been teaching myself access and it's been going well. What I have now been asked to do is produce a report that generates the shift patterns for everyone in the office as an HTML document. Now, the data is all exported from another program and I've had no problem getting the data into access easily. The problem I have is the format some of the data is in.
The major one that I need to solve is showing what time people are meant to take their lunch break each day. The field for break is filled in in the following format.
07/04/2008 12:45:00
Now, I have the date from elsewhere so I really don't want the date to show up so I need something that removes the date from this field. Is this going to be easy to do? (Ideally I'd like it so that the above example actually just returned 12:45 but if it has the 00 on the end that would not be the end of the world)
Apologies if this is in the wrong part of your forum.
View 3 Replies
View Related
Jul 27, 2005
I found a thread from last year that is close to solving this question but not 100% so I'll post this new thread.
I have a field called ITEM_NUM in a database with the following structure:
XX-YYYYYYYYY
XX-YYYYY
XX-YYYYYYYYYYYY
I need to extract the Y portion of the data and a Query format would be the best.
FYI, the X portion consists of 2 Letters/numbers then the Hyphen and the Y portion is variable in length.
thanks for your help!
View 1 Replies
View Related
Sep 6, 2005
I have joined 3 tables using the query design and I have 3 different fileds. There are fields that have information that I don't need. e.g. I don't want the first 10 characters of field 1. How do I do this in query design (even in SQL view). Or do I need to do this in the table itself before joining the table. Hope my question makes sense. Thanks for any input.
View 1 Replies
View Related
May 5, 2006
I have a table with names in a field called [Name] in the format Mr John Smith
I need to append the names to another table but to 3 separate fields for title, initial, surname
I know the format for example Left([Name],1) to take only the first letter, but how do I tell it to take from the left up to the first space for the title, how to take between the first and second spaces for the first name etc?
Any help gratefully received
View 1 Replies
View Related
Jun 12, 2007
Hi all,
I am trying to update the value of a field from say, "Word" to "Test120+". I am using the SQL code as below....
UPDATE tblTable SET tblTable.Field = "Test120+"
WHERE (((tblTable .Field)="120") AND ((tblCurrent.Field02)="TT"));
But the end results are that the value has been changed to just Test120 !! The plus (+) sign has been excluded. Is there anyway I can force the plus sign to be used in this update query or has Access some sort of bug because the plus sign is used as an arithmetic character that prevents it from being used as a value in a query ?
Any workarounds or advice greatly appreciated.
Thanks in advance,
Mitch....
View 4 Replies
View Related
Dec 29, 2005
Hello,
I need to be able to copy the last part of text from 1 field to another.
Speciffically,
yadda yadda (ABC)
to become:
+ field1 + field 2 +
| yadda yadda | (ABC) |
(ABC) is not always of length 3, it could be (XY), (T) or more.
can someone help me with a method to strip this into another field?
thanks!
View 14 Replies
View Related
Dec 13, 2007
I have a table with 84000 records in I need to fined all the records that have the same First two starting characters and the same last two characters but are different between
the first two and last two characters.
Ie. 30123456757 302356757 301234567
I just want the ones that have different number in between . I have tries ever Select statement I can think of and still coming up with every thing but what I need.
View 7 Replies
View Related