Modules & VBA :: Concatenate Data In One Cell In A Report / Query
Mar 27, 2014
Watch the below tables.
I have a large ACCESS list as per the left one and I would like to make it as per right.
The problem is that I want to concatenate the "invoices" in one record separated with a coma , .
This is the only way I can compact the long list into a short one and make it comprehensible.
The invoices , in one cell, very rarely are more than 4 or 5 items.
So room for 6 is just perfect.
View Replies
ADVERTISEMENT
Mar 8, 2014
i need to Concatenate a fields data into one cell in a query according to linked table ID....
View 3 Replies
View Related
Feb 25, 2013
I have 2 gig of order data which has duplicates due to different invoice dates. How can I combine the dates into one cell so I only had one row of Order data.
i.e. order number, price, order date, invoice date
same order number, same price, same order date, different invoice date.
How do I combine the second line with the first so that I only have the data once.
View 10 Replies
View Related
Nov 4, 2013
I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.
Value 1:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs, community residents
Value 2:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs and community residents
Function:
Option Compare Database
Option Explicit
Public Function ConcatField(strSQL As String) As String
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strConcat As String
[Code] ....
View 2 Replies
View Related
Apr 15, 2014
I've got a piece of VBA scripting which runs as an event linked to a button on my MS Access form.
I maintain a database of members of staff at my organisation. It's pretty outdated...
I'm basically wanting to pull in their updated data (extracted from on our payroll system) from a spreadsheet, into a form, when clicking a button on a particular person's record.
This is what I have so far.
Code:
start = Forms!frm_main2!txt_start_row.Value - 1
conv = DDEInitiate("EXCEL", "Staff List.xlsx")
cell_employee_number = "R" & start & "C1"
cell_surname = "R" & start & "C2"
[Code] ....
The function "CStr(DDERequest())" converts the cell number into the readable data, however I seem to have whitespace below the value.
What would I need to do to strip out this whitespace? Would I use strtrim? If so, I am unsure of the syntax... how would I incorporate strtrim into the above?
View 7 Replies
View Related
May 1, 2014
I'm having a rough time trying to figure out how to pass a date to an SQL statement that Excel VBA macro will run. The date is in a cell (A1) formatted as 'm/d/yyyy'. Let's say it's 2/1/2014. I want to run an SQL statement that retrieves data from a table where a field is greater than 'A1'. The table field is a date/time field and has values formatted as 'mm/dd/yyyy'.
I've tried various syntax on the Where but cannot get it to work.
sd = Range("A1")
SELECT [tn].[Date Submitted]
FROM[tn]
WHERE tn.[Date Submitted] > """ & sd & """
This results in the following where clause that does not work.
WHERE tn.[Date Submitted] > "2/1/2014"
View 4 Replies
View Related
Dec 15, 2012
I'm looking for a way to summarize multiple fields of data from the same column in a single field. I've seen this page but have been unable to get it to work. I save the code listed but do not know which of the example fields would relate to my table.
Code:
SELECT CompanyName, ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
FROM tblCompany;
I saw that I can create a report and select "group by," but then all the columns appear on different lines rather than summarized on one line. Here is an example of how I would like the data to appear, separated by commas or with specific dates within parentheses. The reason I want them all on one line is to provide an easier to read/more succinct summary.
e.g.
ITEM
LOCATION
DATE
GORP
EAST
1/1/2012
GORP
EAST
2/1/2012
[code]...
View 7 Replies
View Related
Apr 27, 2013
I have two address fields that I want to tack together due to space contraints.
=([add1] & " " & [add2])
Seems to work in a query but not report?
View 3 Replies
View Related
Nov 16, 2013
I have a table:
"tb_skybox_Types", with the fields, [Policy_URN], [Box_Type]
Sample:
Policy_URN Box_Type
001 Standard
001 Sky HD
002 Sky +
002 Sky + HD
I'm mail merging this, so naturally it's giving me a row per box type against which I need to be one. Desired output:
Policy_URN Box_Type
001 Standard | Sky HD
002 Sky + | Sky + HD
I'm then going to make this into a temp table, then match the URN back to the customer table to get a single row per customer.
View 3 Replies
View Related
Aug 4, 2013
I have a table, "TBL_Email", which simply contains a list of email addresses in a field called "email".
I would like to concatenate all of them together into one string, and add semi-colons to the end e.g.
"123@abc.com;456@def.com;789@hij.com"
I believe a may need a record set, but i'm not entirely sure how they work.
View 4 Replies
View Related
May 15, 2013
I currently have an access database for tracking company assets and performing maintenance tasks on them.We also have a number of Stores Stock Items that are controlled under a separate finance package.We currently have forms that create individually numbered "Job Cards" in the Job Cards Table..we input the data and manually type into a Text Box the we have used 'X' number of Store Stock 'Item Y'
I have then use a query to export the data i need (Job number, dates, location and Store Stock Items).But I have to manually re-construct the data and items into separate lines in an excel sheet for a paste operation into the finance package (lots of typing of 16 digit stock codes)
What i want is to use a second table (linked via the "Job Card#" key).It will be called "Stock Items" which has a separate field for each of our stock items.the user then just opens second form while creating the job that immediately creates a new record.The user then simply has to put the number of each item used in the relevant text box on the form and close.
example of datasheet view
Job# __ ItemX __ ItemY
2417_____3_______7
Now, I have all of that working as I want, But i nee to be able to combine the data from each field into a single field (either in access or excel).What I want to see is a field containing 3x ItemX, 7x ItemY, etc
if there are none of one or more items, I don't want anything displayed for that item (blank space is ok)I have 30 or more stock items, so there will be 30 or more columns in the table. the actual data stored in the table is simply the quantity of each item used.
this data is used twofold,on export to Excel I can split the data into new cells and it's used on a despatch note that is returned with the item so the end user can see the items that were used in the repair.So it needs to be done upon saving the record, record by record.
View 5 Replies
View Related
Jul 11, 2014
I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want
Code:
Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount
[Code] .....
I am trying to achieve something that reads like the following
r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND
.....
View 2 Replies
View Related
Feb 20, 2005
Hi,
Is there any way that i could extract data from a single cell inside the table to the forms, and vice-versa?
View 1 Replies
View Related
Oct 24, 2012
I have a form that is based on a query that returns all the students taught by an indidual faculty member. There are two text boxes which I have added to the form bound to a table used in the query. However when I try to add data (attendance data) is doesn't accept it and simply "pings" each time I try.
View 3 Replies
View Related
Sep 29, 2014
I'm using conditional formatting on a datasheet to change the background colour of a cell. Is is possible to retrieve that colour?
View 6 Replies
View Related
Oct 23, 2007
I have entered one entry in to the database with the correct values (this is record 6) and everytime I enter a new record, the data is the same (I'm using a drop down list) it will automatically appear in the box. But when I goto the table that the data is stored in, each field just has the reference to the first record that the data came from.
Looks a bit like this:
ID Server Name Pattern Engine Program
6 LN1HOME 4.735.00 8.500-1002 5.58.0.1185
7 LN1WORK2 6 6 6
Is there a way of seeing the origonal data in those fields other than the Record ID Number as a reference. :confused:
I know its a lame question, Im just is a learning process at the moment.
thanks in advance.
View 2 Replies
View Related
Apr 4, 2015
I've got a database of club members with names, addresses, contact info etc.
I need to send letters by post to those members that I don't have email addresses for. How to create a query to return a list of people whom I don't have email addresses for.
I know how to mail merge the info with the letter I've written in word, how to group people who's email address cell is empty.
View 1 Replies
View Related
Feb 24, 2014
I have a form and there is a command button on it. I want the code that will run when that commandbutton is pressed and it should check the first sheet "Sheet1" in all the workbooks in the folder C:FolderTemp.
And if the first sheet name is not "sheet1" then just ignore that workbook and move to next workbooks. If the first sheet name is "Sheet1" in a workbook then display the value present in cell B1 in a message box.
So if there are 5 workbooks in the folder C:FolderTemp and two of them hasn't got first sheet named "Sheet1" then display value of B1 in rest of the 3 sheets in a message box one by one.
View 2 Replies
View Related
Aug 26, 2014
I am trying to write some vba code to auto fill a cell's value based on the value of the cell in the previous record (In a datasheet form) i.e. if the value in record one was '1' and the down-arrow key was pressed then the value '2' should be entered into the new ext record
I have written some pseudo-code to show what I am trying to accomplish:
if keydown = down-arrow and current cell contents isnumeric then
Cval = current cell contents
if current record = last record then
create new record
move down 1 record
set cell value of new record to cval+1
else
move down 1 record
if cell value = null then set cell value of record to cval+1
end if
end if
View 14 Replies
View Related
Sep 21, 2011
I have written a query to concat two strings,
select concat([first_name] & ', ' & [last_name]) as [Employee Name] from [employee_details]
THe above query is returning the result correctly only if both first_name & last_name fields are not empty. If the fields are empty, it is dispalyingcomma (,).
View 1 Replies
View Related
Jun 26, 2014
I have the following code to export a query into a excel file:
Code:
Dim outputFileName As String
outputFileName = "C:AccountSpreadsheet est.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_Search_Invoices", outputFileName, True
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open "C:AccountSpreadsheet est.xls", True, False
Set xlApp = Nothing
This works almost exactly how I want it to work.
The only thing wrong is that the columns are all the same width and they are all to narrow.
Is there a way to make the columns automatically become the width of the longest text within them (exactly what happens when you double click on the side of a squashed column in excel)?
View 5 Replies
View Related
Nov 8, 2013
I am creating an Electronic Medical Records database (well, a fake one, for medical simulation purposes). The way it works, you select a patient from a drop-down on the "Selection" form, and click a command button to bring you to a "Patient information" report. From that report you can view EKG's , Lab results or Imaging reports.This database is to be used for medical simulation for medical students. So for i have command buttons for each report, and a check box to "order tests".
When a test is ordered , i have the command buttons linked to go to a different report that appears the same except it has a subreport with the "new" test results in it. If this were an actual hospital , results would not be instantaneous, so i have the sleep api in for the "on load" for the reports with the "new" results.
I would like it so I could enter how long of a delay i want for the loading of a particular report in the dataentry form i have. But I am not the best at VBA, and I cannot seem to figure out how to get the sleep api to get the value for the number of seconds i want to wait from my table.
View 7 Replies
View Related
Jul 9, 2013
I have subform (Datasheet) is there some code e.g. after update, which can adjust size cell to lenght of data?
View 11 Replies
View Related
Jan 21, 2013
I use a Access database to import data from an email generated from google docs..When the data imports to the table it creates a "Memo Field" with several lines of carriage seperated data containing the Form Field name & answer. (ie: Firstname=John, Surname=smith, etc)Is there any way to seperate this data in the cell into seperated cells in a new record?
View 1 Replies
View Related
Jul 1, 2013
I have a table Pmt in Ms Access with following information:
Table Pmt
Cust no Inv no T no Date Amt
123 ABC T1 20110131 1000
123 ABC T2 20130228 1000
123 ABC T3 20130331 1000
789 XYZ A1 20130131 2000
789 XYZ A2 20130228 2000
Fields "Cust no", "Inv no" and "T no" are in Text format, whereas "Date" and "Amt" are in Number.
My question: how can I concatenate row values using a query. The query results that I wanted are as follows:
Cust no Inv no T no Date Amt
123 ABC T1, T2, T3 20130131, 20130228, 20130331 3000
789 XYZ A1, A2 20130131, 20130228 4000
View 2 Replies
View Related
Jan 1, 2013
How do I concatenate a row in a query and put in a text box in a form? I have a Query returning up to 10 dates the row is called [Date] I need to put them all together with a space and a comma between them and place that in a text box on a form.
View 2 Replies
View Related
