Concatenating First 3 Records Then Stops
May 6, 2015
I'm trying to concatenate several fields from a set of tables/subtables. But for some reason, it concatenates the first 3 records, then stops. I should clarify that the concatenation "skips" a subtable. Here's the structure of the relationships (not sure if this pic is working so I'm adding the link to the screen shot)
[URL] ....
and the concatenation itself
As you can see, I'm looking to create a concatenation of (Vendor_Code)&(Vendor_Item_Code)&(Item_ID) which runs through the subtable Items_Child, but doesn't use any field in Items_Child
View Replies
ADVERTISEMENT
Jul 13, 2005
We use two different systems in our office that have notebook facilities. One of them stores only a limited number of characters in the note field per record and as such, some notes actually span several records.
The other system stores the whole note in one record.
We want to transfer all the notes from the first system (1 note over several records) to the second system (1 whole note in 1 record).
I have a table with the notes from the first sytem which looks something like
Note1 - Line1 - Text
Note1 - Line2 - Text
Note2 - Line1 - Text
Note3 - Line1 - Text
Note3 - Line2 - Text
Note3 - Line3 - Text
What I want to do is, for each note, combine the separate lines into one record.
I have been able to do this in Excel with some formulas (see attachment) but am wondering if it is possible to do this in an Access Query (i.e. GroupBy the note number and have a calculated field combining the individual lines of the note, or something similar)?
View 8 Replies
View Related
Feb 14, 2005
This has been annoying me to now end. I have a master form with 3 text boxes, and 3 subforms with about 26 more text boxes.
How can I make the tab stops flow from one subform to another? It's like the tab stops won't get "linked up" by virtue of them being on the same master form.
View 1 Replies
View Related
Nov 17, 2006
just wondering why randomly certain modules in the form code stop working..
when you accidentally press return.. or do something in the wrong order.. or apply one rule to one control.. and then another control rule stops working..
so you have to delete the code.. right click on the control's event.. and re-insert the code into the event section.. and then it works..
why?
View 4 Replies
View Related
Oct 19, 2004
I have written a very simple query which get's it's criteria from a form I also created, which couldn't be any simpler itself. The form simply has a text box that I enter a value into, in this case, the date in a specific format "20041019". The query then pulls all records according to the form value. This query has always worked fine for weeks now. Today, suddenly it would not return any records. If I go into the SQL and manually enter "20041019" it will pull the appropriate records. It will NOT function when relying on the form value though. This has happened to me in the past, of course I did not document what I did to overcome the obstacle though. I have tried matchign the formats of the field in the table to the text box in the form so that they are both general numbers with 0 decimal places, originally they were both text format and it worked. I can't seem to figure out why this would happen. If anyone has any suggestions, I would GREATLY appreciate it.
Thanks,
Tre
View 1 Replies
View Related
Jan 6, 2015
I have a database with a table and 2 forms.One form (frmList) is a list of all records in the database, and the other form (frmInput) displays fields for a single record.There is a button in frmList which runs the following script. The user clicks a record in frmList, clicks the button, and the selected record opens in frmInput. ID is a text field containing digits and an alpha that is in both frmList and frmInput.
Code:
Private Sub Command9_Click()
Dim ID As String
strCriteria = "ID = '" & Me.ID.Value & "'"
DoCmd.OpenForm "frmInput", acNormal
Form_frmInput.Recordset.FindFirst strCriteria
End Sub
This script works as expected in Access 2010. However we recently upgraded to Access 2013 and since then, this script causes Access to crash ("Access has stopped working..." dialog appears).I have stepped through the script and the line causing the crash is
Code:
Form_frmInput.Recordset.FindFirst strCriteria
I can't find any problems with syntax or anything about Recordset.Findfirst being deprecated in 2013 so I suspected some kind of file corruption. To resolve this I have tried: Compacting and repairing the databaseDecompiling and recompiling the VBA modulesCreating a fresh database and copying all the objects into it
could this be due to a missing library or something? Are there other methods of resolving a corrupt database if that is the problem? Or as a last resort is there an alternative method to Recordset.FindFirst that I can use to open a form to a particular record while retaining the ability to navigate through other records?
View 9 Replies
View Related
Feb 7, 2015
I have a question about errors on null value.I have made a small database for tryout, it has to be implemented in another one.And the small database is working.I have one table where there is one field called BatchInput.I scan a barcode into it and let two query's breaking it apart. I scan this batch into the table field
BatchInput: 20 MAY 2004H149-082-79 A4147011A05
Then I have my first query (Qrybreak1) extracting the date and deleting H14
Date: Left([BatchInput];11)
PartCertNr: Right([BatchInput];Len([BatchInput])-14)
The result is this:
Field date: 20 MAY 2004
Field PartCertNr: 9-082-79 A4147011A05
The second query (Qrybreak2) I look for the first space:
space: InStr([PartCertNr];" ")
Then with the result I cut it into two pieces
PartNumber: Trim(Left([PartCertNr];[space]-1))
CertNumber: Trim(Right([PartCertNr];[space]+2))
[code]...
And query (QryResult) even wont start, giving a popup with Invalid procedure call..How could I handle Null on the part where there is no space after the partnumber (missing Certnumber)?
View 7 Replies
View Related
Apr 13, 2013
I'm using a combo box to pull data from a table to auto populate the fields on my form. But for some reason, it stops inserting the data after city (column(6) and I can't figure out what's going on. When I run the query the data is there, if I change the order of the columns, the data shows. Here's the code:
Me.txtadjusterfname.Value = Me.cboAdjusterlkp.Column(0)
Me.txtadjusterlname.Value = Me.cboAdjusterlkp.Column(1)
Me.txtadjustertitle.Value = Me.cboAdjusterlkp.Column(2)
Me.txtadjustertype.Value = Me.cboAdjusterlkp.Column(3)
Me.txtadjustercompany.Value = Me.cboAdjusterlkp.Column(4)
Me.txtadjusteraddress.Value = Me.cboAdjusterlkp.Column(5)
Me.txtadjustercity.Value = Me.cboAdjusterlkp.Column(6)
Me.txtadjusterstate.Value = Me.cboAdjusterlkp.Column(7)
View 1 Replies
View Related
Oct 10, 2011
I'm using an Access 2010 report to recreate a spreadsheet that contains rows consisting of fourteen columns of data. Each column is totaled. The data types are numeric and the property sheet format is either Standard or Percent depdening on the column.
In order to track the totals, I utilize two text boxes for each column. One is visible and contains the data for that particular row. The other is hidden and has its Running Sum property set to "Over All". The final totals row consists of text boxes that reference the final result of the running sum text box.
This works up to a point. I see correct totals results for columns 1-10. Then for some reason the running count appears to stop for the second to last row of column 11. (It's always the second to last row). As a result, the totals for this column (11) and subsequent columns (12-14) do not total.
View 14 Replies
View Related
Nov 12, 2014
I'me running on Win7, Office 2010, Acc2003 format (.mdb), writing data to Excel 2010 (.xlsx)I have code to create an Excel file, send record set data to it, and then add some formulas and formatting. I was trying to tune up the formulas, when I got a pull back:Access VBA code suddenly stops running, and the file gets displayed in Excel
Code:
'ApXl is Application.Excel
'xlWSh is the Excel WorkSheet
'rst is my DAO.Recorset
[code]...
The same happens when I have ApXl.Visible = True
View 10 Replies
View Related
Oct 11, 2005
I have a firstname field, and a surname field.
On a report I have a textbox that is bound to =[firstname] & " " & [surname]
This gives me an error. I have even tried single quotes, but no luck.
Please advise
Thank you in advance
View 2 Replies
View Related
May 28, 2007
Hi All:
, I have two fields to concatenate.
SKU = ps1-
Supp_sku = 98756
I want to have it like ps1-98756.
I have used the following query to test the result -
select sku & “ “ & supp_sku as [my_sku] from inventory_master;
The result is
My_sku = 198756
I do not get ps1-98756.
Could you please help?
Thanks - Prabir
View 9 Replies
View Related
Jun 11, 2006
G'day,
I want to produce some UPCa bar codes which require 11 digits of TEXT for the barcode function to work.
I want the bar code to be made up of several fields
Make 3 Chars (Text) (eg 021)
Model 3 Chars (Text) (eg 103)
Serial_Number 5 Characters (Numeric) (eg 00025)
(I need to keep the Serial_Number field numeric so that it increments in modulo 10 format (ie get Numeric only results))
So i need to produce a field that looks like this 02110300025 after the 3 fields are concatenated
However I am getting 02110325
I'm sure that the answer is simple but I seem to be having a mental block. Does anyone have any ideas? Your help will be very appreciated.
View 5 Replies
View Related
Jan 9, 2005
I am trying to create a basic query that adds four numbers, but when I try '[Mark1]+[Mark2]+[Mark3]+[Mark4]' it concatenates it instead of adding.
e.g. I have '58+96+13+45' and I get '58961345' instead of 212.
View 1 Replies
View Related
Sep 15, 2005
Hi folks,
New person to the forums here. I've had a scroll through the FAQs and can't see an answer to a problem I need to solve. I wonder if there's anyone could offer some help?
My db has three tables: table_a, table_b and table_c. Each table has the following fields:
table_a
a_id (PK)
a
table_b
b_id (PK)
b
table_c
c_id (PK)
a_id (FK (many to one))
b_id (FK (many to one))
c
I would like field c in table c to be a concatenation [table_a].[a]+"-"+[table_b].[b]. I'd like this field to automatically generate and be constrained as unique. Creation of value c in table_c should be by selection of value a & then value b in a form based on table_c.
Can anyone advise me how I do this?
By the way - editing to add this - I am using MS Access 2000 & can't upgrade to a later version.
View 8 Replies
View Related
Mar 21, 2007
I am using this as a criteria for a Query that is pulling the 2 dates from a form - Report Date Range.
The reason I am trying to do this is I have a cumulative report that needs to run for the fiscal year (1/1/xx to 12/31/xx). It will always use 1/1/xx as the base date for the range. I am trying to pick up the year because it will change depending on the periods/years picked.
Between #1/1/ & Year ([Forms]![frmReportDateRange]![txtBeginningDate]) & # And [Forms]![frmReportDateRange]![txtEndDate]
When I run the query I get "The expression you entered has an invalid date value."
I am trying to concatenate the 1/1/ with the year and obviously that is not working. Any ideas?
Thanks in advance!
View 2 Replies
View Related
Nov 25, 2005
I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.
So for example
Column1 Column 2 Column 3
B11 Europe
would concatenate as:
B11.Europe.
View 1 Replies
View Related
Dec 31, 2004
Hi all,
Ive got a table called tblClientCar, this is its structure:
ClientCarID
ClientID
CarID
I want to use an append query to concatenate ClientID and CarID together and putting the concatenated value into ClientCarID. How would i do this?
Ive tried using a form a two combos so when i select the clientID from the 1st combo and then the CarID from the 2nd combo, then run the append query to write the concatenated value into ClientCarID of the table. But it says writing 0 rows to table and the ClientCarID isnt concatenated when i check the table. This is the query sql syntax i was using:
INSERT INTO tblClientCar ( ClientCarID, ClientID, CarID )
SELECT tblClientCar.ClientCarID, tblClientCar.ClientID, tblClientCar.CarID
FROM tblClientCar
WHERE (((tblClientCar.ClientCarID)=[Forms]![frmClientCar].[Combo6] & "" & [Forms]![frmClientCar].[Combo8]));
I also tried another way. The 2 combos add the selected values into ClientID and CarID in the table tblCar. So i made the append query slightly different to concatenate ClientID and CarID to form ClientCarID. This is my query sql syntax:
INSERT INTO tblClientCar ( ClientCarID, ClientID, CarID )
SELECT tblClientCar.ClientCarID, tblClientCar.ClientID, tblClientCar.CarID
FROM tblClientCar
WHERE (((tblClientCar.ClientCarID)=[ClientID] & "" & [CarID]));
I get the same result as the 1st query. What am i doing wrong?
Is there another way of doing what im trying to do? Whats the correct sql syntax for doing this?
Thanks
Jim
View 8 Replies
View Related
Jun 4, 2006
In a query, I'm using concatenation to join multiple address fields (organisation, building number, street address, etc) into one field. I'm also inserting line breaks to create a formatted address and then creating a form on the query so the formatted address appears in a text box and can be copied and pasted straight into my label-writer software to print address labels.
This is the concatenation code I'm using:
Expr1: [Organisation] & Chr(13) & Chr(10) & [Building Number] & Chr(13) & Chr(10) etc.
However, one problem with which I am now presented is where a field is blank. E.g. if one of my contacts is retired, then they don't have an organisation listed in the "Organisation" field. This means that a blank line is left between their name and the rest of the address.
When running a mailmerge there is the option of skipping blank fields so that a blank line is not inserted in such cases.
How can I achieve this within the above code?
Gary
View 6 Replies
View Related
Sep 20, 2006
I have a query that is set up to join two name fields...for example:
[ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]
The issue is that this is perfect for names that are entered in the table like this:
Lstnam: ABC HOSP Fstnam: ITAL
They are entered like that for groups, but when individual doctors are entered, the data entry folks are entering them like this:
Lstnam: SMITH Fstnam: BOB Titlcd: MD
So when I join them the first displays correctly as ABC HOSPITAL, the second displays as SMITHBOBMD and I have to manually go in and add a comma.
Is there a way to do an if/then statement or something to tell the query to display results If the titlecd is NOT NULL, display as [ELIGVENDORS.FSTNAM] & [ELIGVENDORS.LSTNAM]&", "&[ELIGVENDORS.TITLCD], else display as [ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]
I'm sure there is, I am new to IF/THEN statments though and was hoping to get some help setting it up.
Thanks in advance and I hope my description is clear enough.
Han
View 2 Replies
View Related
Dec 26, 2006
Hello,
i am trying to join the results of a many-side of a query into one result field separated by commas...
Table: tblWorkOrderStoresFinal
Row 1: LogNumber: 1StoreNumber: 2
Row 2: LogNumber: 1StoreNumber: 3
Row 3: LogNumber: 1StoreNumber: 4
I would like to make a query that combines the StoreNumber into one field CombineStoreNumbers separated by commas as follows...
LogNumber: 1 CombineStoreNumbers: 2, 3, 4
I tried the following crosstab query, but it's not quite right.
TRANSFORM StoreNumber
SELECT LogNumber
FROM tblWorkOrderStoresFinal
WHERE (((LogNumber)=1))
GROUP BY LogNumber
PIVOT StoreNumber;
Any ideas? Hope this makes sense.
Thanks, Casey
View 7 Replies
View Related
Sep 3, 2013
There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.
Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)
I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.
So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.
Also, I have created the string, called "emailsList"
Code:
Private Sub Email_Button_Click()
'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home"));
Dim emailsList As String
DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True
End Sub
Do I need to do a loop that concatenates?
View 11 Replies
View Related
Jan 31, 2014
I have a query that I want to concatenate some of the results onto a single row, but I'm having trouble.I've followed Allen Browne's example, but when I run the query, I either get the #Type, #Name or a 3201 error (I believe, I can't recreate it now).
View 4 Replies
View Related
Apr 1, 2015
I have five reason fields in my table and I would like combine them into one field on my report, with line breaks between so each reason starts on it's on line. Also if there is a blank record, I would like the report not to display a blank line. I have found ways to do this online for 2 records but I need this for 5.
Also when I do manage to get the lines all to show in the report there are 3 paragraph returns between them - making the reason display of the report 15 lines tall, not just 5. The code I have so far, which displays each of the reasons but spaced as described is:
=[REASON1] & " " & [REASON2] & " " & [REASON3] & " " & [REASON4] & " " & [REASON5]
View 5 Replies
View Related
May 14, 2013
I am trying to concatenate 2 unique values which are parts of 2 identical rows(records) in order to have only 1 row(record) with concatenated field where the values are unique. For illustration here is what I need to achieve :
Initial table:
Fld1 Fld2 Fld3 Fld4
AAA XXX BBB CCC
AAA YYY BBB CCC
Final Table:
Fld1_____Fld2______Fld3__ Fld4
AAA____XXX,YYY___BBB___CCC
View 1 Replies
View Related
Jun 28, 2005
Hi
I did a search to no avail...
I am trying to concatenate Address fields into one text box. The Control Source pull-down menu gives me the Field Names listed as QueryName.ColumnName.
I know that if they listed the fields as just ColumnName, you'd simply go:
ColumnName & " " & ColumnName2...
But the way these columns are defined, I cant find the correct syntax.
Is there a way to do this without changing all my queries??
Thanks again! :)
Mike
View 8 Replies
View Related
