Remove time from date/time field and be able to search with date criteria

[snipe]

Hey guys,

I have a problem, which I really feel like I shouldn't be having. There is a field, [DateChanged], in my ODBC database. It is a date/time field. I don't need the time, and I search with date-only ranges, so I really need to remove the time part. Everything that I have searched has come up with 2 options.



1. Use DateValue([DateChanged])
2. Use format([DateChanged],"mm/dd/yyyy")

When using DateValue, if I search for #4/17/16# (with or without the #) I get the "expression is too complicated" error.

When using Format(), if I search for #4/17/16# (with or without the #) I get no results.

I am 100% stumped on what is going on, or what I am doing wrong. I appreciate any help.

If it makes a difference, the end goal is to be able to use this criteria in the final query: Between [Forms]![F_Switchboard]![DateFrom] And [Forms]![F_Switchboard]![DateTo]

Both of those date fields are stored as text, and work great everywhere else I use that statement.

[alansidman]

Is the original value of the Date Time field also text? If not, then in the datevalue field, try =Int(Original) and format as desired.

[snipe]

No, sir. It is a Date/Time value.

[alansidman]

If date/Time, then use my suggestion above. =Int(fieldName) and format as required.

Look at this link at how dates and times are stored in Excel. It is similar in Access http://www.cpearson.com/excel/datetime.htm

[snipe]

Int([field]) gave me a integer value. Datevalue(Int([field])) gave me a correct looking date. Using #4/17/16# as criteria gives the "too complex" error again.

[alansidman]

Format the integer value as a Date. Here is an explanation on how dates are stored in Access.

Access stores the Date/Time data type as a double-precision, floating-point number up to 15 decimal places. The integer part of the double-precision number represents the date. The decimal portion represents the time.

Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). A date value of 0 represents December 30, 1899. Access stores dates before December 30, 1899 as negative numbers.

Valid time values range from .0 (00:00:00) to .99999 (23:59:59). The numeric value represents a fraction of one day. You can convert the numeric value to hours, to minutes, and to seconds by multiplying the numeric value by 24.

[snipe]

Nulls If I put an Is Not Null in there, all is peachy. Using Cdate(int([field]))

Thanks for the help!