CODEWITHSUNDEEP
ms-access
Please help me
Please help me

Reputation: 81

MS Access Convert text to shortdate

Hi all i have a question,

I have a field in the table whereby its a text field and i want to convert it to date field. Though all the values within are in date format however its not convenient to change because of some circumstances.

Thus i need to change the text field to date field so as to do a comparison or validation.

Im using MS access SQL for this project so please help me

I have tried 

TRANSFORM Count(Registrants.[Field1]) AS CountOfField1
SELECT Registrants.[Country] , Count(Registrants.[Field1]) AS [Total Of Field1]
FROM Registrants
WHERE Cast(Registrants.Field1 As Date) Between #15/6/2014# AND # 30/8/2014#
GROUP BY Registrants.[Country]
PIVOT Format([Field1]);

Upvotes: 0

Views: 114

Answers (1)

parakmiakos
parakmiakos

Reputation: 3020

Cast does not exist in MS Access sql.

Use DateValue instead : 

WHERE DateValue(Registrants.Field1) Between #15/6/2014# AND #30/8/2014#

See here for sample usage and syntax.

Also I noted a space in your 2nd date, I assume that's a typo.

Upvotes: 1

Related Questions