append date records with no time

[javablood]

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,

 

[KARL DEWEY]

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.

 

[John Spencer MVP]

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

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.

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

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

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.

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

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

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.

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,