Where Clause in embedded SQL is not finding record.

[Pack10]

I am setting an embedded SQL in code and it's not working. I'm a bit puzzled.

I have a date/time on a form. Ex. 11/10/2010 5:34:03 AM
and the SQL to pull the record from the table....

sSQL = "SELECT Task_Information.* FROM Tasks WHERE _
Task_Received = " & "#" & me.txtReceived & "#"

This code pulls no records and I know the record is there,
what am I doing wrong. Any help would be great.

 

[MajP]

add a debug.print and tell us what it returns
debug.print sSql

 

[dhookom]

Time values are prone to precision errors. Do you lose necessary functionality if you remove the time part of the value?

Your code can't possibly compile and the SQL references Task_Information in the SELECT and Tasks in the FROM :-(

sSQL = "SELECT [b][red]Task_Information[/red][/b].* FROM [b][red]Tasks[/red][/b] WHERE [b][red]_[/red][/b]
        Task_Received = " & "#" & me.txtReceived & "#"
p/code]

[COLOR=Blue][B][I]Duane
[url=http://www.access.hookom.net/]Hook'D on Access[/url]
[url=https://mvp.support.microsoft.com/profile/Duane.Hookom]MS Access MVP[/url][/I][/B][/color]

 

[Pack10]

Here is the SQL from the debugger

SELECT Task_Information.* FROM Task_Information WHERE Task_Received = 11/8/2010 7:52:15 AM

 

[Pack10]

Actually I was playing with it yesterday...this is the real code returned.

SELECT Task_Information.* FROM Task_Information WHERE Task_Received = #11/8/2010 7:52:15 AM#

 

[PHV]

I know the record is there
How do you know it ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

Its in the table.

 

[lionelhill]

have you tried converting your query into one specifying a small range of time, in case of precision errors?

 

[PHV]

Its in the table
With the very same timestamp ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MarkWalsh]

I've only used SQL in SQLite applications so I'm not sure if it's different in your application, but your date format is not the format I am used to using in SQL; are you sure that's the correct format used in your table?

Have you tried running the search without the 'WHERE' clause to verify that what you have in the table correctly matches your timestamp string?

 

[Pack10]

Hi thanks to everyone for their help. I did some testing.
I set a variable to the table instead of the SQL String and tried a do until loop testing in an IF statement...
IF rst!Task_Received = me.txtReceived Then..... and that finds the record.
So, I have a date/time field in the table and a text box on a form containing a date/time value.
My SQL String....

sSQL = "SELECT Task_Information.* FROM Task_Information WHERE Task_Received = " & "#" & me.txtReceived & "#"

returns nothing, .eof is true and .bof is true

 

[dhookom]

You have only showed us a single line of code. How about sharing your entire code as well as the results of using a date range?

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

First of all, this is an app that is in development. So, I wanted to clean out all the test records and start with fresh data, so I deleted the recs and did a compact and repair...so I could start with record #1 ....
Ever since then it has not worked correctly. So, yesterday, I took the table from the backup and did a copy paste of the structure and it was working, I will play with it more this morning but its wierd.

 

[Gruuuu]

Maybe I'm just being silly but...

sSQL = "SELECT * FROM Task_Information " _
      &"WHERE Task_Received = " & "#" & me.txtReceived & "#"

Furthermore, the table is in an Access Database? What is the datatype of the Task_Received field?