• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

VBA syntax MS Access

I have this code:
Dim Sql As String
Dim Dbs As Database
Set Dbs = CurrentDb
Dim rst As Recordset
Dim Ser As Integer
Ser = Me!SerialNumber

Sql = "SELECT tbl_Inventory.InternalKeyNumber "
Sql = Sql & "FROM tbl_Inventory "
Sql = Sql & "WHERE tbl_Inventory.InternalKeyNumber = Ser"

The criteria is a number but if I print the SQL I get: -
SELECT tbl_Inventory.InternalKeyNumber FROM tbl_Inventory WHERE tbl_Inventory.InternalKeyNumber = Ser

Can anyone help?
0
HKFuey
Asked:
HKFuey
  • 4
  • 4
2 Solutions
 
mbizupCommented:
Change the last line to this:

Sql = Sql & "WHERE tbl_Inventory.InternalKeyNumber = " & Ser
0
 
ASP8MBMCommented:
try this:
Sql = "SELECT tbl_Inventory.InternalKeyNumber "
Sql = Sql & " FROM tbl_Inventory "
Sql = Sql & " WHERE tbl_Inventory.InternalKeyNumber = ' & Ser & "'"

0
 
mbizupCommented:
In explanation, Ser need to be outside of the quotes and concatenated to the SQL string in order for it's variable value to be used.

If it is inside the quotes, it is treated as literal text.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
HKFueyAuthor Commented:
Thanks for quick response. Variable Ser should be a number so this is still not working for me, I get: -

SELECT tbl_Inventory.InternalKeyNumber FROM tbl_Inventory WHERE tbl_Inventory.InternalKeyNumber =' & Ser & '
0
 
mbizupCommented:
Did you try my response (first post)?

Sql = Sql & "WHERE tbl_Inventory.InternalKeyNumber = " & Ser
0
 
HKFueyAuthor Commented:
I tried dim Ser as integer
Ser = value(Me!Serial)
(Me!serial is Text box on form)
0
 
mbizupCommented:
Understood...

What are your results when you use this?

Sql = "SELECT tbl_Inventory.InternalKeyNumber "
Sql = Sql & "FROM tbl_Inventory "
Sql = Sql & "WHERE tbl_Inventory.InternalKeyNumber = " & Ser
0
 
HKFueyAuthor Commented:
mbizup you get the points, I've got a bit further and now need to log a question re "You mus use dbseechanges with openrecordset when accessing SQL server table that has an identity column"
0
 
HKFueyAuthor Commented:
Thanks!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now