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, 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 :)
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 am creating a csv in another application where fields may contain trailing spaces. If I open this in excel, the trailing spaces are preserved, but if I import into Access I lose the trailing spaces. Is there any way to preserve them?
Thanks,
Dave.
I have 4 fields I'm trying to combine, but I need to add spaces between field 1 and the rest of them. The total character count needs to be 22 including the spaces.
Example:
Field 1: THE
Field 2: 1234
Field 3: BOAT
Field 4: 0001
End Result: THE 1234BOAT0001
Need to add 7 spaces to equal 22 characters.
Fields 1, 2, and 3 can vary in number of characters.
I am creating my first form and a field that holds a price is showing that as 000000.00 I would like to eliminate trailing zeros and can find nowhere in a properties to do that in form properties.
View 2 Replies View RelatedFor some reason, whenever I export a query to text, every number has trailing zeroes after it. This happens with any number, even integers.
Examples:
1.000000000
5.250000000
0.600000000
Nine decimal places would be fine if the number extended that far, but I want to get rid of the trailing zeros so that 1.0000000 becomes 1, and 5.2500000 becomes 5.25, etc.
Now, I have noticed that a setting in the Control Panel > Region and Language > Formats Tab > Additional Settings > Numbers Tab controls the numbers of decimal places, but if I adjust it to two or zero it just cuts the data off at that point.
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 RelatedHow can I remove the spaces in the example string!
HICX108-Standard Top -Vendered
Thanks in advance!
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
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)
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
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
hi,
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) & ";"
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!
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
I am having the table in access database which contains customers name with and without leading spaces and the spaces after customer name.
How to find out Leading spaces and spaces after customer name in access Database.
I have a field in a database that needs the data entered without any spaces and I'm trying to figure out how to set either the validation rules or the masks so that the system will either automatically delete any spaces or warn the user not to enter them.
My preference would be for them to automatically be deleted.
Any ideas, suggestions?
I can figure out how to make either all numbers or all letters, but the data is both so that doesn't work.
Thanks, in advance, for your help.
Charis