Database In Access Data Type Problem?!?

Jan 6, 2005

Hey all.. I was wondering if anyone would be able to help because I am really stuck with this..

Basically in an Access Database holding messages of online chat conversations I need to run a query to find out which is the longest message and which is the shortest message sent. In order to this the number of characters need to be counted and then the max and min selected however I'm having problems with the data type of the message

Based on the LEN function I first tried :

SELECT User.User, Message.Message
FROM Message INNERJOIN [User] ON Message.[Message ID] = User.[Message ID]
ORDER BY LEN(Message) DESC;

This selected the user and message fields and ordered them based on the length of the message.

In the original Message Table the message field is set to Memo as there are quite long messages the above SQL did incorporate the full messages and ordered them in the correct manner...

HOWEVER.. I need to be able to count the number of characters per message and retrieve the longest message and then retrieve the shortest message.

I tried..

SELECT Message.Message, LEN(Message) AS [No of characters]
FROM Message INNERJOIN [User] ON Message.[Message ID] = User.[Message ID]GROUP BY Message.Message;

This counted the number of characters for each message but it did not incorporate the full messages, the maximum number of characters for the longer messages came up as 255 whereas I know that they are longer than that.. and it only displayed those 255 characters and cut off the rest of the message.

Ultimately I need to select the 'User' from the user table, 'Message' from the message table, Count the number of characters per message and then display the highest but the function needs to be aware of the messages that have characters more than 255 hence the Memo data type setting in the Message Table..

... any ideas/developments on the above??

I have been trying a few different things but getting no-where at the moment.. Ive tried asking in loads of different places but seems I have baffled everyone else as well as myself.

Any help would be greatly appreciated.

View Replies


ADVERTISEMENT

General :: Cannot Change Field Data Type In Database

Mar 18, 2014

In my database i have a field for Tonnage (quantity) ...

I need for example to input 38.60 or 37.89 so my values must have 2 decimal places, ive searched and found changing my field type to double or decimal should solve the 'numbers rounding up/down' but access will not let me change the data type?

View 1 Replies View Related

Tables :: Converting Text Data Type To Number Data Type

Nov 3, 2012

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.

View 7 Replies View Related

Yes/No Data Type In Access Table

Jul 17, 2007

Is it possible to set the yes/no data type fields in an Access table to behave like radio buttons? In other words, if they click yes in one data field, it automatically prevents them from clicking yes in the next data field. If so, how?

View 4 Replies View Related

Access Text Data Type Options

Nov 14, 2004

Hi All,

I was doing a project for school where I had to make a text field that would only accept the options Client Type(RET - Retail, SER - Service, DIN - Dining)

I thought I did it right by having a line with the field name as "Client Type" and the input as "text" with a field size of "3". and the description I put was "Client Type(RET - Retail, SER - Service, DIN - Dining)". But it doesnt work. I saved and everything. It will let me enter anything I want as long as it doesnt exceed 3 chars. I want it to ONLY accept RET, SER, or DIN and I want it to format it so its in CAPS.

Thanks,
-- Steven

View 1 Replies View Related

Microsoft Access Can't Change The Data Type

Jan 24, 2007

Hi! I have a problem to build Pivot table in Excel 2003.
I’m creating this table base on the “External Data” which is a MS Access 2003 table.
The table has 50 fields and about 500.000 records. (This is the reason, I can’t just export table to Excel and then do pivoting). One of the fields of this table has a “text” type but stored numbers. Excel does not allowed me do Sum or Max function with this field – it needs to have Number data type.
I receiving this table “from outside”, so I can’t get the right data type from the beginning. If I’m trying simply opening the table, before, using as a data source for Pivot Table, in Design mode and just change data type from text to number, I have an error: "Microsoft Access can't change the data type. There isn't enough disk space or memory."
Any advice, how to change Data type in existing table using queries or something else what can help me to solve this problem.

Thank you very much at advance.

Alec.

View 9 Replies View Related

General :: Lookup Data Type In Access 2013 Web App?

Jan 2, 2014

I want to create a Web App in Access 2013 that contains a table of client names, addresses etc, and a second table that contains order details, including client name. It would be nice to ensure that as someone adds a new order they are give a drop down menu containing existing client names, and I can see how you can do this for a brand new table using the lookup data type. But I already have an Excel spread sheet containing client names. If I import this into my Access web app to create the client table, and import the existing orders to create the order table, I then try and change the data type of the client name (in the order table) from short text to lookup, it won't let me!! (If I create an empty client table from scratch, it lets me define the company name as a lookup data type - but I can't then import from Excel into this empty table)

View 14 Replies View Related

Tables :: Access Returns Number In Data Type

Jan 16, 2014

I created several tables that contain the look-up data I want to post to database which I will use as the repository for a SharePoint form.Users visiting SharePoint site will enter the data to be kept in an Access database so we can create reports (not seen by the user)

Problem: When I created the database I linked fields to tables to create the look-up lists using the wizard.

When I saved the database, the first field that I linked returned a value of "number" instead of whatever the default value should be for a look-up text field.

View 7 Replies View Related

Linked Excel Table In MS Access Date Data Type Problem

Aug 29, 2005

I'm using MS access and Excel 2000. I have an Excel spreadsheet that contained 8 columns, the first column has all cell format as Number, the rest of the column is set as custom date format of 'dd/mm/yyyy'. When I create a linked table in MS Access, the data types does not matched my excel spreadsheet columns, the 'Number' data type is a double and I want a Long Integer in Access, and the custom date format become text datatype but I wanted a DateTime datatype. Is there any work around this? Seems like it is a common problem.

Your prompt response is greatly appreciated!

Thanks in advance!
Martina

View 1 Replies View Related

Tables :: Length Of Text Field - MS Access Can't Change Data Type

Aug 6, 2014

I have a table with about 300,000 records. About ten fairly small fields per record. I am trying to change the length of a text field from 25 to 40 characters, and I get the error message, 'MS Access can't change the data type. There isn't enough disk space or memory'.

I have never seen this message before. I have about 64 Gig of free disk space. What can I do?

View 4 Replies View Related

Forms :: Add Time Value To Time Data Type SQL Server Field In Access

May 6, 2015

how to be able to enter time in access form the same way as if would be an access table (1p = 1:00 PM; 1.25 = 1:25 AM etc)

View 1 Replies View Related

Forms :: Make A Button To Search Range Of Columns In Data Table With Data Type Yes / No

Apr 15, 2013

what I want to do is make a button to search range of columns in data table with data type Yes/no and display the results if the value is yes

View 9 Replies View Related

Tables :: Changing Field Data Type But Keeping Data

Oct 23, 2013

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?

View 2 Replies View Related

Help With Customer Care Type Database

Oct 5, 2004

I am having problems with a customer care type database. I used VBAUNBOUND to update information from one table onto a form.

If you select the combo box on the form, it reads the info from the one table, and updates 3 other text boxes in the form, but it doesn't update the main table in the database.

Tables:
Contacts - Customer problem details
tblcustomer - Branch details


Query:
qurycustlisting - Reads info from tblcustomer (used in form Contacts)


Form:
Contacts - Manually input details. Select branch name in combo box (reads from query - qurycustlisting) and then updates the rest of the branch details on the form. But the info insn't updated into Table Contacts

Pleas help

View 1 Replies View Related

Problem Filtering Yes/no Data Data Type

Oct 2, 2006

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.

Pls help.

Thanks.

View 1 Replies View Related

Data Type To Input Data Number

Feb 16, 2014

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?

View 5 Replies View Related

Data Access Pages Using A Relational Database

Aug 11, 2005

Im creating a data access page overtop of a relational database. the database is simple... it has customers and workorders. there may be more than one workorder for a customer.

When I create a new customer record through my data access page, I want it to automaticly create a new workorder (filled with 0's)

Does anyone know how to do that?

Thanks
--Ben

PS: I understand SQL... I just need to know where to enter it (if necessary)

View 1 Replies View Related

Transfering MYSQL Data To An ACCESS Database- HELP ME!

Oct 31, 2005

Could someone please help me?

At work we have a website with a mysql database attached to it. It keeps data such as customer name address postcode contact id etc.

Currently our main database is made in access.

I need to transfer data from the website database (which is in mysql) to our access database. And would like this to happen regulary. We have been quoted £1800 for someone to do this!

As this is a new job i would like to be able to do this myself, is it hard to do?

Could someone provide me with some information of how to do this? or where to start.

Much appriciated.

View 3 Replies View Related

Export An Access Database Structure (i.e. No Data)?

Jan 30, 2007

If I want to duplicate just a table, I can easily select "structure only" under paste options. Is there an easy way to do the same thing for an entire database (tables, queries, etc.) all at once?

In short, someone has a database. They're willing to share the database itself, but not necessarily its contents. The database has a user interface, so the people who use the database don't necessarily know much about Access. Therefore, I can ask them to follow a few basic, built-in Access menu options, but can't ask them to do something manually, such as copying the entire file and then manually emptying the tables. This seems like something that should be easy, but I can't find it. Any help?

Thanks,

Rhythm

View 4 Replies View Related

Export Data From An Access Database To MYSQL

Jul 17, 2007

Hi There,

Whereabouts would I change the date format from the access database from yyyymmdd ?

I am trying to convert this table from access into mysql, and when I ouput to a csv file the date is in access.

Many Thanks

Chris

View 1 Replies View Related

Send Data From Access Database Every 15min.

Aug 22, 2007

Hello

I need to send data from a mdb on an xp machine every 15min. Any tips on how to do this?

View 2 Replies View Related

General :: Website To Get Data From MS Access Database?

Jan 10, 2013

Is it possible for a website to pull data from an Access database?

We have all our department's policies in a MS Access database on our server. Some of these policies need to be made available to the public on our website. I'm wondering if there's a way for the website to pull directly from the database rather than having to manually upload the policies to the site every time they change (annually, semi-annually).

View 1 Replies View Related

General :: Access Based Database / How To Use Data

Apr 26, 2015

I've used MS Access a fair bit in the past but am somewhat lost since moving to 2013Pro.Some years back I was using software to document radio intercepts, this was based on Access but used a program to display and enter the data.That program was lost after upgrading to a new computer (tied to the CPU) .And the company has long since gone out of business.So I am left with the MS Access elements.In the form of a 3mb database that has nothing but a long list of tables to show for itself.Each table opens and has some ID columns with text like this in them 'L096e0dd24757c 1533a7cc1 53e 6ba44 bd' and after has usable data. What I want to do is set up Access so I can use as much of the data as possible.

View 8 Replies View Related

Exporting Data To External Access Database?

Jan 23, 2015

code that will allow me to take data [All Fields] from a Table in my Database and Insert them into another identically structured Table in another MS Access accdb Database.I would also like to filter for records older than a month [MyDateField]

I have tried the following code I found on line. It doesn't throw an error . . . but it doesn't perform the Insert either.

Code:
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.

[code]....

View 1 Replies View Related

Creating Access Database To Enter Data

Aug 17, 2014

I am a newbie at Access. I have uploaded an Access 2013 file and an Excel 2013 file to show what I am trying to do.

(I also incorrectly uploaded this question under QUERIES - since marked there as 'Solved")

The Access tables that I have created are meant to mirror the individual fields on the Excel spreadsheet.

On the Excel spreadsheet, I would enter data in the rows as the data becomes available.

The Access file contains the excel fields as tables. Some of the table fields consist of only one field and others have many fields.

I have created Primary Keys in all the tables. But I don't understand the process well at this point in time.

I need to know how to create proper relationships, and then, to create a query or a form to enter new data as it becomes available so that I can keep adding rows of data (query to enter and update data) and subsequently run queries to analyze the data

Need to create the correct relationships and a query that lets me update all the data for new cases, as an individual row (like on an excel spreadsheet) .

View 1 Replies View Related

Transferring Data To Another Access Database (before Delete)

Aug 16, 2013

I have a database with a main database that has 2 related tables...my problem is that i only keep data that i really need, in the main table. My problem is that i sometimes need to go back to old data that i erased. so i have to keep old data too. but i can;t on the same database. so i need to move the old records from the main table and all the related data to another database that will keep all the erased data, so if i need to find something i will search on the "deleted records" database.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved