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!

View Replies


Split 1 Column Data Into 2 Separate Column


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...

View Replies View Related

How To Split Letter&number Field Into 2 Column And Skip Brackets?

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
in Advance

View Replies View Related

Adding Column At The End Column Of The Table Under Access 97 - Version: 97 (8.0)

Dear All

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

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.


View Replies View Related

Access 2003 To Access 2007 Split Database

Hi All,

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.

View Replies View Related

Access.LockFile.9, Even With Database Split

Hi there

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.

View Replies View Related

Split Function No Good In VB5 - Access 97 - Version: 97 (8.0)

G'day Guys,

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
End If
sCriteria = sCriteria & "[Keywords] like '*" & Trim$(varKeyWordArray(i)) & "*'"
Next i
sCriteria = sCriteria & ")" 'tack on a following )
End If

Any help is very much appreciated because I am stumped.


View Replies View Related

How Many Users Can Access The Back End Of A Split Db ? - Version: 2000 (9.0)

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 ?

View Replies View Related

Access Slower When Database Is Split - Version: 2000 (9.0)

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.


View Replies View Related

Upgrading To XP - Should We Split Our Access Database? - Version: 2000 (9.0)

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!


View Replies View Related

Formatting A Column To Pull Partial Data From Another Column - Version: 2000 (9.0)

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

View Replies View Related

Make New Table, Insert New Column, Update Column - Version: 2002 (10.0) XP

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!


View Replies View Related

Populating A Column In A Table Based On Values In An Existing Column

Hi all,

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?

Kind Regards,


View Replies View Related

Show Column Only Once In Multi Column Report - Version: 2002 (10.0) XP

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?

View Replies View Related

Making Column Entries Into Column Names - Version: 2003 (11.0)

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):

A 1
B 6
C 2
A 3
B 4
B 5

This is what I want (now a 5x3 table, where A,B and C are the column names) :

1 6 -
- - 2
3 - -
- 4 -
- 5 -

Please let me know how I might be able to do this in MS Access.

View Replies View Related

Multiple Column Sub Rpt On Main Rpt Not Showing 2nd Column - Version: 2003 (11.0)

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?

View Replies View Related

Split Function And Enum Keyword Access 97 - Version: 2000 (9.0)


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
End Enum


varItems = Split(strText, mstrSeparator)

View Replies View Related

Re-merging A Split Access (front-end, Back-end) - Version: 2000 (9.0)

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.


View Replies View Related

Access Split: Does That Prevent .Find Or .Seek? - Version: 2003 (11.0)

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?

View Replies View Related

Referencing Dropdown Column Diffferent To Boun Column


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-

im tyring:

"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?



View Replies View Related

Moving And SORTING Multiple Column Data Into One Column

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
1, 6
1, 9
1, 9
2, 7
2, 9
2, 8
2, 3

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
FROM Depth_Velocity_Substrate_Correct
Union all
FROM Depth_Velocity_Substrate_Correct
Union all
FROM Depth_Velocity_Substrate_Correct
Union all
FROM Depth_Velocity_Substrate_Correct
Union all
FROM Depth_Velocity_Substrate_Correct

View Replies View Related

Creating Frontend/backend Split Within On Access Database File - Version: 2000 (9.0)

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,

View Replies View Related

Spliting A Column Into Several In Access 97

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 ??

View Replies View Related

Copy Data From A Column In One Table To A Column Another.

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.

View Replies View Related

Column Permission In Access Table


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


View Replies View Related

Filling Data Down A Column In Access.

Is there something fast to fill down a new column into approx. 900 records with the same data similar to using Excel (Fill Down)?

View Replies View Related

Bulk Update A Column In Access

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.

View Replies View Related

Export Access Data Into Excel 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

View Replies View Related

Transferring Spreadsheet From Excel To Access. Problem With Column Name

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 ?

View Replies View Related

Comparing Data In One Column To Another Column

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.

View Replies View Related

How To Set The Column For Label And Column For Data


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
Me.Print ""

'//-- PRINT Q5 -------------------------------------------------
strRowVal = ""

If Len(Trim(Nz(Me.Q51.Value, ""))) > 0 Then
strRowVal = strRowVal & Q51 & "*"
End If

If Len(Trim(Nz(Me.Q52.Value, ""))) > 0 Then
strRowVal = strRowVal & Q52 & "*"
End If

If Len(Trim(Nz(Me.Q53.Value, ""))) > 0 Then
strRowVal = strRowVal & Q53 & "*"
End If

If Len(Trim(Nz(Me.Q54.Value, ""))) > 0 Then
strRowVal = strRowVal & Q54 & "*"
End If
If Len(Trim(Nz(Me.Q55.Value, ""))) > 0 Then
strRowVal = strRowVal & Q55 & "*"
End If

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

End If

End Sub

Public Function printLabel(strText, nHAxis, nTextFontSize, nTextFontColor)

Me.CurrentX = nHAxis
Me.FontBold = True
Me.FontSize = nTextFontSize
Me.ForeColor = nTextFontColor

Me.Print strText

'Me.CurrentX = nHAxis
Me.FontBold = False
Me.FontSize = nBaseFontSize
Me.ForeColor = nBaseFontColor

End Function
Private Sub Report_Open(Cancel As Integer)
End Sub

View Replies View Related

Copying Access Table Columns To A Single Column In Excel?

HELP :confused:


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 Replies View Related

MS Access Display Excel First Column Values - Version: 2000 (9.0)

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"

End If

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
' http://I.Am/DougSteele
' 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
End If
On Error GoTo Err_ReadFromWorkbook

objXL.Application.Workbooks.Open WorkbookName

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"

End If

On Error Resume Next
objActiveWkbk.Close SaveChanges:=False
Set objActiveWkbk = Nothing
If booXLCreated Then
End If
Set objXL = Nothing
DoCmd.Hourglass False
Exit Sub

MsgBox Err.Number & ": " & Err.Description & " in ReadFromWorkbook", _
vbOKOnly + vbCritical, "Smart Access Answer Column"
Resume End_ReadFromWorkbook

End Sub

View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved