sql statement error - Please look

[Fred]

hi guys - pulling my hair out with this one...


code:-----------------------------------------------------------------------
-------
Dim oRs As ADODB.Recordset
Dim strSQLString As String

strSQLString = "SELECT"
strSQLString = strSQLString & " customer.*,"
strSQLString = strSQLString & " Hardware.hardwa retype,"
strSQLString = strSQLString & " Hardware.hardwa redesc,"
strSQLString = strSQLString & " Hardware.hardwa reprice,"
strSQLString = strSQLString & " Invoiced.Transa ctionDate,"
strSQLString = strSQLString & " Invoiced.Transa ctionAmount,"
strSQLString = strSQLString & " Invoiced.Transa ctionNumber"
strSQLString = strSQLString & " FROM"
strSQLString = strSQLString & " Hardware"
strSQLString = strSQLString & " INNER JOIN"
strSQLString = strSQLString & " (customer"
strSQLString = strSQLString & " INNER JOIN"
strSQLString = strSQLString & " Invoiced ON Customer.Custom erID =
Invoiced.Custom erID "
strSQLString = strSQLString & " ON hardware.hardwa reidID =
Invoiced.hardwa reID)"
strSQLString = strSQLString & " WHERE (Invoiced.Trans actionNumber = " &
InvToFind & ")"


----------------------------------------------------------------------------
--


im getting syntax error on the where line..

anyone know where im going wrong?

fenster.

[Fletcher Arnold]

Re: sql statement error - Please look


"Fred" <removekeyser@b tinternet.com> wrote in message
news:bj9idt$cep $1@hercules.bti nternet.com...[color=blue]
> hi guys - pulling my hair out with this one...
>
>
>[/color]
code:-----------------------------------------------------------------------[color=blue]
> -------
> Dim oRs As ADODB.Recordset
> Dim strSQLString As String
>
> strSQLString = "SELECT"
> strSQLString = strSQLString & " customer.*,"
> strSQLString = strSQLString & " Hardware.hardwa retype,"
> strSQLString = strSQLString & " Hardware.hardwa redesc,"
> strSQLString = strSQLString & " Hardware.hardwa reprice,"
> strSQLString = strSQLString & " Invoiced.Transa ctionDate,"
> strSQLString = strSQLString & " Invoiced.Transa ctionAmount,"
> strSQLString = strSQLString & " Invoiced.Transa ctionNumber"
> strSQLString = strSQLString & " FROM"
> strSQLString = strSQLString & " Hardware"
> strSQLString = strSQLString & " INNER JOIN"
> strSQLString = strSQLString & " (customer"
> strSQLString = strSQLString & " INNER JOIN"
> strSQLString = strSQLString & " Invoiced ON Customer.Custom erID =
> Invoiced.Custom erID "
> strSQLString = strSQLString & " ON hardware.hardwa reidID =
> Invoiced.hardwa reID)"
> strSQLString = strSQLString & " WHERE (Invoiced.Trans actionNumber = "[/color]
&[color=blue]
> InvToFind & ")"
>
>
> --------------------------------------------------------------------------[/color]
--[color=blue]
> --
>
>
> im getting syntax error on the where line..
>
> anyone know where im going wrong?
>
> fenster.[/color]



Once you've created the SQL string put Debug.Print strSQL and exit before
trying to open the recordset. You can cut and paste the resulting string
from the immediate window (CTRL-G) into the query designer - which may help
you identify the problem.
At a guess, I would look here first

" WHERE (Invoiced.Trans actionNumber = " & InvToFind & ")"

Make sure InvToFind is not causing the problem - Is it null, is it
alphanumeric, etc?


Fletcher

[Fletcher Arnold]

Re: sql statement error - Please look

"Fred" <removekeyser@b tinternet.com> wrote in message
news:bj9o8t$t3f $1@titan.btinte rnet.com...[color=blue]
> Hi Fletcher..
>
> heres the dump from query analyzer in access:
> SELECT Customer.*, HardWare.Hardwa reType, HardWare.Hardwa reDesc,
> HardWare.Hardwa rePrice, Invoiced.Transa ctionDate,
> Invoiced.Transa ctionAmount, Invoiced.Transa ctionNumber
> FROM HardWare INNER JOIN (Customer INNER JOIN Invoiced ON
> Customer.Custom erID=Invoiced.C ustomerID) ON
> HardWare.Hardwa reID=Invoiced.H ardwareID
> WHERE (((Invoiced.Tra nsactionNumber) =1));
>
>
> the invtofind is good - numeric 1 when testing...
>
>
> davie.[/color]


Try designing it graphically, and checking how the SQL which Access
generates compares to yours.