Please help... Deadline (ADO recordset.filter)

[greenfalcon]

Hey everyone, thanks for your help, im in a bind and need a little help... I am trying to search a database with excel and find out if a record is blank with recordset.filter... here is my code..

        rstTemp.Filter = "CityDataID=' " & strFilter & "' And Year=' ""' And Month=' ""'"

Pretty much i want it to find a primary key (CityDataID) and if it does find it see if the recors for Year and Month are blank. I cant seem to get this code to work... Any ideas? I would be very grateful

Thanks!

[Dove]

Not sure how to do it with a filter like that, but using traditional SQL
with a recordset object:

--------------------------

Dim string_variable as String
Dim No_CityDataID as Boolean
Dim No_Month as Boolean
Dim No_Year as Boolean
Dim Duplicate_Key as Boolean

' Dim and Define your recordset/connection here

rstTemp.Source = "SELECT * FROM [YourTableName] WHERE CityDataID = '" +
string_variable + "'"

rst.Open

If rstTemp.EOF Then
No_CityDataID = True
Goto SkipMe ' Without the Goto you will get an
error on the move next...
ElseIf rstTemp!Year = "" Then ' or Null, depending how how the database
handles empty data
No_Year = True
ElseIf rstTemp!Month = "" Then
No_Month = True
End If

rstTemp.MoveNext

'Optional Primary Key Check
If Not rst.EOF
Duplicate_Key = True
End If

SkipMe:
rstTemp.Close

-------------------------------------------

Substitue the Boolean variables with what you want to do when those
situations occur, and make sure to change [YourTableName] to whatever the
table's name is in the database.

David

"greenfalcon" <[email protected]>
wrote in message
news:[email protected]...
>
> Hey everyone, thanks for your help, im in a bind and need a little
> help... I am trying to search a database with excel and find out if a
> record is blank with recordset.filter... here is my code..
>
>
> Code:
> --------------------
> rstTemp.Filter = "CityDataID=' " & strFilter & "' And Year='
> ""' And Month=' ""'"
> --------------------
>
>
> Pretty much i want it to find a primary key (CityDataID) and if it does
> find it see if the recors for Year and Month are blank. I cant seem to
> get this code to work... Any ideas? I would be very grateful
>
> Thanks!
>
>
> --
> greenfalcon
> ------------------------------------------------------------------------
> greenfalcon's Profile:
> http://www.excelforum.com/member.php...o&userid=13622
> View this thread: http://www.excelforum.com/showthread...hreadid=560347
>

[Tim Williams]

You seem to have extra spaces in a few places (substituted below with *)

> rstTemp.Filter = "CityDataID='*" & strFilter & "' And Year='*""' And Month='*""'"

Tim
--
Tim Williams
Palo Alto, CA


"greenfalcon" <[email protected]> wrote in message
news:[email protected]...
>
> Hey everyone, thanks for your help, im in a bind and need a little
> help... I am trying to search a database with excel and find out if a
> record is blank with recordset.filter... here is my code..
>
>
> Code:
> --------------------
> rstTemp.Filter = "CityDataID=' " & strFilter & "' And Year=' ""' And Month=' ""'"
> --------------------
>
>
> Pretty much i want it to find a primary key (CityDataID) and if it does
> find it see if the recors for Year and Month are blank. I cant seem to
> get this code to work... Any ideas? I would be very grateful
>
> Thanks!
>
>
> --
> greenfalcon
> ------------------------------------------------------------------------
> greenfalcon's Profile: http://www.excelforum.com/member.php...o&userid=13622
> View this thread: http://www.excelforum.com/showthread...hreadid=560347
>