concatination with cdate

[Jen0dorf]

Hi

just spent some time visiting casual dating sites - not a good idea;-)

OK I have three fields all text and I want to concatenate them into a single field and convert them to a date format so I can sort ascending

So I know that cdate is the way to go and that the & is used to concatenate.

I seem unable to find out how to use cdate to do this on the web hence my visits to funny web sites.

I assume I do it as an expression in a query? and not at table level?



as usual any help appreciated

thanks

Ian

[davegri]

Dim strDate As String
Dim aDate As Date
strDate = "Apr 26, 2016" 
aDate = CDate(strDate)

function CDate will convert any string with a valid date to a date data type.

[pbaldy]

Depending on what the fields contain, DateSerial() may be a better option. I suspect what you were looking for:

CDate(month & "/" & day & "/" & year)

[Jen0dorf]

Hi

thanks for that.

I do seem to having trouble in getting my head around the intricacies of modules, VBA and macros. I think I've cracked macros but the other two are defying my addles brain.

I follow the code you suggested but how do I implement it?

ie in a query do i add

fulldate:CDate(month & "/" & day & "/" & year)

so it's an expression?

or do I ....

Sorry must seem stupid but I'm not getting anywhere

thanks

Ian

[pbaldy]

That should work, using your actual field names.

[Jen0dorf]

Hi

twas me being less than ........

I tried the code again and realised that

FullDateofBurial:CDate(DayOfBurial & "/" & MonthOfBurial & "/" & YearOfBurial)

did work. What confused me was that some of the records do not have an entry for DayofBurial, MonthofBurial and YearofBurial

hence the expression brings me a displayed value of #Error in some records

How do I add a criteria only to display the results if the result is viable

as always thanks

Ian

[pbaldy]

You could add

Is Not Null

to the criteria for all 3 fields.

[Jen0dorf]

Thanks I'll try it but I have no doubt you are correct!! I did try "" but not Is Not Null

cheers

Ian

[orange]

Ian,

Did you look at this https://www.accessforums.net/showthread.php?t=59086