Query Help

[Richard]

I need help with a query to remove the "time" refference in my date/time field.

7/13/2009 12:01:45 PM I would like to update it to this >> 7/13/2009
Thanks for any help.

 

[John Spencer]

Use DateVal in an expression like this

IIF(Isdate(SomeField),DateVal(SomeField),Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Richard]

John I received this error when I ran the query: Extra ) in query expression
'contacts, IIf(IsDate([calldate]),DateVal([calldate]),Null)]'.

here is what I put in the top line of the query.

NewDate: IIf(IsDate([calldate]),DateVal([calldate]),Null)

 

[Ciggys]

Hi

Sorry if i am way of base but cant you just simple use format and click on
short date?

John I received this error when I ran the query: Extra ) in query expression
'contacts, IIf(IsDate([calldate]),DateVal([calldate]),Null)]'.

here is what I put in the top line of the query.

NewDate: IIf(IsDate([calldate]),DateVal([calldate]),Null)

Use DateVal in an expression like this

IIF(Isdate(SomeField),DateVal(SomeField),Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ciggys]

I actually had that problem today and i just changed format and it cleared
the time of the date when i used short date

John I received this error when I ran the query: Extra ) in query expression
'contacts, IIf(IsDate([calldate]),DateVal([calldate]),Null)]'.

here is what I put in the top line of the query.

NewDate: IIf(IsDate([calldate]),DateVal([calldate]),Null)

Use DateVal in an expression like this

IIF(Isdate(SomeField),DateVal(SomeField),Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

What you posted as the error message seems to have an extraneous square
bracket at the end.

What you poste as the calculated expression seems to be correct. All the
parentheses and brackets seem to be in matched pairs.

Try breaking this down into parts. Just run the test first

NewDate: IsDate([CallDate])

And see if you get an error. If no error, try

IIF(IsDate(CallDate),"Date","NoDate")

If no error, try
IIF(IsDate(CallDate),CallDate,Null)

And then add the DateValue function as the last step

If you want someone to review the SQL statement to see if there is an
error, open the query in SQL view (View: SQL on the menu), copy the
text, and paste it into your message.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John I received this error when I ran the query: Extra ) in query expression
'contacts, IIf(IsDate([calldate]),DateVal([calldate]),Null)]'.

here is what I put in the top line of the query.

NewDate: IIf(IsDate([calldate]),DateVal([calldate]),Null)

Use DateVal in an expression like this

IIF(Isdate(SomeField),DateVal(SomeField),Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

If you want to sort in date order or filter by a date range then using
the format function will not work very well.

If the user only wants to "remove" the time portion for display
purposes, then use the format function or format property will work to
do that.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi

Sorry if i am way of base but cant you just simple use format and click on
short date?

John I received this error when I ran the query: Extra ) in query expression
'contacts, IIf(IsDate([calldate]),DateVal([calldate]),Null)]'.

here is what I put in the top line of the query.

NewDate: IIf(IsDate([calldate]),DateVal([calldate]),Null)

Use DateVal in an expression like this

IIF(Isdate(SomeField),DateVal(SomeField),Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Richard wrote:
I need help with a query to remove the "time" refference in my date/time field.

7/13/2009 12:01:45 PM I would like to update it to this >> 7/13/2009
Thanks for any help.

 

[Richard]

John,

All three of your last suggestion produced good results. I also found this
whlie searching access monster: NewDate: DateValue([calldate]) This has
the same effect as your suggestion.

NewDate:IIF(IsDate(CallDate),CallDate,Null)

Thanks for your suggestions
Richard

What you posted as the error message seems to have an extraneous square
bracket at the end.

What you poste as the calculated expression seems to be correct. All the
parentheses and brackets seem to be in matched pairs.

Try breaking this down into parts. Just run the test first

NewDate: IsDate([CallDate])

And see if you get an error. If no error, try

IIF(IsDate(CallDate),"Date","NoDate")

If no error, try
IIF(IsDate(CallDate),CallDate,Null)

And then add the DateValue function as the last step

If you want someone to review the SQL statement to see if there is an
error, open the query in SQL view (View: SQL on the menu), copy the
text, and paste it into your message.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John I received this error when I ran the query: Extra ) in query expression
'contacts, IIf(IsDate([calldate]),DateVal([calldate]),Null)]'.

here is what I put in the top line of the query.

NewDate: IIf(IsDate([calldate]),DateVal([calldate]),Null)

Use DateVal in an expression like this

IIF(Isdate(SomeField),DateVal(SomeField),Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Richard wrote:
I need help with a query to remove the "time" refference in my date/time field.

7/13/2009 12:01:45 PM I would like to update it to this >> 7/13/2009
Thanks for any help.