I have a user input form with a text box that stores a number. When the user inputs the number, I'd like it to display the comma separator (e.g. "1234" becomes "1,234". I've tried putting "#,###" and "#,##0." into the format property of the text box, but I still get 1234.
View RepliesAny way to import a file containing numbers with a "thousand space separator"
example :
1 000
2 000
3 000
4 000 000
5 000 000 000
Only workaround I found so far is to import de numbers into a text field in a temp table, do an update query to remove the spaces and then transfert the data into the real table that has a numeric field.
I needed to change the decimal symbol from a dot to comma for use in Europe. On changing the the settings to a comma everything works fine but for the field that I have the code in the AfterUpdate properties
Private Sub SprayCfuelC_AfterUpdate()
Me.Dirty = False
CurrentDb.Execute ("UPDATE ProcessData SET ProcessData.SprayCFuelC =" & Me.SprayCfuelC & " WHERE (((ProcessData.AuditNo)=" & Parent.[AuditNo] & "));")
Me.Recalc
End Sub
Now when the data is changed I get a run time error 3144 - syntax error in UPDATE statement.
I have a table with an auto number primary key field, and then several other fields, Address, Unit, Owner. I created a form with a combo box that looks up the Address from another table and populates the three fields when you click a record. I have it set to store the first value, (Address) in the table.
When I open the table, it appears as the primary key number, and not the address. It's very frustrating, as this happens a lot to me. What am I doing wrong? and how can I store the value I want, (Address) and not the primary key number?
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....
View 11 Replies View RelatedI have created 3 forms, 1 of which will update the sum of 5 text boxes immediately after numbers are entered in, while on the other 2 forms I had to create a refresh button so that the numbers will add up. The properties in all of the forms and the text boxes seem to be the exact same.
View 6 Replies View RelatedOk so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
View 1 Replies View RelatedI was wondering if there is a way I can store a formatted text in a field in Ms Access. I know memo type can contain a very big string upto approximately 65k but formatting is not possible in that type. Regarding the OLE object, I am not sure if I have to create a word document and then store it as a OLE object or is there a way to do that without creating the word doc.?
I do not want to create a word document for each record in the table. Is there a better way?
Please advise. Thanks
I created a button that when pressed concatenates any items selected in a list box and places them in a text box on the form with the format of "itemselected1" or "itemselected2" or "itemselected3"
However, I need to set the criteria in a query to look at this text box and return the results based on that selection. When I do that I get no results even is the query should run fine with that type of format.
Attached is a copy of the Code for the button and the result
Dim txtValue As String
Dim varItem As Variant
Dim strlnameselect As String
Dim intCount as Integer
'Cycle through selected rows in listbox
For Each varItem In Me.Queueselect.ItemsSelected
intCount = intCount + 1
Select Case Len(txtValue)
Case 0
txtValue = Chr(34) & Me.Queueselect.ItemData(varItem)
Case Else
txtValue = txtValue & Chr(34) & " Or " & Chr(34) & Me.Queueselect.ItemData(varItem)
End Select
If intCount=Me.Queueselect.ItemsSelected.Count Then
txtValue = txtValue & Chr(34)
End If
Next
'Assign variable value to textbox
Me.Queuetorun.Value = txtValue
End Sub
the result in the text box is
"A" or "B" or "C" or "D"
I've implemented on a form a Command Button that opens up a dialog box to allow the user to find a photo and once the photo is selected, it stores the location in a text box, (not the photo, just the location). I need to be able to allow multiple photo's to be store in the same text box. That was one of the benefits of using an Attachment data type but based on lots of comments...
View 3 Replies View RelatedHello,
The title says it. I want to store 500KB text data into a memo field. How can I do that?
Someone told me there is no limit on the size of a memo field, up to the maximum for a .mdb file of 2GB!!!
Your help would be greatly appeciated.
Hi,
As a part of reducing traffic on file server, I am planning to read less record on the form and hence I tried removing forms record source. (Attached sample db)
I kept all unbound textboxes onto the forms and save new record to the table using codes back of the command button. This is working great with main form. But doesn’t with sub form. I tried removing record source of sub form and keeping unbound text boxes to appear record if it exists.
If you enter 1 in InvNum text box on main form, you will find it brings record. I don’t want to show the record on the sub form unless I call it from main form.
How can I use the same trick of unbound text boxes with sub form too?
Please extend your help.
With kind regards,
Ashfaque
We have a database that we use to track the tablets in our organization, a combination of Apple, Android, and Windows.
We label them with a hostname depending on their branding. Example below.:
APPLE01
APPLE02
APPLE03
ANDROID01
ANDROID02
ANDROID03
WINDOWS01
WINDOWS02
In the database we use the field name HOSTNAME for these entries. When sorting the field, due to it being a text field, we get the traditional sort of
APPLE01
APPLE02
APPLE10
APPLE100
APPLE101
APPLE11
APPLE110
ANDROID01
ANDROID02
ANDROID10
ANDROID100
ANDROID101
ANDROID11
ANDROID110
We would like to have them show up as
APPLE01
APPLE02
APPLE10
APPLE11
APPLE100
APPLE101
APPLE110
ANDROID01
ANDROID02
ANDROID10
ANDROID11
ANDROID100
ANDROID101
ANDROID110
What is the best way to tackle this?
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
I was wondering if there is a way of only allowing text in a text field - we need to do this for validation purposes when inporting a text file into a database. I have tried input mask but this only checks when entering new data - I have tried to use validation rule like"???????" however this means that there has to be 7 letters in each field and this is not the case. - the field is for town and a couple of the records in this field have numbers at the end which are incorrect that we want the database to reject :eek:
View 1 Replies View RelatedThe above image is of a table which I need to update according to a new data on daily basis. As you can see when I added two records at the last , the AutoNumber primary key of the table jumped by thousands . I have used the following query to update the table
Code:
db.Execute "INSERT INTO D_Counterparty (CPTY_ENTITY_ID,CPTY_DESC)"_ & "SELECT Ctpy_Entity_Id,Ctpy_Entity_Legal_Nm"_ & "FROM NewCU LEFT JOIN D_Counterparty ON D_Counterparty.CPTY_ENTITY_ID=NewCU.Ctpy_Entity_Id"
I never changed the AutoNumber from incremental to random and I have also tried re-seeding it but had no success. Also when I inserted the sample values using a sample table ,the AutoNumber was working fine . Here's that query
Code:
db.Execute "INSERT INTO D_Counterparty (CPTY_ENTITY_ID,CPTY_DESC,) SELECT a,b, FROM sample"
I did not use join in this query ...
I'm working on a report in Access 2013 and I have a multivalued field that stores employees names in the format "Last, First" and when I put it on my report it displays all the names as "Last1, First1, Last2, First2, Last3, First3" and so on. That's a lot of commas and it was suggested to me by my boss that I change access to use semicolons between the employees as he would like to keep the names as Last, First.
View 7 Replies View RelatedI have a lot of dates in one column (dd/mm/yy), and i somehow need to duplicate them into another column where they are shown without the date separator (ddmmyy), so that i can use that figure in a future calculation where a variety of fields are combined to show one unique product number.
View 7 Replies View RelatedI used to use commas as a parameter separator, but for some reason it is now a semicolon. How can I change it back to a comma?
Currently this works:
Code:
SELECT IIF(MyExpression; TrueThing; FalseThing)
FROM ...
Want to use (and have used before):
Code:
SELECT IIF(MyExpression, TrueThing, FalseThing)
FROM ...
We have a new contractual requirement to track all time spent on Military contracts and report it to our Prime Contractor annually.
I am trying to put into Access 2010, basically the same functionality that I have built into Excel time sheets for years; that is simple entry of time in military time using 3 or 4 keystrokes depending on the time of day.
I have a table which records an employee's employee number and job that he or she is working on. I have formatted fields to short for Start Time and Stop Time, and I have a Calculated field also formatted short for elapsed time, however I need to have it simplified to where there is no colon separator on key entry in the form to 700 for 0700 hrs military and 1317 for 1:17 PM.
I will be using this database on several computers, but only a couple that will be used for time entry.
The Excel spreadsheets work well, but I have found them cumbersome trying to dump all the information into a Master spreadsheet from several different individual spreadsheets and I am using Access as our ERP so it will save time if I just add a simple form to track time from there putting it in the hands of the Repairmen and save someone from data entry late.
I wondered if someone could help.
I am using the Val () Function to convert a text field to number within a query which still gives me a text output.
P.S I do not have permissions to change within table as using Access as a front end to SQL via link Tables.
Hello, I have a Field in a Table called Fund Codes and they are entered as text: 1, 2, 3, 4 all the way to 1000. Now i need to find away to change 1 to 001, 2 to 002, 3 to 003, 10 to 010, but leave 100 as 100 and 222 as 222, so only add 0s to numbers from 1 to 99. Is there a way to do it? I know i can change the text to numbers and use format to do that, but i need to leave it as text. Please help. Many thanks!
View 11 Replies View RelatedHI,
I have a field that contains mainly numbers, but the data type is set to Text because there are a few records where the numbers have a letter suffix.
Eg. Invoice numbers 100, 101, 102, 102A, 103 etc.
These suffixes are a result of poor data entry when the data was stored in Excel.
What I was to be able to do is sort this field and treat all entities as numbers. Currently when I sort it, it shows:
1
10
11
2
20
21
3
...etc
Is this possible, even though the data type is Text?
Is it possible to have text and numbers in one field as access asks you specify the data type of that field?
View 1 Replies View RelatedOne Query cannot store 2 different textbox in different forms:
Query Name: rem
Form Name: QR
Form Name: Pro
Query parameter: Forms!QR!txtbox1
Forms!Pro!txtbox2
I tried it, it cannot run.
If you run the form "Pro" and run the query rem, then you will get the message:
Forms!QR!txtbox1?????
same thing if you run the form QR and the query rem
I have form with student details on it and I want to add some additional information against that student (stored in a seperate table). I've created a form to input the data and a button on the main form to open the new form.
On loading the form I've brought with it the name of the student but I also want the UPN. I've done all the above fine.
What I need to do is then store the UPN in a field in the new table which is where I'm stuck.
What do I put in the Control source to make it store it. Currently I just have
=[Forms]![PupilPremiumStudents]![txtUPN]