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.
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).
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 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.
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.
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 ?
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:?
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!
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 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
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.
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:
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 SELECT Depth2 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth3 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth4 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth5 FROM Depth_Velocity_Substrate_Correct
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?
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?
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.
DUTYPOSC 11B111B1 11B111B1 11C111B1 11B111B1 92Y111B1 11B111B1 13F121B1 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? Thanks.
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
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.
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" Else
' 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 Else Set objXL = CreateObject("Excel.Application") booXLCreated = True End If On Error GoTo Err_ReadFromWorkbook
Set objActiveWkbk = objXL.Application.ActiveWorkbook Set objActiveWksh = objActiveWkbk.Worksheets(1)
End_ReadFromWorkbook: On Error Resume Next objActiveWkbk.Close SaveChanges:=False Set objActiveWkbk = Nothing If booXLCreated Then objXL.Application.Quit End If Set objXL = Nothing DoCmd.Hourglass False Exit Sub