Is there any documentation about the automatic CAST functions in Access 2010/2013?

[magnusstefan]

I will try to explain why I ask this by giving an example first:



Suppose we have a sales order database, and there we have the table "Orders". In the table we have columns like "OrderNumber", "Orderdate" and "Shipdate".
OK?

Now we want to know how many days it is between order date and ship date for each order.
According to SQL Standard, as far as I have learnt it, the following SQL Query is then supposed to work:

SELECT OrderNumber, OrderDate, ShipDate,
CAST (ShipDate - OrderDate AS INTEGER)
AS DaysElapsed
FROM Orders;

But it does not. Instead I got an error message:
"Syntax error (operator missing)..."


After one hours work I did find out this: if I just remove a few things in the second line, and only keep this it works fine:

ShipDate - OrderDate

So this seems to be the way in Access:

SELECT OrderNumber, OrderDate, ShipDate,
ShipDate - OrderDate
AS DaysElapsed
FROM Orders;

Now, is there any documentation anywhere that explains how Access works with automatic CAST-conversions?
Including, syntax, what Access does and does not regarding CAST/conversions when you write SQL queries?
Does anyone know?

(When I search for "CAST" or "Conversion" in the internal help system I do not get anything useful.)

[ranman256]

There is no CAST. Just use the functions and give it a label. AS

[pbaldy]

CAST (and CONVERT) are SQL Server (and perhaps other platforms) functions, not valid in Access. In the case mentioned, CInt() would be an alternative.

[magnusstefan]

OK, Thank you!


You mean like:

ShipDate - OrderDate AS DayDifference

and then I am done? Nothing more to worry about, right?

One more question about this if you please: Since I got the answer as a numeral, not as a date number, i suppose that Access actually convert the dates to numerals which basically is the same as if CAST has been used in another system, only that in the Access World this is not called Casting?

[magnusstefan]

OK, thank you!

CInt() is VB code, right?
If you not are skilled in VB, there is no SQL Alternative, or?

[pbaldy]

OK, thank you!

CInt() is VB code, right?
If you not are skilled in VB, there is no SQL Alternative, or?

No, not VB code and will work in the query.

[magnusstefan]

No, not VB code and will work in the query.

Yes, of course you are right! (Sorry for late answer!)

That is: I have now read or worked through all examples of SLQ commands in Pindaro Epaminonda Demertzoglou's excellent book "Microsoft Access SQL Comprehensive". So I know for sure now, that you are right! =)
And I can, without reservation, recommend the book to anyone who need some improvement of their knowledge about SQL-code related to MS Access, or just for repetition or checking up something!