1

I'm trying to collect some data from a database using VBA code

I have the following

enter image description here

I have 2 variables (VIN_T & PN5D_T) and I want to return he RO_Num, for that, I have to use as a filter the 2 variables on my database. I have declared:

strQuery = "SELECT [RONum] FROM [dbo].[Warranty] WHERE RIGHT([VIN],6)=VIN_T AND LEFT([PN],5)=PN5D_T"

However, I get an error: Run-time error '-2147217900 (80040e14) Invalid column name 'VIN_T', I have tested the same query code with the direct value and the code works

strQuery = "SELECT [RONum] FROM [dbo].[Warranty] WHERE RIGHT([VIN],6)='470765' AND LEFT([PN],5)='27301'"

I have declared both variables as:

Dim VIN_T As String
Dim PN5D_T As String

So i don´t understand why I'm getting this error, therefore, I would like to know, how can I pass the 2 variables on my strQuery?

Thanks in advance

Rafael Rodrigues Santos
  • 482
  • 1
  • 11
  • 29
  • Set a break point and inspect the value of `strQuery`. That should give you some insight. – Brian M Stafford Mar 29 '23 at 18:53
  • Can you give me an example? – Rafael Rodrigues Santos Mar 29 '23 at 19:33
  • 2
    Advise not to use variable names same as field names. Must concatenate variables or use parameters. https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access – June7 Mar 29 '23 at 19:58
  • 1
    In the first query, the value of VIN_T is not be inserted into the string but rather the string VIN_T which is then evaluated as a column name. You must concatenate as @June7 mentioned. – Brian M Stafford Mar 29 '23 at 20:02
  • Does this answer your question? [concatenate in VBA ACCESS](https://stackoverflow.com/questions/16276656/concatenate-in-vba-access) – June7 Mar 29 '23 at 20:05

1 Answers1

1

Like this:

strQuery = "SELECT [RONum] FROM [dbo].[Warranty] WHERE " & _
           " RIGHT([VIN],6)='" & VIN_T & "' AND " & _
           " LEFT([PN],5)='" & PN5D_T &"'"
Tim Williams
  • 154,628
  • 8
  • 97
  • 125