Re: SQL Where Statement mixed integer and text

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" wrote:

> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Jim Greene" <JimGreene@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:9C4D0F1C-032A-405D-B34E-BD6B735FFAB2@xxxxxxxxxxxxxxxx
> >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
> >
> >
>
>
>
.

Relevant Pages