Having an issue with a select statement not returning a record. 1

[Pack10]

I am trying to get an SQL statement to work. It should return 1 record and it's not. The field Task_Received is a date time type.
And the record shows the full date and time.


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

Set rst = db.OpenRecordset(sSQL)

 

[lameid]

Try debuging and printing your sSQL variable in the immediate window.... of course copy and paste that into the SQL view of a new empty query and investigate why it does not work.

Perhaps there is an issue with time part of the datetime data?

Only other thought is that the control value is somehow not commited when you run the code.... If it lost focus by clicking on a button you should be good.

 

[SkipVought]

hi,

Is Task_Received Date/Time?

If so, are there only INTEGER Date/Time values? If not, then

"WHERE CLng(Task_Received) = CLng(#" & Me.txtReceived & "#)"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Skip's solution is getting to the date part of the date/Time...

It is based on the fact that time is stored in the decimal portion of the field where as the days is stored in the numeric portion... While this has been true for some time, I would not code it that way assuming it won't change. Assuming TxtReceived is to be a date (something to validate elsewhere)...

"WHERE Task_Received Between #" & Me!txtReceived & "# And #" & & "#" & DateAdd("s",-1,DateAdd("d",1,Me!txtReceived)) & "#"

You could also just get the date portion with something like...

DateSerial(Year(Me!txtReceived),Month(Me!txtReceived),Day(Me!txtReceived))

 

[dhookom]

You can also try:

sSQL = "SELECT * FROM Task_Information WHERE DateValue(Task_Received) = #" & Me.txtReceived & "#"

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

The Task_Information field is a date time field. The data is actually the received time of an email. So, its a full date time value. It is used because it is a unique value.
The user selects the date time value from a grid and the date time is stored on a user form. I use this to update the status, so the record disappears from the grid on a refresh.
There are few examples of this....is it a no-no to use a date time value to retrieve records. Date fields work fine.

 

[Pack10]

Hi all...
Lameid's solution worked. I changed it a little by adding 1 to the second and changing the day to 0....so the between would find the appropriate record. I have tested it two times and it seems to do the trick. But I will test it some more....anyway thanks much.

"WHERE Task_Received Between #" & Me!txtReceived & "# And #" & & "#" & DateAdd("s",1,DateAdd("d",0,Me!txtReceived)) & "#

 

[lameid]

Intersting... I was in a hurry earlier and made a syntax mistake. You should not need 2 amperstands in a row.... or more than one #...

"WHERE Task_Received Between #" & Me!txtReceived & "# And #" &  DateAdd("s",-1,DateAdd("d",1,Me!txtReceived)) & "#"

That being said I'm not sure why you are not getting a syntax error with what you have...

Beyond that, Datetime should work anyway with your original code ASSUMING that you have the Full Datetime in your control... I'd also change your Me.txtReceived in that example to Me!txtReceived. But I expect if your not getting an error using the period it is doing what you expect.

The issue with Datetime is that it can be difficult to get an exact match when using time.