Limit Query By Part Of A Field

Sep 1, 2005

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

View Replies

Query On Part Of A Postcode Field

Jul 7, 2005

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!

:)

View 8 Replies View Related

Queries :: Group By Part Of Field In Query

Jun 26, 2015

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.

View 3 Replies View Related

Update Query To Change Display Value Part Of Hyperlink Field

Nov 11, 2007

Given a table field that is a hyperlink type.
I need an Update Query to set all records of that table so that the Displayed Value part of the hyperlink field (not the Address part) is set to a particular value.
Any ideas how?
Thanks.

View 1 Replies View Related

Query Criteria If On Form Field Limit, Otherwise Do Nothing?

Apr 26, 2006

I tried to word the title as accurately as possible.
What I'm trying to build is a Form where they can select data in different fields to limit (filter) the list. In my query criteria I want to read the form, if there's a value use THAT in the criteria (for that field) otherwise ignore it.

I've tried isnulls etc. If I put a value (like City) it properly returns all the records with that city, however if I put nothing the "ISNULL" returns a value that the criteria doesn't match i.e. I get nothing.

There must be a way to tell a query to NOT use any criteria if an object (form!field) is blank...?

View 2 Replies View Related

Queries :: Limit Query Results To Numeric Value Of Text Field?

Aug 18, 2014

I have a table that has several fields including CallID (autonumber) and SKU (text)

SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...

Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:

Code:
SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU
FROM Calls
GROUP BY Calls.SKU
HAVING ((Not (Calls.SKU) Is Null))
ORDER BY Count(Calls.CallID) DESC;

The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.

Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.

View 7 Replies View Related

Tricky Question - Form Part Data Entry, Part Not

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

Limit A Field To User Defied Values Depending On The Input Of Another Field.

Nov 23, 2005

Hey all,

I have two fields 1 & 2

field 1 is a simple combo list of user defined values ie A, B, C or D

Field 2 relates to a attribute of the data in field 1 and is not always the same for A, B, C and D. i.e

A could have a,b,c,d or e
B could have c,e,f,g or h
C could have a,g,h,i or j
D could have v,w,x,y or z

I would field 2 to have a combo box which only displays a,b,c,d, or e when A is chosen in field 1; c,e,f,g or h when B is chosen; etc

How do I do this?

Cheers all,

Matt :confused:

View 2 Replies View Related

Forms :: Limit Form Field Selection Depending On Another Field

Feb 5, 2014

I am creating a training database and first I have tables in relationship to the courses:

tbl_Courses
Course ID (Primary Key)
Course Title
Objectives

Instructors (lookup field allowing multiple instructors to be selected, meaning they are skilled to teach the course)

tbl_Instructors
Instructor ID (Primary Key
Last Name
First Name
Full Name (Caluclated to put Last Name, First Name)

[code]....

What I am trying to do on a form to create a new event is once the user selects the Course from the Course ID combo box, then I need the Instructor combo box to only display the instructors who are skilled to teach the course which are selected in the tbl_Courses.

I can get all Instructor ID's, but not the names and the class that has multiple instructors show on one line.Should I have not put the Instructors field in the tbl_Courses? Do I need another table for instructor skills or something?

View 1 Replies View Related

Date Problem - Part Constant - Part Now()

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

General :: How To Limit The Options Of A Field Depending On Another Field

Nov 26, 2012

In one table I want to limit the options of one field depending on what is chosen in another field.

Field Type has two options: Receivable and Payable.

I want field Sybtype with this limits:

If Receivable is chosen in Type field you will only have CreditCard & Lockbox available in the Subtype field. If Payable is chosen you will only have Cheque & Transfer to choose.
Shall I have two separate tables? or only one table with Type and SubType as below? but then how can I make the above work?

Type SubType
Receivable CreditCard
Receivable Lockbox
Payable Cheque
Payable Transfer

View 2 Replies View Related

Displaying Only Part Of A Field

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

Filtering Out Part Of A Field

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

Search Part Of Name Field

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

Extracting Part Of A Field

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

How To Select Part Of A Field

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

Appending Part Of A Field

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

Updating A Field With The + As Part Of The Value

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

Copy Part Of 1 Field To Another

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

Select On Part Of Field

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

Criteria Matches 1st Part Of Another Field

Jun 22, 2005

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

View 3 Replies View Related

Use A Criteria To Search For Part Of A Field

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

Copy Part Of A Record As Value In Another Field

Dec 14, 2006

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!

View 5 Replies View Related

Queries :: Lookup Using Part Of A Field

Feb 28, 2015

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.

View 10 Replies View Related

Can One Field In A Table Be Used To Limit The Result Of Another Field In The Same Rec

May 25, 2007

Hi,

I have a fairly simple database that looks like the attached. It is the beginnings of a school database. Unfortunately, the MS template doesn't quite do it.

We have around 700 pupils in 5 year groups. Each year's intake is assigned the year of that intake (e.g. 2002, 2003 etc).

The youngest pupils are in Year 7 whilst the oldest are in year 11.

Each Class is assigned based on the intake year but given the name of the schooling year - (e.g. year 7, year 8 etc). And there are 6 classes per year - hence names 7.1, 7.2, 7.3 etc.

My "Students and Classes" table should combine both the classes and pupils. However, I want to be able to firstly select the class to put the pupil into (and hence infer the induction year), then just show in the next filed only
the pupils that belong to that induction year and hence go into the selected class.

However I design my lookup or query, I can't seem to figure out how to do this, or if this can even be done.

Any help is much appreciated.

Chris

View 14 Replies View Related

Find A Field Via Typing Part Of Its Characters

Aug 21, 2007

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

View 4 Replies View Related