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
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.
CDEC() (cast to DECIMAL): it takes a different number of arguments than its VBA equivalent and doesn't work! But good answer ;)
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);
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);