I need help creating a Select string 1

[RickLiebespach]

Using VB.Net...
I have an Access table with the following fields:
Unique_ID - Integer
Completed - Date/Time
User_ID - Integer

I need to get all records for the current user where the Completed field is empty.

I've tried the following but it isn't working right...
I think it's how I'm testing for an empty Date/Time field,
what should it be???

SelectCommand = & _
"SELECT " & _
"Activities.Unique_ID, " & _
"Activities.User_ID, " & _
"Activities.Completed " & _
"FROM Activities " & _
"WHERE Activities.User_ID = " & Session("OCM_User_ID") & _
" AND " & IsDBNull("Activities.Completed")



Rick Liebespach

http://www.omniagroup.com

http://www.careermaze.com

 

[bigmelon]

What error or problem are you having now?

Did you try "Activities.Completed" = "" or
"Activities.Completed" = Nothing

 

[chrissie1]

"SELECT " & _
         "Activities.Unique_ID, " & _
         "Activities.User_ID, " & _
         "Activities.Completed " & _
 "FROM Activities " & _
 "WHERE Activities.User_ID = " & Session("OCM_User_ID") & _
 " AND Activities.Completed IS NULL"

BTW try reading up on sqlparameters they will prevent something called sql-injection wich can be very dangerous


Christiaan Baes
Belgium

What a wonderfull world - Louis armstrong

 

[RickLiebespach]

Yes, I tried those...
part of the problem is I have an ORDER BY clause after the WHERE clause... I didn't mention it because I didn't think it was relevant to the question.

The Select runs but returns an incorrect dataset...
I have 4 records, 3 with a Completed date, and 1 without a Completed date...
I want just that last record.


SelectCommand = & _
"SELECT " & _
"Activities.Unique_ID, " & _
"Activities.User_ID, " & _
"Activities.Completed " & _
"FROM Activities " & _
"WHERE Activities.User_ID = " & Session("OCM_User_ID") & _
" AND " & IsDBNull("Activities.Completed") & _
" ORDER BY Activities.Start_Time DESC "



Rick Liebespach

http://www.omniagroup.com

http://www.careermaze.com

 

[Kris11]

Rick,

Chrissie's SQL should work fine, except that you need to add the ORDER BY clause to it.

"SELECT " & _
         "Activities.Unique_ID, " & _
         "Activities.User_ID, " & _
         "Activities.Completed " & _
 "FROM Activities " & _
 "WHERE Activities.User_ID = " & Session("OCM_User_ID") & _
 " AND Activities.Completed IS NULL" & _
" ORDER BY Activities.Start_Time DESC "

-Kris

 

[chrissie1]

may we presume user_id is numeric?

Christiaan Baes
Belgium

What a wonderfull world - Louis armstrong

 

[RickLiebespach]

Christiaan,
Thank you for your post. It worked. My previous post was being created/posted at the same time you sent your response.

Thanks!

Rick Liebespach

http://www.omniagroup.com

http://www.careermaze.com