Text to Date??

[branston]

Hi,

Is there a way to change text to date format? I have text which I have formatted to be in the dd/mm/yyyy format, but it doesn't recognise it as a date so I can't ask it to (for example) show the ones where the date is after another field which is a date (I get an error saying the I have a Data Type mismatch)

Any ideas if I can change it to be a date?

Thanks!

 

[CBrighton]

Try something like:

DateSerial([right([FieldName],4),mid([FieldName],4,2),Left([FieldName],2))

 

[DCrake]

Or you could use CDate([YourField])

 

[branston]

Thanks for your suggestions!
Its sort of worked...
The Query I mentioned calls on other queries where the date is formatted etc. I have managed to get it in date format in the 1st query, but it doesn't keep it in the 2nd query... I am using it in a iif statement:
ToDate_a: IIf([QryResource].[Staff] Is Null,"",[ToDate_])
Could it be because of the "" option - would that be messing it up?

 

[CBrighton]

No idea.

You could try removing the "" by changing it to: IIf([QryResource].[Staff] Is Not Null,[ToDate_])

 

[branston]

Thanks for the suggestion, but it doesn't help.

 

[vbaInet]

The "" is causing the date field to be casted back to String. You can't have a mix of data types in a column.

Change "" to Null.

By the way, can you give us an example of the string you want converted to Date and indicate which part is dd, mm and yyyy.

 

[branston]

Oooh, ooh, I got it to work a bit more!
If I use the CDate([DateField]) every time I call it it seems to work... now its saying invalid use of Null but I think thats an unrelated thing.
Thank you for all your help!

 

[branston]

Ah, yep, Null error was just be being daft. All seems to be working now I've got rid of the "" and put CDate in a few more places!

Thank you!

 

[vbaInet]

I you use "", then that column will still be casted to String. Use VarType() and you will see what data type it is casted to, 8 is for String. You need a second IIF() function to ensure the length of [ToDate_] before casting with CDate() or converting with DateSerial().

 

[branston]

Why will it still be cast as string? I changed the "" to Null as you said...
And it seems to be being recognised as a date now...
Just trying to understand.

 

[vbaInet]

My post came a bit late by the time I posted but I will explain.

"" is Text data type and obviously CDate() will cast to a Date/Time data type. If those two results appear in one column it will be automatically casted to Text. Although it may look like data but the actual data type representation will be Text. So using Null will ensure that it remains as Date, which you've already done.

Is it the [Staff] field you're converting to Date?

 

[branston]

Ah, great. Thanks for clearing that up.
No, its the ToDate_ field... wanted to only show a date if the Staff field was empty.

 

[vbaInet]

So if the Staff field is Null, then ToDate_ will never be empty? If at some point both are Null, CDate() will bomb. So maybe an extra check is necessary, but if you're positive that it will not be Null when Staff is Null, then your current IIF() statement is good to go.

However, I will write it this way:

ToDate_a: IIf([COLOR=Red][B]IsNull([/B][/COLOR][QryResource].[Staff][COLOR=Red][B])[/B][/COLOR], Null, CDate([ToDate_]))

 

[branston]

I'm fairly sure it would never be null... will have another think through the scenario though.
And thanks for the different way of writing it.