VBA SQL, INSERT INTO where SELECT is VALUE

[trickshot1126]

Hello and happy holiday weekend for us in the US.

I'm having an issue with some SQL code formatting. I am attempting to insert into a table where one of the values would be selected from another table and the remainders are set earlier in the SUB.

            TSsql = "INSERT INTO [tblFetchTimer]" _
            & " SELECT LAST TS FROM tblPolNum WHERE tblPolNum.LANID='" & UsName & "' AND tblPolNum.PolNum='" & Me.txtPolNum & "', '" & UsName & "', '" & Me.txtPolNum & "', " & FtTime & ""
            DoCmd.RunSQL TSsql

I have also tried

            TSsql = "INSERT INTO [tblFetchTimer]" _
            & " VALUES ((SELECT LAST TS FROM tblPolNum WHERE tblPolNum.LANID='" & UsName & "' AND tblPolNum.PolNum='" & Me.txtPolNum & "'), '" & UsName & "', '" & Me.txtPolNum & "', " & FtTime & ");"
            DoCmd.RunSQL TSsql

Any insight would be greatly appreciated.

Thanks for your time!

Rob

 

[SkipVought]

From MS Acess HELP

https://msdn.microsoft.com/en-us/library/bb208861(v=office.12).aspx

 

[Andrzejek]

I'm having an issue with some SQL code formatting" - so what is the issue?
Do you get any error(s)?

Also, what do you get when you do:

TSsql = "INSERT INTO ...[blue]
Debug.Print TSsql[/blue]

Also, do you get the information you need out of your SELECT statement?
If so, do you get the right number of fields in the right order?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[trickshot1126]

Thanks SkipVought... I will review that.

Andrzajek, the first option the error is get is:

Run-Time erro '3075':
Syntax error (Missing Operator) in query expression 'LAST T'.

Debug.Print = INSERT INTO [tblFetchTimer] VALUES ((SELECT LAST TS FROM tblPolNum WHERE tblPolNum.LANID='rlh745' AND tblPolNum.PolNum='1234567'), 'rlh745', '1234567', 11.48438);

The second option i get:

Run-Time erro '3075':
Syntax error in query expression '(SELECT LAST TS FROM tblPolNum WHERE tblPolNum.LANID='rlh745 AND tblPolNum.PolNum='1234567)'.

Debug.Print = INSERT INTO [tblFetchTimer] SELECT LAST TS FROM tblPolNum WHERE tblPolNum.LANID='rlh745' AND tblPolNum.PolNum='1234567'), 'rlh745', '1234567', 11.95703

The Select statement seems to be the error.

 

[MakeItSo]

Is your column name "LAST TS" or "TS"?
If it is "LAST TS", enclose it in brackets:

SELECT [LAST TS] FROM

If it is "TS" and you want the last record, use BOTTOM 1 instead:
SELECT BOTTOM 1 TS FROM...

Cheers,
MakeItSo

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[Andrzejek]

It looks like you first have to create a valid and correct SELECT statement:



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[trickshot1126]

Thank you all for your help.

I was able to get the code to work by reworking the order i had the insert into fields.

See the working code:

            DB.Execute " INSERT INTO tblFetchTimer (LANID,PolNum,FetchTime,TS)" _
            & " SELECT '" & UsName & "', '" & Me.txtPolNum & "', '" & FtTime & "', LAST(TS) FROM[tblPolNum]" _
            & " WHERE tblPolNum.LANID='" & UsName & "' AND tblPolNum.PolNum='" & Me.txtPolNum & "';"
            DB.Close

Hope this helps someone out!