I set up my query in data set view and then I switch over to SQL view, this is what I have
================================================== ===
SELECT Sample.Postcode, split([Postcode], RIGHT(postcode, 2))(0) AS NewPostCode
FROM Sample;
================================================== ====
When doing this query I get an error saying invalid syntax and it reads the (0) as (0. Or something
Code: SELECT tblZlecenia.ID_Zlecenia, tblZlecenia.TerminOdbioru, tblOffset.ID_RodzajZlecenia, tblZlecenia.NazwaZlecenia, tblZlecenia.Id_K, tblZlecenia.Id_Rodzaj_Pracy FROM tblZlecenia LEFT JOIN tblOffset ON tblZlecenia.ID_Zlecenia = tblOffset.ID_Zlecenia where
I have a very large query that I need to export to text. I'm hitting the 4GB limit, so I'd like to "split" the query and create two text files instead.
What's the most efficient way to do this? Also, are there any export file formats that can handle more data? (i.e. is there a format that could fit this massive query into a single file?)
I have a time reporting database called Productivity. I also have a split version with ProductivityFE containing the forms, queries, and reports and ProductivityTables containing the data. All three are on my PC. Each month I update Productivity with new records and run a report or two to check things out. Then from ProductivityTables I import the data tables. If there are any updates to the forms, queries, and reports these are imported into ProductivityFE. I again test a couple of reports before finally placing both FE and Tables on a server. The managers then place a copy of FE (from the server) onto their desktop and run reports from there. So far so good until this month when I created a new query for a new report. It runs fine in Productivity, but in the split database (on my PC) I get the error 'Invalid Bracketing of name [tblCostcenters.ProdCenter]'.
Below is the SQl from the query. Of note here is a time record contains both a home costcenter and a worked costcenter for the employee. I have two instances of tblCostcenters in order to reference the field ProdCenter for both the home and worked costcenters. The code fails at the first IIF statement on [tblCostcenters.ProdCenter]. Based on other posts on this sight I tried replacing these brackets with [tblCostcenters].[ProdCenter] and [tblCostcenters_1].[ProdCenter] and it seemed to be working until the last bracketing was changed, after which I was faced with the parameter query dialog box looking for a value for tblCostcenters.ProdCenter.
So I'm stumped. Why would the query work in a single database but not in a split database?
SELECT tbleTimeData.HCCtr, tblCostcenters.Title AS HTitle, tblCostcenters.ProdCenter AS HProdCenter, tbleTimeData.WCCtr, tblCostcenters_1.Title AS WTitle, IIf([WCCtr]="","Other Affiliates",IIf([tblCostcenters.ProdCenter]<>[tblCostcenters_1.ProdCenter],"Outside Product Center",IIf(IsNull([tblCostcenters_1.ProdCenter]),"Outside Product Center","Within Product Center"))) AS WProdCenter, Sum(tbleTimeData.Hours) AS SumOfHours FROM (tbleTimeData LEFT JOIN tblCostcenters ON tbleTimeData.HCCtr = tblCostcenters.Costcenter) LEFT JOIN tblCostcenters AS tblCostcenters_1 ON tbleTimeData.WCCtr = tblCostcenters_1.Costcenter WHERE (((tbleTimeData.FY)=[Forms]![frmMainMenu]![cboFY]) AND ((tbleTimeData.FP) Between IIf(IsNull([Forms]![frmMainMenu]![cboFPFrom]),[Forms]![frmMainMenu]![cboFPTo],[Forms]![frmMainMenu]![cboFPFrom]) And IIf(IsNull([Forms]![frmMainMenu]![cboFPTo]),[Forms]![frmMainMenu]![cboFPFrom],[Forms]![frmMainMenu]![cboFPTo]))) GROUP BY tbleTimeData.HCCtr, tblCostcenters.Title, tblCostcenters.ProdCenter, tbleTimeData.WCCtr, tblCostcenters_1.Title, IIf([WCCtr]="","Other Affiliates",IIf([tblCostcenters.ProdCenter]<>[tblCostcenters_1.ProdCenter],"Outside Product Center",IIf(IsNull([tblCostcenters_1.ProdCenter]),"Outside Product Center","Within Product Center"))) HAVING (((tbleTimeData.HCCtr) Between IIf(IsNull([Forms]![frmMainMenu]![cboCCtrFrom]),[Forms]![frmMainMenu]![cboCCtrTo],[Forms]![frmMainMenu]![cboCCtrFrom]) And IIf(IsNull([Forms]![frmMainMenu]![cboCCtrTo]),[Forms]![frmMainMenu]![cboCCtrFrom],[Forms]![frmMainMenu]![cboCCtrTo])) AND ((tbleTimeData.WCCtr)<>[HCCtr]));
I have a table that contains a memo that is delimited by line breaks. For each of these breaks i need a new record in the query results that i can then use in a Labelling application.
My current query looks something like:
ID | Product | Pack Size 1 | item a | 1x1000,1x1050 2 | item b | 1x1000,20x25 3 | item a | 1x1000
(Where the , is a new line)
Whereas my ideal output is:
ID | Product | PackSize 1 | Item a | 1x1000 1 | Item a | 1x1050 2 | Item b | 1x1000 2 | Item b | 20x25 etc.
I think im supposed to use the Split() Function though i dont have a clue wherw to start.
I have a manufacturer that has sent me payments from 8/2/2012 to 05/20/2013 but there was a wide gap where we did not do business from the end of 2012 to April 2013.
How can split the date ranges up in the query so when I create my report, I can get two ranges for the same manufacture.
For example one list of payments from <#12/31/20112# and the other from >#4/1/2013#
I've developed a database that was working fine until I split it to use in a network enviroment. The problem is a make-table query that now make it's table in the front end instead of the back-end. I need to run a make-table query because the query uses a custom function which then causes errors when the query is accessed by a web form that I use.
The only way I thought of getting round it was to create a permanent table in the back-end which is cleared every time and an append query used to populate it.
I have searched and search and I can't find the answer to this question when I thought it would be simple. Hopefully you can put me out of my misery!
I have a access 2007 split form with a series of search boxes and a query result window. Enter info into one or more of the search boxes, click search and you get the matching results in the query window below. That works fine but I want to work with the results of the query, how can I do this?
I was looking for the user to be able to select a record and then click a command button to open that record in a form. Or in another instance select a record in the result window, have the current from close and have the primary key passed to a another form which I use to open the split form.
I just can't see how to do it but I expect there are several ways. Happy to use VBA but my experience is with Excel VBA so I'm still getting used to the objects.
i have developed an application in access 2010 . and split into front and back end . now i want to add more tables in back end and i need to define lookup list in table definition from the query presently in front end . when i get into lookup list and query builder doesnt show front end content ... how to solve this problem ?
I have an Access Database and I want to split up one column into multiple columns. The one column has multiple rows that relate to one person, so for instance I have the following:I am taking information from both tables and I want to create a query but I need to separate out the mother, father, and guardian with the names attached
So from the Child table :
Joe Smith Address Phone
The query that I created is from two tables, the child table and the family table: ( I used the dashes just for it to be easier to read)
First Name--- Last Name--- First Name--- Last Name--- Relationship Joe ----------------Smith ----------Jane --------Doe-------------Mother Joe ----------------Smith ----------John---------Smith---------- Father
So in this scenario I need to get the name of the second parent in another column....meaning have all the parents appear on the same line for this child, and each child there after..
First Name------ Last Name------ First Name------ Last Name------ Relationship------ 2First Name------2Last Name------2Relationship Joe----------------Smith------------ Jane-------------- Doe------------- Mother-----------John---------------- Smith------------ Father
I have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?
Hi. I am trying to creat some sort of security for my database so none else make changes to design of the database apart from me. I have got a small database that i run on the company network i work for. all the staffs have a short cut of this database on ther desktop where they can add/edit/view data and unfortunatly able to change design of the database if i havent set the startup to only show switchboard. what I want is this:
Open database, switchboard popup and nothing else(ie. database window where you can see table,forms,queries)..sounds simple..but havent able to do it..
people say use database split...not so sure on...by the way, I DONT WANT OTHER USERS TO HAVE PASSWORD TO ACCESS THE DATA...hint,,no password and user name popups....
i am kinda new to access,,pleeeeeeeease help
Hi. I am trying to complete my access database by creating some security for it. I want to know how to setup the database file so when I execute, let say in my document folder so that when it opens it only shows my switchboard and not the editable(table,queries...) panel on the left hand side. I know i can hide it via the startup option but I want to completly blocking it so it doesn't allow any user to edit the design of the database. If anyone can help I realy appreciate it...thank you
I know that this might be extremely obvious to most people, but at the risk of sounding stupid I'm going to ask it anyway. I plan to share my split DB over the network. I know that the BE will be hidden from the view of most people so they won't try to meddle with it, but which would be better for the FE, should it be accessed by all of the users, or should they each create their own shortcuts, or does it even matter?
And I want them to have them linked so that no one else can have the same time in the court.
For example:
I booked the morning (AM) in a court for today (21/2/08). Somebody may want to book for the same day and can do so BUT he/she can only booked for the afternoon (PM) session of that day.
I have an Access 2003 db that works extremely well as a stand-alone application but I am keen to “split” it so that many users can operate it together.
Accordingly I did the split and placed the tables on our server, and tried running the front end locally.
Much to my horror, it takes about a minute to open, and many seconds to do anything after a button press etc.
I have been testing it on my wireless home network and the same thing happens, except now I can monitor network activity from the “network working” light.
What I see baffles me and would welcome comments, in particular, what I can do to speed it up by a factor of 100!
1.It takes forms about 50 seconds to open; however, this is due to about 40 seconds of network activity BEFORE the form’s load event fires! 2.In DESIGN mode, any minor change to a control for example, results in about 40 seconds network activity. 3.All queries upon which forms are based take well under a second to run over the network. 4.Making an mde file has no effect.
I thought the idea of splitting was to keep the forms/queries/code etc, local to a machine so I don’t understand what all the network activity is about, particularly in design mode. Also, given the actual queries are so fast, just what is Access doing??
Hi, I just recently split my Access db (FE/BE), copied the FE to each of the clients, and it seemed to be working fine. This morning however, two users went into the db, and the second user kept getting the error can't find the file \servernameshare_namedb_fe.mdb. Now, none of my users can launch it. How can I resolve this? Will this keep happening?
The first section is the Category, the second is the Product, the third is the SubProduct and the fourth is the keyword.
What is the best way to split each of these words into its own variable? I think I can do the first and end one but not the Car Import and Car Import one.
:confused: I have read a quite a few threads on spiting the database. My database is 50 Meg and running very slow. I have compacted and repair, still the same. This just happen all of sudden. Could that be possible?. What should I look out before I try to spit it?. It’s given that I will make a backup. I am running Access 2003. I have over 150 users.
I am almost at the finishing line with a great deal of help from here. I have now normalised the tables, built complicated queries, forms, reports, all from scratch as I knew absolutely nothing before I started with Access three months ago. I have compacted and repaired the database using the wizard, and also complied the code and analyzed all tables, forms, queries, and reports and relationships. The final step is to make it an MDE file so that it can go on the local network at work (not on the internet). My questions are these;
Do I need to split the database into front and back ends?
If I save a copy of the original MDB file and then make it a MDE file would that not suffice in making it run more efficiently?
Can a split MDB database be made into an MDE database?
I have a database that resides on a file server share. I have split the database to create a backend. I then posted a shortcut of the front end to the desktops of two users who have access to the share. I then tried to access the front end from the two machines simultaneously. It opened on the first PC but not the second? When I viewed the server share it had a 'padlock' icon?
Where am I going wrong? This is the first time I have attempted this scenario!! Thanks in advance, Phil
Should splitting only be done when all tables,queries,forms,reports, etc. are done. Or can it be done anytime. I am almost to that stage but didnt want to jump the gun and run into problems later on.
I was having a discussion with Rickster57 concerning my newly developed database, and he recommended that I split it so that it has a front end and a back end. Rick listed a number of very good reasons for doing this (so I will definitely do it). But I wanted to pose the question so I could get some of the reasons the more seasoned Access programmers have for creating a front and back end to their programs.
I have a table that holds 250 records, and dont really want to split manually as the saving grace is that each record, has a hyphen that marks the point where the split needs to take place.
For example, the field currently holds
'Movicol sachets - 1 or 2 sachets dissolved in water at 21.30'
That is stored in 1 field, I now need to be stored in to 2 fields, whilst removing the hyphen, and spacing immediately before and after so it works out like this.
Field 1 = Movicol sachets Field 2 = 1 or 2 sachets dissolved in water at 21.30
I know it can be done using either a query or VBA, but I am not sure which function/procedure/command I need to use to carry this out. Any assistance would be gratefully recieved.
I recently split my database and also used Bob Larson's Autoupdating tool. Everything appeared to be working fine until a few users were unable to access the db. I later found out that because they are at a different site, they don't map to the server that houses the database BE. However, even if they manually map to the correct server, they are unable to bring up the db Form. They get an error stating the BE "...is not a valid path". My questions are:
1) If I were to copy all the files (the Master FE, BE, and MDE) to a public folder that ALL users have access to, will everyone be able to bring up the database Form?
2) How would this affect what was already set up when I ran Bob Larson's utility (or even the splitting and MDE creation)?
3) Is there a way to "Undo" what was previously "Done" to this database (ie: Splitting, Larson Utility, MDE creation) in order to correct the issue, or do I need to start from scratch and split the database while it's on a Public drive.
I hope this makes sense. Thanks in advance for your help.
