Check query for data (1 Viewer)

[mrssevans]

I have been working all morning to figure this out and I can't seem to find any information on it. I am trying to produce some code to check a query and see if it is empty. Here is what I am using:

Option Compare Database
Public Function VersionChk()
DoCmd.OpenQuery "Version Qry"
If IsNull(Version) = True Then
    MsgBox "No Update Needed", vbOKOnly
Elseif IsNull(Version) = False Then
    MsgBox "Needs Update", vbOKOnly
End If
End Function

It keeps coming back telling me it has data. I run the query, but no data. Here is the query

SELECT [Version TBL1].Version, [Version TBL].Version
FROM [Version TBL] RIGHT JOIN [Version TBL1] ON [Version TBL].Version = [Version TBL1].Version
WHERE ((([Version TBL].Version) Is Null));

I'm just comparing the version(the only field in the table). Please help, it's driving me mad.

 

[modest]

You can't call a field that way. It's looking for a variable/function in VBA.

I'm not sure if you would call it like IsNull([Version Qry]!Version), but there are better ways to find if a field is populated.

You should look at Dlookup() ... or open the query with a Recordset variable and check the field that way.

 

[mrssevans]

Thank you for the reply. Is there a better way to compare the two values in the table? There will never be more than 1 value in either table and they will either match or not. If they don't match I am going to run a batch file which I have setup and that works fine. This is my only problem area. Any suggestions?

 

[ghudson]

I am guessing that you are tyring to compare a "version" number between a front end and a backend or something like that?

As mentioned, use a DLookup to get the values and then test if the two strings match.

If DLookup("[Field1]", "Table1", "[RecordID] = " & "'1'") <>  DLookup("[Field2]", "Table2", "[RecordID] = " & "'1'") Then

 

[ghudson]

There will never be more than 1 value in either table and they will either match or not.

Are you sure? I always use a table field named RecordID and give the field a value of 1. Then I reference that field to ensure I am getting the value I want. See above code.

 

[mrssevans]

I am sure it won't have more than one, but your code looks like it is going to work great. I am going to give it a try right now. Thank you so much!!!

***That worked great.****

 

[Pat Hartman]

If RecordID is numeric, the syntax should be:
If DLookup("[Field1]", "Table1", "[RecordID] = 1") <> DLookup("[Field1]", "Table2", "[RecordID] = 1") Then

 

[mrssevans]

Thank you Pat. Right now I have them all set to text, but I will keep that in mind in case I make any changes.
Thanks again