Modules & VBA :: How To Delimit Column Into Several Columns In Access
Nov 24, 2014
I have an imported table and within this table contains a column that needs to be further delimited. I've read that it is possible to delimit columns using the left, right and mid functions, however, I need to delimit it to more than 3 columns. Maybe an estimated 6.
Examples of the data contained in the column are as follow:
ITM~W01GGASPAPP1B:W6400~12.34.56.78~~W01GGASPAPP1B ~W01GGASPAPP1B~ACK~
ITM~a01gibapp3a:AC900~12.34.56.78~~a01gibapp3a~a01 gibapp3a~ACK~
TEC~~01.234.567.89~~~~ACK~
I need to delimit it by "~". It must also be similar to Excel's text-to-column feature whereby if there're no values between 2 "~", it will be recorded as null.
The main thing I require from each record in the column is contained within the first and second "~" (even if it's null).
View Replies
ADVERTISEMENT
Feb 1, 2005
HELP :confused:
Hi,
I need to copy 5 columns in an Access table into a single column in Excel. How can I do this?
Pictures To Help explain below:
http://uploads.savefile.com/users/uploads/1_154.jpg 261kb
http://uploads.savefile.com/users/uploads/2_154.jpg 192kb
View 4 Replies
View Related
Dec 23, 2012
How would I be able to separarte this text STRANG,JENNIFER N either on a table, query, or on a form.
View 3 Replies
View Related
Aug 8, 2013
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
View 2 Replies
View Related
Jan 12, 2015
I have the below SQL statement... In table2 there is another field called timestamp1... Is it possible to have the timestamp1 included in the below statement so that I will have a record of time the moment the records were inserted in table2? There is no timestamp1 field in table1.
Code:
strSQL = "Insert Into Table2(Business_Unit, Account) Select Business_Unit, Account From Table1"
View 1 Replies
View Related
Oct 22, 2014
I have a listbox set to Multiselect property of Simple. The listbox is populated by using a table. There are 4 columns in the listbox
Code:
1 3/23/2014 4/5/2014 2014
2 4/6/2014 4/19/2014 2014
3 4/20/2014 5/3/2014 2014
The columns are PayPeriod, StartDate, EndDate, FiscalYear
What I want to be able to do is highlight a chunk of dates and have the first selected StartDate and the last selected EndDate populate two hidden text boxes so I can use them for my queries/reports.
I've tried a couple different ways. Each time what happens is it only uses the last item I have selected in it's calculations.
Code:
Dim ItemIndex As Variant
For Each ItemIndex In Me.lstPayPeriods.ItemsSelected
If Me.lstPayPeriods.Selected(ItemIndex) And Me.lstPayPeriods.Selected(ItemIndex - 1) = False Then
Date1.SetFocus
Date1.Text = Me.lstPayPeriods.Column(2, Me.lstPayPeriods.ListIndex)
End If
Next
In this example I tried to have it go through each Item of the listbox. I wanted to check to see if the current row was selected and the row before it wasn't. That way I could determine it was the first item selected in the group of selected items. It would always only use the last item I had selected.
Code:
Dim CurrentRow As Integer
Dim FirstDate As Date
For CurrentRow = 0 To Me.lstPayPeriods.ListCount - 1
If Me.lstPayPeriods.Selected(CurrentRow) Then
Date2.SetFocus
[Code] ....
I tried to do something similar with this code. Again, it only uses the last item I have selected.
View 2 Replies
View Related
Aug 2, 2013
I'm trying to import a text file with 273 fields into two tables. I've been able to do this with the code I found on an old thread and I'm now trying to accomplish everything with one step. The file I'm importing is tab delimited text file. With this current code I'm only able to populate the first record in the table and then I get error message. (Run-time error '3265') (Item cannot be found in the collection corresponding to the reqested name or ordinal).
Code:
Public Sub ImportTextFile()
' to use the ADODB.Recordset, be sure you have a reference set to ADO
Dim rst As ADODb.Recordset
Dim rst2 As ADODb.Recordset
Dim strFile As String
Dim strInput As String
Dim varSplit As Variant
Dim intCount As Integer
[code]...
View 4 Replies
View Related
Feb 13, 2014
I have an append query. I want to get a column that is the sum of 3 other columns:
Oil_T_C_(bpd) Gas_T_C_(MMcfd) LNG_T_C_(Tonnes_per_Annum)
1000 2000 3000
I want to get a column called Total_Throughput
What is the exact syntax that I have to put into the query design form?
so: Total_Throughput: than what?
View 14 Replies
View Related
Jun 30, 2015
Currently I am having a table in which the data is in 1 column, but this needs to be in 2 columns. How to do? I have:
VendorCode VendorName Quality MaxClaim
411411 SCA PACKAGING MUNKSUND 1001 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1200 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1300 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1400 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1500 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1600 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1700 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1001 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1200 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1300 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1400 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1500 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1600 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1700 FSC Mix 90%
But what I want to have is:
VendorCode VendorName Quality MaxClaim MaxClaim
411411 SCA PACKAGING MUNKSUND 1001 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1200 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1300 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1400 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1500 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1600 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1700 FSC Controlled Wood FSC Mix 90%
I looked everywhere, but the crosstab function is not the one, as that function will put all other data in separate columns, whilst I only want to have 2 column MaxClaim.
View 3 Replies
View Related
Apr 5, 2015
I have 1 combo box and 1 text box i look up 2 columns in the combo box from that combo box i want to look up 2 column to text box
example:
Table values:
Col 1 Col 2
A 1
A 2
combo box successfully look up 2 columns but i look up to text box
Formula: =combo1.column(1)
But the text box look up the first row always even i choose the second row A
Also look-up first row 1
Any solution to look up 2nd row?
View 3 Replies
View Related
Mar 25, 2006
Hello.
I don't quite know where to ask this...
i didn't think things through when i initially created the DB, and created a column called dateOfBirth that holds user entered dates as dd/mm/yyyy.
This causes many query issues, and I would LOVE to have this changed.
Is there a script i can run that will take dateOfBirth, determine the month, write the month as Jan/Feb/Mar/... in a column named dobMonth, and take the day value from dateOfBirth (1-31) to a column called dobDay?
BEFORE:
dateOfBirth |
23/10/00 |
After
dateOfBirth | dobDay | dobMonth|
23/10/00 | 23 | Oct |
I don't care about the "year" data, and I already have the dobDay and dobMonth columns set up...
Thanks.
View 10 Replies
View Related
Dec 6, 2014
adding columns in Access2013,I'd like to add a column to an existing datasheet and place it between 2 existing columns,I see where and how to an a new column but don't see where I can insert it where I want it.
View 2 Replies
View Related
May 30, 2014
I have a query where I need to incrementally count the repetition of column values and add that value in a new column.
Example:
"Column One" and "RepeatCounter" are fields in a query.
Column OneRepeat Counter
Value 11
Value 21
Value 31
Value 12
Value 13
Value 32
I want to create the outcome in the field "Counter" in an Access Query. I am a novice in Access VBA. I found I can do it in Excel with the formula CountIF. I am unable to do it in Access.
View 9 Replies
View Related
Oct 14, 2014
I am designing a project in which there is a form and on that form there is a textbox and Browse button. With browse button you can select a file from the dialog box and that file path will appear in the textbox. This part has been done as below:
Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] ....
Now the next Part i.e Part2 in which we have to see the references present in Column D of the "Summary" worksheet of the workbook whose path is present in textbox1 and check if they match with any of the references in field "Ref" of the Access table named tblLiterature.
If value in column D matches with Ref field of the Access table then change the status field of the Access table to the corresponding columns A,B,C named as "Withdrawn","Obsolete","Updated". that means if the column A of the corresponding Reference is Y then change the status field to "Withdrawn". If column B is Y then change the status to "Obsolete" and if column C is Y then change the status to "Updated".
Please see attached the workbook as this kind of workbook will be searched against Access table .
View 3 Replies
View Related
Dec 7, 2013
I would like to export from access a recordset into Excel and bold the column headers.
My current code works for the most part, except for the bolding.
You run the code in Access module and the code does the following:
1) checks to see if the excel file is open
2) if excel file not open, it opens the file, clears existing records, and starts copying and pasting new recordset into the RAW worksheet
3) it is then supposed to bold the column headers.
bolding is not working.
Code:
Sub TestFileOpened()
Dim lastRow As Long
' Test to see if the file is open.
If IsFileOpen("c: est2003.xls") Then
' Display a message stating the file in use.
MsgBox "File already in use!"
[code]....
View 1 Replies
View Related
Feb 7, 2008
How can I move multiple columns data into a single column so that:
Name Age Location
Mike 25 Essex
Jack 32 Surrey
Bob 36 Newcastle
appear in a single column with data appended column-wise as
Mike
Jack
Bob
25
32
36
Essex
Surrey
Newcastle
Any help would be much appreciated. I'm a novice at VBA, so if anybody could do the code, it would be great!
View 3 Replies
View Related
Jan 16, 2007
Below is the code that I have for a query. Currently the
GrandTotal Column appears to the left of all the Weekly Columns.
Is there a way I can have the GrandTotal column appear to
the right of all the Weekly Columns? The weekly columns
will expand or contract depending on the dates selected.
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 );
TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
View 1 Replies
View Related
Feb 7, 2008
How can I move multiple columns data into a single column so that:
Name Age Location
Mike 25 Essex
Jack 32 Surrey
Bob 36 Newcastle
appear in a single column with data appended column-wise as
Mike
Jack
Bob
25
32
36
Essex
Surrey
Newcastle
Any help would be much appreciated. I'm a novice at VBA, so if anybody could help me with the code, it would be great!
View 14 Replies
View Related
Jul 3, 2014
I have a column that's called "Date" which stores values for begining to end date as 'Text' like: 070314-073114 So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114?
This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.
View 14 Replies
View Related
Oct 14, 2014
I am working with other data that has been created by someone else.
There are a number of columns with the same information in (a serial number). What I need to do is get this into one long column so that I can run other queries from it.
So far I have tried using this SQL:
SELECT A1-TX1 POWER AMPLIFIER 1
FROM SM_Cabinet_T
UNION ALL
SELECT A1-TX1 POWER AMPLIFIER 2
FROM SM_Cabinet_T
[Code] .....
But it is not working - Is it to do with the field names or am I entering something incorrectly?
View 8 Replies
View Related
May 21, 2013
I have 4 tables that store information on guest charges (stayinfo/room rate, fuel charges, bar tab, merch charges) each linked to table carrying guest ID's. Is there a way to combine/merge all the tables into one in such a way that all of the guestID's are in one column, all of the charges are in another column etc? Basically adding one table to the bottom of another.
View 1 Replies
View Related
Feb 4, 2008
I will need to create a new column in a query with data based off of two columns currently in my DB.
Current fields are: [TestType] & [TestReason].
New TEMP field will be [TestCombo]
TestType has 4 possible option via drop down.
TestReason has 21 possible options via drop down.
TestCombo will be one of 10 options depending on the data in TestType & TestReason.
examples: (here is an example of how the combo field will be populated. There will be ten total rules like this. one for each possible text option in the combo box.)
If [TestType] is "DRUG" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTDT".
If [TestType] is "ALCOHOL" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTAT".
If [TestType] is "DRUG" & [TestReason] is "N-PRE" or "N-OTHER" or "N-FUP" then [TestCombo] would be "NDDT".
The data does not need to be saved anywhere at all in the database, just generated when pulling this query. It will then be exported and dumped into another system.
Thanks. Here is a temp copy of the database with some junk data to maybe make it a little more clear.
View 9 Replies
View Related
Jun 6, 2013
I want to create the date from multiple columns to single colums. Just for example
table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013
Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013
Now i want to make one query, which can you the date as follows when we give criteria = apple
Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import
Means two dates from different table into one query column... One way in my mind to make one table for both tables.
View 9 Replies
View Related
Sep 2, 2014
In sheet 1, In column A all the Batch numbers are present. So I want to check if each of these is present in Access table named "tblmain". If its present then display its corresponding Policy number in Column B of attached workbook.
View 1 Replies
View Related
Oct 4, 2013
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code:
TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID]
SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID]
FROM Register
GROUP BY Register.National
PIVOT Register.P_Gender;
Crosstab query did the trick..
View 1 Replies
View Related
Jun 24, 2014
My question is that can we do multiplication of data of two columns and result is automatically displayed in third columns in datasheet view.? Is it possible ?
View 1 Replies
View Related
