Split Access Column In Two...
I have a column in my table which contains DATE information in the format: 23/02/2005
I would like to split this date into two additional columns:
A) the MONTH as a number eg. 02
B) the YEAR also as a number eg. 2005
I know this is possible in Excel but can it be done in Access 97? :confused:
Cheers for your help!
I need a query that could split data into 2 different field.
The table pic is attached for your ready reference. I tird with Left & Right funtions with no results.
Any one can help me please.
Table name is Agcad and field on which I need to split is Board_Size. The problem is Board_Size data is sometime in 3 digit (first half part) and sometime second half part is in 4 digit. It is a text data type field.
Thanks in advance...
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
Exactly, I have to modify a existed program and the requirements make me add a field in table of SQL Server 2000 to fulfil the application. This table links a special program which copys data from SQL Server 2000 to Access(a loop copy each field each value, field order must match). The copy data program cannot change because it affects too many things.
Therefore, I have to add a field into two side (SQL Server 2000 and Access97)
Then, I find I add a field in SQL Server 2000 and it must be placed at the end of table. However, access97 place the field at unknown order of the table.
I use program to add column at the end column of the table under access 97, and find the following problem.
table testabc have columns a1, b2, d4
now, I wanna add column c3 at the end of the table, and run the underlying query using VB6 program at Access
ALTER TABLE testabc ADD c3 VARCHAR(20)
the testing result is the table become testabc(a1, b2, c3, d4).
My need result is the table become testabc(a1, b2, d4, c3).
My platform is windows 98 plus access 97.
Background Info: I developed our main department's Access 2003 split database which is on a server for 15+ Users. I've now been given Access 2007 for development -- Users still have 2003. There are also 2 other smaller databases that are not split (.mdb).
Problem: In the split database, I've saved the Application .mdb as 2003, relinked and made a new .mde. But the Users still cannot open the database. (I did this in a copy until I figure out the nuances with 2007.) The 2 other unsplit databases can be saved as 2003 version and Users can open OK.
I'm grateful for any suggestions on working with Access 2007. I've been trying to tackle the ribbon which is a whole other question.
I'm a bit confused with this.
I have a database which I've split and I've put both the frontend and the backend onto the network drive.
I'm pretty sure it's the case that the backend should be on the server and the copies of the frontend should be deployed to each of the computers that people are working with rather than each person working on the same frontend copy within the server. would this be right to say this?
At the moment there is two people working on the database and the third person cannot access the database because it's freezing and the 'access.lockfile.9' is showing on the third users computer for both the frontend and the backend.
Does anyone know how I can resolve this problem? is it a case of copying the frontend to each of the computers that the users are working with rather than them accessing the same frontend from the network drive.
Thankyou in advance for any help.
I've got this code courtesy of Lawmart - thanks mate! And it works fine in Access 2000 but it has since occured to me that it will not run in Access 97 which must use VB5??
It is to do with the Split function.
How can I modify the code so as to be VB5 compatible??
If Nz(Me.Keywords, vbNullString) <> vbNullString Then
sCriteria = sCriteria & " AND (" 'tack on a leading (
varKeyWordArray = Split(Me.Keywords, " ")
For i = 0 To UBound(varKeyWordArray)
If Right(sCriteria, 1) <> "(" Then 'already have 1 or more LIKE tests?
sCriteria = sCriteria & " OR " 'yes, tack on an OR condition
sCriteria = sCriteria & "[Keywords] like '*" & Trim$(varKeyWordArray(i)) & "*'"
sCriteria = sCriteria & ")" 'tack on a following )
Any help is very much appreciated because I am stumped.
I have a split database that has the back end at a central location and the front ends on local user machines in different parts of the county that see the back end via a VPN .At present it is accessed by about 5 local users. Is there any limit to the number of local users it can handle ?
I have eventualy splitted my database. It is halfway slower than it used to be.
The data/table file is on the network, while each individual user has a version of the frontend on their harddrive. The tables are linked to their frontend from the network.
Why is it so much slower? Is there anything else I need to do?
Your reply would be appreciated, because it is a bit embarrising that it is slower now, and I promised them it would be faster.
Hi, all - we will be upgrading from Windows NT to XP soon. We're currently using Access 2000 but will migrate to Access 2003 as part of the conversion. Will it be necessary to split our database as a result? I read the following in an online article: "If you created your database using an older version of Microsoft Access and you choose to migrate to a newer version of Microsoft Access, you should split your database to ensure continued access to the older database files."
We currently have 9 users using one database that was created in 2004. If we should indeed split the database into FE and BE, then I have the following question: it seems that most articles indicate that each user should load their FE on their own machine (one article even specified the hard drive,) as opposed to a network drive. Is this correct, or can a network drive be used? I.E., could the BE be housed on network drive G: while each user's copy of their FE is housed in their own folder on network drive H:?
Thanks for any help you can give!
I think this is an easy one, but my knowledge of access is very limited.
I have a column of information that has for example;
Product Qty: 200
I want to create new column that pulls only the qty. Basically pulls in the 14th character to the 16th characters to the right of the cell.
Hopefully I explained this well enough.
Thanks for any help
Hi there -
I would like to have a command button on an access 2003 form. When the button is clicked I would like the user to be prompted to enter a date. When the date is entered, a new table is created with an additional column to hold this date. So this date would now be with each record in the table. Can someone point me in the right direction? Thank you!
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
How do I do this?
I am creating a report with 4 columns. Each column contains four controls. The first control is MONTH and is the same for each of the 4 columns. Due to space limitations, is there a way that I can show the MONTH column only once, along the left side of my report?
I have a column with character entries. I want to split the entries in the column as column names. For e.g.:
This is what I have ( a 6 x 2 table):
This is what I want (now a 5x3 table, where A,B and C are the column names) :
A B C
1 6 -
- - 2
3 - -
- 4 -
- 5 -
Please let me know how I might be able to do this in MS Access.
I have added a multi column sub report to my main report, but it is adding it as one column only. Both reports are 6.75 inches wide. Is there a simple solution?
I have used code from the Access 2000 Developer's Handbook (Getz, Litwin et al) to resize my forms on small(er) resolution screens. I have managed to get this working to an acceptable degree. However, I have been informed that there will be a small number of potential users using Access 97. I converted the db to 97 and found that the "Enum" keyword and the "Split" function are not recognised in the older version.
I was wondering if there is a way round this problem? Here are 2 code examples:
Public Enum ControlSize
czRight = 1
czBottom = 2
czBoth = 3
czNone = 0
varItems = Split(strText, mstrSeparator)
I have a need to save off at the end of each month my access friont-end and back-end databases for archiving.
Plan is to place this in a folder with subfolders for each month.
We plan to have one of the users move the "production databases" to the frozen sub-folder.
The users will need to from time to time access and run from these "frozen" databases.
I was wondering if there was a way to automatically rejoin the two databases (front-end & back-end) back into one database within these frozen folders? (My databases are always being modified and so need to keep everything together)
I thought this might be easier than trying to make sure the frozen databases are being linked properly. And since these frozen folders are accessible on the our network, concerned about the issue of possible multiple users accessing a front-end at the same time or of an user copying the frozen database to their machinecand having a issue arise because of the link.
Someone told me that if I were to split a database that any .Find or .Seek commands in the front end would not work properly.
Is there any truth to that?
im wanting to reference a value of a dropdown which is already serving a function - the dopdown already has it's bound set to 3 but i want this new function to refercne the second column-
"Tutor = '" & Me.ComboStaff.Value(2) & "'"
which isn't working- how do i reference the second column of a dropdown if its bound to its third?
Hi. I have a question I'm hoping someone can help me with. I would like to take data from multiple columns and put the data into one column. Additionally, I do not want to exclude any data (union all) and I would like to group the resulting union by another field. For example:
Original data layout:
Column Headings: Sample Event, Depth 1, Depth 2, Depth 3,
1st Row Data: 1, 6, 9, 12, 9
2nd Row Data: 2, 7, 9, 8, 3
Desired data layout:
Column Headings: Sample Event, Depths
So far I'm using the following SQL. What do I need to add or change to get my desired result of grouping the unioned depths by the 'sample event' field?
I appreciate any help anyone may have to offer. Thank you.
SELECT Depth1 AS Depths
Hi, real new to UA here with what should be a simple question. I want to create a frontend and backend split within one database file, not linked to any other file. After searching the forums I can't find anything on something this simple.
In a related vein, is it necessary to have a password to create this kind of internal split?
thanks for any help,
I have a field in a table that looks like this:
there is no set length to the bits between the slashes.
I want to write a query that moves the data into a new table in three separate fields using the slashes as field separators.
Can anyone help ??
Please would you be able to advise me how I would copy data from a column in one table to a column another table.
But I need to only use table not a query.
Thank you in advance for your help.
I have a table in Access database. I would like to set read only permissions to some of the fields/columns in the table. I do not want user to alter or edit some fields/columns in a table. Could someone give a solution for this?
thanks in adavance
Is there something fast to fill down a new column into approx. 900 records with the same data similar to using Excel (Fill Down)?
Is it possible to import a column of info into an existing database? The column already exist, but I want to do a bulk update of that specific column.
I have a table in Access and would like to export it using code into specific fielfs of a template in Excel.
My table has 3 fields:
I would like to export the recorsed to a template named MyTemplate. This template has a workbook named MyWorkbook.
The only problem is that I need to copy the active recorset (meaning the one which I will select) in a column and not into a row.
Example: I will select a record using a combo and then data will be copied from my Table into the cells B1 (ssn), B2 (FIRSTNAME), B3 (LASTNAME)
Any idea or help? Thanks
i am transferring spreadsheet from Excel to Access table
pathbgc = Path & "X.xls"
DoCmd.TransferSpreadsheet acImport, , "tableX", pathbgc, vbYes
pathbgc = Path & "Y.xls"
DoCmd.TransferSpreadsheet acImport, , "tableY", pathbgc, vbYes
This works fine as long as column names in Excel do not have periods. (.)
Other then asking whoever is sending this not to put periods, is there painless way ignore that fact that excel has it, un just do it without it ?
I have two separate columns in my query but they contain the same kind of data. These two columns data should match but some of the data doesn't. Can I write a criteria that will show me only the rows that do not match.
For example, I want the query to show me only the rows in which the data in the Duty column doesn't match the POSC column.
I am trying to create a report to set all Labels on one column (left) and data to 2nd column, but it does not work.
My code below will combine a few table columns on one column and delete any empty rows.
Can any one help please?
Dim nBaseXAxis As String 'var to keep left most position of the cursor at starting
Dim nBaseFontSize As Integer
Dim nBaseFontColor As Variant
Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'//-- Report format
nBaseXAxis = 0
nBaseFontSize = 12
nBaseFontColor = vbBlack
printLabel "Last Name: " & Trim(Me.LastName.Value), nBaseXAxis, 12, vbBlack
'Me.Print Me.LastName.Value & vbCrLf & vbCrLf
'//-- PRINT Q5 -------------------------------------------------
strRowVal = ""
If Len(Trim(Nz(Me.Q51.Value, ""))) > 0 Then
strRowVal = strRowVal & Q51 & "*"
If Len(Trim(Nz(Me.Q52.Value, ""))) > 0 Then
strRowVal = strRowVal & Q52 & "*"
If Len(Trim(Nz(Me.Q53.Value, ""))) > 0 Then
strRowVal = strRowVal & Q53 & "*"
If Len(Trim(Nz(Me.Q54.Value, ""))) > 0 Then
strRowVal = strRowVal & Q54 & "*"
If Len(Trim(Nz(Me.Q55.Value, ""))) > 0 Then
strRowVal = strRowVal & Q55 & "*"
If (Len(Trim(strRowVal)) > 0) Then
strRowValArr = Split(strRowVal, "*")
printLabel "Q5: " & Trim(strRowValArr(0)), nBaseXAxis, 12, vbBlack
x = 420
For i = 1 To UBound(strRowValArr)
printLabel strRowValArr(i), x, 12, vbBlack
Public Function printLabel(strText, nHAxis, nTextFontSize, nTextFontColor)
Me.CurrentX = nHAxis
Me.FontBold = True
Me.FontSize = nTextFontSize
Me.ForeColor = nTextFontColor
'Me.CurrentX = nHAxis
Me.FontBold = False
Me.FontSize = nBaseFontSize
Me.ForeColor = nBaseFontColor
Private Sub Report_Open(Cancel As Integer)
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:
This portion of the coding (below) will show the first column (which usually contains column headers) when I select an excel sheet. I was wondering if a loop could be used instead to show all first column values until end of recordset.
strOutput = "Column A1 contains " & objActiveWksh.Cells(1, 1).Value & vbCrLf & _
"Column B1 contains " & objActiveWksh.Cells(1, 2).Value
MsgBox strOutput, vbInformation + vbOKOnly, "Smart Access Answer Column"
For example, Right now, I have a message box will open and show Column A1 As Shipment #, Column B1 as PO #, etc.
Sub ReadFromWorkbook(WorkbookName As String)
' This code was originally written by
' Doug Steele, MVP AccessHelp@rogers.com
' You are free to use it in any application
' provided the copyright notice is left unchanged.
' Description: This routine opens an Excel workbook (passed as a parameter)
' It displays a message box showing the contents of cells A1
' and A2 on Worksheet(1) in that workbook.
' (NOTE: Worksheet(1) isn't always the left-most sheet when you
' open the workbook!)
' Inputs: Workbook Name Fully-qualified path to an Excel spreadsheet (String)
On Error GoTo Err_ReadFromWorkbook
Dim objActiveWkbk As Object
Dim objActiveWksh As Object
Dim objXL As Object
Dim booXLCreated As Boolean
Dim strOutput As String
' Check to make sure the workbook exists
If Len(Dir(WorkbookName)) = 0 Then
MsgBox WorkbookName & " not found.", vbOKOnly + vbCritical, "Smart Access Answer Column"
' Get a instance of Excel that we can use
' If it's already open, use it.
' Otherwise, create an instance of Excel
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
' An error will be raised if Excel isn't already open.
If Err.Number = 0 Then
booXLCreated = False
Set objXL = CreateObject("Excel.Application")
booXLCreated = True
On Error GoTo Err_ReadFromWorkbook
Set objActiveWkbk = objXL.Application.ActiveWorkbook
Set objActiveWksh = objActiveWkbk.Worksheets(1)
strOutput = "Cell A1 contains " & objActiveWksh.Cells(1, 1).Value & vbCrLf & _
"Cell A2 contains " & objActiveWksh.Cells(1, 2).Value
MsgBox strOutput, vbInformation + vbOKOnly, "Smart Access Answer Column"
On Error Resume Next
Set objActiveWkbk = Nothing
If booXLCreated Then
Set objXL = Nothing
MsgBox Err.Number & ": " & Err.Description & " in ReadFromWorkbook", _
vbOKOnly + vbCritical, "Smart Access Answer Column"