Date query error after upsizing to SQL Server 2005 /2008

[Freo]

After upsizing MS Access 2003 DB to a SQL Server I can see all the tables
with a respective data. But when I try to query date field I recived the
message "Microsoft Office Access has encountered a problem and needs to
close" ModName: vbe6.dll.
I can query other fields with out problems.
I tried to change the ODBC Connection (SQL Server, SQL Native, etc) but the
problem continues.
Please Help!!!

[Alex Dybenko]

Hi,
what is sql of such query, which cause Access to crash?
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by Freo
After upsizing MS Access 2003 DB to a SQL Server I can see all the tables
with a respective data. But when I try to query date field I recived the
message "Microsoft Office Access has encountered a problem and needs to
close" ModName: vbe6.dll.
I can query other fields with out problems.
I tried to change the ODBC Connection (SQL Server, SQL Native, etc) but the
problem continues.
Please Help!!!

[Gina Whipp]

Freo,

Might help to show the query...
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
After upsizing MS Access 2003 DB to a SQL Server I can see all the tables
with a respective data. But when I try to query date field I recived the
message "Microsoft Office Access has encountered a problem and needs to
close" ModName: vbe6.dll.
I can query other fields with out problems.
I tried to change the ODBC Connection (SQL Server, SQL Native, etc) but the
problem continues.
Please Help!!!

[Freo]

When I create with a Query Wizard a new Query I do not have any problem.
I can see all the records and I can to filter with any field except date
field.
When I try to filter with Date field I receive an error and Access closes.

When I do the same with an original (before upsizing) mdb file I do not have
problems.

Thank for your time.

Post by Gina Whipp
Freo,
Might help to show the query...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
After upsizing MS Access 2003 DB to a SQL Server I can see all the tables
with a respective data. But when I try to query date field I recived the
message "Microsoft Office Access has encountered a problem and needs to
close" ModName: vbe6.dll.
I can query other fields with out problems.
I tried to change the ODBC Connection (SQL Server, SQL Native, etc) but the
problem continues.
Please Help!!!

[Gina Whipp]

Freo,

You still have not show us the query in question...
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
When I create with a Query Wizard a new Query I do not have any problem.
I can see all the records and I can to filter with any field except date
field.
When I try to filter with Date field I receive an error and Access closes.
When I do the same with an original (before upsizing) mdb file I do not have
problems.
Thank for your time.

Post by Gina Whipp
Freo,
Might help to show the query...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
After upsizing MS Access 2003 DB to a SQL Server I can see all the tables
with a respective data. But when I try to query date field I recived the
message "Microsoft Office Access has encountered a problem and needs to
close" ModName: vbe6.dll.
I can query other fields with out problems.
I tried to change the ODBC Connection (SQL Server, SQL Native, etc) but the
problem continues.
Please Help!!!

[Freo]

The Query:

SELECT dbo_Assignation.tra_ID, dbo_Assignation.tra_name,
dbo_Assignation.tra_hour, dbo_Assignation.tra_lamp,
dbo_Assignation.tra_mineplace, dbo_Assignation.tra_workdescription,
dbo_Assignation.tra_comments, dbo_Assignation.tra_lampreturn,
dbo_Assignation.tra_hourreturn
FROM dbo_Assignation
WHERE (((dbo_Assignation.tra_date)=#2/17/2009#));

Post by Gina Whipp
Freo,
You still have not show us the query in question...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
When I create with a Query Wizard a new Query I do not have any problem.
I can see all the records and I can to filter with any field except date
field.
When I try to filter with Date field I receive an error and Access closes.
When I do the same with an original (before upsizing) mdb file I do not have
problems.
Thank for your time.

Post by Gina Whipp
Freo,
Might help to show the query...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
After upsizing MS Access 2003 DB to a SQL Server I can see all the tables
with a respective data. But when I try to query date field I recived the
message "Microsoft Office Access has encountered a problem and needs to
close" ModName: vbe6.dll.
I can query other fields with out problems.
I tried to change the ODBC Connection (SQL Server, SQL Native, etc) but the
problem continues.
Please Help!!!

[Gina Whipp]

Freo,

Is that how the date is being stored on the SQL Server? Please open the
table and confirm because I believe it is how the SQL Server is 'seeing' the
date.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
SELECT dbo_Assignation.tra_ID, dbo_Assignation.tra_name,
dbo_Assignation.tra_hour, dbo_Assignation.tra_lamp,
dbo_Assignation.tra_mineplace, dbo_Assignation.tra_workdescription,
dbo_Assignation.tra_comments, dbo_Assignation.tra_lampreturn,
dbo_Assignation.tra_hourreturn
FROM dbo_Assignation
WHERE (((dbo_Assignation.tra_date)=#2/17/2009#));

Post by Gina Whipp
Freo,
You still have not show us the query in question...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
When I create with a Query Wizard a new Query I do not have any problem.
I can see all the records and I can to filter with any field except date
field.
When I try to filter with Date field I receive an error and Access closes.
When I do the same with an original (before upsizing) mdb file I do not have
problems.
Thank for your time.

Post by Gina Whipp
Freo,
Might help to show the query...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
After upsizing MS Access 2003 DB to a SQL Server I can see all the tables
with a respective data. But when I try to query date field I
recived
the
message "Microsoft Office Access has encountered a problem and needs to
close" ModName: vbe6.dll.
I can query other fields with out problems.
I tried to change the ODBC Connection (SQL Server, SQL Native, etc)
but
the
problem continues.
Please Help!!!

[Freo]

Hi Gina,
The date is stored as Datetime, not null.
If I use in the query the function Datevalue("date string") I can run the
query without errors but is is not what I want.

Thanks

Post by Gina Whipp
Freo,
Is that how the date is being stored on the SQL Server? Please open the
table and confirm because I believe it is how the SQL Server is 'seeing' the
date.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
SELECT dbo_Assignation.tra_ID, dbo_Assignation.tra_name,
dbo_Assignation.tra_hour, dbo_Assignation.tra_lamp,
dbo_Assignation.tra_mineplace, dbo_Assignation.tra_workdescription,
dbo_Assignation.tra_comments, dbo_Assignation.tra_lampreturn,
dbo_Assignation.tra_hourreturn
FROM dbo_Assignation
WHERE (((dbo_Assignation.tra_date)=#2/17/2009#));

Post by Gina Whipp
Freo,
You still have not show us the query in question...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
When I create with a Query Wizard a new Query I do not have any problem.
I can see all the records and I can to filter with any field except date
field.
When I try to filter with Date field I receive an error and Access closes.
When I do the same with an original (before upsizing) mdb file I do not have
problems.
Thank for your time.

Post by Gina Whipp
Freo,
Might help to show the query...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
After upsizing MS Access 2003 DB to a SQL Server I can see all the tables
with a respective data. But when I try to query date field I
recived
the
message "Microsoft Office Access has encountered a problem and needs to
close" ModName: vbe6.dll.
I can query other fields with out problems.
I tried to change the ODBC Connection (SQL Server, SQL Native, etc)
but
the
problem continues.
Please Help!!!

[Gina Whipp]

Freo,

Ummm, still... that is because I believe the SQL Server is storing DateTime.
Perhaps try using wrapping you Date part in the Format function to just pull
the Time and not show the date. Hope that made sense, if not post back!
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
Hi Gina,
The date is stored as Datetime, not null.
If I use in the query the function Datevalue("date string") I can run the
query without errors but is is not what I want.
Thanks

Post by Gina Whipp
Freo,
Is that how the date is being stored on the SQL Server? Please open the
table and confirm because I believe it is how the SQL Server is 'seeing' the
date.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
SELECT dbo_Assignation.tra_ID, dbo_Assignation.tra_name,
dbo_Assignation.tra_hour, dbo_Assignation.tra_lamp,
dbo_Assignation.tra_mineplace, dbo_Assignation.tra_workdescription,
dbo_Assignation.tra_comments, dbo_Assignation.tra_lampreturn,
dbo_Assignation.tra_hourreturn
FROM dbo_Assignation
WHERE (((dbo_Assignation.tra_date)=#2/17/2009#));

Post by Gina Whipp
Freo,
You still have not show us the query in question...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
When I create with a Query Wizard a new Query I do not have any problem.
I can see all the records and I can to filter with any field except date
field.
When I try to filter with Date field I receive an error and Access closes.
When I do the same with an original (before upsizing) mdb file I do
not
have
problems.
Thank for your time.

Post by Gina Whipp
Freo,
Might help to show the query...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm

Post by Freo
After upsizing MS Access 2003 DB to a SQL Server I can see all
the
tables
with a respective data. But when I try to query date field I
recived
the
message "Microsoft Office Access has encountered a problem and
needs
to
close" ModName: vbe6.dll.
I can query other fields with out problems.
I tried to change the ODBC Connection (SQL Server, SQL Native, etc)
but
the
problem continues.
Please Help!!!