hi, im a bit stuck. i am creating a mail merge and i want access to retrieve only the first character of the forename to input into the mail merge.
e.g. Dear Mr. R Price.
I want the 'R' to be retrieved by access from the full forename, so 'R' would be retrieved from the name Robert in the forename field.
Thx
Hi
im trying to combine the
Forename Column and Surname column so i can produce another column which is initial surname eg
Matthew | Williamson | M Williams
Kevin | smith | K Smith
how do i do this ive completely gone blank!!!!!! :(((
I have a form that contains a combobox. I want the default value for this combobox to be the Staff_ID of the first forename to appear in a table when sorted alphabetically.
The table I'm querying is called 'Staff'. Fields within are 'Staff_ID', 'Forename', 'Surname'. The table is sorted by 'Forename' in ascending order.
From the example tables below I would expect to return a value of '3' for example 1 and '9' for example 2.
Example 1
Staff_ID Forename Surname
3 Andrew Banks
7 John Jacobs
2 Mark Jones
8 David Smith
Example 2
Staff_ID Forename Surname
9 Alice Jones
3 Andrew Banks
7 John Jacobs
2 Mark Jones
8 David Smith
I'm using Access 2007
How do i trim a name in a string.
I have 2 rows with firstname in one and the second has surname. if i wanted to view the results to have A Einstein rather than Albert Einstein, how would this be done?
thanks.
I have table where First Name, Last Name and Initial are all in one field. What is the syntax to remove initial? Been playing with Right and Len with no success, I'm sure this has been done before.
View 14 Replies View RelatedI run duplicate check on a form to make sure the file name doesn't match one that's previously been entered. Currently I have:
If Me.NewRecord Then
If Not IsNull(DLookup("File", "tblFileProcessing", "[File]=""" _
& Me![File] & """" & " and [FileClientID] = " & Me![FileClientID])) Then
DoCmd.OpenForm "frmDuplicateFiles", acNormal, , , , acDialog
End If
End If
However we've recently changed the way we work and have now added a unique ID number prefixed to the file name: 567_File_Name_1 How do I run the DLook up but parse off the digits prior to the first underscore and only check on the remainder of the file name?
I have a form set up so that a supplier code combo box is populated with a simple SQL statement to get values from a lookup table. I have a second combo box that is populated when the supplier code is selected by the user. It works well. For example, if the user selects supplier Dog then the current record value of the Product field is displayed and the combo box shows all Dog products. So that's great.
My problem arises when the form loads. I can't convince Access to fill the Product combo box with the current products. For example, if the first record has Cat as the supplier, I want it to display the Product value for the first record and to show all the Cat products in the drop down. It isn't doing it on load or on record navigation. It does work if the user reselects the supplier.
I have tried various events.
Right now I have the following code in the AfterUpdate event of cboSupplier:
qry = "SELECT SupplierCodeProductCodeEquityTypeQuery.Product.Nam e, SupplierCodeProductCodeEquityTypeQuery.ProductCode ,
SupplierCodeProductCodeEquityTypeQuery.SupplierCod e, "
qry = qry & "SupplierCodeProductCodeEquityTypeQuery.Supplier.N ame, SupplierCodeProductCodeEquityTypeQuery.ProductId FROM SupplierCodeProductCodeEquityTypeQuery "
qry = qry & "WHERE SupplierCodeProductCodeEquityTypeQuery.Supplier.Na me = '" & cboSupplier.SelText & "';"
Me.cboProduct3.RowSource = qry
I have 2 Comboboxes, one for Region and one for Country, and am attempting to initalize/cascade them. Here is the basic info for the the underlying tables:
tblRegion
regionAbbreviation (Text, PK)
regionName (Text)
regionOverseer (Text, Foreign Key...essentially a parameter)
[code]...
Some relational constraints of which to be aware:
1. Every Region has at least one Country
2. Every Region has one and only one Overseer
3. An Overseer may be responsible for more than one Region
Characteristics of the Comboboxes:
1. Both use 2 columns with the Name field (first column) being displayed and the Abbreviation or Code field (second column) being the bound column.
2. The nominal case is that both comboboxes will have multiple items to select and in that case the comboboxes are initialed with a query containing a UNION that inserts the string "<ALL>" (same string in both columns).
3. When the case arises that an Overseer manages a single Region or a Region contains a single Country, the Union query is bypassed such that the "<ALL>" string is excluded.
The comboboxes are used to create a filter condition to drill down into a potentially very large recordset.Now to the issue at hand ...
The Region combobox is initialized first. The initialization is accomplished by setting the RowSource property to an appropriate query. The first entry in the item list is then loaded to the combobox Value property by the following line of code:
Me.cbxRegion.Value = Me.cbxRegion.Column(0,0)
This always works whether there are multiple items or just one. If there is just one I set the Enabled property to FALSE; no point in reselecting the only item avaliable.The Country combobox is then initialized in a similar manner using appropriate code that is virtually identical to that used for the Region combobox.The problem I am having is that if there is only a single Country, the Name is not displayed in the combobox but is contained in the selection list. Note that i do not have a problem with the Region combobox when it is initialized with a single Region.
I have searched this forum and others and found similar inquiries and a couple of possible solutions; some clearly don't work while others appear to but result in run-time errors later in the execution of the code (run-time error '2115').I do not have a simply test case I can upload at this time; also new to the forum and appear to be restricted as to my ability to perform uploads.
I want access to generate for me an autonumber, but with the current year as initial value. ( To give new hired employees a unique number). For example, we are now in 2013
If there a new hire, that employee is going to get a unique number 2013001
The next new hire, will get 2013002 and so on
I know how to append a table to set the initial value, but this means, that next year I will have to append a new table again to set the initial value to 2014.
I want access to do this automatically for me, so that next year a new hire will get the unique number of 2014001, the next one will be 2014002 etc...
Hello,
Is there an easy way to split a full name into firstname, middle name and lastname? I have a field name with names such as James R Lowes and i would like to split the name in 3 like.
Firstname : James
Middle Initial: R
Lastname: Lowes
I was able to find a module to extract the first name from a string, but don't know much about VB and cannot figure out to do the rest.
Please help!
thanks,
Pablo
Hello,
Is there an easy way to split a full name into firstname, middle name and lastname? I have a field name with names such as James R Lowes and i would like to split the name in 3 like.
Firstname : James
Middle Initial: R
Lastname: Lowes
I was able to find a module to extract the first name from a string, but don't know much about VB and cannot figure out to do the rest.
Please help!
thanks,
Pablo
I am trying to display an open file dialog window so that the user can pick up a file. I wish the window to show a specific folder. How can I do this? The code I am using is below. The parameter InitialFileName has no effect on the outcome.
Code:
Function GetFileName(strPath As String, imtype As String) As String
On Error GoTo Err_GetFileName
Dim Dlg As FileDialog
Dim sfl As FileDialogSelectedItems
Dim sflitem As Variant
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
[Code] .....
I have a form presented in datasheet layout.
I set the initial sort by two columns, but I would like to keep highest priority for that initial sort, so when user wants to sort by some other columns that sort happens inside my initial sort.
Hi,
I am new to access and need help trying to retreive data. Basically I have a unique ID field (123545). I what a user via a form to be able to retreive data using this unqiue ID. Does anyone know the best way to achieve this in access please?
Thanks
Ket
I have an Access2003 database that contains a table. The table has 2 fields. One is a counter and the other stores a picture which is datatype ole object. I want to do a one time export of the pictures. I want to save the pictures as jpegs in a designated directory. I know very little about working with ole object datatypes. Can someone tell me the easiest way to do this?
View 1 Replies View RelatedHello All,
Is there a way to retrieve the very last entry to a table (via a query) without passing a value to the query.
Lets say I have a table Pets
ID desc
==============
1 Dog
2 Cat
3 Lizard
For example lizard was added last, is there a way I could pull just this out using a query? (keep in mind that I don't know wahat the last entry is, so I cannot pass any kind of value to the query)
Cheers,
Aaron
Hello all,
I would like, from a form, to get the file size of a particular file.
I would really like something like: FileSize("C: estfile.exe")
I am sure I have had something like this before, but cannot for the life of me find the answer I originally got!
Thanks!
I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below (although the table headers have moved a bit). There are 5 engine capacity headers and numerous pence per litre rows.
Pence per litreUp to 1000cc1001 to 1500cc
75.08.5259.653
75.28.5509.682
75.58.5759.710
75.78.6009.738
75.98.6259.767
76.19.6509.795
So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.
Is there anyway I can get access to look up this information for me?
Does anyone know how to setup an ODBC to the Ebay Web Server?
View 3 Replies View RelatedI have a table containing the following two fields, one with monthly dates (end of month plus year) and one with profits (per month). However, for some dates the records are missing. For example, for the 31-1-1994 there is no record (not in the date field, nor in the profits field).
How can i create a query that will only show me the records if 10 or more monthly subsequent profits are known, so meaning that in those 10 months no records are missing? So that only the timespans without the gaps (missing records) are shown.
So if the 31-1-1994 and the 30/6/1994 record are missing, then the 4 subsequent records in between those two dates should not be shown,, since the amount of records is not 10 or more. However, if the next missing date would be 30/6/1995, then all the 11 subsequent records between 30/6/1994 and 30/6/1995 should be shown. Since the number of records is bigger than the required 10.
Thanks for helping me out!
Luuk
Hi,
I making a query which creates a list of customers in a month. For the start and end dates of the month, they are retrieved from a table and put into 2 seperate subforms. The query isn't working through, so I was wondering if anyone would see where I'm doing wrong.
In the order_date field in the query, I have this as the criteria:
Between [Forms]![sub_fltStart]![DateList_Start] And [Forms]![sub_fltEnd]![DateList_End]
If you want any more clarification then just ask :)
Cheers,
Ben
EDIT: spelling/grammer
Need some help, please.
I'm writing a simple report that needs to show individuals and the number of times that each individual has been designated the author and/or owner of a document.
The two tables in the query (simplified) are: Person, with columns personID (PK) and personName; Document with columns docID (PK), authorID and ownerID.
Each report line needs to show one line per person, with the ID, name, count of authorID and ownerID (showing the number of times he/she was designated the author and/or owner of one or more documents).
For example:
ID ... Name ..........Author ... Owner
1 .... John Smith .... 0 ......... 3
2 .... Mary Smith .... 2 ......... 0
3 .... Peter Smith ... 1 ......... 2
I need to create a query to retrieve one row per person, then do some kind of subselect (?) to count the number of matches for Person.personID against Document.authorID and Document.ownerID.
I'm having all kinds of problems in what I thought would be a simple SQL statement. Can't find anything out there, so all suggestions are welcome.
Thank you.
I put this on the tables forum but my answers have now stopped, can anyone here help me with how I get this information to appear on a form....
I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below. There are 5 engine capacity headers and numerous pence per litre rows.
Pence per litre ....Up to 1000cc.......1001 to 1500cc
75.0 ..................8.525.................9.653
75.2 ..................8.550.................9.682
75.5...................8.575.................9.710
75.7...................8.600.................9.738
75.9...................8.625.................9.767
76.1...................9.650.................9.795
So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.
Is there anyway I can get access to look up this information for me?
.................................................. .................................................
Ans:
It would be nice if you could find out the formulae for working out the pence per mile bit based on the cc.
Either way, I think your table needs changing to this design
Price. . . . CC. . . . . . . . . . . . . VAT
75.0 . . . .1000. . . . . . . . . . . .8.525
75.0 . . . .1000 to 1500. . . . . . .9.653
etc
etc
Col
__________________
This ain't no technological breakdown. . .oh no, this is the road to hell.
.................................................. .................................................
Thanks,
Unfortunately I can't get hold of a formula. I'm still not sure how I would look up a value, even if I changed the table as you suggested. The user would need to select a cc size and then a price per litre which would then need to be cross referenced to give a value. I could set up different tables for each engine size, but then I'm not sure how I could point the answer at the correct table.
I don't even know if what I am trying to do is possible in access.
.................................................. .................................................. .
Sharon, you could have the price per litre in one ComboBox and the engine size in another. Both these would then be the criteria for a query.
Can you post your Db and I'll have a look for you, failing that I could knock one up (in Access97)
Col
__________________
This ain't no technological breakdown. . .oh no, this is the road to hell.
.................................................. ..................................................
I think I've attached the file, but I've never done this before so it might not be there!
The table I'm trying to create is called pence per litre but it is completely stand alone at the moment until I can work out how to get any information out of it. I have changed the table to your suggested layout but have only entered a few records, there are hundreds to be entered if it can be made to work!
Thanks
Sharon
.................................................. ................................................
I can't seem to get the file to attach it doesn't seem the right extention code it's an mdb
In my employee attendance database each record contains an employee id#, a number corresponding to an attendance infraction, and a corresponding date. Each week a clerk queries the database to pull up records for all employees who have a yearly 'total number' of infraction values over a certain numerical limit.
Any suggestions as to what is the best way to:
1 - Calculate that yearly 'total number' for every employee.
2 - Retrieve the date of the most recent attendance infraction for each employee that has a total value that is over the limit?
Thanks
Hi Everyone.
Lets hope that after finding this forum, my slight problems will begin to ease off a little.
I am unfortunately one of those newbies trying to get in well above my head and level of ms access workings, but we all have to start somewhere, right?!
My problem at the moment is as follows:
The scenerio is i work for an excursions provider in Cyprus and I am trying to set up an online excursions site for them.
Now with any excursion, the price flutuates through the year when its low season, high season etc. I have built a MS Access database with the following tables so far.
Excursions details: this contains everything about the excursion on offer, along with additional columns for the price changes and dates that these apply for. ie. [adprice1][fromdate1][todate1], [adprice2][fromdate2][todate2] etc
Now what I am trying to acheive if at all possible is that when a viewer takes an interest in an excursion and selects the date they would like to go on the excursion, that the correct price is displayed for that specific time period i.e if it the date was betwen [fromdate1][todate1] or [fromdate2][todate2]
Is this at all possible and if so can someone please explain to me in real laymans terms what I need to do for this too occur within the database please.
Thank you in advance and sorry for waisitng anybody's time if this seems obvious to others and not myself!
Regards
2rsGarry
I'm trying to retrieve the value of a record in a table. Why doesn't this work:
tabName = "Plan Data"
Dim db As DAO.Database, rst As Recordset
Dim strQryWhole As String
Dim dbStatus As Integer, rcnt As Long
[code]....