I have a an address field in my current MS Access table that has records following this sort of format eg. 21 Brown St, 21/56 Smith St
I want to separate every word in this field in to it's own field eg. field1=21
field2=Brown
field3=St
Addresses come in different formats (see examples above, with / or - etc.)
I'd like some code to complete this in VBA - please note I'm very new to programming so unfortunately everything must be explained.
I'm making a table with a Yes/No choice many fields which represent subscriptions.
The problem is that the subscriptions available will need to be changed fairly often - will this mean the table and the form associated with it need to be redesigned?
If so, I don't know how to make fields that change depending on a different table.
If this can't be done, I'd be interested to find out how else I could go about this.
I'm working with a linked table that has a seperate field for the date, and a seperate field for the time (which show in the linked table as '11/30/1899 9:46:00 AM'). I need to calculate the time difference between 2 entries.
How can I merge the 2 fields into a combined date/time field for my calculations?
I am working on a DB and would like to include the following information on a report. The information is as follows;
tblPersonnel.Name Tony Jennifer James Tom
tblPersonnel.LOA tblPersonnel.Vacation
Tony is on a Leave of Absence (LOA) Jennifer is on Vacation which leaves James and Tom at work. Lucky them! The fields "LOA" and "Vacation" are two separate check boxes.
I would like to limit the query to display employees that are either on a "LOA" or on "Vacation" Since they are in two separate fields how do I write the expression? I know how to limit it to only employees on LOA. Or employees who are on vacation, but how do I qry those who are on vacation or LOA?
I have a txt file that I'd like to import, which contains answers to a test. The number of questions on the test varies year to year, but this year there are 110 (split in to two sections).
I'd like to import the file, so each answer is a field, and then score the answers against a 'correct' answer lookup table.
What's the best way of approaching this? I first thought of using vba to read the text file, split the text, and then use an SQL command to insert to to my table. This seems tedious (there are around 6500 respondents).
Information 8|REPORT BY THE COMMITTEE APPOINTED TO STUDY CLASSIFICATION, TRANSPORTATION, DISTRIBUTION STORAGE AND SALES OF PESTICIDES IN *******|***** *. *****|******** ** *** **********|****|||PA2|
Okay, so my office had this emergency data dump, we lost the library, the records were saved but to do so they had to be backed up as .t files :-s which means less formating then a txt file :eek: :eek: :eek: , anyway, my office is hopeful I can fix their problems, sorry bout the ***'s, I was told that I can not put that information up on the net for security/privacy purposes. Anyway, what I have to figure out to do is to take this 1 field and split it into 8 fields, these correspond with the | character. If you see 2 || or more together it means that field was empty. Anyway, I just need a way to break these into 8 seperate fields, any feed back would be great.
Been handed a contacts database, one field of the DB includes all the industry sectors associated with the customer, with multiple industry references separated only by a comma within the field (e.g. Insurance-Life,Insurance-Broking,Insurance-Motor,Insurance-Pensions,Insurance-Home,Insurance-Health) – I want to split this one field into multiple fields using the comma as the break point (this is something that’s relative easy to do in Excel with the ‘Text to Columns’ tool – but unfortunately it can’t take the 130,000 odd records of the databasel)
Can any one recommend an expression or method that will see the 6 industry references split into 6 separate fields ?
I can't figure out how to skip the first space from the right, and reference to the second occurring space, so that I get "T4T 1L7" to populate the ADD_2 field, and everything to the left of "T4T 1L7" placed in the ADD_1 field.
Hello, How can I combine two seperate fields into one field? For example field1 has 123 field2 has Smith St. I want to put it in one column that has 123 Smith St.
I've spent about an hour searching this site for email issues but most I found we how to send. I want to take existing data and make an email address. I have a FName field and an LName field. In my email field I want FName.LName@email.com. the @email.com will always be the ending. Also where (on gotfocus) is the best place to put this code so it autopopulates after the FName and LName are entered?
I am bulding a contacts DB for work and am a bit of a newbie with Access. I have a table for Individuals and a table for Organisations. Both have address fields. On the individual's form there is a drop down for Organisation, which is linked to the organisations table (fk). The form has a sub-form which displays work address. I want the work address field to update to the Address field in the Organisations table when an organisation is selected on the drop down. I am using Acess 2003. Could somebody point me in the right direction please?
I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.
I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.
I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.
I designed and used an Access 2003 for some time but I have recently updated to Access 2013 and want to rejig and improve my database. My query is that I need to print invoices from my database, there is a header section with my details, then a section with the customers name and address at the left hand side, and the invoice details such as invoice number, invoice date, PO number, credit terms, etc. at the right hand side. I would like to remove any blank lines from the customers address but to still have the invoice details at the right formatted and spaced correctly. Is there any way of doing this, the canshrink property will not work due to the invoice details which still need to be printed to the right?
I do have another problem with my "Membership Monster" After designing the base with only registential Addess information in the data entry Form. I had to include fields for a separate postal address. This only applies to maybe 10 of 400 entries is there a simple clever way I can copy this data over to the new postal detail fields from the Residential detail feilds ? I thought it may be possible to set thee residential fields as source data. But I am unsure if it is then possible to enter different data over the top if the postal address is different.
I also then need to be able to mail merge the postal details of current members so as print out address labels for the magazine.
I have an address field containing "City", Street Name, and street Address. How do I link these fields together on a form so that when I enter data into the "City" field the rest of the fields making up the address fills in?
I have an access table which lists a customers address, however, if the customer has a temporary address it will also list that on a separate line -
CustID Address Type 1234 5 The Street P 1234 12 The Street T 2345 13 The Road P 3456 12 The Avenue P
Where P = Permanent and T = Temporary
I want to set up a query to show the permanent address where there is only one address, but where the customer has a temporary address also, I want to list the temporary one instead.
I have a form bound to a table which stores contact info for a person. I have two controls that are email_address and website_address. I set them both as hyperlinks. So now when the user clicks on the email_address Outlook opens a new message with an email_address in the To field. (I had to use a function found on this forum to replace the "htttp" with "mailto" on AfterUpdate event in order for this to work properly.) However, now trying to add some other features to my DB I ran into problems listed below. I wonder if I should have rather left that control as Text instead of Hyperlink, and use the DoCmd.SendObject on doubleclick event instead of using the above solution. How people usually store email addresses and enable emailing on click?
The problems I ran into: 1) My main form is in popup mode and therefore right click on the hyperlink does not give an option to edit the hyperlink 2) I copied a function from this forum which gathers a list of emails and sends one message to the emaillist. However, my email list instead of generating as: email1@a.com; email2@b.com it generates as: email1@a.com#mailto:email1@a.com#; email2@b.com#email2@b.com#
I guess I could extract the email using vba (not sure how), but I still don't know how to solve issue number 1. So maybe it is better to siwtch the field to plain text and forget the hyperlinks?
Thanks, Mariusz
PS. What procedure on double click would open a default browser, since I also have to deal with web addresses and thought of turning them into text controls?
I was wondering if there was a query to run that would seperate a field in a column into to.
The Current Field is Prec and the entries all are numeric numbers followed by a dash and another numeric number i.e. 9700064-0 7600049-0 Another 7 digits-one digit I would like the first 7 in a column called prec1 and the one after the dash in a column called grp
Is it possible to have 2 sepaerate sub forms, with different source objects on a single form. I thought it was, but every time I save the main overall form, the source object of the second subform changes to the source object of the first.
Hey all, For securing a database, is it possible to set a certain password for users to view just one table? like i've lots of tables in my d.b that i plan to use a workgroup file for different users to view it, but there's one that i only want certain people to access...can this be done at all??
I have imported data from Outlook. The field I am interested in is the 'Subject' Field. This contains 3 effective segments, 1 employee name 2 description 3 job and sub job number
I wish to move the employee name into another field and also the job and sub job number. I have tried the following Right() Function but it is not quite working as I want
Is it possible to create a database and link this to a form and another database? What i want to do is link a network database with a local database on a client machine?
I want to import a csv file and store the good data (which matches input masks, validation etc) into one table, and all the bad data which is rejected into another table.
I've read a bit about an import error table, and although I do get errors when importing the csv file, no such table is generated.
Hi. Having a bit of a problem getting a subform to requery/refresh after running a query from a seperate form. The seperate form is acting as a 'search form' and running query qSearch.
I've tried various things (requery the form, subform etc) but here is the code I'm currently using:
Private Sub RunqSearch_Click() DoCmd.OpenForm "Papers" Forms.Papers.RecordSource = "qSearch" Forms.Papers!Authors.Form.RecordSource = "qSearch" Forms.Papers!Authors.Form!AuthorName.ControlSource = "qSearch.AuthorName" Forms.Papers!Authors.Form.Requery Forms.Papers.Requery DoCmd.Close acForm, Me.Name End Sub
The main form is requerying fine, but the subform isn't. Any help would be appreciated. Thanks
