DateValue

[Kevin3NF]

I have a datetime field in SQL Server. I want to query on this field in my .mdb using an unbound text box on a form as criteria. Some of my records have date, some date and time, which causes a problem, as the criteria is entered as date only.

This brings back all records, but is not sortable (Data type mismatch in criteria expression)

SELECT DateValue([DateCalledIn])
FROM Transactions;


This is what I really want:

SELECT DateValue([DateCalledIn]) AS Expr1
FROM Transactions
WHERE (((DateValue([DateCalledIn]))=[Forms]![Transaction Reports]![LocationCardPrintDate]));

but I get "The expression is typed incorrectly or it is too complex to be evaluated....."

SQL 2000 sp3, Access XP in 2000 file format, recently converted form 2.0.

Anyone see what I am doing wrong?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com

 

[Ken Snell]

Kevin -

Is it possible that the [DateCalledIn] value can be Null? If yes, then
you're likely seeing this error because the DateValue function cannot handle
Null values.

Wrap the argument in Nz this way:
WHERE (((DateValue(Nz([DateCalledIn],"")))=[Forms]![Transaction
Reports]![LocationCardPrintDate]));


--
Ken Snell
<MS ACCESS MVP>

I have a datetime field in SQL Server. I want to query on this field in my
..mdb using an unbound text box on a form as criteria. Some of my records
have date, some date and time, which causes a problem, as the criteria is
entered as date only.

This brings back all records, but is not sortable (Data type mismatch in
criteria expression)

SELECT DateValue([DateCalledIn])
FROM Transactions;


This is what I really want:

SELECT DateValue([DateCalledIn]) AS Expr1
FROM Transactions
WHERE (((DateValue([DateCalledIn]))=[Forms]![Transaction
Reports]![LocationCardPrintDate]));

but I get "The expression is typed incorrectly or it is too complex to be
evaluated....."

SQL 2000 sp3, Access XP in 2000 file format, recently converted form 2.0.

Anyone see what I am doing wrong?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com

 

[Kevin3NF]

Thanks Ken, that was exatly what I needed. The column is Nullable, and
there were 4 Null values (out of 23K records).

Much appreciated.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Ken Snell]

You're welcome.

Thanks Ken, that was exatly what I needed. The column is Nullable, and
there were 4 Null values (out of 23K records).

Much appreciated.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

Kevin -

Is it possible that the [DateCalledIn] value can be Null? If yes, then
you're likely seeing this error because the DateValue function cannot handle
Null values.

Wrap the argument in Nz this way:
WHERE (((DateValue(Nz([DateCalledIn],"")))=[Forms]![Transaction
Reports]![LocationCardPrintDate]));


--
Ken Snell
<MS ACCESS MVP>

I have a datetime field in SQL Server. I want to query on this field in my
.mdb using an unbound text box on a form as criteria. Some of my records
have date, some date and time, which causes a problem, as the criteria is
entered as date only.

This brings back all records, but is not sortable (Data type mismatch in
criteria expression)

SELECT DateValue([DateCalledIn])
FROM Transactions;


This is what I really want:

SELECT DateValue([DateCalledIn]) AS Expr1
FROM Transactions
WHERE (((DateValue([DateCalledIn]))=[Forms]![Transaction
Reports]![LocationCardPrintDate]));

but I get "The expression is typed incorrectly or it is too complex to be
evaluated....."

SQL 2000 sp3, Access XP in 2000 file format, recently converted form 2.0.

Anyone see what I am doing wrong?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com