UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert Statement With A Subquery, Access 2016    
 
   
magicscreen
post Dec 27 2017, 10:05 PM
Post#1



Posts: 451
Joined: 18-November 07



Table tblInsertInto
Dim strField1 As String
Dim strField2 As String
DimField3 As String

Table tblInsertFrom
Dim strFField1 As String
Dim strFField2 As String
Dim strFField3 As String
Dim dtFField4 As Date

Dim dtVariable as Date
Dim strSQL as string

dtVariable = #08/04/2017#

I want to insert records from tblInsertFrom into tblInsertInto
Where dtFField4 = dtvariable

Because this is an Insert query, I could not select based on the extra date field.
So, I tried to use the fiollowing SQL:

strSQL = “Insert Into tblInsertInto (strField1, strField2, strField3)
From tblInsertFrom Where Exists (Select f.* From tblInsertFrom As f Where f.dtFField4 = #” & dtVariable & “#)”

DoCmd.RunSQL (strSQL)

What is happening is that I am selecting ALL of the records in the tblInsertFrom table instead of only those records with matching dates.

Thanks for any help.

magicscreen
Go to the top of the page
 
MadPiet
post Dec 27 2017, 10:12 PM
Post#2



Posts: 2,507
Joined: 27-February 09



I want to insert records from tblInsertFrom into tblInsertInto
Where dtFField4 = dtvariable

Something like this?:

INSERT INTO tblInsertTo(Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM tblInsertFrom
WHERE Field4 = [Enter a value for Field 4:];

Go to the top of the page
 
magicscreen
post Dec 27 2017, 10:51 PM
Post#3



Posts: 451
Joined: 18-November 07



Thank you for the quick response.

The values I am checking is entered into a form.

Once it is entered I use it in a second form so I placed it into a public variable.

I use a public variable because I need the value in various places.

Thanks again,

magicscreen
Go to the top of the page
 
magicscreen
post Dec 27 2017, 10:57 PM
Post#4



Posts: 451
Joined: 18-November 07



A second reason is that in order to use the field as you suggested, I need to have it in the select statement but the date field is not part of the insert statement.

If I use it in the Select statement I would then need to make it part of the actual insert or the number of fields ne3eded will not match the number of fields being inserted.
Go to the top of the page
 
PhilS
post Dec 28 2017, 08:29 AM
Post#5



Posts: 509
Joined: 26-May 15
From: The middle of Germany


QUOTE
A second reason is that in order to use the field as you suggested, I need to have it in the select statement but the date field is not part of the insert statement.

Your second reason is as invalid as the first one. - Did you actually look at MadPiet's suggestion?

Go to the top of the page
 
magicscreen
post Dec 28 2017, 02:45 PM
Post#6



Posts: 451
Joined: 18-November 07



Yes, I tried it. I get the same results.

Instead of getting the records that match the criteria, I get all records in the table.

This is my code.

CODE
INSERT INTO tblEmailRecipients (EmpFullName, EmpEmail, Company, FollowUpID) SELECT tblTrackSales.Contact, tblCompany.Email, tblCompany.Company, tblTrackSales.ID FROM tblTrackSales INNER JOIN tblCompany ON tblTrackSales.ID = tblCompany.ID WHERE Exists (SELECT t.* FROM tblTrackSales AS t Where DateValue(t.FollowUpDate) = DateValue([Enter Email Date:]))


I tried it without the DateValue and got a type mismatch. I guess is that it did not take the date as a date.
Go to the top of the page
 
magicscreen
post Dec 28 2017, 10:57 PM
Post#7



Posts: 451
Joined: 18-November 07



I want to thank Madpiet and PhilS for your help.

Madpiet, thenk you. Your solution is the closest so far. At least I am able to get all of the records from the table.

When I tried my solutions, I got NO records.

Phil, you were also correct, both of my previous reasons were invalid. I must have had a brain cramp.

I tried Madpiet's solution and, although I was able to get records, I was not able to get records meeting the subquery's criteria.

I keep getting all of the records from the table.

I only have one record in my database with a date of 08/04/2016 which is what I tried to get but no luck.

This below query gives me a type mismatch error.

I don't know why.

strSQL = "INSERT INTO tbltempid ( ID, EmailSendDate ) SELECT ID, FollowUpDate FROM tblTrackSales WHERE DateValue(FollowUpDate)=DateValue([Enter Email Date:]);

DoCmd.RunSQL (strSQL)
Go to the top of the page
 
PhilS
post Dec 29 2017, 08:52 AM
Post#8



Posts: 509
Joined: 26-May 15
From: The middle of Germany


Did you make sure EmailSendDate and FollowUpDate are both of data type Date/Time?

I think the most likely cause for the type mismatch error is FollowUpDate not being of type Date/Time and containing string data that cannot be converted to a date.

What happens if you merge your current query with the original approach?

CODE
Dim dtVariable as Date
Dim strSQL as string

dtVariable = #08/04/2017#

strSQL = "INSERT INTO tbltempid ( ID, EmailSendDate ) SELECT ID, FollowUpDate FROM tblTrackSales WHERE FollowUpDate = #" & dtVariable & "#;"


But this also requires FollowUpDate to be an actual Date!

As the Date/Time data type seems to be the focal point here, I'd like to point to my recent text on the Date Data Type in VBA and Access for further reference.
Go to the top of the page
 
magicscreen
post Dec 29 2017, 07:00 PM
Post#9



Posts: 451
Joined: 18-November 07



Thanks for your response.

Some of my dates if followupdate are empty

I probably should get rid of them

Could they be causing the problem?

Go to the top of the page
 
PhilS
post Dec 30 2017, 04:47 AM
Post#10



Posts: 509
Joined: 26-May 15
From: The middle of Germany


QUOTE
Some of my dates if followupdate are empty

If they are empty strings (Text datatype) they will cause problems. If they are dates they should not.
Go to the top of the page
 
magicscreen
post Dec 30 2017, 07:53 AM
Post#11



Posts: 451
Joined: 18-November 07



Thanks PhilS, I deleted those records since I didn't need them but I am still having an issue.

Go to the top of the page
 
PhilS
post Dec 30 2017, 10:46 AM
Post#12



Posts: 509
Joined: 26-May 15
From: The middle of Germany


Ok, first can you please answer my question from the previous post?
QUOTE
Did you make sure EmailSendDate and FollowUpDate are both of data type Date/Time?

Then, can you please be a little bit more descriptive about "still having an issue"?
Go to the top of the page
 
magicscreen
post Dec 30 2017, 12:13 PM
Post#13



Posts: 451
Joined: 18-November 07



PhilS,

Thank you and DanielPineault for your help. I resolved my issue, it was a spelling mistake.

Everything seems to be working now.

My app allows me to email group recipients.

My app waits around an hour and then it goes through the Inbox of my Outlook and scans for returned emails due to no longer found and puts the email address into a table.

Next, my app goes through the table and searches for the email addresses in my main Access table and deletes the corresponding records so I don't resend the emails.

I changed my app a little. This is the query I am using as suggested by DanielPineault.

CODE
strSQL = "INSERT INTO tblEmailRecipients ( FollowUpID, Company, FollowUpDate, EmpFullName, Closed_YN, EmpEmail ) " & _
              "SELECT tblTrackSales.ID, NZ(tblTrackSales.Company, ''), NZ(tblTrackSales.FollowUpDate, ''), NZ(tblTrackSales.Contact, ''), " & _
              "tblTrackSales.Closed_YN, NZ(tblCompany.Email, '') FROM tblTrackSales INNER JOIN tblCompany ON tblTrackSales.ID = tblCompany.ID " & _
              "WHERE ((tblTrackSales.FollowUpDate >= [Enter Start Email Date] And tblTrackSales.FollowUpDate <= [Enter End Email Date]) AND tblTrackSales.Closed_YN=False); "
    
DoCmd.RunSQL (strSQL)

Thanks again both of you for all of your help.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 06:46 AM