I am having problems with variables in SQL statements

Question

I am kind of a newbie with VBA. I have this routine that has three recordsets. The first two will get the part number and the date, the last one will give the OH inventory for that date and part number. I then use the three variables in an append query to create an inventory table per day. I am getting stuck while opening the third recordset, as I am coming up with no records, but I know that there is OH inventory for the first item on the first date. Here is the definition of the SQL string with the variables:

StrSQL2 = "            SELECT  top 1 "
StrSQL2 = StrSQL2 & "  dbo_ViewQtStock.KIPRODMAG, "
StrSQL2 = StrSQL2 & "  dbo_ViewQtStock.DTTRANS, "
StrSQL2 = StrSQL2 & "  dbo_ViewQtStock.QTSTOCK  "
StrSQL2 = StrSQL2 & "  FROM dbo_ViewQtStock     "
StrSQL2 = StrSQL2 & "  WHERE (((dbo_ViewQtStock.KIPRODMAG)=" & KIPRODMAG & ") "
StrSQL2 = StrSQL2 & "  AND ((dbo_ViewQtStock.DTTRANS)<='" & DTTRANS & "')) "
StrSQL2 = StrSQL2 & "  ORDER BY dbo_ViewQtStock.DTTRANS DESC "

The part number KIPRODMAG is defined as an integer and the date DTTRANS as string (varchar type on server). I used the Debug.Print to he me figure some things out:

Debug.Print DTTRANS = 20130501
Debug.Print KIPRODMAG = 1 

Debug.Print StrSQL2 = 
SELECT  top 1            
dbo_ViewQtStock.KIPRODMAG,            
dbo_ViewQtStock.DTTRANS,            
dbo_ViewQtStock.QTSTOCK             
FROM dbo_ViewQtStock                
WHERE (((dbo_ViewQtStock.KIPRODMAG)=0)            
AND ((dbo_ViewQtStock.DTTRANS)<=''))            
ORDER BY dbo_ViewQtStock.DTTRANS DESC

I cannot unerstand why it assigns null in lieu of 20130501 and 0 in lieu of 1. Can anyone out there help me figure this out?

In case it is required, here is the complete routine:

Option Compare Database

Public Sub Stock1()
Dim cnn1 As ADODB.Connection
Dim cnn2 As ADODB.Connection
Dim cnn3 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Set cnn2 = CurrentProject.Connection
Set cnn3 = CurrentProject.Connection
Dim RS_IPRODMAG As New ADODB.Recordset
Dim RS_Date As New ADODB.Recordset
Dim RS_Stock As New ADODB.Recordset
RS_IPRODMAG.ActiveConnection = cnn1
RS_Date.ActiveConnection = cnn2
RS_Stock.ActiveConnection = cnn3
Dim StrSQL0 As String
Dim StrSQL1 As String
Dim StrSQL2 As String
Dim StrSQL3 As String
Dim DTTRANS As String
Dim KIPRODMAG As Integer
Dim QTSTOCK As Integer

'_____________________________________________________________________________
'RS_IPRODMAG
StrSQL0 = "            SELECT "
StrSQL0 = StrSQL0 & "  dbo_IPRODMAG.KIPRODMAG "
StrSQL0 = StrSQL0 & "  FROM dbo_IPRODMAG "
StrSQL0 = StrSQL0 & "  INNER JOIN dbo_VIProduit "
StrSQL0 = StrSQL0 & "  ON dbo_IPRODMAG.KIPRODUIT = dbo_VIProduit.KIPRODUIT "
StrSQL0 = StrSQL0 & "  WHERE (((dbo_VIProduit.flstock)=1) "
StrSQL0 = StrSQL0 & "  AND ((dbo_VIProduit.fllocation)=1)) "
StrSQL0 = StrSQL0 & "  ORDER BY dbo_IPRODMAG.KIPRODUIT "

'_____________________________________________________________________________
'RS_Date
StrSQL1 = "            SELECT dbo_View_ITrans_Periodes.DTTRANS "
StrSQL1 = StrSQL1 & "  FROM dbo_View_ITrans_Periodes "
StrSQL1 = StrSQL1 & "  WHERE (((dbo_View_ITrans_Periodes.noannee)=2014)) "

'_____________________________________________________________________________
'RS_Stock
StrSQL2 = "            SELECT  top 1 "
StrSQL2 = StrSQL2 & "  dboViewQtStock.KIPRODMAG, "
StrSQL2 = StrSQL2 & "  dboViewQtStock.DTTRANS, "
StrSQL2 = StrSQL2 & "  dboViewQtStock.QTSTOCK  "
StrSQL2 = StrSQL2 & "  FROM dboViewQtStock     "
StrSQL2 = StrSQL2 & "  WHERE (((dboViewQtStock.KIPRODMAG)=" & KIPRODMAG & ") "
StrSQL2 = StrSQL2 & "  AND ((dboViewQtStock.DTTRANS)<='" & DTTRANS & "')) "
StrSQL2 = StrSQL2 & "  ORDER BY dboViewQtStock.DTTRANS DESC "

'______________________________________________________________________________
'Append to STOCK
StrSQL3 = "            INSERT INTO STOCK ( KIPRODMAG, DTTRANS, QTSTOCK ) "
StrSQL3 = StrSQL3 & "  SELECT "
StrSQL3 = StrSQL3 & "  & KIPRODMAG  & ", "
StrSQL3 = StrSQL3 & "  '" & DTTRANS & "'" & ", "
StrSQL3 = StrSQL3 & "  '" & QTSTOCK & "'" & " "

'_____________________________________________________________________________
'Open recordset RS_IPRODMAG
RS_IPRODMAG.Open StrSQL0
RS_IPRODMAG.MoveFirst

'_____________________________________________________________________________
'Start of loop #1
Do While Not RS_IPRODMAG.EOF
KIPRODMAG = RS_IPRODMAG.Fields(0).Value
'_____________________________________________________________________________
'Open recordset RS_Date
 RS_Date.Open StrSQL1
 RS_Date.MoveFirst

 '____________________________________________________________________________
 'Start of loop #2
 Do While Not RS_Date.EOF
 DTTRANS = RS_Date.Fields(0).Value
 '_________________________________________________________________________________
 'Open recordset RS_STOCK
 Debug.Print DTTRANS
 Debug.Print KIPRODMAG
 Debug.Print StrSQL2
 DoCmd.RunCommand acCmdDebugWindow
 RS_Stock.Open StrSQL2
 QTSTOCK = RS_Stock.Fields(2).Value

    '_____________________________________________________________________________________
'Append table STOCK
DoCmd.RunSQL StrSQL3
RS_Stock.Close
RS_Date.MoveNext
Loop
end of loop #2
    '_____________________________________________________________________________________

RS_Date.Close
RS_IPRODMAG.MoveNext
Loop
'END of loop #1
'______________________________________________________________________________________

RS_IPRODMAG.Close
cnn1.Close

End Sub

VBlades VBlades · Accepted Answer · 2014-07-22T00:42:42

You never give values your variables. You need to do this:

...
Dim KIPRODMAG As Integer
Dim QTSTOCK As Integer

DTTRANS = "20130501"
KIPRODMAG = 1
....

This is a debug statement which will print in your debug window:

Debug.Print DTTRANS = 20130501
Debug.Print KIPRODMAG = 1

It will tell you whether the statement reseolves to true or false, not set your variable values.

I am having problems with variables in SQL statements

1 Answers