SQL Where Statement mixed integer and text

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
 
G

Guest

Its best to design your tables so the same data has the same attributes in
different tables. Always compare strings with strings and numbers with
numbers. You can force conversions by using functions e.g. CStr(Num) or
CInt(String) but it will slow down the SQL execution.

-Dorian
 
D

Douglas J. Steele

The difference between the two samples would appear to be the following line
in the first example:

"AND ((tblAccountPeriod.TypeNumber)= & intQtr ) " & _

It should be

"AND ((tblAccountPeriod.TypeNumber)= " & intQtr & ") " & _
 
A

askjerry

One of your problems is this part of your strSQL variable, & "AND
((tblAccountPeriod.TypeNumber)= & intQtr ) " &. The entire substring is
inbetween double quotes so the entire string is treated as characters.
If you want to pick up inQtr you need to break the character string
before and after intQtr, e.g. ,
"AND ((tblAccountPeriod.TypeNumber)= " & intQtr & " ) "

You really have to be carefull when assembling character strings for
SQL. I did not check the rest of your syntax. The other thing you
should always do when assembling (and debugging) SQL strings is to
ouput the final string to either a msgbox or Debug.print to check that
the string is being assembled correctly. Usually, you can then catch
your own errors and won't need to ask for help and wait for an answer.
 
G

Guest

I not sure you understand my question, how does one build the sql statement
using all the " ((( ))) " when one or two fields are text and another one is
number or integer when passed for data type, I understand what you are saying.
thanks

mscertified said:
Its best to design your tables so the same data has the same attributes in
different tables. Always compare strings with strings and numbers with
numbers. You can force conversions by using functions e.g. CStr(Num) or
CInt(String) but it will slow down the SQL execution.

-Dorian

Jim Greene said:
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
Click to expand...
Click to expand...
 
G

Guest

intQtr is as integer what you show will give error data type mismatch

"AND ((tblAccountPeriod.TypeNumber)= " & intQtr & ") " & _

this will work for a text field but not a number field.
thanks, jim


Douglas J. Steele said:
The difference between the two samples would appear to be the following line
in the first example:

"AND ((tblAccountPeriod.TypeNumber)= & intQtr ) " & _

It should be

"AND ((tblAccountPeriod.TypeNumber)= " & intQtr & ") " & _


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jim Greene said:
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
Click to expand...
Click to expand...
 
G

Guest

please re-read my text question,

intQtr is a integer valve; it holds a number data type.

"WHERE FiscalYear='" & strYear & "' " & _

note: FiscalYear is a text value in table

"AND Right([Type],3)='Qtr' " & _

note: Type is a text value in table

"AND TypeNumber= " & intQtr & _

note: TypeNumber is a number value in table

" ORDER BY TypeNumber;"

note: notice the extra space bewteen ( " ORDER BY )

intQtr is a integer ByVal

thanks for your response
 
D

Douglas J. Steele

What I posted will work for a numeric field in the table, not for a text
field. For a text field, you need quotes:

"AND ((tblAccountPeriod.TypeNumber)= '" & intQtr & "') " & _

The difference is that one will generate:

AND ((tblAccountPeriod.TypeNumber)= 5

while the other will generate

AND ((tblAccountPeriod.TypeNumber)= '5'

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jim Greene said:
intQtr is as integer what you show will give error data type mismatch

"AND ((tblAccountPeriod.TypeNumber)= " & intQtr & ") " & _

this will work for a text field but not a number field.
thanks, jim


Douglas J. Steele said:
The difference between the two samples would appear to be the following
line
in the first example:

"AND ((tblAccountPeriod.TypeNumber)= & intQtr ) " & _

It should be

"AND ((tblAccountPeriod.TypeNumber)= " & intQtr & ") " & _


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jim Greene said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I see where I miss the mark to your first reply, small text characters do not
help either, so if I got it right now:

string text is: single quote _double quote_space_ &_space_ strString
_&_space_double quote_single quote

integer is: double quote_space_&_intInteger_&_space_double quote

underscore used to seperate words only

I tried it works, now to get brain to retain it. question answer.
thanks for doing it again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AutoIncrement Text Field in an Insert Statement 12
MS Access VBA - Run-time error 3144 - Error in Update Statement 5
Question about Autocolumnwidth example from Lebans website 1
PLEASE HELP.....List Box Search Form 1
SQL Statement created from ItemsSelected from Several Listboxes.. 2
pass integer to query statement 1
Pass numeric value in SQL statement 3
Can't set the null or "" text box to be a calculable text box 0

Top