Data Type Mismatch

[rochak]

Hi,

I am facing problems in doing this:

I have an access query that is query using linked tables to SQL server. My sql table has a field called ImportDate with datatype date/time and its sample value is like: 10/18/2005 11:56:36 AM

I have now an access query which has to gather records from this table only based upon the ImportDate specified by the user. I have built a form for this purpose. In the access design query I added another another column with criteria [Forms]![QueryForm2]![singleDate]. singleDate is the name of the textbox control that contains the import date inputted by the user and QueryForm2 is the name of my form.

Now the problem, if I enter in the text field 10/18/2005 11:56:36 AM and click on RunQuery, I am returned a blank query with no records shown (though I know there is). Someone mentioned to me that the date should be in the format #10/18/2005 11:56:36 AM#. When I type this in the text field, I get a weird message saying "You cancelled the previous operation". For further debugging, I went to the design query and changed the criteria from [Forms]![QueryForm2]![singleDate] to #10/18/2005 11:56:36 AM# and ran the query. Then I got an error message saying "Syntax Error converting Date Time to Character String"

I am really tensed. Any help would be gladly accepted. Thanks

[nagasrikanth]

#" & [Forms]![QueryForm2]![singleDate] & "#

This should work...if not can u post the code that u used..orelse the database..

Thanx & regards
Anu,

[rochak]

Anu, the moment I paste this in the design query criteria and try to move away from it I get this error dialog "The expression you entered has an invalid date value"

[nagasrikanth]

ooopss...is it...
then if it is the case i need any one aither code or aither data..
untill ..unless im help less...

[rochak]

As I said the table is an SQL table and not access. Hence even if I send you the query design, you won't be able to run it since the table would be missing.

I am helpless here too. Thanks

[nagasrikanth]

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("select datec from tbl_cdm_feedback where datec=#" & Me.Text1 & "#")
If Not rst.RecordCount = 0 Then
    MsgBox rst("datec")
End If

this is what the code im using for the same case ,
Im also using sql server tables which are linked in my access database..
and the datec column is having data like "dd-mmm-yy hh:mm:ss"(exactly how u are sayin)
but im not getting any error when i use like this...

[rochak]

Anu,

I am not using VBA for this query. I am using the access design query. Anyway, here is the SQL code generated by the access design

SELECT [Security Master Data].ImportDate
FROM [Security Master Data]
WHERE ((([Security Master Data].ImportDate)=#10/18/2005 11:56:36#));

The output is blank - no records.

Thanks

[nagasrikanth]

okay ...i got u now..

first of all ...r u sure there is a record with the same data that u provide and the same format..??

[rochak]

Yes, Anu there is.

I am working on it. Will keep posting with the new developments. Thanks till then