Select Query for VBA - SQL

Question

Can someone correct this VBA Sql for me please

SQL = "SELECT * from [AnalyzedEmployeeStats] " & " WHERE [AnalyzedEmployeeStats].[UID] = " & "[AnalystInfo].[UID]" And "[AnalystInfo].[TLUID] = " & "' & TL_UID_Sending_Report & '"

I am getting "Type mismatch" error.

Below is what I have from Access SQL and it is working fine. Just need to change this to a vba-sql.

SELECT AnalyzedEmployeeStats.UserID, AnalystInfo.TLUID
FROM AnalyzedEmployeeStats, AnalystInfo
WHERE (((AnalyzedEmployeeStats.UserID)=[AnalystInfo].[UID]) AND ((AnalystInfo.TLUID)="abc123"));

"abc123" will be replaced by a string variable "TL_UID_Sending_Report"

YowE3K · Accepted Answer · 2018-01-12 17:35:23Z

1

The portion of your statement saying

"[AnalystInfo].[UID]" And "[AnalystInfo].[TLUID] = "

is not valid as you cannot perform a logical operation such as And on String values.

I assume you meant to say

"[AnalystInfo].[UID] And [AnalystInfo].[TLUID] = "

This will then allow your statement to run, but it will set SQL to the string

SELECT * from [AnalyzedEmployeeStats]  WHERE [AnalyzedEmployeeStats].[UID] = [AnalystInfo].[UID] And [AnalystInfo].[TLUID] = ' & TL_UID_Sending_Report & '

That is not a likely SQL statement (unless you do have records where the TLUID field actually does have a value of " & TL_UID_Sending_Report & ").

I believe you want the following VBA statement:

SQL = "SELECT * from [AnalyzedEmployeeStats] WHERE [AnalyzedEmployeeStats].[UID] = [AnalystInfo].[UID] And [AnalystInfo].[TLUID] = '" & TL_UID_Sending_Report & "'"

which, when split into several lines to improve readability, becomes:

SQL = "SELECT * from [AnalyzedEmployeeStats] " & _
      " WHERE [AnalyzedEmployeeStats].[UID] = [AnalystInfo].[UID] And " & _
            " [AnalystInfo].[TLUID] = '" & TL_UID_Sending_Report & "'"

answered Jan 12, 2018 at 17:35

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3323922 Over a year ago

I am getting "Too few parameters. Expected 2". I updated the original question with SQL in Access. Please refer.

YowE3K Over a year ago

In that case, change the SELECT * from [AnalyzedEmployeeStats] to SELECT AnalyzedEmployeeStats.UserID, AnalystInfo.TLUID FROM AnalyzedEmployeeStats, AnalystInfo.

user9210111 · Accepted Answer · 2018-01-12 17:39:57Z

0

You've got some unbalanced quotes. The word And is outside of your quotes and causing your error.

I think you also need to fix the quotes around TL_UID_Sending_Report, though I can't say for sure since I don't know what you're trying to do.

You might want to consider reading SQL from a .txt versus writing the SQL directly into VBA. If you ever change this query, you'll have a very annoying time trying to parse line continuations to change the bits you want.

answered Jan 12, 2018 at 17:39

user9210111

Collectives™ on Stack Overflow

Select Query for VBA - SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related