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 RepliesI 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.
I need to give the user the ability to select either all of a table say
[tblStores].StoreNo
or only those stores where [tblStores].StoreNo Is Null
I cant for the life of me get my query to accept Null or Is Not Null in the parameter box.
What do I need to do.The end game is to produce a subform to allow users to maintain tblStores
eg
tblStore
StoreNo | StoreNm
000001 | StoreA
<Empty>| StoreB
000003 | StoreC
Null returns
Store B
Is Not Null returns
StoreA
StoreB
StoreC
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
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,
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
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.
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.
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!
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
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....
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!
undefinedHi, apologies if this has been answered before but can't find anything relevant!
Trying to match one field to the first 5 digits in another i.e.
Field 1 = 55667
Field 2 = 5566785431 so would want to match the 1st five digits in field 2...
Any ideas?
Thanks
Hello all!
I have a customer database and I basically want to find out their geographical distribution. To do this I need to run a query that gives me the sum of customers for each postcode, but this is dependent on only the first few characters of the postcodes (or prefix), eg BH3. The length of the prefix varies between 2 characters and 4 characters with one or two characters followed by one or two numbers.
What I don't want to happen for example is to have postcodes counted as BH1 when in fact they are BH13 or to have postcodes coutned as BH13 when they are actually BH1 3LV.
Does anyone have any suggestions of how I can do this? :confused:
Any help most gratefully received!
:)
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
I have a table that I need to identify the records in that have specific text in one of the fields, the field also contains other data. i.e. the field (accessdescription) can contain any combination of the following text (Bridge, Report, Email). and I want to list only the records that have email in this field, noting that the field usually contains at least two of the possible entries.
Any pointers in the right direction would be greatly appreciated.
Thanks
Jubb
Im using the UPDATE Query in Access
UPDATE MAT_Clean SET FCODE = 'BC', NOTES = 'HC RAMP'
WHERE FCODE='BC HC RAMP';
Instead of typing what needs to go into NOTES everytime, how can I specify whatever comes after 'BC' to go into NOTES? Use 'BC *'?
Thanks!
I have a table - RDC/NDC - that holds details about various warehouses, including a field - [Short_Code] (TEXT) - to hold the warehouse short code or ID.
I have an import table - PickDataImport - that contains details of goods picked, including a field - ToAssignRef (TEXT) - that is made up of 3 elements; Type-Short_Code-Date (EG GREEN-MAN-210215).
I am trying to create a query that will return the warehouse name from RDC/NDC by looking up the depot short code in the ToAssignRef.
The Type element of ToAssignRef can be any length of text.
I am thinking i need to use HAVING or IN (or a combination) but everything i try fails - either blank or errors.
Hi,
I hope you'll be able to help since I think it is going 2 B a tough one.
I'm looking for a code that will do the following:
I have a table (lets say customers) with a lot of names and instead of typing again and again the same name I would like to have a kind of an auto complete mechanism which will find the name from the table and use it to input it back to the table itself and if it isn't there then it will use the new name as an input, exp: in table customers I have the following names:
ACC, ABC, ABD, EFG .....
lets say I'm typing A then the ComboBox will show all the names that starts with A (e.g. ABC, ABD & ACC), if I will continue typing A=>B then only the names ABC & ABD will be shown then I can choose one of them or continue typing ABD which will cause only the name ABD to show up then I could use this as a name to input to the table or if I create a new name like ABDG then this one will be used.
I hope I didn't mess things up and explained myself correctly.
Thanks in advance
Sean
Hey all
I have a table that contains a list of news items. For each item there is a link to an appropriate image and a load of text.
I display a list menu of news items showing the picture and the first few lines of text. At the moment for the first few lines of text I have a separate field and just copy and paste the first few lines of the main article into it.
My question is is there a way where instead of having to have a separate field with just the first few lines in I can somehow just retrieve the first few lines of data from the full article field when displaying the menu listing?
Hope this makes sense, and any ideas greatly appreciated.
Many thanks
I am using a combobox to lookup contact names.
The rowsource is taken from a field in a contact table that contains the contact's First name and then Last name.
You could use the dropdown to pick the name or type the First name into the combobox.
The problem is that the combobox matches only the beginning part of the field as you type. So if you start typing the person's last name, the combobox would not display anything.
Is there an easy way to allow the combobox to lookup any part of the field that matches the text being typed?
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".
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.
I am trying to group records in a query and count them. I have records containing ABC12345
ABC67890
ABE12345
ABE67890
Basically the third letter is what I need to group on so that I can count the number of records with ABC, ABE and any other variant of the third letter but with the numbers all varying all over the place.In the example above I want to find ABC 2 and ABE 2.
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 RelatedI 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?