Table Select Field Shows Up As A Number To Text?
Sep 3, 2015
I have a KidShop table that shows the Diagnosis for the person,there are 5 diagnosis fields because one person could have up to 5 Diagnosis and each field is a lookup from the diagnosis table.in the report they show up as the ID number not and not the text.
I inserted Diagnosis from the Diagnosis Table and a window popped up asking what Diagnosis from the Diagnosis table matched in the KidShop Table and gave the selection of Diagnosis 1 or 3 or 3 or 4 or 5. I could only select one and I selected Diagnosis1 in the KidShop table and that showed Diagnosis as text for the Diagnosis1 field but the other 4 diagnosis fields still show a number. I understand what that did but it does not give me the option to match Diagnosis 2,3,4,5 to diagnosis Table. If I changed the Data source for the other 4 to Diagnosis then only Diagnosis1 shows up it the other 4 fields which makes since.
View Replies
ADVERTISEMENT
Dec 8, 2006
It's my understanding of general database practice that an Autonumber field should be used as a primary key in normalisation tables. I realise my understanding could be wrong, and I'm quite happy to change that practice if need be.
I have my normalisation tables. For the most part these consist of an Autonumber field ([UID]) as the primary key, then an indexed, no duplicates text field with the normalisation data. For this case, we'll say it's a list of departments ([DepartmentID]).
I have a form for entering users. Again, the PK is an autonumber field. I used a wizard to create a combo box. I selected the fact that I want to store the value in [DepartmentID], NOT [UID].
This works fine, I can add my users and the combo boxes work, although I had to change the bound column to 2, as 1 is selected by default although it is [UID].
I then created a subform based on a query of all users. This subform is not bound to the main form in any way, it simply shows the result of the query.
When entering users, upon saving a record, the form is updated and the new user shown in the subform. Great, apart from "txtDepartment" in the subform showing [UID], and not [DepartmentID] as stipulated.
I have a horrible feeling this is to do with [UID] being my PK, but I'd like someone to tell me I'm wrong. I can quite happily remove the autonumber field [UID] and make the unique [DepartmentID] field the PK, but this just doesn't seem "correct" to me.
Should I do this? Should I have an autonumber field as the PK?
I realise this is more a design theory question than a form problem as such, but anything that clarifies my understanding of correct design practice will help.
EDIT:- I'd also like to say that I did it this way to get away from using the "LOOKUP" field type at table design time, specifically to avoid this very problem of numbers showing up when I want the text there!
View 3 Replies
View Related
Oct 21, 2012
I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.
My Question is there a way to convert it without loosing all my data, as there are over 5000 records?
My Table with the multivalue field is called "Contacts", the field is called "Groups".
I tried update and append queries but I must be doing something wrong....
View 2 Replies
View Related
Nov 26, 2004
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
View 6 Replies
View Related
Oct 1, 2014
I have a form with a search box, Its UNBOUND called ID . Everytime I search for an ID number it shows no results, However I know there are entries in the table. This is the code I am using :
Code:
Private Sub ID_AfterUpdate()
If (ID & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Id] LIKE ' " & ID & " ' "
[Code] ....
I am assuming I need to add something to search for auto numbers but i don't know what!
View 2 Replies
View Related
Feb 13, 2006
Hi all, i have a 2 fields in a subform named "HRS_ABSENT" & "ABSENCE_REASON" i'm trying to create some code that will display a message if the user inputs any number into the "HRS_ABSENT" field & leaves the "ABSENCE_REASON" field empty. I want to force the user to select a ABSENCE RESON (these are 3 letter codes) from the drop down list, if they enter a number in the HRS ABSENT field. Ive tried the below code but it doesnt do anything :-(
Anybody please help me out?
-------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If HRS_ABSENT = >0 & ABSENCE_REASON = FALSE Then
MsgBox "Please select an Absence reason"
Cancel = True
End If
View 2 Replies
View Related
Nov 7, 2013
I am opening an excel file from access and changing the formatting from "dd/mm/yyyy hh:mm" to number with 15 decimal places.Then I am linking the file to the database and subsequently doing a lookup on the date time on the access form against this table.The excel file when formatted from vba in access shows the wrong number
datetime = 03/05/2013 11:26
database number = 41397.4763888889
Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)
now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.Also I have tested the code with manually editing the excel file and this works fine.
Code:
Private Sub Command288_Click()
Dim s As String
Dim t As Integer
Dim ws As Worksheet
s = LaunchCD(Me)
MsgBox (s)
[code]....
also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows.
Code:
Private Sub LoadData_Click()
Dim w As String
w = Forms![frmList1]![Combo0]
w = "[" & w & "]"
Dim dtA As String
[code]....
View 7 Replies
View Related
Oct 3, 2006
I need to convert my text data to a number but when I convert using the VALUE function or use "format cells" to the numbers category, I loose the leading zeros. I need to keep them for sorting purposes.
What formula do I use?
View 6 Replies
View Related
Jul 31, 2014
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
View 4 Replies
View Related
Nov 27, 2005
I'm new to access. Hope someone can offer some help!
I have created a simple database to better organise some day-to-day office data management. For instance, I have a form (just) to record event attended by staff. The data would then be output as a brief report on overall office activities. Since it's event-oriented, staff attendance is just one complementary information. I went through most of the post re multi-select. I understand that it may not be a good idea to return values from multi-select list boxes into a text field, as it would be problematic for using data-analysis in the future.
In my case, I just need to have a text box, neatly (and easy for user, too) shows who attended. As suggested in one of the posting, I follow the instruction of http://support.microsoft.com/default.aspx?scid=kb;en-us;210203. But it doesn't seem working. I attach my databsae (sorry, it's very simple, as I want to test whether the multi-select work).
Your help would be greatly appr:confused: :confused: :confused: ciate!
Also, what about if I group option button, will it allow multiple return by the way?
delamerbleu
View 2 Replies
View Related
Nov 18, 2005
As the title says, i have an identifier field which combines two codes eg. AAAAAA/1234.
I am using a query to extract the number part, however when i try to link to another query it says 'type mismatch'.
I assume that this is because the main query is based on a table where [ShipID] is numeric and the extracted data is based upon a underlying table where [PackageID] is a text field.
Is there any way to get round this?
Many thanks
View 2 Replies
View Related
Feb 28, 2007
Hi,
I have a text field in a table and need to convert it in a query to a number field for sorting purposes, but not have it change in the table.
Thanks,
Jeff
View 3 Replies
View Related
Aug 22, 2012
Is it possible to have a combo box that shows date and text? I would like to have a combo box with dates that also allows the user to choose the word "unassigned." the reason for this is because sometimes we don't know dates due until a future time and that is specifically what my boss wants. They don't want the field empty either.
View 2 Replies
View Related
Jul 14, 2005
I'd like to take a branch number field, let's assume it is "223" and change it to "223 No Com". Is there a way to add the "No Com" on the end of it like that?
View 5 Replies
View Related
Jan 7, 2013
I randomly read on another website that your field type in a table should be 'text' unless you're doing math on the number'. Is this true?
View 14 Replies
View Related
Jun 4, 2013
I'm scanning serial numbers in a field, but the results always start with an S...like S123456789 , S107432567 etc.. how can I eliminate the S and make this field numeric?Access 2007 being used.
View 4 Replies
View Related
Jun 18, 2015
I want to find a way to grab the text from the window that shows the location of the file using vba in Access.
View 3 Replies
View Related
Jun 27, 2013
I have a number field in my subform that's autoupdated but I'd like it to display "weeks" beside the number without trying to store it, is this possible?
View 2 Replies
View Related
Jun 6, 2014
I am trying to run graph on a report from query but what's happening is query shows name but report on graph shows ID'S of the field .
Below is my query
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
GROUP BY tblMainTWTTPSheet.txtRootCause, tblMainTWTTPSheet.Date
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null) AND ((tblMainTWTTPSheet.Date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]));
My bound column on main menu form and back end table is 1
column count 2
column width 0;1
View 1 Replies
View Related
Jul 5, 2007
Hi,
Can anybody help me ?
I've a case where ms access displays oracle's numeric field as text where msaccess is linked to oracle.The possible reason could be when we don't specify precision in declaring the filed in oracle table, default precision is 38 which ms access cannot hold. So, it(driver) converts this to text field.But this is with MSORCL32.dll(microsoft driver for oracle).Is there any other possible reason that will result this behavior?
The following are the configuration details:
1.Oracle 9i on Unix server.
2.MSAccess on Windows XP.
MSAccess holds linked data of Oracle server.
Thanks Inadvance.
Ramesh
View 4 Replies
View Related
Nov 13, 2014
I have an existing form where users type in information and it generates a couple of reports. In one of the fields, Customer PO Number, the user enters a number from a customer. Up until yesterday all of the customers we have been dealing with have used numbers only for their PO numbers. However, we have a new customer that requires alphanumeric PO's. Is there a simple way to change this field from a number to alphanumeric without having to redo each form, report and/or query. I am using MS Access 2010.
View 9 Replies
View Related
May 24, 2005
I have a table where my primary Key is of a text type, the key is made up of a single letter then a number... ie.
R1
R2
R3
...
R30
R31
..R100
etc.
This is an indefinate table and will go as high as I need to
Unfortunately I can't sort this table because if its mixture of both text and numerical characters, can anyone provide any assistance?
Thanks!
View 5 Replies
View Related
Apr 6, 2006
I have a text field I need to convert to numbers. There are both empty and non empty fields. I then need to show the numbers like this "1.234" and a "*" in the empty fields.
I have tried to convert the fields with Cint, but I get an #Error in the empty fields.
Is there any way to solve this??
Thanks in advance..
View 6 Replies
View Related
Feb 24, 2014
I just started my project database on my subject ITM4. My Database is entitled PCExpress Inventory System. I would like to know how to input (Text) in a Field where the Data Type is (Number) just like the North Wind.
View 3 Replies
View Related
Jul 10, 2012
I have
Table called "Products"
Field 1= "Product ID" which is a text field (PK) but numbers are used (ie 1 -20)
Field 2= Products -showing our list of 20 products
When I enter a new product, currently I have to look in the table to find the last ID used then use the next one available. I have created a form to be used for data entry to enter new products
What I am trying to do is :- 1, have the form open at data entry level but still able to scroll and see all records and 2, Have the form auto generate the next number available. For example, I have 20 products entered so when the form opens to enter a new product, the ID is automatically at number 21.
I wondered if its because the field is a text field or I am trying to insert the code in the wrong place.
View 1 Replies
View Related
Jul 14, 2014
Is there a way to create a text default value for a control box that is bound to a number field?
I have a combo box that is bound to an auto number ID but displays text. Bound column = 1, Number of column = 2, Column width = 0; 4cm
I want to use sample text in all my controls (and I know an alternative would be to use the control tip text). In a text box it is easy, I just set the default value to that value and then a before_update event considers the text box empty if the field value is the default value (e.g. Name text box has: Name... as default value). This doesn't work with my combo box since the bound column is a number. I could create a value in the table but then it would appear in the drop box and it is messy.
[URL] .....
View 14 Replies
View Related
