Removing Time from a Date/Time field

[jereece]

I have a query that is uses a linked table to an SQL database. Threre is a Date/Time field which is formatted like this: 10/7/2008 2:43:25 PM. In my query I am only interested in the date. The reason is that I run the report using the due date and approved date to show me the records where the approved date was after the due date. Since both fields also include the time, I get a return when the date is the same but the approval time is after the time stamp in the due date. So I am looking for a way to totally ignore the time piece of this field. I know I can use an append query to copy the data locally and set up that field to strip it out, but I am looking for a way to do this without having to copy the data locally.

Thanks for any suggestions.

Jim

 

[boblarson]

Create your own field in a query by using DateValue()

MyDateField: DateValue([YourFieldName])

 

[gemma-the-husky]

int or fix function will truncate values

so just int(mytime)

example

Sub dateplay()
Dim mytime As Date

mytime = Now()
MsgBox (mytime & vbCrLf & [COLOR="Red"]Int(mytime))[/COLOR]

End Sub

 

[jereece]

Thanks for the help. Bob I used your suggestion as it looked the easiest. As a follow-up however, when I now run the query any fields that are blank show "#Error". Before they were just blank which is what I want. Any way to eliminate this error?

 

[boblarson]

Try this instead:

MyDateField: IIf(Not IsNull([YourFieldName]),DateValue([YourFieldName]))

Yes, without specifying a value for False (don't know why but it worked for my test)

 

[jereece]

That works! I really appreciate the help.

Jim