How to specifiy data type in mk-tbl qry

  • Thread starter Thread starter Hafeez Esmail
  • Start date Start date
H

Hafeez Esmail

My make-table query retrieves all my data as text data
types. Some fields are numbers while others are dates.
Is there an SQL keyword I can use to tell it to convert to
a Number or Date data type?

Thanks
Hafeez Esmail
 
My make-table query retrieves all my data as text data
types. Some fields are numbers while others are dates.
Is there an SQL keyword I can use to tell it to convert to
a Number or Date data type?
Click to expand...

CLng([field]), CDbl([field]), CCur([field]), CDate([field]) will
convert a text Field to Long Integer, Double, Currency, or Date/Time
respectively.

MakeTable queries should be VERY RARELY needed. Normally you would
store the basic data in one or (more commonly) several tables; you can
create a Query joining, selecting, and sorting the data, and base a
Form or Report directly on the Query.
 
Hi Hafeez,

Make table query won't convert data type to text data type, it remains the
data type from the source table.

1. Open sample database NorhtWindow.mdb.
2. Create a new query in design view
a. Click the new button
b. In the wizard, select Design View, click OK.
3. Close the Show Table dialog box.
4. Click View->SQL View.
5. enter the following sql query statement.
SELECT * INTO aa
FROM employees;
6. Save the changes, run the query.
7. It creates a new table called aa, open the table in design view, the
data type are the same with the original table employees.

To use the CDate function in query, create a query in SQL view and type the
statement like below
:
SELECT CDate(BirthDate) AS birthday INTO aa
FROM employees;

This maketable query creates a new table with one datatime data type
column.
Note: I have modified the original table employees's BirthDay column to be
text data type.

Please feel free to reply to the threads if you have any questions or
concerns.



Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: John Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
| Newsgroups: microsoft.public.access.queries
|
| On Fri, 17 Oct 2003 12:27:07 -0700, "Hafeez Esmail" <[email protected]>
| wrote:
|
| >My make-table query retrieves all my data as text data
| >types. Some fields are numbers while others are dates.
| >Is there an SQL keyword I can use to tell it to convert to
| >a Number or Date data type?
|
| CLng([field]), CDbl([field]), CCur([field]), CDate([field]) will
| convert a text Field to Long Integer, Double, Currency, or Date/Time
| respectively.
|
| MakeTable queries should be VERY RARELY needed. Normally you would
| store the basic data in one or (more commonly) several tables; you can
| create a Query joining, selecting, and sorting the data, and base a
| Form or Report directly on the Query.
|
| John W. Vinson[MVP]
| Come for live chats every Tuesday and Thursday
| http://go.compuserve.com/msdevapps?loc=us&access=public
|
 
Back
Top