DateValue

K

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
 
K

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
 
K

Ken Snell

You're welcome.

Kevin3NF said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DateValue & Type Mismatch Error 2
SQL date and milliseconds 7
Date fields break GROUP By or ORDER By clauses 1
Help with Repeating Records 0
DATE ERROR IN QUERY "DATA MISMATCH...ETC. 8
DateValue 5
Data type mismatch 4
query for date using form 0

Top