Forms :: Comma Separated Numbers In One Form Field
Apr 28, 2013
I've current got two tables, one with Products and the other with Suppliers. The products table has a column in which I may indicate Supplier IDs that carry the product. I'd like to make a report that groups the Products by the Supplier that carries them and I'm having some trouble. The way I see this working is to relate the Supplier ID found in the Product table with the auto generated Supplier ID in the supplier table.
My trouble is this, when the field is set to text I'm able to enter values separated by commas no problem but I can't report on them because I'd be relating a number to a string. How may I overcome this?
I have a single 'text' box on a form that captures test scores. The person inputting the test scores puts in all of the scores separated by commas (77, 85, 100, 75.5).. Up to 6 test scores can be placed in this single text box. The field is formatted for text. What I propose to do is take the numbers in the text box and average them out and then display the test scores average in a number field named 'avescores' next to this field in the form. Once the inputer puts the series of scores into the text box, they would hit an 'update' button next to this box to 'update' the 'avescores' box. Right now they use a number pad and add up the numbers manually then place the average into the avescores box....
If I have a text box (Text1) on my form with comma separated values entered i.e. 100,120,250,300 what would be the easiest way to enter these into a table column with a button click event. I would like to enter these values into my table tb_test under column Values.
I would like update a table column values separated by ; For example
1. ColumA has no data, I want to do an update and place a comma at the end 2. If ColumA has already value do not delete add new valued at the end where ; if found.
My start attempt as follow:
Code: qry_test= "UPDATE Table1 SET ColumA =" & "Enter Entered ;" & & _ " WHERE ID=" & 1 Set RS = cnnDB.Execute(qry_test)
What do I need to do to add more stuff where ; is found without delete existing value if values exist.
I use Access 2003. I am trying to populate a list box with the names of files found in specific folder in my computer. The problem is that if the file name has a comma (,) in it, so the name is being separated in two parts - first what is before a comma and second what is after. I need them exactly as they appear in directory in order to open the files from this list box.
I have an access table for SampleTests. Each sample may have multiple tests carried out on it. Each test is one record in the access table and a sample may have multiple records therefore depending on how many tests are to be carried out on this sample. I need to create a query that would have sampleID as one of the fields which it gets from the SampleTests table and then I need the second column to have all the tests associated with that sample ID in the format test1,test2, etc. but I cannot see how I can get access to put these records together separated by a comma.
I created a multivalue list in my table. It contains a list of names and a checkbox. I designed the form based on this and am able to check off multiple names. When I go back to look at the table, it stores in each name separated by a comma. So far so good.
When I create a report to display the data, it displays it as 1,2,3..etc., instead of the names separated by a comma.
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code: Option Compare Database Option Explicit Private Sub Form_Current() Dim oItem As Variant Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
I have inherited a database where technicians track equipment that has been repaired.In this database, there is a memo field where the technician lists all the part numbers he / she uses to repair the piece of equipment. This field was never reported against and was strictly used for reference. However, someone is coming to me now and asking that a report be generated with that information. Then she will separate those parts out on her end. because it is a free form field, the technician is not forced to put a comma between each part number. I was wondering if there is a way for the database to go through approximately 62,000 records and ensure that there is a comma between each part number? I was also wondering if there is way to program the database to automatically insert a comma after each part number.
I'm trying to turn any period entered into a certain textfield on my form into a comma (to prevent access from turning 1.1 into 11 for example).
I have the on dirty event set to me.fieldname = replace(me.fieldname,".",",").
Everything works fine as long as I use the period key on the numpad. However, when I use the regular period key on the keyboard, it does not convert it.
I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:
[Field1]&", "&[Field2]&", "&[Field3] and so on.
However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?
I have a field in a table that I'd like to extra the texts from. The are stored in below format (separated by colons). They don't always the same amount of characters and not all of them have the same amount of texts.
Some may only have Text1:Text2::, while some may have Text1:::Text4.
I've got a form where the user can only enter the character asterisk or numbers. No letters or other special characters allowed.
The asterisk is a problem since its a wildcard. How do I allow the user to use it or numbers only? I've tried Validation Rules, I've tried vba but I don't seem to manage to get this to work properly.
I have a csv file which seperates each field with a comma. Now i have some fields that contain comma's within them but they are enclose in quotation marks. How would i import it so that it doesnt seperate these fields?
I am using Access 2013 and have created a music inventory database.I have a question about formatting some text in a table. One of my tables has the following fields:
tblArtist Artist ID DVD Number Artist Last Name Artist First Name Artist Full Name
In my form, I want the artist's name to appear like this:
Mozart, Wolfgang Dylan, Bob Beatles Aerosmith
I know how to format the table to do this, and I know how to construct the form for this. To format the name, in the tblArtist table, I use the following expression in the Artist Full Name field:[Artist Last Name] & ", " & [Artist First Name]
If an artist has only one name, such as Aerosmith, I enter it in the Artist Last Name column. Artists such as The Beatles or The Rolling Stones, I just omit "The."When a one-name artist's full name appears in the form, there is a comma after their name.
Is there a simple way to eliminate the comma if there is no text in the Artist First Name column? If so, what is the simplest, easiest, most practical novice-friendly way to do this?
show the total/sum of the input fields (unbound form).I tried sum function in control source for "Total working day", but I am getting #Error =sum ([working daysperiod1][workingdaysperiod2]...)
I used plus feature; like we do in Excel =[workingdaysperiod1]+[workingdaysperiod2]...... the answer I am getting 22192120.....
Period; Month; Working days 1 - Jan - 22 2 - Feb - 19 3 - Mar - 21 4 - Apr - 20
I have one field containing a couple of 6 digit codes and would like to count the number of codes in the field and write the number to a calculated field. Each code is separated by a comma and a space. How can I count the no of comma within the field?
With microsofts article, I have made to store multi options value of a list box in a text box with comma. However, since these are IDs being stored, I want these values to run a query and get results also.
But I am confused since have never used comma like in query
I have a database I am using to record financial transactions. I have a transaction edit/entry form that uses combo box lists to select the different segments of my 32 digit account numbers. The issue I am running into is that when I enter a value value in the first box/field the form jumps to the very first record.I then can use the navigation buttons to get back to the last record, and all the subsequent boxes/fields work fine without jumping to a different record.
