but the data type of these colums doesn't end up being text but instead MEMO
i then run into problems later on designing a query which trys to do a join on DupeCode
any idea as to why my text fields end up as memo?
they get set later on from queries
If Me.cboProgram.Column(0) < 3 Then
strSQL = "UPDATE TBL_TmpSubmission SET EnergyUnit = 'GWh'"
Else
strSQL = "UPDATE TBL_TmpSubmission SET EnergyUnit = 'CarbonTonne'"
End If
CurrentProject.Connection.Execute strSQL
strSQL = "UPDATE TBL_TmpSubmission SET DupeCode = [Measure]&[NumberOrName]&[Postcode];"
CurrentProject.Connection.Execute strSQL
I have a table with a field with names set to text data type and i want to change it to number data type but when i do it in design view the data get lost. I want to know if there is a way to convert the data in the field as number type and keep the data in the field.
I have a field in a table that is comprised of mostly numerical data but some records are text.
I want to convert this field to numerical only and make a new field to put the textual data in.
However converting the field will delete the textual data. What is the easiest way to convert the field but save the textual data AND append the textual data to the SAME record that they were in originally in the new field?
I’m not an expert in Access and hope that someone can help me with my problem. I have about 20 fields of Yes/No data type.
E.g. StudentID- Tex Science – Yes/No Math – Yes/No Biology – Yes/No Chemistry – Yes/No Economics – Yes/No ...
I would like to create a parameter query (without using form combo box) where when I run the query, it would prompt me for the subject name. Let say I keyed in Science, it would list out all the StudentsID who took up Science only (with a Yes) and the other subjects.
I want to input data number such as 0.5 in my table, but it doesn't work. I already fill field size : integer with format : Standard with Decimal : 2, but the result is always 0.00 not 0.50 as my expectation. How to define that in my table?
When exporting a table to excel is there anyway to presever the data structure - ie a text field of length 30 will only take text length 30, Interger would only take integer and date would only take date etc, etc ,etc.
Hi, Trying to get back into Access after a long time, just need some refreshing :) tips.
I have 2 tables, one of the tables stores titles (mr, mrs, etc). I tried using the 'lookup' data type to link the data from the other table. This however stores the field as a number and causes problems on my form as only a number is shown rather that the text.
I have a rich text editor that I want to use to post HTML to an access database field. The average post will probably be four hundred or more characters. What is the best Data Type to use for this. I am looking at going with Memo but I want to be sure.
How can format proper display for percentage values. What happens is I chose a field as percent, but if I put in 9 meaning 9 % I get 900% as the value is multiplied by 100, but if I put in 0.09 I get 0%. I just want to put in day 9 and be saved as 9%.
hello people, i wanted to add a string at the end of a long column of Currency. Is there any to to change only the last field to a string or do i have to change the whole column just for the last field?
I'm using Access 2002 and need to increase some field sizes, some by increasing the maximum number of characters and others by changing from Text to Memo. When I try to do this I get the message "Microsoft Access cannot change the data type. There isn't enough disk space or memory"
I have two 320GB hard drives and 4GB of RAM and the Access file is a mere 280MB. Can anyone please explain the problem and tell me how to achieve my objective?
I have a DB that was given to me with a SSN field that has the Number Data Type. Therefore, if the SSN has a zero as the first number, it is not coming up. Other than changing data type to Text, is there a property that would keep the initial zero? Thank you for your assistance.
I have inherited a table where the data type is binary in some cases and I want to change these to Numerical. When I do this in design, I get a message which says I can't do that. I have something like 200,000 records.
If I try to export as Text file I get Invalid Argument message coming up. Can anyone help?
i am creating a database that will include four (4) tables that need to have an autonumber as a primary key, but i need that autonumber to be preceded by a letter (one letter for each table). i am creating forms for the users to based on these tables for users to enter data. please help...i'm running short on time. thanx!
the database i'm creating will be used to store city issued permits. there a 4 different types of permits that can be issued, some w/the same info (i.e. contractor, owner) and others with different data (certain permits are only issued for a specific # of days, others require a tap or meter size, etc.). i have separate tables for the contractors, owenrs, and payments, but each payment needs to be associated with a particular permit #. for example: check #3 from john doe contracting is associated with permit # m123, or h123, etc. that's why i decided to have a separate table for each permit type. i was attempting to avoid data redundancy. i also need separate forms for the users to enter data into as per their request. i'm open to suggestion if you have some other ideas. thanx in advance. :confused:
I have a table that i need to be able to store text that may be over 255 characters long. I have changed the fields datatype setting to memo as I thought this was able to show Up to 65,535 characters. But the data is still being trimmed to 255 characters. I have looked in Access help at data types and it states that the memo field length is 65,535 characters
"if manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database."
My question is?
How do I know if it is manipulated by DAO
If it is not manipulated by DAO how can I change it (if possible) as I really need to full data to be retained
Hello everyone, I am buiding up a database for activity school. Here first step of buiding the base, I got an attendance data type problem. i am now using "yes/no", but if i want to query about the number of student each class, is that possible?
I am using Access 2007 and I am trying to create an attachemnt type field in a table. Yet when I select the types of Data Types, attachement is not an option. Can you possibly tell me why?
OK, i'm getting a type mismatch. I've tried searching for that and read a lot of posts, but none hit the nail on the head.
I know where it is. I am trying to link two table by using a field which is a string in one to link to a field which is a number of some kind in the other. I can;t change the formats of these as it's a query i'm running on a database i don't control. I understand I need to convert the format of one of the fields in a query and then link using that, but when i try to reformat the number it doesn't do what i'm expecting after reading the help.
in the help it suggest using this: Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
So i'm trying to use that to reformat the string field to a number so i can use that query.
Numbers: Use predefined named numeric formats or create user-defined numeric formats: This is where i fail completely!! it just rejects everything i write in my query.
I have a query to go through a table and pick up the amount of money earnt during a period then I use that field to calculate commission, but the commission field just shows up as numbers and not as currency as the set field in the table does. Is there anyway in SQL to say what sort of data type a field should be... Example of sql string
SELECT Sum([Work].[Earnings]) AS [Period Earnings], [Period Earnings]*(17.5/100) AS [Commisssion] FROM Policy
Ive been working at this problem for a while now and I can't figure it out. I have a table full of Work Orders, and on the form for inputing the Work Order data, there is a field for the date that the WO was received, and the date it is wanted. There is also a field that tells if this was a rush job (Yes/No) field.
I need to make a query that will grab all the rush jobs, and sort by how many days were between the date received and date wanted. This would be easy, just subtracting the dates, but I need to have it not include weekends and holidays. I found an algorithm to do that and it seems to work well. However, when I run the query, it gives me a "Data type mismatch in criteria expression." error. At this point, to debug it, I put in a message box to see if it processes any entries. It does process them one at a time, but once it gets to a certain point I get that error. I have checked all the dates, making sure they are all valid and they are. I'm just really stumped here. Here is the code for my query:
SELECT [Usable Orders].[AI Number], [Usable Orders].[District Name], [Usable Orders].[CCM Name], [Usable Orders].[8255 Received], [Usable Orders].[Date Wanted], [Usable Orders].Rush, WorkingDays2([8255 Received],[Date Wanted],[AI Number]) AS [Working Days] FROM [Usable Orders] GROUP BY [Usable Orders].[AI Number], [Usable Orders].[District Name], [Usable Orders].[CCM Name], [Usable Orders].[8255 Received], [Usable Orders].[Date Wanted], [Usable Orders].Rush HAVING ((([Usable Orders].Rush)="Yes") AND ((WorkingDays2([8255 Received],[Date Wanted],[AI Number]))<=10));
and here is the code for my WorkingDays2 (counts all working days excluding holidays) the AINumber field I put in for debugging purposes to see which entries were being processed.
Public Function WorkingDays2(StartDate As Date, EndDate As Date, AINumber As String) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2
Dim intCount As Integer Dim rs As Object Dim stSql As String Dim con As Object Dim Message As String
If StartDate > EndDate Then MsgBox "Start Date is After End Date" WorkingDays2 = -1 GoTo Exit_WorkingDays2 End If
stSql = "SELECT [HolidayDate] FROM tblHolidays"
'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above
intCount = 0 Set con = Application.CurrentProject.Connection Set rs = CreateObject("ADODB.Recordset") rs.Open stSql, con, 1 ' 1 = adOpenKeyset 'MsgBox "Today is " & Weekday(StartDate) & ", " & StartDate & "Opening AI Number is " & AINumber Do While StartDate <= EndDate rs.Find "[HolidayDate] = #" & StartDate & "#" If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then If rs.EOF Then intCount = intCount + 1 ' Message = Message & " and today is a work day." End If Else ' Message = Message & "and today is not a work day." End If ' MsgBox Message 'rs.MoveNext StartDate = StartDate + 1 Loop 'MsgBox "Closing AI Number " & AINumber & "Count is " & intCount 'MsgBox "After Loop count is " & intCount & " and today is " & StartDate WorkingDays2 = intCount 'MsgBox "Date is " & StartDate & "AI Number is " & AINumber & " and there were " & intCount & " days." Exit_WorkingDays2: Exit Function
Err_WorkingDays2: Select Case Err
Case Else MsgBox Err.Description & "Start date is " & StartDate & "End Date is " & EndDate Resume Exit_WorkingDays2 End Select
I want a column in the query to be used for sorting but it depends on the type of data. In preparing to use my tables, I used a simple conditional formula:
Given that the test condition discovered that the data is numeric, the result is the data typed as double. Yet the result is left-justified, showing that the datatype is NOT numeric.
Then when I do it explicitly:
good: CDbl("123")
I do get a right-justified numeric field.
Why doesn't IIF() return the value as a numeric type?
