Grab Parts Of Field Value

Feb 27, 2008

I know how to Grab the Right and left Characters and grab the mid characters from a field value

ex: Right([fieldName],4)

My question is how to I grab characters based on a space
Say for instance "Fred Smith" or a name field. There are going to be varing lengths of last names...but the format is the same...First Name, Space, Last Name....

QUESTIONS:

How do I search the string, count to the space, then take all characters after the Space?

One step further how would I grab the middle name in this example?
"Fred M Smith"

View Replies


ADVERTISEMENT

Grab First Characters From Field

Oct 4, 2011

Is there documentation/reading out there on how to query fields and take only a certain number of characters from said field?

We are setting up a user database on a 3rd party web application and they have a 15 character username limit so we want to pull the first 3 letters of a persons first name, middle initial, and first 4 letters of a persons last name to use as the username. We have an Access database will all of this information but I am not sure what the correct query is to complete this task.

John P Roberts = JohPRobe

View 1 Replies View Related

Selecting Parts Of A Field For Querie

Aug 8, 2006

I have one field with complete url´s.

Example
http://www.rocketspanish.com/links_spanish_immersion.php

or

http://apps5.oingo.com/apps/domainpark/results.cgi?q=naco&cid=ca-dp-260com_nopop&url=http%3A%2F%2Fnaco.com%2F&hl=en&ac=s

I like to make a groupby but only from the left side up to ".com"
Is there a way to select only the text untill ".com"

Thanks

Wouter

View 2 Replies View Related

Generating A Field With Parts Of Other Fields

Jan 1, 2008

If I have three fields

Surname - text
Given name - text
Dateofbirth - dd/mm/yyyy

how can I generate a field consisting of

surname(then)first letter of given name (then) year of birth

(ie SMITHK66)

Thanks in advance

View 2 Replies View Related

Modules & VBA :: How To Grab A Value From A Query And Put In Into Text Box

Apr 4, 2014

How do I grab a value from a query and put in into a text box? I need to grab the name that returns.

Private Sub text_badgeRDM_AfterUpdate()
Dim EMP_ID As String
Dim strsql As String
Dim sqlempinfo As New QueryDef

[code]...

View 3 Replies View Related

Modules & VBA :: Grab And Calculate Days Between Two Dates

Feb 11, 2015

What I have is two fields, one is a simple date and the other is slightly more complex as it holds a date but in a different structure (a dated case number).

The case numbers are for example, 150211551223 the date being the first 6 numbers and equaling to 11/02/2015. so the code will need to grab this data, spin it around and convert it.

The formula will then be:

simpledate - convertedcasenumberdate = days between.

(simpledate will always be the latest date of the two).

View 7 Replies View Related

Queries :: QTREE - Link And Grab Information From 2 Tables

Mar 11, 2015

I have 2 tables, and each table having different information and i want to link those 2 and grab information from both tables.

From below tables, i want to map the Qtree and then grab the rest of the information. for e.g. qtree1 suppose the same as qtree1/a

Table 1
Qtree | Mountpoints | Hostname
qtree1 | MP1 | Host1
qtree1 | MP2 | Host1
qtree2 | MP2 | Host2

Table2
Qtree | InstallDate | Owner
qtree1/a | 1-JAn | Vic
qtree2/a | 2-Jan | Gab

Now, How can i map the Qtree since i cant use = as the qtree are different name.

I tried to use LIKE but no luck as well.

btw, in actual... qtree looks like

//amsdc1-s-12345.europe.xxx.com/dhc-amsdc1-s-03015$
/dev/backuppool02

from above....

/dev or //amsdc1-s-12345.europe.xxx.com/ will appear in the other table to map the rest of information.

View 3 Replies View Related

Reports :: Query Fails To Grab Second Date Criteria Unless Tab Off Of It First

Sep 9, 2013

Before I generate a particular report, the user must input a start and end date. If the user mouses to the generate report button, the query fails to grab the second date that was input. If the user tabs off of it, then it works just fine.

I think I could solve it by requerying the form before I generate the report, but that feels like a bulky work around. Is there a better way?

View 2 Replies View Related

Question On How To Grab An Excel File On The Local Hard Drive

Jun 15, 2006

Hello, I was needing some help coming up with the code to search the local hard drive and grab a file and then import it into an Access table. I'm trying to do this on a form if possible. If you know of another way, that would work. Any help would be appreciated. Thanks.

View 9 Replies View Related

Modules & VBA :: Way To Grab Text From The Window That Shows Location Of File

Jun 18, 2015

I want to find a way to grab the text from the window that shows the location of the file using vba in Access.

View 3 Replies View Related

Queries :: Huge Report With Visits Per Customer - Grab Information Of Last Visit?

Dec 2, 2013

we have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.

I want to create a querry that exports a table with 3 columns:

1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit

I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.

how to handle this in MS access 2010?

View 9 Replies View Related

Queries :: Grab Custom Fiscal Year Count Based On System Clock Date

Aug 13, 2015

How do you grab a custom Fiscal Year's values based on the system clock's date?

I am building a query where I want to see the number of closed cases based on the current custom fiscal year with the system clocks date. The report that it feeds only cares about the current FY.

I need the System Clock's FY value in this query

Code:
SELECT shortname AS Station, NZ(TotalCount.TotalCases,0) AS [Cases Complete]
FROM StationList LEFT JOIN (SELECT station, count([Open Issues].ID) AS TotalCases FROM [Open Issues] WHERE [Status]="Closed" GROUP BY Station) AS TotalCount ON StationList.shortname =TotalCount.station;

Within the Query Open Issues I have the FY broken up

Code:
fiscalYear: IIf(Month([Issues].[Opened Date])>=10,"FY" & Year(DateAdd("yyyy",1,[Issues].[Opened Date])),"FY" & Year([Issues].[Opened Date]))

View 4 Replies View Related

Parts Database Qty In - Qty Out

Nov 8, 2006

Hello!
Simple one for most, but I have having problems with a Database I am setting up
The database controls stock movement (i.e. inwards stock and outwards stock)

I have set up a Parts Inwards table with the following fields:
Part Number (linked)
Qty In
Stock Location

And I have set up a Parts Out bound with the following fields:
Part Number (linked)
Qty Out
Stock Location

Now for the problem, when I run a query to tell me how many parts I have left in the stock room i.e. Qty In - Qty Out, it duplicates the data in the Qty In.
I assume its got something to do with the fact that I have multiple Qty out’s for a single Qty In entry…hope this make sense?

Can someone help?

View 4 Replies View Related

Creation Of Two Fields From Parts Of Another

Jul 5, 2006

In a field “NAME” I have “Adam Smith”.
From this field I want in a QBE window (not in VB) to make two new fields “FIRST NAME” “Adam” and “LAST NAME” “Smith”.
In Excel there are the functions FIND and SEARCH with which I take the number of the gap “ ” (5) between Adam and Smith and with the functions LEN, RIGHTS and LEFTS I have a result.
What could I do in Access 2003?

View 3 Replies View Related

Delete Parts Of A String

Apr 28, 2008

Hi,

I am trying to delete a part of a string but not all of it.

For example; American Express Inc. I would like to delete only the "Inc.", but leave "American Express".

How is this done?

Many Thanks,

Tim

View 4 Replies View Related

Parts Quantity And Orders

Mar 1, 2006

Hello All,

I am new to this forum :) and learning Access :confused: .
The Database
Tables:
Employees
Products
ExpectedQuantity
PartQTY
Parts

Most of the products are similar so they have the same parts for the majority. However they do not all have the same parts. So I created a parts table that contains all the parts needed for any product. Now each Product also can contain multiples of one part (such as wheels) where I would want to list the Product and the parts list and quantity needed of those individual parts for that product. Hence the PartQTY table.

Now what I want to be able to do, is go into a form where each record is one of the products. A subform (datasheet view) for that product that shows all the parts and the quantity of each part that make up the product. Such as 4
wheels, 5 screws, etc for Product A.

I would like there to be an ExpectedQauntity field in the main form which once you found the product you can enter in the number of that product expected. Then this Qauntity would be multiplied by the qauntity of each part required (in the datasheet subform a column with =[expectedQTY]*[PartQTY]) and list next to each part the number needed to order. So if for product A the expected quantity is 50 I would have to order 200 wheels and 250 screws for that product.

A report would Sum each part qauntity after the expectedqauntity has been entered for each product. So I could print a report that tells me how many of each part we need to order based on the expected qauntity of all the products.

I think I am complicating things and would like a fresh mind to assist me in which direction to go.

Thank You,
Adam

View 9 Replies View Related

How To Calculate Salary In Parts

May 6, 2012

How to find out the Salary. A school remains closed for Winter vacation from 15th Dec. of Current year to 28-29 Feb next year.)

The management disburse the salary to the school as per following procedure

If the Employee has not completed 6 months period (180 days) as on 15th December of current year he will not be entitled to winter salary (2 and a half months) from 16th Dec. of current year to Last day of Feb. next year. For rest of the period he will get normal salary. i.e. for 11 days of June and July, August, Sept. Oct, Nov and 15 days of Dec. of current year he will get full salary.

To elaborate it further it means that if an employee has been appointed on 19th June of the current year his winter salary with effect from 16th Dec. of current year to last day of Feb next Year will not be disbursed. For other time period he is entitled for normal salary.

View 6 Replies View Related

Quick Q About Product Tables (parts)

Jun 30, 2006

OK, here's my delima (my head is spinning)

I have a standard Product table:

tblProducts
ProductID (PK)
ProductName
VendorName
Description
UnitPrice
InStock

Every Product has many Parts, but one Part may go to many Products

I will build this table:

tblProductParts
PartID (PK)
PartName
VendorName
Description
UnitPrice
InStock

How would I relate them together? I figure I need to use a Many to Many, with this table inbetween:

tblProductPartsList
ProductPartsListID (PK)
ProductID (FK to tblProducts)
PartID (FK to tblProductParts)

I feel like I'm missing something, can you let me know what you think???
THANKS!!!

View 5 Replies View Related

Replace Multiple Parts Of A String?

Aug 8, 2007

Hi, can anyone help me do this...


In my products table I have fields such as Bulletpoint1, Bulletpoint2, MainDescription etc.

Each holds part of the product description

In a seperate table I have my description templates with placeholders for the bulletpoints and maindescription. for example....

<ul><li><!-- Bulletpoint1 --></li><li><!-- Bulletpoint2 --><li></ul>



Now, for each record I want to grab the template and replace the placeholders with the real text.

I've got as far as putting this in a query...

Expr1: Replace([DescTemplates]![TemplateData], "<!-- Bulletpoint1 -->", [Products]![Bulletpoint1])

That does the job nicely or the first bulletpoint but where from there? There must be a neater way than having a load of query columns each updating the last. ie.

Expr2: Replace([Expr1], "<--Bulletpoint2 -->", ...
Expr3: Replace([Expr2],...

Or is that the simplest way of doing it?

Cheers!

View 1 Replies View Related

Alternate And Related Parts From 1 Table

Aug 2, 2012

I have a table with an ID and PartNumber field.

Parts tbl
PartNumID PK(Auto)

PartNumber

Heres some example data:
1 M24308/2-2F
2 M24308/4-2F
3 ORD9F0000
4 ORD9M0000
5 D90000VLO
6 D90000JOO

How would I design the table to allow me to define alternatepart numbers and also related parts? For example:

Record 1 is the primary part and record 3 is the alternatepart.
Record 2 is the primary part and record 4 is the alternatepart.
Records 5 and 6 are related parts to records 1, 2, 3 and 4.

I have a form that allows the user to select a part and displaythe part data. But I also want the alternate parts and related parts displayedon the form, maybe in a subform with tabs. I havent gotten there yet because Icant determine the table design.

View 3 Replies View Related

Printing Parts Of Records In A Specific Format

Oct 27, 2005

i've created a database for city permits to be issued (which works perfectly thanx to alll your help), but now i need to be able to print out 1 permit at a time when it is issued (immediately after the data has been entered into the forms). i have 3 tables & 3 forms: contractors/owners, permtOrders (which has tabs for the different types of permits), and permitOrderDetails. the problem is the forms are set up for data entry (and only open in add mode for obvious reasons)...i need to be able to print an actual permit (like the carbon copy, landscape kind that usually tears out of a book). once the user enters the information into the forms, i need a little bit of that info from each form to be carried onto the actual printable permit. i'm not really sure how to accomplish this. :confused: i don't really know if i explained this right, but any suggestions are greatly appreciated.

View 6 Replies View Related

Simple Inventory Of Ind. Parts But Selling Kits -- Please Help!

Nov 3, 2004

I'm not sure what the best way to set this up is, and I'm hoping someone can point me in the right direction.

I have a long list of different parts that I have acquired and taken inventory of. I am selling kits that contain different amounts of certain parts. For example, Kit 1 contains 2 of part A, 3 of part B, and 14 or part L. I will only be selling complete kits and not the individual parts.

Here's what I'm thinking. I need a table for the individual parts that lists the quantities of each. A table for the kits and a subtable for the kits that lists the quantities of each part. A transaction table.

Then I just need a form that has a combo box with the kits listed, date of transaction and quantity shipped, and a button that runs a query to subtract from the quantities of the individual parts based on the kits table and subtable.

Can someone please help? I'd really appreciate any tips.

Thanks.

Richie

View 5 Replies View Related

Query That Finds Parts That Start With * Wildcard

Feb 26, 2007

I have a sales invoice table that contains parts that start with *, for example *SPR362.

I need to select only those parts that start with the *, as this is the wildcard in queries how do i select those products that start with it without it being recognised as the wildcard and selecting all records?

The field name is simply: Part

:o

Many Thanks

View 4 Replies View Related

Forms :: Create A Form To Search Parts

Nov 4, 2013

I want to create a form to search my parts.example I typed in a part number 123 , the other text box also will come out the detail about the part 123.

View 2 Replies View Related

Queries :: Show All Unlinked Parts With Query

Nov 22, 2013

How to calculate all records that have not yet been linked to other records. For example: I have 1 Product, which is linked with multiple components which is subsequently linked to subcomponents. All these types can be linked to Alternate Parts.

In order to reduce redundant information I would like to show the unlinked parts to the user. Therefore I would like to make a query which shows all unlinked parts.

I have the following tables with subsequent PK Part Number field values:

tblProduct->ProductPN
tblComponent->ComponentPN
tblSubComponent->SubComponentPN
tblAltParts->AltPartPN

I am unable to find a appropriate query structure with criteria for this issue due to the many different fields required and the fact that if in one query all these tables are shown no data will be shown ...

View 5 Replies View Related

Queries :: Single Total Of Parts Between Range

Jan 8, 2014

I am running a query to some me total parts made between 2 dates. The dates are selected from a form with the query looking to this form to selected the date range.

When I run the query it shows me total parts made for each day. What i would like is a single total of parts between the range.

I assume I could achieve this by a 2nd query...

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved