Thanks I tried the simple solution but then I got the error message:
Run time error 3075
Syntax error (missing operator) in query expression 'Max(Page Number)'
My one line of code is :
intPageCounter = CInt(Nz(DMax("Page Number", "Indexes"), 0))
"Brian Wilson" wrote:
>
> "JeffH" <(E-Mail Removed)> wrote in message
> news:6A7DFE08-693D-486D-8148-(E-Mail Removed)...
> >I need to locate the last page number in an "Indexes" table that I have and
> > then use it as the beginning number in a second module. The "Indexes"
> > table
> > has two fields, a description and page number.
> >
> > Specifically I am unclear what the 1st argument in the CreateQueryDef
> > should
> > be. I have "PageNumberLast" in the CreateQueryDef but I this can't be
> > correct. I thought that I would actually be creating this variable that I
> > could then use to reset the page count (intPageCounter).
> >
> > Here is what I have tried (I tried to supply only what we need):
> >
> > Dim db5 As DAO.Database
> > Dim intPageCounter as Integer
> > Dim intLastPageNo as Integer
> > Dim strSQL as String
> >
> > Function InitToc3()
> > 'Called from the OnOpen property of the report.
> >
> > Dim qd5 As DAO.QueryDef
> > Set db5=CurrentDB()
> >
> > 'Set the page number to the last page used in the "Indexes" table (I
> > believe
> > this is were my problems begin)
> > strSQL="Select Max(Indexes,[PageNumber] from [Indexes]'
> > Set qd5=db5.CreateQueryDef("PageNumberLast",strSQL)
> >
> > qd5.Execute
> > qd5.Close
> >
> > intPageCounter=PageNumberLast
> >
> > I was hoping to have a query that would identify the maximum page number
> > in
> > the "Indexes" table and then use that to set "intPageCounter"
> >
> > I am a newbie, but learning lots. If there is an easier way I open to
> > making
> > life simplier.
>
>
> Well, if we start by making it a bit simpler then how about one line?
>
> intLastPageNo = CInt(Nz(DMax("PageNumber", "Indexes"), 0))
>
> If you are looking up the maximum value in a table, then this is all you
> need. Your use of dbs.Execute and the QueryDef object are both pretty far
> from how they should be used, but if you wanted an example of how to do
> things the not-so-simple way, see the code below. The reason for avoiding
> the easy dmax function shown above is to increase speed, but if you call the
> function only once per routine you will not notice any difference:
>
>
> Public Function GetMaxIndex() As Long
>
> On Error GoTo Err_Handler
>
> Dim dbs As DAO.Database
> Dim rst As DAO.Recordset
> Dim strSQL As String
> Dim lngReturn As Long
>
> lngReturn = -1
>
> strSQL = "SELECT Max(PageNumber) FROM Indexes"
>
> Set dbs = CurrentDb
>
> Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)
>
> If Not rst.EOF Then
> lngReturn = Nz(rst.Fields(0), 0)
> End If
>
> Exit_Handler:
>
> GetMaxIndex = lngReturn
>
> If Not rst Is Nothing Then
> rst.Close
> Set rst = Nothing
> End If
>
> If Not dbs Is Nothing Then
> Set dbs = Nothing
> End If
>
> Exit Function
>
> Err_Handler:
> ' No error message just let the function return a negative number
> Resume Exit_Handler
>
> End Function
>
>
>
>
> .
>
|
Similar Threads
