Query to determine if records already exist in table

[WLC]

I have a file that I want to import on a daily basis and append to an existing table in my database. The date changes each day. I want to create a query that checks to see if the date (of the first record) already exists before I import and append the new file. If it does, I want to show a message saying something to the effect of "This file has already been imported".

 

[MarkK]

The first record in the file or in the database? Or both?

 

[WLC]

Check to see if the first record of the file already exists in the database.

 

[MarkK]

You can count records that satisfy certain criteria using the DCount() function, or by opening a recordset that uses Count(*). Here's example using DCount() to see if any dates in a table are greater than our TestDate, which is what I think you are trying to do . . .

Function DateAlreadyExists(TestDate as Date) as Boolean
[COLOR="Green"]'  This function returns True if at least one record in the table
'  has a Date value equal to or greater than our TestDate
[/COLOR]   DateAlreadyExists = DCount("*", "YourTable", "DateFieldInTable >= #" & TestDate & "#")
End Function

So now you can test using that function before running your process, like . . .

   If Not DateAlreadyExists(Date()) then 
[COLOR="Green"]      'do something here[/COLOR]
   End If

hth

 

[WLC]

I'm not quite sure what you're saying is what I want to do.

I have 2 tables. I want to make sure table #1 has not already been appended to table #2. I can do that by comparing the date from record #1 table #1 to the date field from table #2. If the date from record #1 table #1 exists on table #2 I need an error message to pop up and all processing to stop. If it doesn't exist, then I'll continue on with my processing.

Is there not a way to be able to do this in a query?

 

[MarkK]

"I need an error message to pop up"

Wouldn't that be something you do in code?

 

[WLC]

I suppose, but I need someone to walk me through what goes where as I'm not as familiar with code.

Could it be a query and then a macro maybe for the error message?

I do apologize for my ignorance but I'm still definitely in the learning stage.

 

[MarkK]

Do you have anything working so far? Any code you can post, or a database or anything. Then we can build on that.

 

[WLC]

I don't have anything code wise. But I can post a stripped down version of my database first thing in the morning if that's ok.

 

[MarkK]

Well, what's the scope of work? I got started here thinking we need to check if a thing exists, and that is solved. But now we don't have a button or any code or anything. Do we have a working query?

 

[WLC]

Well this is something I thought of after the fact. My database is built. I have a macro that does the import (this will eventually become a button that you press) which made me start thinking there are no warnings and right now I could potentially import a file more than one time. That's why I want to do the check. I don't have a working query because I didn't know how to do it.

 

[MarkK]

Well, the macro can call the function to run the check and show a message box if it fails.

 

[WLC]

Here is a really stripped down version of my database. Basically the one table I append to and my macro. Also in the zip file is the file I would append from. So how would the macro call the function to run the check and and show a message box if it fails?