date formats

[ginger]

hello

i have a make table that has a client id and a field called Service (derived from): Format([ServiceDate] & " " & Format(dbo_ClientProgressNoteTime!StartTime," hh:mm"),"General Date")

i made a query and grouped by the Client ID and want the min of Service but my results show a numerical order result

10/8/2013 2:00:00 PM
11/12/2013 1:00:00 PM
11/5/2013 2:15:00 PM
12/17/2013 12:00:00 PM
2/12/2013 12:55:00 PM
2/12/2013 3:55:00 AM


i don't write much, if any, code, so i'd like to do this within my query.

thanks for your help

 

[Brianwarnock]

The Format function does indeed create a string, try wrapping it all in CDate

Brian

 

[pr2-eugin]

Format returns a String, if you Sort a String it will not be the result you are looking for, try CDate function instead.

 

[CJ_London]

No, they are showing text ordering otherwise

10/8/2013 2:00:00 PM
11/12/2013 1:00:00 PM
11/5/2013 2:15:00 PM

the third date would be appearing second.

I think the problem is you are formatting the date before finding the minimum - perhaps in your maketable query?

Suggest you simply need

Service (derived from): [ServiceDate] +dbo_ClientProgressNoteTime!StartTime

 

[Brianwarnock]

CJ is correct if ServiceDate and StartTime are proper Date/Time fields

Brian

 

[ginger]

that, sir, worked perfectly! thanks to all

 

[ginger]

just an fyi

CJ's suggestion worked great till I realized all my services dates increased by 2 days. the date field displays as 2/12/2013 while the time shows
as 1/1/1900 3:55:00 AM. so i took Brian's fix and it works

thanks again

 

[CJ_London]

Not sure what you have done there - if you format time as datetime rather than just time then you will get 1/1/1900 3:55:00 AM.

So intrigued to know how the service dates were incremented by 2 days

But if you have something that works - go with it!

 

[ginger]

so i got this as an explaination. both fields are date/time. because i used "+", the "1" of 1/1/1900 3:55:00 AM was consider a 24 hr period and added 2 days to the service date.

 

[Brianwarnock]

A date value of 0 represents 30/12/1899 thus 1/1/1900 will indeed add 2 days, use TimeValue to extract just the time from the field.

Brian

 

[Brianwarnock]

so i got this as an explaination. both fields are date/time. because i used "+", the "1" of 1/1/1900 3:55:00 AM was consider a 24 hr period and added 2 days to the service date.

Only just noticed this , it is incorrect I am sorry to say, see previous post

Brian

 

[Brianwarnock]

Hi Ginger, it strikes me that you do may not know how dates are stored if so read this

http://support.microsoft.com/kb/210276

Brian

 

[ginger]

Thanks Brian

Self taught Access user with still alot to learn

 

[Brianwarnock]

We have all been there and made the same mistakes.

Brian