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?
HKFueyAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
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
 
mbizupCommented:
Change the last line to this:

Sql = Sql & "WHERE tbl_Inventory.InternalKeyNumber = " & Ser
0
 
ASP8MBMConnect With a Mentor Commented:
try this:
Sql = "SELECT tbl_Inventory.InternalKeyNumber "
Sql = Sql & " FROM tbl_Inventory "
Sql = Sql & " WHERE tbl_Inventory.InternalKeyNumber = ' & Ser & "'"

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.