I have a combo box that gathers data based on a query. Is it possible to add a line of text to be displaed every time i pull down the box?
For example I have:
Data1
Data2
Data3
I would like to have:
Not In List (the added txt)
Data1
Data2
Data3
Can anyone help....
I have a field on an Access db, this is a "Memo " type field which has many lines of text within it.
I need to run a query on this to get the last line of text within this field.
Aside from records where this memo field is filled in I also have records where the memo field is blank. So basically if the memo field is blank I want a blank field returned as well as fields with data.
I am using Access 2003 but also am running this on Access 2000 (not sure if there has been a change to function names)
This is seriously bugging me - can anyone put me out of my misery?
how to set focus in first or last line, but I need set to second.
View 11 Replies View RelatedI am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code:
Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Open "C:TestTest.txt" For Input As #1
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1.
Here's the statement
Code:
sqlfinal = "SELECT Employees.ID, Employees.Name "
sqlfinal = sqlfinal & "FROM ((qryDeptVBA INNER JOIN qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID) "
sqlfinal = sqlfinal & "INNER JOIN Employees ON qryDeptVBA.ID = Employees.ID) "
sqlfinal = sqlfinal & "INNER JOIN qryAreaVBA ON Employees.ID = qryAreaVBA.EmpID "
sqlfinal = sqlfinal & "GROUP BY Employees.ID, Employees.Name;"
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
View 4 Replies View RelatedI got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
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.
Hi all, I'm new to this forum. I'm having a problem with a query I'm trying to create.
I have two tables in the database: tblCustomers and tblInstallations. These two tables are linked by a field 'CustomerID'.
CustomerID is the Primary Key in tblCustomers.
In tblInstallations, the primary key is InstID and for each CustomerID in tblInstallations there are either 1 or 3 InstID records.
Now what I want to do is create a query with fields: Customer; CustomerID; InstID1; InstID2; InstID3.
InstID1 will contain the first InstID for the CustomerID, and InstID2 & InstID3 will contain the second and third, if applicable.
I see in Query Design View in the 'Totals' line there is a option to display the 'First' and 'Last' match for a field. So is there any way i can use this method and also display the second record? If not, is there any other way to make this query work?
I hope somebody can help!!
Kerry
Is there a built in function which can be used to create line numbers in a query?
I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?
I currently have a database that has the following tables:
t_Employees
t_Cost_Centers
t_Changes
The [t_Changes] tracks what cost centers the employee is in with a start date and end date:
Change_ID
Employee_ID
Cost_Center_ID
Start_Date
End_Date
I want to run a query that will show a single line per employee, and different columns for each of the cost centers the employee was in. If the employee was in 3 different cost centers, the query would have 4 columns (1 for the employee, 1 for the first cost center, 1 for the second cost center, 3 for third cost center).
I saw another thread to linked to a concatenate example by Allen Browne, but that places multiple values in a single cell. I want different columns per cost center.
Access Database in which i download the table from an Excel file.I am looking to create a query with certain criteria but did not have any luck recently.
1) I will need the query to show <Short code> that are only unquie to Korea.That means shortcode with a count of 1 belonging to Korea only.
2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.
3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.
I have a table x where the field value is selected via a combo box in a form that is populated from another table z.
When I look in x, it appears to have correctly stored the text from z, not the ID number.
I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.
Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.
How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.
I am just querying a single table, no relationship involved with another table. As you can see form the attached jpeg, the ZIP field in some cases is empty. I would run a search using Is NULL but the field is NOT numerical. It's a long story but I had to make this field a TEXT field. Basically, what statement do I have to insert in the criteria field to just pull up the EMPTY ZIP fields?
View 2 Replies View RelatedI have two tables, and I'm trying to create a crosstab query...I think. One table is Contracts, one is Contacts. Each contract could have multiple contacts, but they are numbered (1, 2, 3...etc.) based on importance. I want to create a query that keeps each contract on one line, and separate fields for each contact and each field of the contact. So a contact will have Title, First, Last, Address, etc. So I want my query to show as follows:
Code:
Contract-----Title1-----First1-----Title2-----First2
ContractX Mr. James Mrs. Sally
I of course need this to be dynamic, so if a contract has 9 contacts, there are fields up to Title9, First9, etc.
Produce a query that returns a yes or no (Y or N) field if other text field contains particular text (with wildcard on either side).
Idea is to have a column for each preset word.
According to the books and examples I should get multiple lines of text in my text box with...
messageBody = "Line one stuff" & Chr(10) & "next line stuff"
Forms!NotifyEmails!Text2 = messageBody
...but instead I get funny symbols where the line feed should be. But it works sending it to email.
Anyone shed any light on this behaviour?
how to populate a textbox with a line break?
=[Forms]![Violations]![cboSection4] & i want the line break here & [Forms]![Violations]![cboSection5]
I want to open a txt file through my access database and append a line to it. When I use the opentextfile method for appending but it always appends my new text line to the end of the last line. I want it to append the new text to a new line. I have tried object.skipline but this is not supported when appending?
This is what I get:
"oldline" "newline"
but I want:
"oldline"
"newline"
Here is my code
sub ConcatenateTxtFile ()
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fd.OpenTextFile(dpath, ForAppending)
'Need a line here to skip to next line
f.writeline ("newline")
End Sub
I have a database that prints out a contract of line items (as a report) for the home remodeling business.
I use a query to limit the line items which show the description and cost as a subreport.
The only way I could create a total of these line items was to enter an unbound text control, and put in the formula =sum([cost]) where cost being the field name. It works fine, but I need to but the result in a currency format - - two decimals and a $ sign. I sure would like it to do it at the query level, instead of at the report level (will make it more user friendly)
Any suggestions?
how to make a line break in a rich textbox in access 2007? Chr(13) & Chr(10) worked until I changed the textbox to rich, which is necessary because certain words in the textbox need to be bold.
View 2 Replies View RelatedHi All,
I'm a noobie with Access, and on my form which displays Account information, I also want to display the multiple Addresses this account can have. I can't find any type of scrollable text box (something to look like a spreadsheet), and I know it's not a list box I need to use.
How do I create this and what is it called? I tried using a data grid but I dont think that is it either :confused:
Also, my scrolling text icon on the toolbox is greyed out, not sure why.
Please help!
Thanks,
Mike
I am creating a database and need to produce a report that will form an itemised quotation.There can be up to 8 items on the quotation along with their associated costs to the customer. I am trying to make the "item" field dynamically expand if necessary when longer text is entered into it and then expand the corresponding "Totals" fields to match the new size.I have tried using the "Can Grow" property however for some reason the formatting does not cascade down to the fields below and the "Totals" fields remain the same size.
To try and explain better I have included a screen shot of the relevant part of the report and how it looks when printed. I should point out that the "QuotesQuote" box at the top of the screen is set to "Can Grow" and when that expands the boxes below automatically move to accommodate this.
Is there an equivalent to Alt-Enter in excel to put a line break in a text box from the keyboard?
View 2 Replies View RelatedI have a textbox on continues form which contains multiple lines and I d like to set it up so it ll be scrolled down completely when it displays the form. I ve already found a few so called solutions but nothing worked for me.
View 5 Replies View RelatedI have a function which takes a recordset and writes the contents to a text file. One of the fields in the recordset is a free format text field, the contents of which originate from a text box on a form. Occasionally, a user can hit the 'Enter' key to add a new line (carriage return) to the note within the text box.
The problem is, when the output text file is subsequently imported to Excel, the carriage returns within that text field serve to move the next piece of data to the next row (even though the CR is within the double-quote text-qualifier in the text file)I want line breaks within the text field to be retained within the cell.
One option would very simply be to replace the carriage returns with simple spaces in the write function. I could do that quite easily. However, I would prefer to retain whatever the user has entered into the text box, including line breaks, if at all possible?So if the user broke his free format 'note' into multiple lines (for whatever reason), it would similarly appear as multiple lines in the same cell (and in the same row / record) in the Excel worksheet.
I could replace the carriage return with some obscure character during the write, then switch it back during the Excel import, but that seems a little clunky (plus Murphy's Law dictates that no matter how obscure a character I choose, somebody, at some point, will genuinely use it, and it will get switched out. Which means I'd have to use a non-printing character?could I simply replace the carriage return with a line feed? (Or is it the other way around? I always get carriage returns and line feeds mixed up!)there's a simple way around this but I always get confused as to precisely which ASCII character the 'Enter' key produces when keying into a text box.