How To Cut Second And Third Letter In One Column Records
Nov 2, 2005Hi!
I have one column with text field
I would like to know how to cut for. example 2nd and 3 letter in records collected in this column?
Thanks in advance for any examples
ADVERTISEMENT
How To Split Letter&number Field Into 2 Column And Skip Brackets?
Dec 2, 2005HI!
I have big problem I have telephone number field like this
Tel(XX)XXXXX - X are numbers
I have to split it into two columns and skip brackets like this
Column1 - Tel
Column2 - XXXXXXX
Is it possible?
I have no idea how to manage it...I'd be very thankful for any
examples ? Or Help....I'm beginner
Thanks a lot
in Advance
Storing The First Letter Of First Name, First Letter Of Last Name And Data Of Birth
Feb 19, 2006Hi,
I'm designing a database in which the primary key is a combination of the first letter of first name, the first letter of last name and the date of birth.
How can I do that?
Regards,
CS
Letter By Letter Search Through A Table
Aug 21, 2005ok here is what i want.. imagine a search box, just an empty text box that allows the user to type things into it...
there will be a simple table from the database behind it, here is the typical structure of a record
<Country>Poland
<Price1>60
<Price2>85
<Price3>95
in this table there will be say a 1000 of these records...
my user is going to have to quickly search through these records while on the phone to a customer, and quote one of the relevant prices associated with that country
what i would like is this most convenient system..
as the user types in the letter 'P' just below all the records with countries that begin with 'P' are displayed (with a scroll down arrow if needed)..as well as the 3 relevant prices with that record
if they then type an 'O' into the box (which will now hold 'Po') all the records with countries beginin with 'Po' will be displayed (eg Poland)
if they delete the 'o'. once again the displayed records below the box will return to just the countries begginin with 'p'
all that is needed is for the records to be displayed, thats it.. but the adding and deleting of the letters within the text search box will need to instantly manipulate this list..
i have posted on other forums, and have been told this can be acheived in access, is it hard?
Separate Records By Column
Apr 26, 2006I have a table set up as the following:
ssn account
01234567 1234567890
01234567 9876543210
01234567 7539514562
98765432 8765432109
98765432 9876541230
and so on...
I would like to create a query that will split the records up like this:
ssn account1 account2 account3
01234567 1234567890 9876543210 7539514562
98765432 8765432109 9876541230
and so on...
It can be in any format, either query or report, I just have to be able to export it into a text file in the same format. Does anyone have any ideas?
Thanks!
Vassago
Count Records With The Same Value In Column B
Jun 24, 2005Yes i'm having trouble with a Count problem, don't laugh at me.
I've got a query with 200000 plus records. Each record has an equipment ID number (its not the primary key) in column B. I just want to count up the number of records that have each equipment ID code, but im not sure how to do it.
Please help
Deleting Records In Column
Oct 4, 2006I'm trying to delete just ONLY the records inside of the column, Not the whole ROW. But I just don't get it. This is what I used.
DoCmd.RunSql "DELETE tblMasterProfileSheet.QTY FROM tblMasterProfileSheet"
Everytime when I execute that script, it's deleting the whole row. I just want to delete records inside of the QTY column, not the ROW.
Concantenating Records Within A Single Column
Feb 20, 2006Hey buddays,
I'm not sure what the definition of this type of concatenation is, so I'm having trouble searching for it.
I have three tables - one is tblNames (with ID, first_name, last_name) with 25 records, and tblStates (with StateID, state_name, state_abbrev) with 50 records, and the requisite join table between them - tblNamesState (with autonumber and the two FKs), because some of the folks in tblNames can have locations in multiple states. Good enough.
But now it's output-to-spreadsheet time, and I want to run a query that will give me 25 records, and concatenate the location records in a single row. An example would be:
first_name | last_name | state_name
John | Johnson | AL, AK, TX, FL
Eddie | Edwards | MA, TX, FL
Sally | Smith | VT, WA, CA, TX
How do I concatenate records from a single column?
Queries :: Records Count In New Column
Oct 21, 2013I have a bunch of records in 1 table. Some records could have 1 duplicate field. What I am trying to do is create a query that produces the total or count in a new column. For example:
ID
FName
SName
Account#
If account appears more than once, I would like a new column to count the number of times in total that record appears. Then use this in a report afterwards.
Is this possible?
Exporting Records That Match One Or More Than One Column
Jan 12, 2015I have stored data in access. The records have name, address and unique Id. I want to be able to extract the unique ID from the Access database for all those records that match the name and address in any new file that I compare my Database with.
View 10 Replies View RelatedQueries :: UPDATE Multiple Records In Same Column
Jan 16, 2015I am trying to run below to update multiple records in the same column and get error message saying characters found after end of SQL statement. I tried to remove ; but then get a syntax error.
Code:
UPDATE [tblMonthly] SET [Date] = #20130701# where [File] = 'A';
UPDATE [tblMonthly] SET [Date] = #20130801# where [File] = 'B';
Tables :: Updating One Column Of Data Across Many Records
Jul 9, 2014I have a database containing 10,000+ trouble tickets identified by a unique field called "Ticket ID". This is the primary key for my table. (It's on just one table.) These tickets came from an excel sheet that was exported out of a different database. (For various reasons, we've moved the tickets to access.)
Among many descriptor fields, I have a field called "SPR Type" that shows what type of ticket each entry is. (Bug, Defect, Enhancement, etc, etc) The problem we have here is... not all of the ticket entries have an SPR Type listed on my access table because not all of the tickets coming out of the original excel sheet had one.
I was given a new excel sheet containing 400 to 500 ish Ticket IDs and the SPR Type that applies to each of those tickets. How can I take that sheet and use it to update my access table? Ie. Use the excel sheet containing Ticket ID and SPR Type to populate the blank SPR Type fields for each of their respective Ticket IDs?
**Update: I'm trying something like this...
UPDATE [SPR Priority] inner join [EB Update of SPR] on [SPR Priority.Ticket ID] = [EB Update of SPR.Ticket ID]
SET [SPR Priority.SPR Type] = [EB Update of SPR.SPR Type]
But I am getting an error that says "Invalid Bracketing of name [SPR Priority.Ticket ID] and similar errors. What am I getting wrong here?
I thought about an Update query that would populate the SPR Type fields based on Ticket IDs. I could import the new excel sheet into a separate access table and update from there.
Forms :: Restrict Column Records Based On Criteria
Jun 16, 2014This seem to be simple for single criteria but i am not able to do it multiple. I want to restrict user to customer list they r able to see in form combo box drop down list.
users are in employee TBL
employee group criteria is in tbl_Groups which contains value Admin,Read Only, manager etc
employee r grouped through tbl_AssignUsersToGroups.
customer r grouped in customerType TBL. i.e class1, class2 etc
created EmployeesClassTypeRestriction TBL to store restrictions for employees
Restrictions criteria:
1-employee can view only specific class which includes all customers in that class
2- employee can view only specif customers in a class only.
3-employee can view only specif customers in a ANY class.
4- No criteria No restrictions and can view all customers in all classes( For Admin )
Tried to used Tempvar criteria in customer combo box qry but it works for above point 1 only.
Attached accdb sample also.
Multiple Text Search On One Column To Filter Records?
Mar 6, 2013I have a column that has a bunch of keywords they are separated by comma... so for each row of that column it will have a few different keywords example: lake superior, river, mountain, lake wollongong
I know its a bit of a nono with databases to have columns with comma separated text.. well so i read somewhere anyway but the document i have been handed to work has hundreds of rows in this column with up to 14 keyword entries.
I have a form that searches through Item's names based on 2 keyword boxes.
Here's the criteria in my query:
Is Null Or Like "*" & [Forms]![frmItemView]![SearchPhrase1] & "*" And Like "*" & [Forms]![frmItemView]![SearchPhrase2] & "*"
This will show all records when both keywords are blank, and filter records using the 2 keywords otherwise.
I have a form with two text boxes and have set the correct values as outlined in Evans post. I then have the query set to run via a button. I run it but it will only give back records for the entry i have put in the 1st text box. This would work well for me otherwise... mine is like this:
Is NULL or Like "*" & [Forms]![Searchtable]![Key1] & "*" And Like "*" & [Forms]![Searchtable]![Key2] & "*"
Searchtable being my search form
key1 being my first text box entry
key2 being my second text box entry
Modules & VBA :: How To Use Form Fields To Fill Numerous Records Under Same Column
Feb 2, 2015I am using .FormFields to fill a quotation template in Word, but the subform I am retrieving the data from has rows of records under each column and I need to send more than just the first row to fill the required bookmarks in Word.
The code I'm using is
Set doc = appWord.Documents.Open("S:TemplatesQuotation Template - Test.doc", , True)
With doc
.FormFields("FirstName").Result = Me!FirstName
.FormFields("FirstName2").Result = Me!FirstName
.FormFields("LastName").Result = Me!LastName
.FormFields("LastName2").Result = Me!LastName
[Code] ......
Letter Help
May 14, 2007hi,
Im creating a letter. It is based on a query. I have some details that I'd like to display , one on it's own line. I cant print them out now, but they dont all fall on their own separate lines. How should I do that? If I put a carriage control at the end of each detail line, will it know to print each of the fields on its own line? I need advice . I tried doing a subreport -type letter, but it looks weird!
thanks!
Letter Feeding From DB
Oct 16, 2007having database full of names and info, how to create a letter which feeds from specific database records with one click, so it can be printed and post it?
cheers
Starting Letter
Jun 15, 2005I want a criteris in access to get the records having field 2 starting with "num". Num is example. I want to do withdifferent letters.
number
numbiur
numg
numds
the first three letter should be num in this example. i want for different words i.e, i need to enter the paramter message box.
Letter And Query
Oct 12, 2007Hi,
I'm fairly new to Access. I have few questions.
How to set up database so it searches through my table, finds the name and inputs it into letter in a space I want?
Where does this letter suppose to be located in the file?
Thank you
IOI
Does Not Start With A Letter
Oct 15, 2007Hi All:given a table with, let's say, Job Numbers that start with a letter (such as RES2345) or just plain numbers (such as 253180001 or 9817) how can I set the criteria in the query to just give me jobs that start with a number?I first trimmed the field to eliminate blank characters and then I used the Not Like "A - Z" but it still gives me jobs with letters at the beginning of the name.thanks for all your help in advance. :D
View 1 Replies View RelatedDisplay Only One Letter
Jun 29, 2006I am trying to get the firstname to only display the first letter in a report.
I have put this format in the control source =Left([firstname],1)
When I run the report I get error in the firstname field.
Any suggestion.
Thanks
How Send The Letter
Dec 31, 2004 Hello all:
In order to set up an multi-receiver email program with ASP/AccessDB,
I downloaded “Beginners ASP mailing list program” “newsletter.zip”
After unzipped it, it contains 11 files. I put them all in the wwwroot of IIS.
I have made the address database “newsletter.mdb” successfully. The next
step is to send letter.
First using “newslettercreat.htm” to write a letter and then press “submit”.
After submitting, it prompt a IE page with address “newlettercreat.asp”, but
says: “The page cannot be displayed” and with following
information:
---------------------------------------------------
Technical Information (for support personnel)
Error Type:
Server object, ASP 0177 (0x800401F3)
Invalid class string
/newslettercreate.asp, line 22So, the letter can not be sent.
What is the ERROR of “Server object, ASP 0177 (0x800401F3) Invalid class string” ?
My OS is winXP pro, email program is Outlook Express. Who could help l me solve
this problem? Thanks!
Edit A Report Letter
Oct 26, 2005i'm trying to work with this database.
i want to know how to change a few things
we have a command button that generates a letter
i need to edit that letter as it contains names and numbers no longer in the office
is there a way i could find that letter
i tried to have sent to a .rtf file which i could modify, but that's tolerable for a dozen of recipient not 2 to 3 hundreds
thanks
Generating A Letter From MS Access
Jun 20, 2006I need to generate a pre-formatted letter from within MS Access, where the name, address and other information will be pulled from the current record on an Access Form.
Basically it is like a welcome letter I could send to any new client I enter into the data base. Since I am doing one letter at the time, mail merge in MS Word is not an option
What would be the easiest way to do this?
Thanks
Letter Storage & Retrieval
Nov 6, 2006I am not sure whether this is a problem with MS Access, Visual Basic or Windows.
I have taken over supporting & developing an MS Access 2000 DB for a small charity & am not an Access expert or a programmer. The Application includes processing to create, amend & store retrieve standard letters based on a Word document called MyMerge.doc. The operating systems is Windows XP for the PCs with a MS Server 2003.
Each letter is allocated a number ‘CallID’ which is used to retrieve the letters later. The letter text is in MessageC.
The VB code to store the letters (Save As) is
Dim strTest As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
MessageC = "%fa" & MessageE & "Db Letters" & MessageC & " " & (CStr(Forms!Contacts![PostalCode])) & " " & Trim(DLookup("[TitleType]", "Title Types", "[TitleTypeID] = Forms!Contacts![TitleTypeID]") & " " & Forms!Contacts![FirstName] & " " & Forms!Contacts![LastName])
objWord.Application.Activate
SendKeys MessageC
The VB code to retrieve the letters is
Dim MessageE As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
Dim WordApp As Word.Application
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
WordApp.Application.Activate
MessageE = "%fo" & MessageE & "Db Letters*" & CallID & "*.doc"
SendKeys MessageE
Set WordApp = Nothing
GoTo Exit_Command53_Click
The success rate varies from PC to PC and user to user. It will work with one letter and not the next. It is very difficult to identify a pattern. When the Save As does not offer the expected name & path, the Application (or operating system?) offers to save ‘MyMerge’ to the user’s My Documents folder.
In that case I tell the users to correct the path themselves and save the document under the CallID. Theoretically, retrieval should work since this uses the CallID and wild cards. Sometimes it does but often it will instead retrieve a document in the user’s My Documents folder. If it does go to the right folder, you sometimes have to replace the last wild card with ‘.doc’. I have tried replacing the last wild card in the VB code with ‘.doc’ but this does not work!
Finally, I alone get the message ‘Save failed due to out of memory or disk space’, neither of which is true. This makes it very difficult to continue investigating the problem. If I use a copy of the DB on my hard drive rather than the network I can save but not retrieve.
Form Letter Ideas
Feb 5, 2007I want to pick everyone's brain on this one, because I'm not sure what I want to do can be done very easily.
I have a database that tracks client information for insurance policies. I'm going to be giving it to other agents, many who are very computer illiterate. One of the requested functions is for them to be able to create form letters from the database.
So, I'd like some ideas on how to do that. Ideally, I'd like for there to be a form that someone could load some letter templates, but also tweak them or even create new ones that can be saved. It would also need to have "merge fields" much like you would in Microsoft Word. Basically since every agent will want to have their own letters, it needs to be easily editable.
Is there an easy way for a computer illiterate person to just select "interest letter," change some wording, and have it merge into a report? Or, is there an easy way for someone to get access to the merge fields if they were to do a Word mail merge? I don't really want people trying to find the right tables/queries amidst all the different tables in the database.
Or, throw out some other ideas. I'm open for most anything. Thanks in advance.