Select from a table in an IF statement

[gmazza via AccessMonster.com]

Hey there,
I am trying to determine if a value from a field in a recordset exists in
another table but I am not sure how to code it.
I open a recordset and when I hit the correct field, I check if it is null,
if not then I want to see if the value of the field exists in another table.
Here is part of my code:
Set rs = CurrentDb.OpenRecordset("Select * from 455Export")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF

For Each fld In rs.Fields

If fld.Name = "FundCode" Then
If IsNull(fld.Value) Then
Else
If fld.Value in (Select Code from TaxArea) then
End If
End If

I know this line is wrong but I need something like it:
If fld.Value in (Select Code from TaxArea) then

Any help is appreciated, thanks!

 

[Stuart McCall]

Hey there,
I am trying to determine if a value from a field in a recordset exists in
another table but I am not sure how to code it.
I open a recordset and when I hit the correct field, I check if it is
null,
if not then I want to see if the value of the field exists in another
table.
Here is part of my code:
Set rs = CurrentDb.OpenRecordset("Select * from 455Export")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF

For Each fld In rs.Fields

If fld.Name = "FundCode" Then
If IsNull(fld.Value) Then
Else
If fld.Value in (Select Code from TaxArea) then
End If
End If

I know this line is wrong but I need something like it:
If fld.Value in (Select Code from TaxArea) then

Any help is appreciated, thanks!

You could create a boolean function, passing fld.value as a parameter, to
open a recordset using your 'Select Code from TaxArea' using your passed-in
value as a criteria. Have the function return True if the RecordCount is >0.

Then, back in your loop:

If MyFunction(fld.Value) Then

 

[John Spencer]

If all you need to know is that the code exists then use DCount or DLookup
If IsNull(DLookup("Code","TaxArea","Code=""" & fld.Value & """")) Then
...
End If

If you need more than knowing if the value exists in TaxArea then you might
need to open another recordset based on a SQL that looks like

strSQL = "SELECT * From TaxArea WHERE Code =""" & fld.Value & """"
Set rstTwo = Currentdb.OpenRecordset(strSQL)
If rstTwo.Count > 0 THen

End if

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[gmazza via AccessMonster.com]

Thats what I need, thanks John!

If all you need to know is that the code exists then use DCount or DLookup
If IsNull(DLookup("Code","TaxArea","Code=""" & fld.Value & """")) Then
...
End If

If you need more than knowing if the value exists in TaxArea then you might
need to open another recordset based on a SQL that looks like

strSQL = "SELECT * From TaxArea WHERE Code =""" & fld.Value & """"
Set rstTwo = Currentdb.OpenRecordset(strSQL)
If rstTwo.Count > 0 THen

End if

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hey there,
I am trying to determine if a value from a field in a recordset exists in

[quoted text clipped - 20 lines]

Any help is appreciated, thanks!