Re: Help please on Record sets

I tried running it from the query builder after deleting all the
concatenations and the (&) you sugested before the HostId clause, (it didn't
like that at all) but after it had made a successful query out of it and
accepted it, It said that the calculation was too complex and came up with no
resulting table. I copied it into the immediate window to examine it and all
looked ok but then 'some times you can't see the wood for the trees' as we
say.
Question: which would be better? To filter all the unwanted records within
the SQL string leaving a small recordset to work with but a complex string or
just to 'SELECT* from' and then deal with unwated data in the following code?
My thanks for your patience with me.
Andy...
****************************
"Douglas J. Steele" wrote:

Sorry: You're correct that you needed single quotes (or you could have
doubled up the double quotes in the Format statement).

You haven't answered my question about strSQL, though. Did you print it out
to the Debug window and look at it? Did you try running it in the query
builder? Just because your "original SQL string was generated in a working
query" doesn't actually guarantee that you're building it correcting in your
VBA code.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Andy6" <Andy6@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:80CA87B2-FA30-41B1-B007-1DEEDCCFE97D@xxxxxxxxxxxxxxxx
Doug Many Thanks.
I had to add a quote to your first Formated Date and Change the quotes to
single quotes before my compiler would accept them like this:

Format(DateAdd('d', 13, [Forms]![Host Data]![Da1]), '\#mm\/dd\/yyyy\#')

but needless to say it still crashed Access.
My regional settings are dd\mm\yy by the way.
As my original SQL string was generated in a working query, would it be
easier to leave out strSql and make a reference back to that to collect my
recordset or would that lead to more problems?
Andy...
****************************

"Douglas J. Steele" wrote:

What's your Short Date format set to (in Regional Settings)? If it's
dd/mm/yyyy, you'll definitely have problems with your SQL.

I'd recommend

And AccomodationDates.StartDate <= " & _
Format(DateAdd("d", 13, [Forms]![Host Data]![Da1]),
"\#mm\/dd\/yyyy\#) _
& " And AccomodationDates.EndDate >= _
Format([Forms]![Host Data]![Da1], "\#mm\/dd\/yyyy\#")

Try printing strSQL out to the Debug window and take a look at it. Does
it
look right? Does it run when you copy that SQL into the Query Window's
SQL
View?


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Andy6" <Andy6@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E7859F92-F52F-48AC-82F9-C30883AC739D@xxxxxxxxxxxxxxxx
Many thanks Doug for your prompt reply, I tried with the spaces and it
didn't
work. I must admit I wasn't worried about the Sql string, it was the
rest
of
the connection and recordset handling that I wasn't sure on. I must be
truthful and admit that the SqlString was a little more complex and
involved
dates which I edited out to make the question simple. This is the real
SQL
string:-
strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& " FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& " WHERE (AccomodationDates.FamilyID =&[Forms]![Host
Data]![HostId])
And AccomodationDates.StartDate <= #" & [Forms]![Host Data]![Da1] + 13
&
"#" _
& " And AccomodationDates.EndDate >= #" & [Forms]![Host Data]![Da1]
&
"#;"
(This editor realy screws it up doesn't it)
does it look correct to you or is my handleing of dates wrong? and can
you
confirm that the Connection and Record set parts look ok.
Again Many Many thanks
Andy..
***********************************
"Douglas J. Steele" wrote:

You're missing spaces in the SQL statement: either put a space before
the
closing quote on each line, or start each line with a space after the
opening quote.

As well, you're missing a closing quote in the last line of the SQL
statement.

If FamilyID is numeric, I'd recommend

& "WHERE AccomodationDates.FamilyID = " & [Forms]![Host
Data]![HostId]

If it's text, then

& "WHERE AccomodationDates.FamilyID = '" & [Forms]![Host
Data]![HostId]
&
"'"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Andy6" <Andy6@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3E10242B-0930-4DAE-AB73-C54F4ADB5FD4@xxxxxxxxxxxxxxxx
I am Trying to create a recordset from an SQL query and cycle through
the
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see Why.
This
is
what I have so far:

Private Function Dateline()
On Error Resume Next

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit
working'

strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host
Data]![HostId]);

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop

End If

End Function

I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form but
I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.









.

Relevant Pages

  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: ASP - FROM statement slows down connection to database
    ... Open your database in Access, switch to the Queries tab, create a new query ... in Design View without choosing a table, swtich to SQL View, paste the sql ... connection string rather than using an ... recordset open statements. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Loading recordset at runtime
    ... difference in loading time for the entire recordset and the default 10 ... query or build the SQL string in VBA. ... Ah, ok, then use the forms "filter" option. ...
    (microsoft.public.access.forms)
  • Re: Help please on Record sets
    ... Doug, I don''t know if this helps you but, using toggle breakpoint I ... perhaps the SQL is ok and it's the rest of the Function that is at fault?? ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Loading recordset at runtime
    ... cases the recordset will be modified after the form is opened. ... Ah, ok, then use the forms "filter" option. ... my personal preference is to build the sql string in code and just ... advantages to using a query (the only exception here is if the "base" sql ...
    (microsoft.public.access.forms)