I 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)
I have a very large database with record that are from 3 to 11 characters long. I would like to run a query to inset dashes at position 5 and 9 so a part number would look like this 0000-000-0000 or following the same convention but never dashes as the last character this is what i currently have UPDATE stihl SET [part number] = left([part number], 4)& "-" & mid ([part number], 5); then run a second as follows
I know this is probably the dumbest thing to ask, but I am just not getting it. I'm tryin to run a Query, and I assume it's a SELECT query for Last Name, First Name, SSN. The tbl containing this information [tbl_Employees] automatically places the dashes in the SSN through an input mask. However for one type of report I will later need to generate, the SSN cannot contain the dashes. What is the criteria for this?!? I am so lost! I know it's probably something so simple that I'm just overlooking it, but I'm clueless. Can someone help me out? Thanks so much! :)
I have a database with a series of fields, all set to a String datatype, that I want to be able to fill and then use with MS Word as a mail merge data source. This function lets you query a MS Access db and use the results to fill in predefined spots attached to the various data values in the table. This is great for my needs however a large percentage of times that I have a dash in the value being queried it does not return any results. For example, 01-01 would return no entry found even though I can clearly see it in the database, however it would work fine if I queried for 0101 or any other value in the given field.
Basically I'm trying to figure out why dashes would cause problems. They are obviously fine for being stored in the access database and don't cause any troubles until they are queried for, also retrieving data containing dashes is fine so long as that value is not the one being queried for.
I basically can't avoid the dashes in the data for this setup and as such hopefully can figure out why this happens and how to get around it. Thanks for any help offered.
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?
Hi, 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.
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.
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]
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
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?
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?
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
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?
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?
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.
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.
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
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...
