CreateQueryDef question

[Guest]

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.

 

[Marshall Barton]

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.

This is the long way around, even ignoring that Execute only
operates on action queries, not on Select queries.

I think all you need to use is the DMax function:

Dim intLastPageNo as Integer
intPageCounter = DMax("PageNumber", "Indexes")

 

[Brian Wilson]

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

 

[Guest]

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))

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




.

 

[Guest]

I just figured out my problem. It works great!!!! You were a huge help.

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))

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




.