Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > access > answers

VBA SQL query resets variable values to "0"

Newbie
  
Join Date: Jul 2007
Posts: 4
#1: Jul 28 '07
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?

Expand|Select|Wrap|Line Numbers
  1.    strTxSQL = "SELECT tblTaxType.TaxTypeID,tblTaxType.Desc,tblTaxType.TaxCode,tblTaxRates.Rate,"
  2.    strTxSQL = strTxSQL & " tblTaxRates.EffectiveDate"
  3.    strTxSQL = strTxSQL & " FROM tblTaxRates, tblTaxType, tblItemsReqdTaxes"
  4.    strTxSQL = strTxSQL & " WHERE (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID)"
  5.    strTxSQL = strTxSQL & " AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax)"
  6. 'THIS LINE WILL NOT WORK   strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType like " & lngItemCode & " )"
  7. 'USED NEXT LINE INSTEAD
  8.    strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType = 1 )"
  9.    strTxSQL = strTxSQL & " AND (tblTaxRates.EffectiveDate)="
  10.    strTxSQL = strTxSQL & " (select max(tblTaxRates.EffectiveDate)"
  11.    strTxSQL = strTxSQL & " FROM tblTaxRates"
  12. 'THIS LINE WILL NOT WORK   strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= #" & datMyDate & "#"
  13. 'USED NEXT LINE INSTEAD
  14. strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= (#" & Forms!frmReservation!ArrivalDate + intInvcCtr & "#)"
  15.    strTxSQL = strTxSQL & " AND (tblTaxRates.TaxTypeID = tblTaxType.TaxTypeID)"
  16.    strTxSQL = strTxSQL & " AND (tblTaxRates.TaxTypeID=tblItemsReqdTaxes.ReqdTax)"
  17. 'THIS LINE WILL NOT WORK   strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType = " & lngItemCode & ")))"
  18. 'USED NEXT LINE INSTEAD
  19.    strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType = 1)))"
  20.    strTxSQL = strTxSQL & " ORDER BY tblTaxRates.Rate DESC"
  21.  
  22.    intInvcCtr = 0
  23.    intLengthOfStay = Forms!frmReservation!DepartureDate - Forms!frmReservation!ArrivalDate
  24.    For intInvcCtr = 0 To intLengthOfStay - 1
  25.      '================================================================
  26.      ' set datMyDate
  27.      '================================================================
  28.       datMyDate = Format(DateAdd("d", intInvcCtr, Forms!frmReservation!ArrivalDate), "MM/DD/YYYY")
  29.      '================================================================
  30.      ' Load the room information in the RsRoomRates recordset
  31.      '================================================================
  32.       Set cnnRoom = CurrentProject.Connection
  33.       Set RsRoomRates = New ADODB.Recordset
  34.       RsRoomRates.ActiveConnection = cnnRoom
  35.       RsRoomRates.Open strRoomRatesSQL
  36.       strItemCode = RsRoomRates.Fields(1).Value
  37.       lngItemCode = RsRoomRates.Fields(0).Value
  38.      '================================================================
  39.      ' Load the tax types and tax rates in the RsTaxRates recordset
  40.      '================================================================
  41.       Set cnnTax = CurrentProject.Connection
  42.       Set RsTaxRates = New ADODB.Recordset
  43.       RsTaxRates.ActiveConnection = cnnTax
  44.       RsTaxRates.Open strTxSQL
  45.       Debug.Print strTxSQL    
  46.        :
  47.        :
  48.    Next
Here is the "Debug.Print strTxSQL" output from the immediate window when I run it as shown above.

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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's Avatar
Expert
  
Join Date: May 2007
Location: Florida
Posts: 1,915
#2: Jul 28 '07

re: VBA SQL query resets variable values to "0"

Try the syntax used below:

The lngItemCode is a numeric data type, therefore your SQl string should be formatted as a numeric string
Expand|Select|Wrap|Line Numbers
  1. strTxSQL = strTxSQL & " AND (tblItemsReqdTaxes.ItemType = " & lngItemCode & “)"
  2.  
Your date variable is defined as a date data type, so you shouldn’t need the delimiters
Expand|Select|Wrap|Line Numbers
  1. strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= " & datMyDate 
  2.  
Newbie
  
Join Date: Jul 2007
Posts: 4
#3: Jul 29 '07

re: VBA SQL query resets variable values to "0"

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:

Expand|Select|Wrap|Line Numbers
  1. strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= #" & datMyDate & "#"
to this:
Expand|Select|Wrap|Line Numbers
  1. 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's Avatar
Expert
  
Join Date: May 2007
Location: Florida
Posts: 1,915
#4: Jul 29 '07

re: VBA SQL query resets variable values to "0"

Quote:

Originally Posted by 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:

Expand|Select|Wrap|Line Numbers
  1. strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= #" & datMyDate & "#"
to this:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= " & DateValue(datMyDate)
  2.  
Newbie
  
Join Date: Jul 2007
Posts: 4
#5: Jul 29 '07

re: VBA SQL query resets variable values to "0"

Maybe it would work better in a string format, but datMyDate is defined as
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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's Avatar
Expert
  
Join Date: May 2007
Location: Florida
Posts: 1,915
#6: Jul 29 '07

re: VBA SQL query resets variable values to "0"

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

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [datMyDate] DateTime;
  2. '_______________________________________________________________
  3. strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate Is Not Null And tblTaxRates.EffectiveDate <= " & [datMyDate]
  4.  
Newbie
  
Join Date: Jul 2007
Posts: 4
#7: Jul 29 '07

re: VBA SQL query resets variable values to "0"

Thanks very much, you helped me get on the right track! The secret is to use Parameters. I changed the following code
Expand|Select|Wrap|Line Numbers
  1. Dim cnnTax As ADODB.Connection
  2.       :
  3. Dim RsTaxRates As ADODB.Recordset
  4.       :
  5. strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= #" & datMyDate & "#"
  6.       :
  7. Set cnnTax = CurrentProject.Connection
  8. Set RsTaxRates = New ADODB.Recordset
  9. RsTaxRates.ActiveConnection = cnnTax
  10. RsTaxRates.Open strTxSQL
to:
Expand|Select|Wrap|Line Numbers
  1. strTxSQL = strTxSQL & " WHERE (tblTaxRates.EffectiveDate <= [parmMyDate]"
  2.      :
  3. Dim cmdTax As New ADODB.Command
  4. Dim RsTaxRates As New ADODB.Recordset
  5. With cmdTax
  6.           .ActiveConnection = CurrentProject.Connection
  7.           .CommandText = strTxSQL
  8.           .CommandType = adCmdUnknown
  9.           .Parameters.Append cmdTax.CreateParameter("[parmMyDate]", adDate, adParamInput, Date)
  10.           .Parameters("[parmMyDate]") = datMyDate
  11. End With
  12. 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's Avatar
Expert
  
Join Date: May 2007
Location: Florida
Posts: 1,915
#8: Jul 29 '07

re: VBA SQL query resets variable values to "0"

You are welcome. Glad I could help you get it resolved.
missinglinq's Avatar
Moderator
  
Join Date: Nov 2006
Location: Richmond, Virginia USA
Posts: 2,992
#9: Jul 29 '07

re: VBA SQL query resets variable values to "0"

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)>
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,295 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved