Hi,
i want to replace spaces in my query, but there are a few hard parts in it.
I dont always know how many spaces. en second, the spaces that need to be replaced are after the first character. The rest needs to be removed.
i have:
----1----1----2
the "-" are spaces!!
and i want it to show
1.1.2
this is what i tried replace([MinutesID];" ";".") & " - " & [minutesTitle]
can someone assist...
thanks
Hi,
Is there a way that i can remove the space in between the cahracters of the string.
like: 78- ASDGFKGJ OR 78-SJDHF AJSDFH TO RESULT IN 78-ASDGFKGJ / 78-SJDHFAJSDFH
THANKS SO MUCH
Hi
I have big problem and i have to find solution within one hour
I have numbers like this
"space"numbers"space"numbers"
I would like to cut the spaces
I know there is trim function but i'm pretty beginner, i read help but i can't still use it and trim will work only on first space?
I'd be very thankful for giving me some practical example or explane?
Thank's a lot!
I have a table that for some reason when I imported the data, placed some spaces before about 75% of the numbers in only one column. I need these numbers to be exactly the same as in another table because I use this number to compare to records and import other data depending on the corresponding numbers.
I tried doing a find and replace, but for some reason it doesn't find the spaces to replace. If I type in the space and the number, then in the replace with type only the number, it works. But I cannot do that since there are literally 10's of thousands of different numbers.
Someone mentioned a "LTRIM" command. Does this work within Access and if so, how does it work?
Thanks,
David
Hi,
I'm trying to run an update query on a field called "DisplayURL" to remove all of the spaces that the field contains. Some have 1 space, some 5 and some have no spaces.
I'm using Access 2000 and the replace function ( Replace("DisplayURL"," ","") or Replace([DisplayURL]," ",""). However it produces an error stating that there is an "Undefined function "Replace" in expression".
I've searched online and found that Access 2000 doesn't have this function, but its easy to create a "wrapper" to make it work.
I've tried various examples online with no luck. Does anyone know what "module" I'd have to create to get this working, and how to "call" the function as I do not understand how to do this?
Thanks in advance for your help, I've been having a nightmare with this problem.
Thanks again.
Dean
I need help. I'm trying to do an update query to delete extra spaces in a field
I want to Change 1234[space][space][space]Elm[space][space] St.
to
1234[space]Elm[space]St.
Thanks,
Vern
I'm trying to improve the dialer that i place on microsoft access forms. basically there's a command button that dials the number that is in a text box, but we have a new job that's just come in that requires numbers to be copied and pasted into the text box. these numbers have a space between the dialing code and the telephone number and i need to make the dialer check for a space and remove it before dialing.
Here's the code for the dialing buttons:-
Private Sub cmd_Dial_Click()
On Error GoTo Err_cmd_dial_Click
CT.MakeCall "8" & txt_telephone.Value & "#", "", False, "", "", False
[code]...
We use a Database which has been constantly developed over 10 years using earlier versions of Access2003.We have Upgraded to Access 2013 and we are experiencing many conversion issues which I need to resolve.One key problem is that many of the earlier tables were developed with spaces in their names (Hindsight is a wonderful thing?). There are over 200 Tables that need changing. I would like to update the Table names replacing every "space" with an underscore"_". AT the same time I would also need to Update all of the QUERIES that use these particular Tables to be updated to reflect these changes made and still work as normal. I can do this manually but it will be very time consuming and perhaps someone has already had to do this when recently upgrading to Access 2013?
View 9 Replies View RelatedGOAL - I would like to have one text box where I enter a sentence
txtKeywords: Access Is For Smart People Smarter Than I
I would like to create a button when clicked moves txtKeywords into another textbox [txtKeywordscombined] field and removes the spaces between the words and adds hyphens
txtKeywordscombined: Access-Is-For-Smart-People-Smarter-Than-I
Would I use the 'Replace' command in even procedures to do this? Another way? Example?
I have a table w/field set as text that I don't want users to enter a "space" into, how would I stop this? I suspect " " in the field validation field? Does this sould correct? What does everyone else use?
View 4 Replies View RelatedHi, I have a text box in a from for data entry, how do I disable a space. For example I don't want the user to have a space between charaters or before and after a charater. Thanks.
View 7 Replies View RelatedHi, I hope everyone is well.
As part of an extraction program I'm making, I've got a table (lets call it TABLE1) with values that have trailing spaces
e.g. "ENGINEERING ", "LOGISTICS ", etc....
This has been set up as such to match the values that exist in a read only table I am linking to from another database.
I have used a query of TABLE1 as a row source for a combo box. When the values are selected in the combo box the trailing spaces are lost.
Can I remedy this?
Thanks in advance :)
How can I remove the spaces in the example string!
HICX108-Standard Top -Vendered
Thanks in advance!
Hello,
I have the following SQL query that runs perfectly. Now I need to put some blank spaces in between for the first expresion (EXPR1). At the moment, as you can see, brings me the 4 fields together.
I have tried using + ' ' + and + " " + but it won't let me as it says that the syntax is Incorrect. What should I use to get spaces?
Thank you!
**********
SET @SQL = 'SELECT dbo.brnch.addr + dbo.brnch.suburb + dbo.brnch.state + dbo.brnch.PIN AS EXPR1, dbo.individuals.posit, dbo.individuals.phn, dbo.company.name
FROM dbo.company INNER JOIN
dbo.brnch ON dbo.company.ID = dbo.brnch.com_ID INNER JOIN
dbo.individuals ON dbo.brnch.br_ID = dbo.individuals.br_ID INNER JOIN
WHERE dbo.brnch.country in (' + @OrderList + ') '
EXEC(@SQL)
My database:
*TblNames
-NameId
-FirstName
-LastName
-UnitID
-AreaID
-RoomID
*TblUnitID (this table doesn't matter)
*TblAreaID (this table doesn't matter)
*TblRoomId
-RoomID
-RoomLabel
I hope you can follow me fine up to this point.
"think of this database as a hotel"
I need a report that looks like:
LastName FirstName Area RoomLabel
name1 name1 B 1
name2 name2 B 2
[empty] [empty] B 3
name3 name3 B 4
As you see not all the rooms are occupied therefor where you see [empty] a blank space must appear or something like [empty] so it indicates that the room is empty.
My problem is that when i do my query it will only show data that has all the fields requested for instance if I know the last name of a client but i don't know their first name, the query will not show this field. even tho there is a client using a room. I don't know how to explain my problem any better. any suggestions?
I have used spaces in the names of my tables, queries, forms and reports. I'm beginning to think this may have been a mistake, as I'm having trouble with syntax.
I have a form called Number of Words. This contains an option box, for which I always want option1 to be the default whenever the form is opened.
So I tried doing Forms!Number of Words!Option1.SetFocus but it didn't like the syntax. Do I perhaps need square brackets or something?
I have 2 seperate fields within a select query (In this example Field 1 - Country and Field 2 Number).
I have combined the 2 together into one field.
However a space appears within my results i.e
Field 1 Country Field 2 - Number
----------------- -------------------
England 4
USA 10
Combined fields:
Field 3 - Combined Country + Number
--------------------------------------------
England 4
USA 10
How can I remove this space within Field 3
Thanks
Hi,
I imported an Excel spreadsheet into Access. As you know, Access will automatically create 255 characters size for each 'text' datatype.
Now the issue is, when I create a query and I specify
Right([Field Name],3) I get no/empty results. I know my query syntax is correct, but the issue is, Access is picking up the right 3 character of the 255 field size. But in reality, my field's data are not 255 characters long; the max is probably 30 characters.
So, the question is, how do I go about picking up the 'actual' right 3 characters?
Please advise. Thanks!
Leon
Access 2000. I have table of many hundreds of records. One of th fields of text looks like this: XXXXXXXXXXXX and I want it to look like this: XXX XXX XXX XXX by adding the spaces. Would someone please show me how to write the function or code to add the spaces to the text as shown : Thanks :o
View 3 Replies View RelatedI have a db, that I used to maintain as a end user, now it's in a multiuser environment, problem is the user has to enter job numbers.
Below are all valid job numbers, therefore a mask will not work.
So I was thinking "HOW" could this be done, perhaps eliminating Spaces, underlines and dashes from this field. How would I do this on the same field for all 3 conditions (course i would have to backtrack)
01-AB134
02-091
2-2177
3-715
03-021-A
05-001-PBPL
07-07-955
I have tables that get their data from SQL server. Some of the fields in the table have trailing spaces. Does this issue need to be resolved in SQL server or in Access?
View 4 Replies View Relatedhi,
I want to put spaces in a table. With spaces i mean for example: i have the word "hallo" and i want it to be writen in the table as: "____h____a_____l_____o"
the _ are spaces. I have the string perfectly in a variable. But when i add it to the updatequery i get an syntax error on the string.
I used the following query:
cnn.Execute "UPDATE Minutes SET Minutes.MinutesID = " & strFinal & " WHERE (Minutes.MinutesCode)= " & [Forms]![MinutesMF]![itemlist].Column(4) & ";"
I have a query that I need to add trailing spaces to. I have a query that pulls the first and last name of our employees, but after the initial text, I need to add spaces up to 20. So the text of the first name plus spaces would be 20 characters and the last name in a different field would be last name plus spaces would add up to 20.
Thanks for your help.
Hi,
Does anyone know if its possible to have a page break/line space in a query's results?
For example, a query returns 5000 results, and after every 45 there is a space of 2 or 3 spaces. When I say "space" it could be a couple of empty records or rows.
The reason I ask is because I use a query to filter the records I want from my table. I then use a make table query to make a new table with these records, then I use a macro to export the table into a .txt file.
This text file is then used as a product feed, except the web application I use to import the data can only take a maximum of 50 records - so it'd be nice to already have a space so when I cut and paste the records into the web application I don't accidently try to import 51 records and mess everything up.
I know it's a strange one, but if theres anyway of setting the format of the .txt file to contain spaces would save a lot of time.
Any ideas would be welcomed, thanks for your time.
Dean
Right ive searched the forum and people are just asking silly questions that can be answered with an input mask but.
What im looking for is to be able to type a name for example in one textbox and the textbox will Capitalize the first Name and recognise the space key has been pressed and Capitalize the first letter of Second Name
Thanks for any help
Hi there,
I am currently working on a similar system that theme parks use as a 'fast pass' where the user an pre-book a place on a ride. I have only 5 tables consisting of a customer, card (membership card), session, reservation (composite table) and rides (park rides).
I need to make a reserve rides form which as user can reserve a place on a ride. When the user books it, a place from the rides session for that time is reduced by one...
Can anyone help me?
It would be greatly apprieciate it!
Noel