G
Guest
I am having a problem with syntax for SQL statements which have both string
text and integers in the same Where cluase.
Public Sub FYQtr(ByVal strYear as String, intQtr as Integer)
Dim strSQL as String
strSQL = "SELECT tblAccountPeriod.PeriodID, " & _
"tblAccountPeriod.FiscalYear, " & _
"tblAccountPeriod.Type, " & _
"tblAccountPeriod.TypeNumber " & _
"FROM tblAccountPeriod " & _
"WHERE (((tblAccountPeriod.FiscalYear)='" & strYear & "') " & _
"AND ((tblAccountPeriod.TypeNumber)= & intQtr ) " & _
"AND ((Right([Type],3))='Qtr')) " & _
"ORDER BY tblAccountPeriod.TypeNumber;"
''''more code'''
This always causes an error.
I have it working by do it this way:
strSQL = "SELECT PeriodID, FiscalYear, " & _
"Type, TypeNumber " & _
"FROM tblAccountPeriod " & _
"WHERE FiscalYear='" & strYear & "' " & _
"AND Right([Type],3)='Qtr' " & _
"AND TypeNumber= " & intQtr & _
" ORDER BY TypeNumber;"
I use it to set a DAO.Recordset in a module to get just one record. PeriodId
is auto and Type is a number in table, rest of the fields are text. I realize
this is but one table. The reason for the question is what to do with two or
more tables joined when criteria could be mixed with text and integer from
two tables or more.
Thanks for any help on this
text and integers in the same Where cluase.
Public Sub FYQtr(ByVal strYear as String, intQtr as Integer)
Dim strSQL as String
strSQL = "SELECT tblAccountPeriod.PeriodID, " & _
"tblAccountPeriod.FiscalYear, " & _
"tblAccountPeriod.Type, " & _
"tblAccountPeriod.TypeNumber " & _
"FROM tblAccountPeriod " & _
"WHERE (((tblAccountPeriod.FiscalYear)='" & strYear & "') " & _
"AND ((tblAccountPeriod.TypeNumber)= & intQtr ) " & _
"AND ((Right([Type],3))='Qtr')) " & _
"ORDER BY tblAccountPeriod.TypeNumber;"
''''more code'''
This always causes an error.
I have it working by do it this way:
strSQL = "SELECT PeriodID, FiscalYear, " & _
"Type, TypeNumber " & _
"FROM tblAccountPeriod " & _
"WHERE FiscalYear='" & strYear & "' " & _
"AND Right([Type],3)='Qtr' " & _
"AND TypeNumber= " & intQtr & _
" ORDER BY TypeNumber;"
I use it to set a DAO.Recordset in a module to get just one record. PeriodId
is auto and Type is a number in table, rest of the fields are text. I realize
this is but one table. The reason for the question is what to do with two or
more tables joined when criteria could be mixed with text and integer from
two tables or more.
Thanks for any help on this