SQL in VBA

[Dizzzy44]

Hi all

I am having an issue with this statement.

I have Dim'd CalcSQL as String and am getting a Compile Error message saying that the Method or Data Member not found on the 'Me.txtStateElec' piece of code.

I have checked that the spelling matches and even copied all code and controls into another form. It is now starting to really annoy me ANy help is much appeciated.

CalcSQL = "SELECT tblStdEFElectricity.Scope2EmissionFactor1, tblStdEFElectricity.EmissionFactorRegion, tblStdEFElectricity.FinancialYear " & _
          "FROM tblStdEFElectricity " & _
          "WHERE tblStdEFElectricity.EmissionFactorRegion='" & Me.txtStateElec & "' AND tblStdEFElectricity.FinancialYear='" & Me.txtFYElec & "';"

 

[Poppa Smurf]

I had the same problem using SQL and Me.fieldname. So I replaced the Me. with the following format [forms]![name of the form]![field name]

So, replace Me.txtStateElec
with [forms]![name of your form]![txtStateElec]

 

[ajetrumpet]

is "Me.txtStateElec" produced by the intellisense dropdown?

 

[DCrake]

Try

CalcSQL = ""
CalcSQL = CalcSQL & "SELECT Scope2EmissionFactor1, EmissionFactorRegion, FinancialYear "
CalcSQL = CalcSQL & "FROM tblStdEFElectricity "
CalcSQL = CalcSQL & "WHERE EmissionFactorRegion='" & Me.txtStateElec & "' "
CalcSLQ = CalcSQL & "AND FinancialYear='" & Me.txtFYElec & "';"

Debug.Print CalcSQL

Note: When generating sql based on one table only there is no need to refer to the table for each field in table.

What appears in the immediate window?

 

[Dizzzy44]

I had the same problem using SQL and Me.fieldname. So I replaced the Me. with the following format [forms]![name of the form]![field name]

So, replace Me.txtStateElec
with [forms]![name of your form]![txtStateElec]

I tried this and still got the same error message.

The txtStateElec is a dropdown. Does this present an issue it hasn't in the past?

 

[Dizzzy44]

Try

CalcSQL = ""
CalcSQL = CalcSQL & "SELECT Scope2EmissionFactor1, EmissionFactorRegion, FinancialYear "
CalcSQL = CalcSQL & "FROM tblStdEFElectricity "
CalcSQL = CalcSQL & "WHERE EmissionFactorRegion='" & Me.txtStateElec & "' "
CalcSLQ = CalcSQL & "AND FinancialYear='" & Me.txtFYElec & "';"

Debug.Print CalcSQL

Note: When generating sql based on one table only there is no need to refer to the table for each field in table.

What appears in the immediate window?

This actually ran. Why would this be?

 

[DCrake]

Because I'm a clever git. Actually it could be to do with spaces in syntax.

What you can do to prove this is to repeat your version of CalcSQL and compare the differences.

 

[Khalid_Afridi]

When is saw your code:

Hi all

I am having an issue with this statement.

CalcSQL = "SELECT tblStdEFElectricity.Scope2EmissionFactor1, tblStdEFElectricity.EmissionFactorRegion, tblStdEFElectricity.FinancialYear " & _
          "FROM tblStdEFElectricity " & _
          "WHERE tblStdEFElectricity.EmissionFactorRegion='" & Me.txtStateElec & [COLOR="Red"]"' AND [/COLOR]tblStdEFElectricity.FinancialYear='" & Me.txtFYElec & "';"

I think the problem was in
WHERE tblStdEFElectricity.EmissionFactorRegion='" & Me.txtStateElec & "' AND
The quotation was not properly applied.