Problems with Recordset in Access 2000

[Tom Hagen Jr]

I've been trying to figure out how to do this in either Access or Excel 2000
but to much has changed and the coding I'm use to in Access 95/97 is not
working. Can someone please look at the code and tell me whats wrong? I can
also send both the excel and access files to see what I'm trying to do and
compare.

Here is the code:


Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency) As
Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income which is
figured out from the W2 form.

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a Recordset from the table Federal Tax
Dim rstFT As New ADODB.Recordset
rstFT.Open "Federal Tax", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Create a temp storage location for the info needed to do the math
Dim rstMS As New ADODB.Recordset

'Using the old method of recordset to try and create the temp numbers needed
Set rstFFT = db.OpenRecordset("Select * FROM '" & rstFFT & "' WHERE
[Marriage Status]= '" & MS & "'And [Payroll Period]= '" & PP & "' And
[Over]<='" & TI & "' And [Not Over]>'" & TI & "'")

'Do the math for the amount to be taxed
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI - rstFFT![Over]) *
rstFFT![% to withhold]

End Function


Thanks for the help.

Tom Hagen Jr

 

[Dan Artuso]

Hi Tom,
The simplest soloution is to continue to use DAO
Just make sure you have a reference set.
Open any code module and go to Tools-> references and check
off DAO.
If you leave the reference to ADO in, make sure you declare
your recordsets like:
Dim rs As DAO.Recordset

As for the code below, you don't say what is not working.
I don't see rstFFT declared anywhere but you try to open a recordset on it.
(so it's bound to fail), and why are you refering to a recordset inside your sql statement??
Is that a typo, did you mean rstFT?

Use a string variable to hold your sql statement, it's easier to debug that way.

 

[Tom Hagen Jr]

Thanks Dan,

I'm currently using DAO 3.6.
I'm having issues with the OpenRecordset. The SQL is how I was shown how to
best get the data, as to why I'm using the record set instead of the table
name, I kept getting failures because the table names have spaces in them
(Federal Tax) and it seemed to be the only way the compile would not fail.
Yes I did have a typo in the sql statement I've changed it but I'm still
getting errors when I run the function. At this point I'm willing (an have)
to completely change the code so it works.

Again I'm use to Access 2.0, 95 and 97. I've not found any good books on
what the changes have been from the old to new standards and the Help files
are a joke when 80% of the hyperlinks I click on tell me that the help files
are not installed even after I installed everything to the HD.


The code with updates as you requested: (I've put in the comment areas
where the code fails, basically the last part when doing the OpenRecordset
with the sql call. More then willing to eliminate using the recordset rstFT
if someone tells me how to do it with the spaces.


Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency)
As Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a Recordset from the table Federal Tax
Dim rstFT As New ADODB.Recordset
rstFT.Open "Federal Tax", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Create a temp storage location for the info needed to do the math
Dim rstMS As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
Set sqlCode = "Select * FROM '" & rstFT & "' WHERE [Marriage Status]= '" &
MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And [Not
Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI - rstFFT![Over]) *
rstFFT![% to withhold]

End Function
Thanks again
Tom Hagen Jr

 

[Dan Artuso]

Hi Tom,
If you have spaces in table names (never a very good idea) you have to
enclose them in [].
So..
"Select * FROM [Federal Tax] WHERE [Marriage Status]= '" &

does that help?

--
HTH
Dan Artuso, Access MVP

Thanks Dan,

I'm currently using DAO 3.6.
I'm having issues with the OpenRecordset. The SQL is how I was shown how to
best get the data, as to why I'm using the record set instead of the table
name, I kept getting failures because the table names have spaces in them
(Federal Tax) and it seemed to be the only way the compile would not fail.
Yes I did have a typo in the sql statement I've changed it but I'm still
getting errors when I run the function. At this point I'm willing (an have)
to completely change the code so it works.

Again I'm use to Access 2.0, 95 and 97. I've not found any good books on
what the changes have been from the old to new standards and the Help files
are a joke when 80% of the hyperlinks I click on tell me that the help files
are not installed even after I installed everything to the HD.


The code with updates as you requested: (I've put in the comment areas
where the code fails, basically the last part when doing the OpenRecordset
with the sql call. More then willing to eliminate using the recordset rstFT
if someone tells me how to do it with the spaces.


Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency)
As Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a Recordset from the table Federal Tax
Dim rstFT As New ADODB.Recordset
rstFT.Open "Federal Tax", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Create a temp storage location for the info needed to do the math
Dim rstMS As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
Set sqlCode = "Select * FROM '" & rstFT & "' WHERE [Marriage Status]= '" &
MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And [Not
Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI - rstFFT![Over]) *
rstFFT![% to withhold]

End Function
Thanks again
Tom Hagen Jr

Hi Tom,
The simplest soloution is to continue to use DAO
Just make sure you have a reference set.
Open any code module and go to Tools-> references and check
off DAO.
If you leave the reference to ADO in, make sure you declare
your recordsets like:
Dim rs As DAO.Recordset

As for the code below, you don't say what is not working.
I don't see rstFFT declared anywhere but you try to open a recordset on it.
(so it's bound to fail), and why are you refering to a recordset inside your sql statement??
Is that a typo, did you mean rstFT?

Use a string variable to hold your sql statement, it's easier to debug that way.

 

[Tom Hagen Jr]

Dan,

I did the change to the code and am posting a new copy of the code. It did
take a little longer before the error came up:

Run-time error '3001':
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

When debugging it goes to the line Set rstFFT = db.OpenRecordset(sqlCode)

I'm posting the code with the updates again. I figure its has something to
do with the OpenRecordset and sql statement but I'm not sure.

Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency)
As Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a Recordset from the table Federal Tax
Dim rstFT As New ADODB.Recordset
rstFT.Open "Federal Tax", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Create a temp storage location for the info needed to do the math
Dim rstMS As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= '" &
MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And [Not
Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed as the rstFFT is not working
this will also fail
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI - rstFFT![Over]) *
rstFFT![% to withhold]

End Function

Thanks again,
Tom

 

[Tom Hagen Jr]

Made a mistake in the code when posting here is the updated version:

Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency) As
Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a temp storage location for the info needed to do the math
Dim rstFFT As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= '"
& MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And
[Not Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed as the rstFFT is not working
this will also fail
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -
rstFFT![Over]) * rstFFT![% to withhold]

End Function

Hi Tom,
If you have spaces in table names (never a very good idea) you have to
enclose them in [].
So..
"Select * FROM [Federal Tax] WHERE [Marriage Status]= '" &

does that help?

--
HTH
Dan Artuso, Access MVP

Thanks Dan,

I'm currently using DAO 3.6.
I'm having issues with the OpenRecordset. The SQL is how I was shown how to
best get the data, as to why I'm using the record set instead of the table
name, I kept getting failures because the table names have spaces in them
(Federal Tax) and it seemed to be the only way the compile would not fail.
Yes I did have a typo in the sql statement I've changed it but I'm still
getting errors when I run the function. At this point I'm willing (an have)
to completely change the code so it works.

Again I'm use to Access 2.0, 95 and 97. I've not found any good books on
what the changes have been from the old to new standards and the Help files
are a joke when 80% of the hyperlinks I click on tell me that the help files
are not installed even after I installed everything to the HD.


The code with updates as you requested: (I've put in the comment areas
where the code fails, basically the last part when doing the OpenRecordset
with the sql call. More then willing to eliminate using the recordset rstFT
if someone tells me how to do it with the spaces.


Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency)
As Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a Recordset from the table Federal Tax
Dim rstFT As New ADODB.Recordset
rstFT.Open "Federal Tax", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Create a temp storage location for the info needed to do the math
Dim rstMS As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
Set sqlCode = "Select * FROM '" & rstFT & "' WHERE [Marriage Status]= '" &
MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And [Not
Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI - rstFFT![Over]) *
rstFFT![% to withhold]

End Function
Thanks again
Tom Hagen Jr

Hi Tom,
The simplest soloution is to continue to use DAO
Just make sure you have a reference set.
Open any code module and go to Tools-> references and check
off DAO.
If you leave the reference to ADO in, make sure you declare
your recordsets like:
Dim rs As DAO.Recordset

As for the code below, you don't say what is not working.
I don't see rstFFT declared anywhere but you try to open a recordset

on
it.

(so it's bound to fail), and why are you refering to a recordset

inside
your sql statement??

Is that a typo, did you mean rstFT?

Use a string variable to hold your sql statement, it's easier to debug that way.

 

[Douglas J. Steele]

TI is a currency field, yet you've got quotes around its value in the SQL
string. Assuming that the field Over in the table is a numeric field, lose
the quotes. (spaces added for clarity:

sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= ' "
& MS & " 'And [Payroll Period]= ' " & PP & " ' And [Over]<= " & TI & " And
[Not Over]> " & TI

instead of

sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= ' "
& MS & " 'And [Payroll Period]= ' " & PP & " ' And [Over]<= ' " & TI & " '
And
[Not Over]> ' " & TI & " ' "


--
Doug Steele, Microsoft Access MVP

Made a mistake in the code when posting here is the updated version:

Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency) As
Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a temp storage location for the info needed to do the math
Dim rstFFT As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= '"
& MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And
[Not Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed as the rstFFT is not working
this will also fail
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -
rstFFT![Over]) * rstFFT![% to withhold]

End Function

Hi Tom,
If you have spaces in table names (never a very good idea) you have to
enclose them in [].
So..
"Select * FROM [Federal Tax] WHERE [Marriage Status]= '" &

does that help?

--
HTH
Dan Artuso, Access MVP

Thanks Dan,

I'm currently using DAO 3.6.
I'm having issues with the OpenRecordset. The SQL is how I was shown how to
best get the data, as to why I'm using the record set instead of the table
name, I kept getting failures because the table names have spaces in them
(Federal Tax) and it seemed to be the only way the compile would not fail.
Yes I did have a typo in the sql statement I've changed it but I'm still
getting errors when I run the function. At this point I'm willing (an have)
to completely change the code so it works.

Again I'm use to Access 2.0, 95 and 97. I've not found any good books on
what the changes have been from the old to new standards and the Help files
are a joke when 80% of the hyperlinks I click on tell me that the help files
are not installed even after I installed everything to the HD.


The code with updates as you requested: (I've put in the comment areas
where the code fails, basically the last part when doing the OpenRecordset
with the sql call. More then willing to eliminate using the recordset rstFT
if someone tells me how to do it with the spaces.


Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency)
As Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a Recordset from the table Federal Tax
Dim rstFT As New ADODB.Recordset
rstFT.Open "Federal Tax", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Create a temp storage location for the info needed to do the math
Dim rstMS As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
Set sqlCode = "Select * FROM '" & rstFT & "' WHERE [Marriage

Status]=
'" &

MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And [Not
Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -

rstFFT![Over])

*

rstFFT![% to withhold]

End Function
Thanks again
Tom Hagen Jr

Hi Tom,
The simplest soloution is to continue to use DAO
Just make sure you have a reference set.
Open any code module and go to Tools-> references and check
off DAO.
If you leave the reference to ADO in, make sure you declare
your recordsets like:
Dim rs As DAO.Recordset

As for the code below, you don't say what is not working.
I don't see rstFFT declared anywhere but you try to open a recordset on
it.
(so it's bound to fail), and why are you refering to a recordset inside
your sql statement??
Is that a typo, did you mean rstFT?

Use a string variable to hold your sql statement, it's easier to debug
that way.

 

[Tom Hagen Jr]

I'm really starting to get fustrated. I went back to the basics. I
remarked the major parts of the statement as you will see below. I'm
getting the same 3001 error. As at this point it should be only getting the
records where "Marriage Status" is equal to the string MS. If I go in
designe the SQL statement in the query view I get the following SQL, which
does work in the query.

SQL stright from query for the original code:

SELECT [Federal Tax].[Marriage Status]
FROM [Main Sheet], [Federal Tax]
WHERE (([Federal Tax]![Marriage Status]=[Main Sheet]![B5] And [Federal
Tax]![Payroll Period]=[Main Sheet]![B7] And [Federal Tax]![Over]<=[Main
Sheet]![Federal Tax B5] And [Federal Tax]![Not Over]>[Main Sheet]![Federal
Tax B5]));

The code I'm trying to get work now:

SELECT [Federal Tax].[Marriage Status]
FROM [Main Sheet], [Federal Tax]
WHERE ((([Federal Tax].[Marriage Status])=[Main Sheet]![B5]));


Revised code where most of the areas are remared (') out:

Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency) As
Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a temp storage location for the info needed to do the math
Dim sqlCode As String
Dim rstFFT As New ADODB.Recordset

'Using the old method of recordset to try and create the temp numbers
needed this is not working
sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= '"
& MS & "'"
' And [Payroll Period]= '" & PP & "' And [Over]<=" & TI & " And [Not
Over]>'" & TI
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed as the rstFFT is not working
this will also fail
Find_Fed_Tax = TI
'rstFFT![Income tax to withhold] + (TI - rstFFT![Over]) * rstFFT![% to
withhold]

End Function

Thanks again for any help.

Tom Hagen

TI is a currency field, yet you've got quotes around its value in the SQL
string. Assuming that the field Over in the table is a numeric field, lose
the quotes. (spaces added for clarity:

sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= ' "
& MS & " 'And [Payroll Period]= ' " & PP & " ' And [Over]<= " & TI & " And
[Not Over]> " & TI

instead of

sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= ' "
& MS & " 'And [Payroll Period]= ' " & PP & " ' And [Over]<= ' " & TI & " '
And
[Not Over]> ' " & TI & " ' "


--
Doug Steele, Microsoft Access MVP

Made a mistake in the code when posting here is the updated version:

Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency) As
Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a temp storage location for the info needed to do the math
Dim rstFFT As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage

Status]=

'"
& MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And
[Not Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed as the rstFFT is not working
this will also fail
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -
rstFFT![Over]) * rstFFT![% to withhold]

End Function

Hi Tom,
If you have spaces in table names (never a very good idea) you have to
enclose them in [].
So..
"Select * FROM [Federal Tax] WHERE [Marriage Status]= '" &

does that help?

--
HTH
Dan Artuso, Access MVP


Thanks Dan,

I'm currently using DAO 3.6.
I'm having issues with the OpenRecordset. The SQL is how I was

shown
how to

best get the data, as to why I'm using the record set instead of the table
name, I kept getting failures because the table names have spaces in them
(Federal Tax) and it seemed to be the only way the compile would not fail.
Yes I did have a typo in the sql statement I've changed it but I'm still
getting errors when I run the function. At this point I'm willing

(an
have)

to completely change the code so it works.

Again I'm use to Access 2.0, 95 and 97. I've not found any good

books
on

what the changes have been from the old to new standards and the

Help
files

are a joke when 80% of the hyperlinks I click on tell me that the

help
files

are not installed even after I installed everything to the HD.


The code with updates as you requested: (I've put in the comment areas
where the code fails, basically the last part when doing the OpenRecordset
with the sql call. More then willing to eliminate using the

recordset
rstFT

if someone tells me how to do it with the spaces.


Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency)
As Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a Recordset from the table Federal Tax
Dim rstFT As New ADODB.Recordset
rstFT.Open "Federal Tax", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Create a temp storage location for the info needed to do the math
Dim rstMS As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
Set sqlCode = "Select * FROM '" & rstFT & "' WHERE [Marriage

Status]=
'" &

MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "'

And
[Not

Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -

rstFFT![Over])

*
rstFFT![% to withhold]

End Function
Thanks again
Tom Hagen Jr

Hi Tom,
The simplest soloution is to continue to use DAO
Just make sure you have a reference set.
Open any code module and go to Tools-> references and check
off DAO.
If you leave the reference to ADO in, make sure you declare
your recordsets like:
Dim rs As DAO.Recordset

As for the code below, you don't say what is not working.
I don't see rstFFT declared anywhere but you try to open a

recordset
on

it.
(so it's bound to fail), and why are you refering to a recordset inside
your sql statement??
Is that a typo, did you mean rstFT?

Use a string variable to hold your sql statement, it's easier to debug
that way.

 

[Dan Artuso]

Hi,
Two things, do a Debug.Print sqlCode to see what your string evaluates to.
Copy and paste that into the query designer and see if it works.

Second, I just noticed that you have:
Set rstFFT = db.OpenRecordset(sqlCode)

To the best of my knowledge there is no OpenRecordset method of the
connection object (I checked this out from VB, not Access, using ADO 2.6)

I use this to open an rs:

rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic

Once again, you don't have to use ADO, why not just stick with DAO and eliminate
all these problems?

--
HTH
Dan Artuso, Access MVP

I'm really starting to get fustrated. I went back to the basics. I
remarked the major parts of the statement as you will see below. I'm
getting the same 3001 error. As at this point it should be only getting the
records where "Marriage Status" is equal to the string MS. If I go in
designe the SQL statement in the query view I get the following SQL, which
does work in the query.

SQL stright from query for the original code:

SELECT [Federal Tax].[Marriage Status]
FROM [Main Sheet], [Federal Tax]
WHERE (([Federal Tax]![Marriage Status]=[Main Sheet]![B5] And [Federal
Tax]![Payroll Period]=[Main Sheet]![B7] And [Federal Tax]![Over]<=[Main
Sheet]![Federal Tax B5] And [Federal Tax]![Not Over]>[Main Sheet]![Federal
Tax B5]));

The code I'm trying to get work now:

SELECT [Federal Tax].[Marriage Status]
FROM [Main Sheet], [Federal Tax]
WHERE ((([Federal Tax].[Marriage Status])=[Main Sheet]![B5]));


Revised code where most of the areas are remared (') out:

Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency) As
Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a temp storage location for the info needed to do the math
Dim sqlCode As String
Dim rstFFT As New ADODB.Recordset

'Using the old method of recordset to try and create the temp numbers
needed this is not working
sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= '"
& MS & "'"
' And [Payroll Period]= '" & PP & "' And [Over]<=" & TI & " And [Not
Over]>'" & TI
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed as the rstFFT is not working
this will also fail
Find_Fed_Tax = TI
'rstFFT![Income tax to withhold] + (TI - rstFFT![Over]) * rstFFT![% to
withhold]

End Function

Thanks again for any help.

Tom Hagen

TI is a currency field, yet you've got quotes around its value in the SQL
string. Assuming that the field Over in the table is a numeric field, lose
the quotes. (spaces added for clarity:

sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= ' "
& MS & " 'And [Payroll Period]= ' " & PP & " ' And [Over]<= " & TI & " And
[Not Over]> " & TI

instead of

sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage Status]= ' "
& MS & " 'And [Payroll Period]= ' " & PP & " ' And [Over]<= ' " & TI & " '
And
[Not Over]> ' " & TI & " ' "


--
Doug Steele, Microsoft Access MVP

Made a mistake in the code when posting here is the updated version:

Public Function Find_Fed_Tax(MS As String, PP As String, TI As Currency) As
Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a temp storage location for the info needed to do the math
Dim rstFFT As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
sqlCode = "Select [*] FROM [Federal Tax] WHERE [Marriage

Status]=

'"
& MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And
[Not Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed as the rstFFT is not working
this will also fail
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -
rstFFT![Over]) * rstFFT![% to withhold]

End Function



Hi Tom,
If you have spaces in table names (never a very good idea) you have to
enclose them in [].
So..
"Select * FROM [Federal Tax] WHERE [Marriage Status]= '" &

does that help?

--
HTH
Dan Artuso, Access MVP


Thanks Dan,

I'm currently using DAO 3.6.
I'm having issues with the OpenRecordset. The SQL is how I was shown
how to
best get the data, as to why I'm using the record set instead of the
table
name, I kept getting failures because the table names have spaces in
them
(Federal Tax) and it seemed to be the only way the compile would not
fail.
Yes I did have a typo in the sql statement I've changed it but I'm still
getting errors when I run the function. At this point I'm willing (an
have)
to completely change the code so it works.

Again I'm use to Access 2.0, 95 and 97. I've not found any good books
on
what the changes have been from the old to new standards and the Help
files
are a joke when 80% of the hyperlinks I click on tell me that the help
files
are not installed even after I installed everything to the HD.


The code with updates as you requested: (I've put in the comment areas
where the code fails, basically the last part when doing the
OpenRecordset
with the sql call. More then willing to eliminate using the recordset
rstFT
if someone tells me how to do it with the spaces.


Public Function Find_Fed_Tax(MS As String, PP As String, TI As
Currency)
As Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a Recordset from the table Federal Tax
Dim rstFT As New ADODB.Recordset
rstFT.Open "Federal Tax", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Create a temp storage location for the info needed to do the math
Dim rstMS As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp numbers
needed this is not working
Set sqlCode = "Select * FROM '" & rstFT & "' WHERE [Marriage Status]=
'" &
MS & "'And [Payroll Period]= '" & PP & "' And [Over]<='" & TI & "' And
[Not
Over]>'" & TI & "'"
Set rstFFT = db.OpenRecordset(sqlCode)

'Do the math for the amount to be taxed
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI - rstFFT![Over])
*
rstFFT![% to withhold]

End Function
Thanks again
Tom Hagen Jr

Hi Tom,
The simplest soloution is to continue to use DAO
Just make sure you have a reference set.
Open any code module and go to Tools-> references and check
off DAO.
If you leave the reference to ADO in, make sure you declare
your recordsets like:
Dim rs As DAO.Recordset

As for the code below, you don't say what is not working.
I don't see rstFFT declared anywhere but you try to open a recordset
on
it.
(so it's bound to fail), and why are you refering to a recordset
inside
your sql statement??
Is that a typo, did you mean rstFT?

Use a string variable to hold your sql statement, it's easier to debug
that way.

 

[Tom Hagen Jr]

Dan,
Thanks. I changed the code from:
Set rstFFT = db.OpenRecordset(sqlCode)
to
rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic

Now I get an error saying that = is expected. I tried a few thing but
nothing worked. I feel we are close....

As to why ADO vs DAO. I'm new to the changes to 2000. I was trying to get
the code up and running ASAP. All I could find was references to ADO. The
Help files are not working when I try and pull up help on DAO nothing would
come up. Still have no answer as to how to fix the help files so I can look
at all the info. If someone can tell me how to get the help files to work I
should be able to figure this stuff out.

Thanks again for your help.

Tom

 

[William Wang[MSFT]]

Hi Tom,

The following code seems to be fine on my side. Please try it to see
if it works:

Public Function Find_Fed_Tax(MS As String, PP As String, TI As
Currency) As Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a temp storage location for the info needed to do the math
Dim rstFFT As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp
numbers needed this Is Not working
sqlCode = "Select * FROM [Federal Tax] WHERE [Marriage
Status]= " & "'" & MS & "' And [Payroll Period]= '" & PP & "'

And [Over]<= " & TI & " And [Not Over]> " & TI

rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic

'Do the math for the amount to be taxed as the rstFFT is not
working this will also fail
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -
rstFFT![Over]) * rstFFT![% to withhold]
rstFFT.Close

End Function

Sincerely,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
| From: "Tom Hagen Jr" <[email protected]>
| References: <[email protected]>
<[email protected]>
<#[email protected]>
<#[email protected]>
<#[email protected]>
<#[email protected]>
<[email protected]>
<[email protected]>
| Subject: Re: Problems with Recordset in Access 2000
| Date: Wed, 17 Sep 2003 22:14:53 -0500
| Lines: 45
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <e#[email protected]>
| Newsgroups: microsoft.public.access.modulesdaovba.ado
| NNTP-Posting-Host: mplsdslgw26poolb28.mpls.uswest.net 63.231.161.28
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.access.modulesdaovba.ado:13908
| X-Tomcat-NG: microsoft.public.access.modulesdaovba.ado
|
| Dan,
| Thanks. I changed the code from:
| Set rstFFT = db.OpenRecordset(sqlCode)
| to
| rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic
|
| Now I get an error saying that = is expected. I tried a few thing
but
| nothing worked. I feel we are close....
|
| As to why ADO vs DAO. I'm new to the changes to 2000. I was
trying to get
| the code up and running ASAP. All I could find was references to
ADO. The
| Help files are not working when I try and pull up help on DAO
nothing would
| come up. Still have no answer as to how to fix the help files so I
can look
| at all the info. If someone can tell me how to get the help files
to work I
| should be able to figure this stuff out.
|
| Thanks again for your help.
|
| Tom
|
| | > Hi,
| > Two things, do a Debug.Print sqlCode to see what your string
evaluates to.
| > Copy and paste that into the query designer and see if it works.
| >
| > Second, I just noticed that you have:
| > Set rstFFT = db.OpenRecordset(sqlCode)
| >
| > To the best of my knowledge there is no OpenRecordset method of
the
| > connection object (I checked this out from VB, not Access, using
ADO 2.6)
| >
| > I use this to open an rs:
| >
| > rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic
| >
| > Once again, you don't have to use ADO, why not just stick with
DAO and
| eliminate
| > all these problems?
| >
| > --
| > HTH
| > Dan Artuso, Access MVP
|
|
|

 

[Tom Hagen Jr]

William
Thanks a TON. That did it.

Can anyone tell me a great book to get that explains the coding? Like I've
been saying my help files are not work even after deleting and reinstalling
Office 2000. I went out and bought Running Access 2000 and Access 2000
Fundamentals but they don't go into great amounts of detail that I'm use to
with Access 2, 95 and 97. Like the difference between DAO and ADO coding.
Which turns out I need to learn about.

Thanks again everyone for your help.

Tom Hagen Jr

Hi Tom,

The following code seems to be fine on my side. Please try it to see
if it works:

Public Function Find_Fed_Tax(MS As String, PP As String, TI As
Currency) As Currency
'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a temp storage location for the info needed to do the math
Dim rstFFT As New ADODB.Recordset
Dim sqlCode As String

'Using the old method of recordset to try and create the temp
numbers needed this Is Not working
sqlCode = "Select * FROM [Federal Tax] WHERE [Marriage
Status]= " & "'" & MS & "' And [Payroll Period]= '" & PP & "'

And [Over]<= " & TI & " And [Not Over]> " & TI

rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic

'Do the math for the amount to be taxed as the rstFFT is not
working this will also fail
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -
rstFFT![Over]) * rstFFT![% to withhold]
rstFFT.Close

End Function

Sincerely,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
| From: "Tom Hagen Jr" <[email protected]>
| References: <[email protected]>
<[email protected]>
<#[email protected]>
<#[email protected]>
<#[email protected]>
<#[email protected]>
<[email protected]>
<[email protected]>
| Subject: Re: Problems with Recordset in Access 2000
| Date: Wed, 17 Sep 2003 22:14:53 -0500
| Lines: 45
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <e#[email protected]>
| Newsgroups: microsoft.public.access.modulesdaovba.ado
| NNTP-Posting-Host: mplsdslgw26poolb28.mpls.uswest.net 63.231.161.28
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.access.modulesdaovba.ado:13908
| X-Tomcat-NG: microsoft.public.access.modulesdaovba.ado
|
| Dan,
| Thanks. I changed the code from:
| Set rstFFT = db.OpenRecordset(sqlCode)
| to
| rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic
|
| Now I get an error saying that = is expected. I tried a few thing
but
| nothing worked. I feel we are close....
|
| As to why ADO vs DAO. I'm new to the changes to 2000. I was
trying to get
| the code up and running ASAP. All I could find was references to
ADO. The
| Help files are not working when I try and pull up help on DAO
nothing would
| come up. Still have no answer as to how to fix the help files so I
can look
| at all the info. If someone can tell me how to get the help files
to work I
| should be able to figure this stuff out.
|
| Thanks again for your help.
|
| Tom
|
| | > Hi,
| > Two things, do a Debug.Print sqlCode to see what your string
evaluates to.
| > Copy and paste that into the query designer and see if it works.
| >
| > Second, I just noticed that you have:
| > Set rstFFT = db.OpenRecordset(sqlCode)
| >
| > To the best of my knowledge there is no OpenRecordset method of
the
| > connection object (I checked this out from VB, not Access, using
ADO 2.6)
| >
| > I use this to open an rs:
| >
| > rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic
| >
| > Once again, you don't have to use ADO, why not just stick with
DAO and
| eliminate
| > all these problems?
| >
| > --
| > HTH
| > Dan Artuso, Access MVP
|
|
|

 

[William Wang[MSFT]]

Hi Tom,
Thanks for your feedback. For references to ADO and DAO, I'd
recommend visiting the MSDN website.
http://msdn.microsoft.com

Search the key words ADO and DAO, you may find a couple of technical
articles may be useful. Here is an article provides a guide to
revising code that uses Microsoft Data Access Objects (DAO) into code
that uses Microsoft ActiveX Data Objects (ADO), as well as a guide to
writing new code using ADO with the Microsoft Jet Provider. Includes
a discussion on the general differences between DAO and ADO and looks
at some of the advantages to using ADO over DAO.

Migrating from DAO to ADO
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao
/html/daotoadoupdate.asp

You may alos want to refer to the following links for more
information.

Data Access Objects (DAO)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccor
e/html/_core_database_topics_.28.dao.29.asp

ActiveX Data Objects 2.8 Start Page
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado27
0/htm/pg_introduction.asp

Sincerely,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
| From: "Tom Hagen Jr" <[email protected]>
| References: <[email protected]>
<[email protected]>
<#[email protected]>
<#[email protected]>
<#[email protected]>
<#[email protected]>
<[email protected]>
<[email protected]>
<e#[email protected]>
<#[email protected]>
| Subject: Re: Problems with Recordset in Access 2000
| Date: Thu, 18 Sep 2003 12:30:54 -0500
| Lines: 147
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.modulesdaovba.ado
| NNTP-Posting-Host: mplsdslgw26poolb28.mpls.uswest.net 63.231.161.28
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.access.modulesdaovba.ado:13913
| X-Tomcat-NG: microsoft.public.access.modulesdaovba.ado
|
| William
| Thanks a TON. That did it.
|
| Can anyone tell me a great book to get that explains the coding?
Like I've
| been saying my help files are not work even after deleting and
reinstalling
| Office 2000. I went out and bought Running Access 2000 and Access
2000
| Fundamentals but they don't go into great amounts of detail that
I'm use to
| with Access 2, 95 and 97. Like the difference between DAO and ADO
coding.
| Which turns out I need to learn about.
|
| Thanks again everyone for your help.
|
| Tom Hagen Jr
message
| | > Hi Tom,
| >
| > The following code seems to be fine on my side. Please try it to
see
| > if it works:
| >
| > Public Function Find_Fed_Tax(MS As String, PP As String, TI As
| > Currency) As Currency
| > 'MS is Marriage Status, PP is Payroll Period, TI is Taxable Income
| >
| > 'Create a connection to the current Database
| > Dim db As ADODB.Connection
| > Set db = CurrentProject.Connection
| >
| > 'Create a temp storage location for the info needed to do the
math
| > Dim rstFFT As New ADODB.Recordset
| > Dim sqlCode As String
| >
| > 'Using the old method of recordset to try and create the temp
| > numbers needed this Is Not working
| > sqlCode = "Select * FROM [Federal Tax] WHERE [Marriage
| > Status]= " & "'" & MS & "' And [Payroll Period]= '" & PP & "'
| >
| > And [Over]<= " & TI & " And [Not Over]> " & TI
| >
| > rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic
| >
| > 'Do the math for the amount to be taxed as the rstFFT is not
| > working this will also fail
| > Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -
| > rstFFT![Over]) * rstFFT![% to withhold]
| > rstFFT.Close
| >
| > End Function
| >
| > Sincerely,
| >
| > William Wang
| > Microsoft Partner Online Support
| >
| > Get Secure! - www.microsoft.com/security
| > =====================================================
| > When responding to posts, please "Reply to Group" via
| > your newsreader so that others may learn and benefit
| > from your issue.
| > =====================================================
| >
| > This posting is provided "AS IS" with no warranties, and confers
no
| > rights.
| > --------------------
| > | From: "Tom Hagen Jr" <[email protected]>
| > | References: <[email protected]>
| > <[email protected]>
| > <#[email protected]>
| > <#[email protected]>
| > <#[email protected]>
| > <#[email protected]>
| > <[email protected]>
| > <[email protected]>
| > | Subject: Re: Problems with Recordset in Access 2000
| > | Date: Wed, 17 Sep 2003 22:14:53 -0500
| > | Lines: 45
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <e#[email protected]>
| > | Newsgroups: microsoft.public.access.modulesdaovba.ado
| > | NNTP-Posting-Host: mplsdslgw26poolb28.mpls.uswest.net
63.231.161.28
| > | Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl
| > microsoft.public.access.modulesdaovba.ado:13908
| > | X-Tomcat-NG: microsoft.public.access.modulesdaovba.ado
| > |
| > | Dan,
| > | Thanks. I changed the code from:
| > | Set rstFFT = db.OpenRecordset(sqlCode)
| > | to
| > | rstFFT.Open sqlCode, db, adOpenForwardOnly,
adLockOptimistic
| > |
| > | Now I get an error saying that = is expected. I tried a few
thing
| > but
| > | nothing worked. I feel we are close....
| > |
| > | As to why ADO vs DAO. I'm new to the changes to 2000. I was
| > trying to get
| > | the code up and running ASAP. All I could find was references
to
| > ADO. The
| > | Help files are not working when I try and pull up help on DAO
| > nothing would
| > | come up. Still have no answer as to how to fix the help files
so I
| > can look
| > | at all the info. If someone can tell me how to get the help
files
| > to work I
| > | should be able to figure this stuff out.
| > |
| > | Thanks again for your help.
| > |
| > | Tom
| > |
| > | | > | > Hi,
| > | > Two things, do a Debug.Print sqlCode to see what your string
| > evaluates to.
| > | > Copy and paste that into the query designer and see if it
works.
| > | >
| > | > Second, I just noticed that you have:
| > | > Set rstFFT = db.OpenRecordset(sqlCode)
| > | >
| > | > To the best of my knowledge there is no OpenRecordset method
of
| > the
| > | > connection object (I checked this out from VB, not Access,
using
| > ADO 2.6)
| > | >
| > | > I use this to open an rs:
| > | >
| > | > rstFFT.Open sqlCode, db, adOpenForwardOnly, adLockOptimistic
| > | >
| > | > Once again, you don't have to use ADO, why not just stick with
| > DAO and
| > | eliminate
| > | > all these problems?
| > | >
| > | > --
| > | > HTH
| > | > Dan Artuso, Access MVP
| > |
| > |
| > |
| >
|
|
|