CODEWITHSUNDEEP
sqlms-access-2007
user793468
user793468

Reputation: 4976

setting data types in MS ACCESS SQL Insert queries

How can we explicitly convert data types in INSERT INTO/SELECT queries in MS Access?

Sample Query:

INSERT INTO pStudents( pStudentID, pDate, pRate, pGrade )
SELECT sStudentID, sDate, sRate, sGrade
FROM sStudents
WHERE (((sStudents.sStudentID) Is Not Null);

Here I want to convert fields from sStudents table before inserting in pStudents to following:

pStudentID = text
pDate = Short Date
pRate = Double
pGrade = text

Thanks in advance

Upvotes: 0

Views: 10092

Answers (3)

Justin Pihony
Justin Pihony

Reputation: 67115

This should work. If you want Date, you can use CDate, however this will make sure the date is a short date (http://www.techonthenet.com/access/functions/date/format.php)

INSERT INTO pStudents( pStudentID, pDate, pRate, pGrade ) 
SELECT CStr(sStudentID), Format(sDate, "Short Date"), CDbl(sRate), CStr(sGrade) 
FROM sStudents WHERE (((sStudents.sStudentID) Is Not Null);

Upvotes: 3

Christian Specht
Christian Specht

Reputation: 36431

You can use the built-in conversion functions of Access in the queries:

Select CStr(NumericColumn) from Table

...or as an INSERT query:

Insert Into AnotherTable (StringColumn)
Select CStr(NumericColumn) from Table

Did you mean something like that?

EDIT:

Okay, your sample query with conversions would look like this:

INSERT INTO pStudents( pStudentID, pDate, pRate, pGrade )
SELECT CStr(sStudentID), CDate(sDate), CDbl(sRate), CStr(sGrade)
FROM sStudents
WHERE (((sStudents.sStudentID) Is Not Null);

However, this will only work if the columns contain only data that can actually be converted into the given type.

For example, you can convert a String column to Double with CDbl() - but only if the selected rows contain only values that can actually be converted into Double.
As soon as you select one row with a value that contains something else than numeric values (like 'hello'), the conversion will fail.

On the other hand, do you really need the conversions?
Access can convert a lot by itself. For example, you can insert values from a numeric column into a string column, Access will convert it by itself. You don't have to use CStr() for that.

Upvotes: 5

Taryn
Taryn

Reputation: 247810

There are many conversion function that are available in Access.

CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)

http://office.microsoft.com/en-us/access-help/type-conversion-functions-HA001229018.aspx

Then you would use these functions in your INSERT INTO/SELECT query:

INSERT INTO table1 (field)...
SELECT Cstr(fieldValue)...
FROM table2

If you need to convert each of the fields in sStudents, then based on your edit you could do the following:

INSERT INTO pStudents( pStudentID, pDate, pRate, pGrade )
SELECT CStr(sStudentID), CDate(sDate), CDbl(sRate), CStr(sGrade)
FROM sStudents
WHERE (((sStudents.sStudentID) Is Not Null);

Upvotes: 1

Related Questions