append date records with no time

I have set the input mask on a table to short date and the format as
mm/dd/yyyy so that when I append records from another table (supplied from an
outside source) I only get date values. However, this is not working as I am
still getting the time values. I realize that time will always be there but
I run some queries so that I get unique dates and if the time is there it
will not work, unless of course the time is alwasy midnight.

I also tried to update with datevalue but Access did not like it. Any help
is appreciated.

Thanks,
--
javablood

Use DateValue in your select query to remove time like this --
MyDate: DateValue([YourDate])
This way they will compare and the time is unaltered for when you need it.

"javablood" wrote:

I have set the input mask on a table to short date and the format as
mm/dd/yyyy so that when I append records from another table (supplied from an
outside source) I only get date values. However, this is not working as I am
still getting the time values. I realize that time will always be there but
I run some queries so that I get unique dates and if the time is there it
will not work, unless of course the time is alwasy midnight.

I also tried to update with datevalue but Access did not like it. Any help
is appreciated.

Thanks,
--
javablood

That works unless you try using DateValue with a null value. So it is safer
to use something like:
IIF(IsDate([SomeField]),DateValue([SomeField]),Null)

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

KARL DEWEY wrote:
Use DateValue in your select query to remove time like this --
MyDate: DateValue([YourDate])
This way they will compare and the time is unaltered for when you need it.

"javablood" wrote:

I have set the input mask on a table to short date and the format as
mm/dd/yyyy so that when I append records from another table (supplied from an
outside source) I only get date values. However, this is not working as I am
still getting the time values. I realize that time will always be there but
I run some queries so that I get unique dates and if the time is there it
will not work, unless of course the time is alwasy midnight.

I also tried to update with datevalue but Access did not like it. Any help
is appreciated.

Thanks,
--
javablood

Thank you Karl and John. It worked and I also saw the error of my ways in my
append query!
--
javablood


"John Spencer MVP" wrote:

That works unless you try using DateValue with a null value. So it is safer
to use something like:
IIF(IsDate([SomeField]),DateValue([SomeField]),Null)

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

KARL DEWEY wrote:
Use DateValue in your select query to remove time like this --
MyDate: DateValue([YourDate])
This way they will compare and the time is unaltered for when you need it.

"javablood" wrote:

I have set the input mask on a table to short date and the format as
mm/dd/yyyy so that when I append records from another table (supplied from an
outside source) I only get date values. However, this is not working as I am
still getting the time values. I realize that time will always be there but
I run some queries so that I get unique dates and if the time is there it
will not work, unless of course the time is alwasy midnight.

I also tried to update with datevalue but Access did not like it. Any help
is appreciated.

Thanks,
--
javablood

Thank you Karl and John. It worked and I also saw the error of my ways with
my append query!
--
javablood


"John Spencer MVP" wrote:

That works unless you try using DateValue with a null value. So it is safer
to use something like:
IIF(IsDate([SomeField]),DateValue([SomeField]),Null)

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

KARL DEWEY wrote:
Use DateValue in your select query to remove time like this --
MyDate: DateValue([YourDate])
This way they will compare and the time is unaltered for when you need it.

"javablood" wrote:

I have set the input mask on a table to short date and the format as
mm/dd/yyyy so that when I append records from another table (supplied from an
outside source) I only get date values. However, this is not working as I am
still getting the time values. I realize that time will always be there but
I run some queries so that I get unique dates and if the time is there it
will not work, unless of course the time is alwasy midnight.

I also tried to update with datevalue but Access did not like it. Any help
is appreciated.

Thanks,
--
javablood