Stumped

[TheSearcher]

Hi All,
Maybe I've been up too late but I can't get the syntax right to give me what I want.
This works:
INSERT INTO t_SubmissionDates ( SubmissionDate )
SELECT #11/11/2013# AS Expr1;
But - I only want to insert the date if it doesn't already exist in the table. I tried the obvious, like isnull but nothing seems to work.
Any ideas? Thanks in advance.
TS

 

[Galaxiom]

Maybe your where clause is getting connected to the Select where you really want it applied to the INSERT.

I normally use this syntax when inserting values directly.

INSERT INTO t_SubmissionDates (SubmissionDate) VALUES(#11/11/2013#)
WHERE SubmissionDate Is Null

 

[Mihail]

But - I only want to insert the date if it doesn't already exist in the table.

That means that the Date field has NO duplicates, isn't it ?
So, create an index that not allow duplicates on this field.
Turn the alerts OFF before run the Append query and ON after this.

If you will use the EXECUTE method you will be able to check if a new record was added by using the RecordAffected function (property ?)

 

[TheSearcher]

Thanks Mihail for responding. I can't index the field because there are certain circumstances in which I would allow duplicates. I just need to protect myself for the instances in which I do not want them.

 

[jdraw]

We only know what you tell us about your application.

You either allow duplicates or you don't-- but it seems you only want dates conditionally.

Please be specific in WHAT you are trying to accomplish.

 

[TheSearcher]

Hi jdraw - It's true. I only want the dates conditionally. My understanding is that in MySql there is a way to accomplish this via a query. I was wondering if the same was true for Access. My guess, based on the research I've done so far, is that it can't be accomplished by a query alone. If it can - and someone can share the syntax - I would be grateful! If not, I will find a workaround.

 

[pr2-eugin]

So what happens when the Date exists in the table and you still want to add it? Based on the description above it will still not be possible to add that record..

Have you considered doing this in VBA?

 

[TheSearcher]

Hi pr2 - I am using VBA. This is my query that will insert the value. It works but I only want to insert the value if it's not already there.

INSERT INTO t_SubmissionDates ( SubmissionDate )
SELECT #11/11/2013# AS Expr1;

There doesn't seem to be a sytax in VBA that will allow me to do it.
Thanks for responding - TS

 

[Mihail]

If it can - and someone can share the syntax - I would be grateful! If not, I will find a workaround

As far as I know this is not possible.
The workaround is to use a DCount in order to count the records where date = YourDate.
If the result is > 0 then a message box will pop up and ask if the new record should be added or not.

 

[jdraw]

As you can see from responses to date, readers do not understand WHAT you are trying to do. Can you tell us in plain English WHAT you are trying to accomplish? You have told/shown us HOW you have tried to do "something", but it doesn't work.

I suggest you use an example, and lead us through a scenario of what you'd like to see as the result.

 

[TheSearcher]

Hi jdraw,
I explained very clearly what I was trying to do in several of my posts. It's very simple. I think it's impossible to explain it more clearly. I'm sorry that you are having difficulty understanding the issue. I think Mihail nailed it in the post previous to yours. He concluded that it is not possible. I agree with him. Thanks for taking the time to respond.
TS

 

[pr2-eugin]

Hi jdraw,
I explained very clearly what I was trying to do in several of my posts. It's very simple. I think it's impossible to explain it more clearly. I'm sorry that you are having difficulty understanding the issue. I think Mihail nailed it in the post previous to yours. He concluded that it is not possible. I agree with him. Thanks for taking the time to respond.
TS

In JD's defense.. I have to say I did not understand what exactly you were trying to accomplish.. I also disagree that it is impossible to explain better.. It sure is possible..

How you ask, show us some more code, explain where it fails, explain the relationship.

I might be wrong, but what Mihail, IMO would have meant was, it is impossible with just a Query.. That's the reason I butt in to ask about VBA..

Not sure what you went down with finally; but would help many if you could share your final result.

 

[TheSearcher]

Hi pr2,
In my first post I described what I wanted to accomplish. I will repost it below:

INSERT INTO t_SubmissionDates ( SubmissionDate )
SELECT #11/11/2013# AS Expr1;
But - I only want to insert the date if it doesn't already exist in the table.

It's as simple as that. Mihail correctly understood that I only wanted to accomplish this with a query.
Imagine an Access database that contains nothing but a table called t_SubmissionDates (no forms, no reports, no modules, no macros). Is it possible to construct a query that accomplishes what I have descibed above? After careful consideration I have concluded that it is not possible. If you conclude otherwise then please post your query!
Thank you for taking the time to review my post. I sincerely do appreciate it.
TS

 

[MSAccessRookie]

I have to wonder if he missed the possibility of searching for it before inserting.

INSERT INTO t_SubmissionDates (SubmissionDate) VALUES(#11/11/2013#)
WHERE DCount("*", "t_SubmissionDates",  "Nz(SubmissionDate, "")="") = 0

I suppose there is also a version that uses DLookup().

-- Rookie

 

[pr2-eugin]

Create a Standard module, paste the following code in there..

Option Compare Database
Option Explicit

Public Sub testMe()
    Dim entryDate
    entryDate = InputBox("Enter the Submission Date you like to Enter in the Table", "Enter a Date", Date())
    If DCount("*", "t_SubmissionDates", "SubmissionDate = " & Format(entryDate, "\#mm\/dd\/yyyy\#")) = 0 Then
        CurrentDB.Execute "INSERT INTO t_SubmissionDates (SubmissionDate) VALUES(" & Format(entryDate, "\#mm\/dd\/yyyy\#") & ");"
    Else
        MsgBox "Nah ! The Date - " & entryDate & " already exists in the Table.", vbInformation
    End If
End Sub

Save and compile the code.. To run it, you can use the Immediate Window..

 

[TheSearcher]

Thanks pr2. I'm getting a compile/syntax error on:
If DCount("*", "t_SubmissionDates", "SubmissionDate = " & Format(entryDate, "\#mm\/dd\/yyyy\#") & ") = 0

 

[pr2-eugin]

Check the edited code in post#15

 

[TheSearcher]

Compile/Syntax error on:
If DCount("*", "t_SubmissionDates", "SubmissionDate = " & Format(entryDate, "\#mm\/dd\/yyyy\#")) = 0

 

[pr2-eugin]

Okay I get a feeling SubmissionDate is not Date type is it?

 

[TheSearcher]

pr2 - I'm not seeing your posts online. I only see them in my email. Anyway, SubmissionDate is Date/Time. I included a "then" if your "if" statement and the code compiled correctly. However, when I run it it tells me that the date is already in the table (even if the table is blank).