Run time error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

im trying to use ADO to bring up associated records in a form so that the
user can view it (in this case its the Gaurantor's details of a Tenant) and
the records are associated by Postcode1, Postcode2 and the GaurantorDate, the
ado code im using is bringing up a Syntax error in the SQl statement that im
using and points to the 'WHERE' section of it, it looks right to me but its
not, can anyone explain this?

Dim ConDatabase As ADODB.Connection
Dim rstFlat As ADODB.Recordset
Dim strSQL As String

' Initialize Connection object

' Specify current database as the Provider and then open the database
Set ConDatabase = CurrentProject.Connection
strSQL = "SELECT House_No, Street, Town, County, Country,
International_Code, AreaCode, Telephone_Number" & _
" FROM Garantors where (Postcode = " & Forms!StudentsNew!SPostcode1 & ")
AND " & _
"(Postcode2 = " & Forms!StudentsNew!SPostcode2 & ") AND (GuarantorDate =
" & Forms!StudentsNew!GuarantorDate & ");"

I ran a query (which worked) and looked at the SQL to see what it should be
and noticed that there are a lot of extra paranthesis' around the
comparisons, ive included this so you can see what im trying to do.


SELECT Garantors.[House No], Garantors.Street, Garantors.Town,
Garantors.County, Garantors.Country, Garantors.[International code],
Garantors.[Area Code], Garantors.[Telephone Number], Garantors.GuarantorDate,
Garantors.Postcode, Garantors.Postcode2
FROM Garantors
WHERE (((Garantors.GuarantorDate)=[Forms]![StudentsNew]![GuarantorDate]) AND
((Garantors.Postcode)=[Forms]![StudentsNew]![SPostcode1]) AND
((Garantors.Postcode2)=[Forms]![StudentsNew]![SPostcode2]));

with much thanks for looking at this,

Amit
 
Hi,

When you build the SQL text you need to enclose the values in ' (apostrophe)
for text values and # (hash) for date values.

i.e.

strSQL = "SELECT House_No, Street, Town, County, Country,
International_Code, AreaCode, Telephone_Number" & _
" FROM Garantors where (Postcode = '" & Forms!StudentsNew!SPostcode1 &
"')
AND " & _
"(Postcode2 = '" & Forms!StudentsNew!SPostcode2 & "') AND (GuarantorDate
=
#" & Forms!StudentsNew!GuarantorDate & "#);"

--
HTH

Mark Phillipson

Free Add-Ins at; http://mphillipson.users.btopenworld.com/
 
Thanks Mark Phillipson,

I should have known that was a problem, ive re-written that line so that it
has apostrophies where it should and it seems to evaluate properly but when
it gets to the next 2 lines:

Set rstFlat = New Recordset
rstFlat.Open strSQL, ConDatabase, adOpenDynamic, adLockOptimistic

It terminates and an error message appears to tell me: 'No value given for
one or more parameters'. Ive used watches on all the parameters in those
lines and they seem to show the correct values in each so what should i be
doing??

With much thanks for your help so far and also for looking at this post too,

Amit
:
 
Back
Top