VBA SQL query resets variable values to "0"

[KelvinA]

I'm constructing a SQL statement called strTxSQL that is
supposed to use two variables: lngItemCode (long integer) and datMyDate (date defined as MM/DD/YYYY).
The SQL statement will not work with either variable, as it thinks they are both "0". If I hard-code
the values in the SQL statement then it works. What needs to be changed to make it work with the variables?

   strTxSQL = "SELECT tblTaxType.TaxTypeID,tblTaxType.Desc,tblTaxType.TaxCode,tblTaxRates.Rate,"
   strTxSQL = strTxSQL & " tblTaxRates.EffectiveDate"
   strTxSQL = strTxSQL & " FROM tblTaxRates, tblTaxType, tblItemsReqdTaxes"
   strTxSQL = strTxSQL & " WHERE (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID)"
   strTxSQL = strTxSQL & " AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax)"
'THIS LINE WILL NOT WORK   strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType like " & lngItemCode & " )"
'USED NEXT LINE INSTEAD
   strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType = 1 )"
   strTxSQL = strTxSQL & " AND (tblTaxRates.EffectiveDate)="
   strTxSQL = strTxSQL & " (select max(tblTaxRates.EffectiveDate)"
   strTxSQL = strTxSQL & " FROM tblTaxRates"
'THIS LINE WILL NOT WORK   strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= #" & datMyDate & "#"
'USED NEXT LINE INSTEAD
strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= (#" & Forms!frmReservation!ArrivalDate + intInvcCtr & "#)"
   strTxSQL = strTxSQL & " AND (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID)"
   strTxSQL = strTxSQL & " AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax)"
'THIS LINE WILL NOT WORK   strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType = " & lngItemCode & ")))"
'USED NEXT LINE INSTEAD
   strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType = 1)))"
   strTxSQL = strTxSQL & " ORDER BY tblTaxRates.Rate DESC"
 
   intInvcCtr = 0
   intLengthOfStay = Forms!frmReservation!DepartureDate - Forms!frmReservation!ArrivalDate
   For intInvcCtr = 0 To intLengthOfStay - 1
     '================================================================
     ' set datMyDate
     '================================================================
      datMyDate = Format(DateAdd("d", intInvcCtr, Forms!frmReservation!ArrivalDate), "MM/DD/YYYY")
     '================================================================
     ' Load the room information in the RsRoomRates recordset
     '================================================================
      Set cnnRoom = CurrentProject.Connection
      Set RsRoomRates = New ADODB.Recordset
      RsRoomRates.ActiveConnection = cnnRoom
      RsRoomRates.Open strRoomRatesSQL
      strItemCode = RsRoomRates.Fields(1).Value
      lngItemCode = RsRoomRates.Fields(0).Value
     '================================================================
     ' Load the tax types and tax rates in the RsTaxRates recordset
     '================================================================
      Set cnnTax = CurrentProject.Connection
      Set RsTaxRates = New ADODB.Recordset
      RsTaxRates.ActiveConnection = cnnTax
      RsTaxRates.Open strTxSQL
      Debug.Print strTxSQL    
       :
       :
   Next

Here is the "Debug.Print strTxSQL" output from the immediate window when I run it as shown above.

SELECT tblTaxType.TaxTypeID,tblTaxType.Desc,tblTaxType.TaxCode,tblTaxRates.Rate, tblTaxRates.EffectiveDate FROM tblTaxRates, tblTaxType, tblItemsReqdTaxes WHERE (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID) AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax) AND (tblItemsReqdTaxes.ItemType = 1 ) AND (tblTaxRates.EffectiveDate)= (select max(tblTaxRates.EffectiveDate) FROM tblTaxRates WHERE (tblTaxRates.EffectiveDate <= (#12/31/2007#) AND (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID) AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax) AND (tblItemsReqdTaxes.ItemType = 1))) ORDER BY tblTaxRates.Rate DESC

Here is the output when I try to use the variables instead:

ELECT tblTaxType.TaxTypeID,tblTaxType.Desc,tblTaxType.TaxCode,tblTaxRates.Rate, tblTaxRates.EffectiveDate FROM tblTaxRates, tblTaxType, tblItemsReqdTaxes WHERE (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID) AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax) AND (tblItemsReqdTaxes.ItemType like 0 ) AND (tblTaxRates.EffectiveDate)= (select max(tblTaxRates.EffectiveDate) FROM tblTaxRates WHERE (tblTaxRates.EffectiveDate <= #12:00:00 AM# AND (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID) AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax) AND (tblItemsReqdTaxes.ItemType = 0))) ORDER BY tblTaxRates.Rate DESC

[puppydogbuddy]

Try the syntax used below:

The lngItemCode is a numeric data type, therefore your SQl string should be formatted as a numeric string

strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType = " & lngItemCode & “)"
 

Your date variable is defined as a date data type, so you shouldn’t need the delimiters

strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= " & datMyDate 
 

[KelvinA]

Thanks for the quick response. Let's work one problem at a time because I still cannot get either one to work.

When I changed this line:

strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= #" & datMyDate & "#"

to this:

strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= " & datMyDate

the following error pops up:

Run-time error '-2147217900 (80040e14)':

Syntax error in query expression '(tleTaxRates ...rest of query...

[puppydogbuddy]

Thanks for the quick response. Let's work one problem at a time because I still cannot get either one to work.

When I changed this line:

strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= #" & datMyDate & "#"

to this:

strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= " & datMyDate

the following error pops up:

Run-time error '-2147217900 (80040e14)':

Syntax error in query expression '(tleTaxRates ...rest of query...

Your date variable is formatted as a string;need to convert to date.
try this:

strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= " & DateValue(datMyDate)
 

[KelvinA]

Maybe it would work better in a string format, but datMyDate is defined as

Dim datMyDate As Date

I get the same error using the DateValue(datMyDate). Also, I tried it with the "#" around the DateValue(datMyDate). That doesn't return an error, but does return 12:00AM, which I think means Access thinks datMyDate is 0, but it is not:

? datMyDate
12/31/2007
? strTxSQL

SELECT tblTaxType.TaxTypeID,tblTaxType.Desc,tblTaxType.TaxCode,tblTaxRates.Rate, tblTaxRates.EffectiveDate FROM tblTaxRates, tblTaxType, tblItemsReqdTaxes WHERE (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID) AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax) AND (tblItemsReqdTaxes.ItemType = 1 ) AND (tblTaxRates.EffectiveDate)= (select max(tblTaxRates.EffectiveDate) FROM tblTaxRates WHERE (tblTaxRates.EffectiveDate <= #12:00:00 AM# AND (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID) AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax) AND (tblItemsReqdTaxes.ItemType = 1))) ORDER BY tblTaxRates.Rate DESC

[puppydogbuddy]

try this:
1.Put parameter statement preceding your original first line of code
2. added brackets to parameter date
3. added "is not null" to where clause

PARAMETERS [datMyDate] DateTime;
'_______________________________________________________________
strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate Is Not Null And tblTaxRates.EffectiveDate <= " & [datMyDate]
 

[KelvinA]

Thanks very much, you helped me get on the right track! The secret is to use Parameters. I changed the following code

Dim cnnTax As ADODB.Connection
      :
Dim RsTaxRates As ADODB.Recordset
      :
strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= #" & datMyDate & "#"
      :
Set cnnTax = CurrentProject.Connection
Set RsTaxRates = New ADODB.Recordset
RsTaxRates.ActiveConnection = cnnTax
RsTaxRates.Open strTxSQL

to:

strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= [parmMyDate]"
     :
Dim cmdTax As New ADODB.Command
Dim RsTaxRates As New ADODB.Recordset
With cmdTax
          .ActiveConnection = CurrentProject.Connection
          .CommandText = strTxSQL
          .CommandType = adCmdUnknown
          .Parameters.Append cmdTax.CreateParameter("[parmMyDate]", adDate, adParamInput, Date)
          .Parameters("[parmMyDate]") = datMyDate
End With
Set RsTaxRates = cmdTax.Execute

and it works perfectly for the datMyDate variant. I'm sure it will work for the lngItemCode as well.
Thanks again for your help, this has stumped me for quite a while.

[puppydogbuddy]

You are welcome. Glad I could help you get it resolved.

[missinglinq]

While single lines of code don't really require the use of Code Tags, all code posted consisting of 2 or more lines does.

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

For VBA code the tag is Code=vb (no spaces) with square brackets surrounding it.

Please use [code] tags in future.

Thank you!

Linq ;0)>