change a form text field to date in query

[mkelly]

I have a locked DB from corporate and the wizards made the [startdate] a text field and will not change it to a date field. I need to run some reports based on date calculations.

Is ther a way to change the text field to a date field (short date) using a query?

All help appriciated.

thanks

 

[sportsguy]

Lol!

I have a locked DB from corporate and the wizards made the [startdate] a text field

Hey, I resemble that remark!

isn't corporate wonderful!

sg

 

[mkelly]

Thanks Pat, not to sound to dumb but where do I use that expression?

 

[mkelly]

Thankd Pat once again you are a life saver

 

[mkelly]

Still can't get it to work

Ok now I know I'm lost.

Here is the Query. POSITIONSTATRDATE is the field that is text but contains a date. However whenever I try to use "cdate" I get an error and cannot change it from text to a date field!

SELECT PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.LASTNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.FIRSTNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.BUSINESSUNIT, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.POSITIONSTARTDATE, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.DATETIMECOMPLETED, [datetimecompleted]-[positionstartdate] AS exp1
FROM PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW
GROUP BY PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.LASTNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.FIRSTNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.BUSINESSUNIT, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.POSITIONSTARTDATE, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.DATETIMECOMPLETED, [datetimecompleted]-[positionstartdate]
HAVING (((PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.BUSINESSUNIT)="PBMS"));

 

[WayneRyan]

mkelly,

SELECT PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.LASTNAME, 
       PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.FIRSTNAME, 
       PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.BUSINESSUNIT, 
       PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.POSITIONSTARTDATE, 
       PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.DATETIMECOMPLETED, 
       [datetimecompleted] - CDate([positionstartdate]) AS exp1      <--- Made Change
FROM   PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW
GROUP BY PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.LASTNAME, 
         PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.FIRSTNAME, 
         PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.BUSINESSUNIT, 
         PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.POSITIONSTARTDATE, 
         PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.DATETIMECOMPLETED, 
         [datetimecompleted] - CDate([positionstartdate])            <--- Made Change
HAVING (((PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.BUSINESSUNIT)="PBMS"));

Wayne

 

[mkelly]

Thanks for the help.

 

[mkelly]

sorting a text date changed to date by CDate

I tried to make another field in the query using just the changed date.

SELECT PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.LASTNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.FIRSTNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.BUSINESSUNIT, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.CODE, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.DATETIMECOMPLETED, [datetimecompleted]-CDate([positionstartdate]) AS exp1, CDate([positionstartdate]) AS exp2, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.COSTCENTER, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.COSTCENTERNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.POSITIONSTARTDATE
FROM PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW
GROUP BY PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.LASTNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.FIRSTNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.BUSINESSUNIT, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.CODE, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.DATETIMECOMPLETED, [datetimecompleted]-CDate([positionstartdate]), PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.COSTCENTER, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.COSTCENTERNAME, PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.POSITIONSTARTDATE
HAVING (((PBOWES_LMS_DR_ILCTRANSCRIPT_VIEW.CODE)="PBMS-COMSG") AND ((CDate([positionstartdate]))>=[forms]![Report Date Range]![BeginDate] And (CDate([positionstartdate]))<=[forms]![Report Date Range]![EndDate]));

And wanted it to sort on this date however when I use the code:
>=[forms]![Report Date Range]![BeginDate] And <=[forms]![Report Date Range]![EndDate]

on exp2 it does not return any records.

any help greatly appriciated.

thanks

 

[WayneRyan]

Mkelly,

SQL won't let you say: "A > B And < C"

You need: "A > B And A < C" or "A Between B And C"

>=[forms]![Report Date Range]![BeginDate] And <=[forms]![Report Date Range]![EndDate]

should be:

Between [forms]![Report Date Range]![BeginDate] And [forms]![Report Date Range]![EndDate]

Wayne