Use Split Function In SQL
Nov 25, 2004hi guys/gals!
can i use the split function in queries?
example:
xxxxxxx / xxxxxxx
i only need the first "x's" in my report.
thanks in advance!
ADVERTISEMENT
Need Help Using The Split Function In Mdb
Jul 13, 2005First, my knowledge is limited so please forgive me. The problem I have is a persons full name in a column and I wish to have first name in a column and last name in a column.
ex: John Public
I would like it to be
John
Public
thanks in advance!
J
Split Function On A Recordset
Feb 2, 2006Hi
i open a query as a recordset in VBA. i then need to use the split function to split up a few fields. how do i do this for the entire recordset.
for example:
MySplitVar = split(rst1("Field1"))
now i need
rst("Field2") = MySplitVar(0)
rst("Field3") = MySplitVar(1)
keep in mind i can't write to table. i need to somehow create a new recordset (maybe clone) and add columns to it. also, i don't know if this is relevant, but i will need to do this for up to 12 columns which means that i will have two per column; i.e. total columns=24
I see that nobody is posting an answer so i will add an explanation what i am doing.
i came up with a great idea. i need a crosstab that shows two values. so i created a a column that has both my columns in one column. value column shows the following:
myValue: = myFirstValue & " " & mySecondValue
and in the total i select First. so now i have a crosstab that shows two numbers with a space between them. now i have a report that will show this crosstab query. then i set each field's controlsource on the report to a particular field on the crosstab. my problem is that i first need to split it up and i am stuck there. i need to somehow add columns to the recordset.
thanks,
sam
Queries :: How To Use String Split Function
May 26, 2013How many kind of string inbuilt function we have or if we create our on function so how to call that function in your query...
View 6 Replies View RelatedModules & VBA :: How To Use Split Function Query
Feb 18, 2014I have a query
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
In where I would like to put code like this:
Code:
Split(tblOffset.DziedziczonePo, ";")(1) & ";" & Split(tblOffset.DziedziczonePo, ";"(2) & ";" = "tak;tak"
Is it possible to create such a query?
Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query
Dec 11, 2013So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedForms :: SUM Function Produces Error From Calculated Function
Jan 30, 2014I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
Date Function/need Time Function
Jun 9, 2005We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
Now Function To Convert To Date Function
May 25, 2006Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
Split Or Not To Split ?
Jul 19, 2006Hi. 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
Split DB
Mar 17, 2005I 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?
Scott
Split A Day Into Two?
Feb 21, 2008HI THERE AGAIN!
I have these two fields:
"DateOfCase" and "AM/PM"
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.
Is this possible to do?
Why Is Split Db So Slow?
Apr 29, 2005Hi all
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??
Any comments please?
Skeletal
Split DB, Keeps Locking Up
May 24, 2005Hi, 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?
AP
How To Split Up This String
Sep 15, 2005I have a string:
Cars > Car Import > Car Import: Car Import
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.
Thanks,
Dave
Split The Database, Should I Do It?
Jan 12, 2006: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.
View 5 Replies View RelatedSplit An MDE Database?
Aug 12, 2006I 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?
Any advice, as always, is most welcome.
Split Database
Nov 21, 2006I 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
Editing After FE/BE Split
Nov 21, 2006I created a database a while back. It was complete to my standards at the time. I split it FE and BE to protect the data.
Since then, I have made several changes on my machine to the user interface and some of the back end tables and queries.
How do I insure that these changes are seen on all the different computers on my network?
They only seem to exist on my personal machine.
When To Split Database?
Dec 21, 2006Hello,
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.
Thanks.
I will have about 20 users sharing this.
Why Should I Split My Database?
Dec 27, 2006I 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.
View 9 Replies View RelatedSplit One Field In To Two - How?
Feb 18, 2007I 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.
Thanks.
Database Split
Jun 29, 2007I 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.
Michael
How To Split Database In FE And BE
Jul 2, 2007Hi!
I just want to know that " How to split the Access database in Front End and Back End application"? I am creating a database that i want to store on server and simultaneously want to give access to 20 terminals (cleints) to access the database.
Secondly, I have to provide a field in a form "Document number". However as per the requirement user can enter "PIR No", "Serial No" or at times both as document number. Both the things "PIR No" and "Serial No" have different format types like "PIR No 001" and "Srl No001".
On basis of document number later i have to give the option to search the particular document. Please guide me how to provide the option to enter the field values.
Appreciate any help on above.
Regards,
Nick
Split Databases
Feb 21, 2008Hi,
I just got Access 2007 and split a database into a front and back end to see what it was like.
Is there any way of joining them back again?
Thanks
David :o
Split And Replicate
Mar 7, 2008Hi,
I have a database that I have successfully replicated. It includes forms, queries and tables. Synchronizing works and all is good, but.....from what I keep reading on this forum, this is not the proper way to do it. It sounds like what I should be doing is splitting my database before replicating, and then just replicating the back-end.
When I do this, I find that when using the forms, etc. in the front end, I am unable to synchronize to the back end. I don't even have the option to do it through the menu options.
How do I make the front end recognize that the backend is replicated??