Exporting query to excel

[John Nurick]

You're setting the starting row
J = 15
*inside* the loop, so each time you go round it undoes the
J = J + 1
at the end of the loop. Should be

...
J = 15
Do Until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
End With
rst.MoveNext
J = J + 1
Loop
...

Okay I have stepped through the code and I realized that the results of the
query are overwritting each other until the last record is reached and then
that record is being written to the spreadsheet. I'm not real sure on how to
get all the results of the query to populate in the spreadsheet. Not sure if
I'm missing something that will allow all the results from the query to be
written into spreadsheet in the preceding cells.

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
J = 15
IRecords = IRecords + 1
.Range("G3") = rst.Fields("BranchNumber").Value
.Cells(J, 11).Value = rst.Fields("GL_Acct").Value
.Cells(J, 12).Value = rst.Fields("GL_Subacct").Value
.Cells(J, 15).Value = rst.Fields("GROSS").Value
.Cells(J, 17).Value = rst.Fields("AccountDescription").Value

End With
J = J + 1
rst.MoveNext
Loop
[\Code]

Any help on how I could accomplish this will be greatly appreciated.

Thanks!

[QUOTE="John"]
There's always a reason. But if you won't step through the code in
order to find out where it goes wrong I'm not willing to guess.


<snip>[/QUOTE][/QUOTE]

 

[SHAWTY721 via AccessMonster.com]

Thanks for all the help! I will try that.

You're setting the starting row
J = 15
*inside* the loop, so each time you go round it undoes the
J = J + 1
at the end of the loop. Should be

...
J = 15
Do Until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
End With
rst.MoveNext
J = J + 1
Loop
...

Okay I have stepped through the code and I realized that the results of the
query are overwritting each other until the last record is reached and then

[quoted text clipped - 33 lines]

 

[SHAWTY721]

Is there a way to automate other rows in excel based on the number of entries
that are produced by the query. For example have a column named line
increment by one for each result of the query.

Thanks for all the help! I will try that.

You're setting the starting row
J = 15
*inside* the loop, so each time you go round it undoes the
J = J + 1
at the end of the loop. Should be

...
J = 15
Do Until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
End With
rst.MoveNext
J = J + 1
Loop
...

Okay I have stepped through the code and I realized that the results of the
query are overwritting each other until the last record is reached and then

[quoted text clipped - 33 lines]

 

[John Nurick]

No probs. You could do something like this:

J = 15
IRecords = 0
Do until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
'put an incrementing number in column 10
.Cells(J, 10).Value = IRecords
End With
...

Is there a way to automate other rows in excel based on the number of entries
that are produced by the query. For example have a column named line
increment by one for each result of the query.

Thanks for all the help! I will try that.

You're setting the starting row
J = 15
*inside* the loop, so each time you go round it undoes the
J = J + 1
at the end of the loop. Should be

...
J = 15
Do Until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
End With
rst.MoveNext
J = J + 1
Loop
...

Okay I have stepped through the code and I realized that the results of the
query are overwritting each other until the last record is reached and then
[quoted text clipped - 33 lines]

 

[SHAWTY721]

Thanks John you have been a huge help with this project.

No probs. You could do something like this:

J = 15
IRecords = 0
Do until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
'put an incrementing number in column 10
.Cells(J, 10).Value = IRecords
End With
...

Is there a way to automate other rows in excel based on the number of entries
that are produced by the query. For example have a column named line
increment by one for each result of the query.

Thanks for all the help! I will try that.

John Nurick wrote:
You're setting the starting row
J = 15
*inside* the loop, so each time you go round it undoes the
J = J + 1
at the end of the loop. Should be

...
J = 15
Do Until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
End With
rst.MoveNext
J = J + 1
Loop
...

Okay I have stepped through the code and I realized that the results of the
query are overwritting each other until the last record is reached and then
[quoted text clipped - 33 lines]

 

[SHAWTY721]

Could this same thing be done to automatically add the year and some other
information that needs to be seen on the worksheet based on the number of
entries.

Thanks John you have been a huge help with this project.

No probs. You could do something like this:

J = 15
IRecords = 0
Do until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
'put an incrementing number in column 10
.Cells(J, 10).Value = IRecords
End With
...

Is there a way to automate other rows in excel based on the number of entries
that are produced by the query. For example have a column named line
increment by one for each result of the query.

:

Thanks for all the help! I will try that.

John Nurick wrote:
You're setting the starting row
J = 15
*inside* the loop, so each time you go round it undoes the
J = J + 1
at the end of the loop. Should be

...
J = 15
Do Until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
End With
rst.MoveNext
J = J + 1
Loop
...

Okay I have stepped through the code and I realized that the results of the
query are overwritting each other until the last record is reached and then
[quoted text clipped - 33 lines]

 

[SHAWTY721 via AccessMonster.com]

Okay I need to get the query output to calculate a sum based on the account
and subaccount number Here is what the query result looks like.
Account Subaccount AcctDesc SumOfGross Check Date
60110 0100 ...... 4160 9/7/2007
60110 0100 ...... 4160 9/21/2007
60810 0900 ...... 842 9/21/2007
60810 0900 ...... 843.5 9/7/2007

I would like to have the result look like this in my spreadsheet in the
appropriate field of course. I am trying to get the sum of SumOfGross based
on the account and subaccout. I tried to take out the Check Date from the
query but I can't because that is part of the parameters used in my query to
get the correct results.
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1685.5

Could this same thing be done to automatically add the year and some other
information that needs to be seen on the worksheet based on the number of
entries.

Thanks John you have been a huge help with this project.

[quoted text clipped - 46 lines]

 

[SHAWTY721 via AccessMonster.com]

I have a field in the excel spreadsheet call Accounting Unit which is the
Branch Number & the Dept combined. But I am having some trouble getting it to
print to the excel file like I have the other fields. For example if the
Branch Number is 778 and the Dept is 600 then the Accounting Unit is '788600'.
This is the code that I thought would work but it isn't.

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
IRecords = IRecords + 1
...

'this is the field that I can't figure out how to write a concatenate
value for
'.Cells(J, 10).Value = rst.Fields("Branch Number&""&GL_Dept").Value

End With

No probs. You could do something like this:

J = 15
IRecords = 0
Do until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
'put an incrementing number in column 10
.Cells(J, 10).Value = IRecords
End With
...

Is there a way to automate other rows in excel based on the number of entries
that are produced by the query. For example have a column named line

[quoted text clipped - 27 lines]

 

[John Nurick]

You have to spell things out more fully. Something like this:

.Cells(J, 10).Value = rst.Fields("Branch Number").Value _
& rst.Fields("GL_Dept").Value

However, if you do it like this, Excel will treat the Accounting Unit
as a number (something you can do arithmetic with) rather than a
string of digits (which is what it really is). To force Excel to treat
it as a string of digits, use something like this:

.Cells(J, 10).Formula = "'" & rst.Fields("Branch Number").Value _
& rst.Fields("GL_Dept").Value

Using .Formula instead of Value makes Excel interpret what you send it
as a formula, and prefixing the digits with an apostrophe ' forces
Excel to treat it as text.

I have a field in the excel spreadsheet call Accounting Unit which is the
Branch Number & the Dept combined. But I am having some trouble getting it to
print to the excel file like I have the other fields. For example if the
Branch Number is 778 and the Dept is 600 then the Accounting Unit is '788600'.
This is the code that I thought would work but it isn't.

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
IRecords = IRecords + 1
...

'this is the field that I can't figure out how to write a concatenate
value for
'.Cells(J, 10).Value = rst.Fields("Branch Number&""&GL_Dept").Value

End With

No probs. You could do something like this:

J = 15
IRecords = 0
Do until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
'put an incrementing number in column 10
.Cells(J, 10).Value = IRecords
End With
...

Is there a way to automate other rows in excel based on the number of entries
that are produced by the query. For example have a column named line

[quoted text clipped - 27 lines]

 

[John Nurick]

You shouldn't need to have the Check Date in your query (except of
course in the WHERE clause). And that may be the problem: your query
uses a HAVING clause where I'd have expected a WHERE.

Make a copy of your query for safety. Then
1) remove CHECK_DT from the GROUP BY clause.
2) change HAVING to WHERE
3) move the WHERE clause in front of (rather than after) the GROUP BY.
After a bit of fiddling the query should work but no longer group the
totals by date.

Okay I need to get the query output to calculate a sum based on the account
and subaccount number Here is what the query result looks like.
Account Subaccount AcctDesc SumOfGross Check Date
60110 0100 ...... 4160 9/7/2007
60110 0100 ...... 4160 9/21/2007
60810 0900 ...... 842 9/21/2007
60810 0900 ...... 843.5 9/7/2007

I would like to have the result look like this in my spreadsheet in the
appropriate field of course. I am trying to get the sum of SumOfGross based
on the account and subaccout. I tried to take out the Check Date from the
query but I can't because that is part of the parameters used in my query to
get the correct results.
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1685.5

Could this same thing be done to automatically add the year and some other
information that needs to be seen on the worksheet based on the number of
entries.

Thanks John you have been a huge help with this project.

[quoted text clipped - 46 lines]

 

[John Nurick]

Yes.

Could this same thing be done to automatically add the year and some other
information that needs to be seen on the worksheet based on the number of
entries.

Thanks John you have been a huge help with this project.

No probs. You could do something like this:

J = 15
IRecords = 0
Do until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
'put an incrementing number in column 10
.Cells(J, 10).Value = IRecords
End With
...


On Wed, 7 Nov 2007 08:10:01 -0800, SHAWTY721

Is there a way to automate other rows in excel based on the number of entries
that are produced by the query. For example have a column named line
increment by one for each result of the query.

:

Thanks for all the help! I will try that.

John Nurick wrote:
You're setting the starting row
J = 15
*inside* the loop, so each time you go round it undoes the
J = J + 1
at the end of the loop. Should be

...
J = 15
Do Until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
End With
rst.MoveNext
J = J + 1
Loop
...

Okay I have stepped through the code and I realized that the results of the
query are overwritting each other until the last record is reached and then
[quoted text clipped - 33 lines]

 

[SHAWTY721]

The query that creates these results produces this output and I want to be
able to get the sum of a field based on the Account & Subaccount matching.
Here is what the query result looks like.
Account Subaccount AcctDesc SumOfGross Check Date
60110 0100 ...... 4160 9/7/2007
60110 0100 ...... 4160 9/21/2007
60810 0900 ...... 842 9/21/2007
60810 0900 ...... 843.5 9/7/2007

This is the results that appear on the excel spreadsheet:
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1684
60810 0900 ...... 1687

I would like to have the result look like this in my spreadsheet
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1685.5

Does anyone know how I can achieve this, using the vba code that I have or
by adding to it.


Thanks!

You have to spell things out more fully. Something like this:

.Cells(J, 10).Value = rst.Fields("Branch Number").Value _
& rst.Fields("GL_Dept").Value

However, if you do it like this, Excel will treat the Accounting Unit
as a number (something you can do arithmetic with) rather than a
string of digits (which is what it really is). To force Excel to treat
it as a string of digits, use something like this:

.Cells(J, 10).Formula = "'" & rst.Fields("Branch Number").Value _
& rst.Fields("GL_Dept").Value

Using .Formula instead of Value makes Excel interpret what you send it
as a formula, and prefixing the digits with an apostrophe ' forces
Excel to treat it as text.

I have a field in the excel spreadsheet call Accounting Unit which is the
Branch Number & the Dept combined. But I am having some trouble getting it to
print to the excel file like I have the other fields. For example if the
Branch Number is 778 and the Dept is 600 then the Accounting Unit is '788600'.
This is the code that I thought would work but it isn't.

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
IRecords = IRecords + 1
...

'this is the field that I can't figure out how to write a concatenate
value for
'.Cells(J, 10).Value = rst.Fields("Branch Number&""&GL_Dept").Value

End With

No probs. You could do something like this:

J = 15
IRecords = 0
Do until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
'put an incrementing number in column 10
.Cells(J, 10).Value = IRecords
End With
...


Is there a way to automate other rows in excel based on the number of entries
that are produced by the query. For example have a column named line
[quoted text clipped - 27 lines]

 

[John Nurick]

Did you try my suggestion of modifying your query so it does not group
on CHECK_DT?

The query that creates these results produces this output and I want to be
able to get the sum of a field based on the Account & Subaccount matching.
Here is what the query result looks like.
Account Subaccount AcctDesc SumOfGross Check Date
60110 0100 ...... 4160 9/7/2007
60110 0100 ...... 4160 9/21/2007
60810 0900 ...... 842 9/21/2007
60810 0900 ...... 843.5 9/7/2007

This is the results that appear on the excel spreadsheet:
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1684
60810 0900 ...... 1687

I would like to have the result look like this in my spreadsheet
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1685.5

Does anyone know how I can achieve this, using the vba code that I have or
by adding to it.


Thanks!

You have to spell things out more fully. Something like this:

.Cells(J, 10).Value = rst.Fields("Branch Number").Value _
& rst.Fields("GL_Dept").Value

However, if you do it like this, Excel will treat the Accounting Unit
as a number (something you can do arithmetic with) rather than a
string of digits (which is what it really is). To force Excel to treat
it as a string of digits, use something like this:

.Cells(J, 10).Formula = "'" & rst.Fields("Branch Number").Value _
& rst.Fields("GL_Dept").Value

Using .Formula instead of Value makes Excel interpret what you send it
as a formula, and prefixing the digits with an apostrophe ' forces
Excel to treat it as text.

I have a field in the excel spreadsheet call Accounting Unit which is the
Branch Number & the Dept combined. But I am having some trouble getting it to
print to the excel file like I have the other fields. For example if the
Branch Number is 778 and the Dept is 600 then the Accounting Unit is '788600'.
This is the code that I thought would work but it isn't.

Do Until rst.EOF
With wbk.Sheets("JournalEntry")
IRecords = IRecords + 1
...

'this is the field that I can't figure out how to write a concatenate
value for
'.Cells(J, 10).Value = rst.Fields("Branch Number&""&GL_Dept").Value

End With

John Nurick wrote:
No probs. You could do something like this:

J = 15
IRecords = 0
Do until rst.EOF
IRecords = IRecords + 1
With wbk.Sheets("JournalEntry")
...
'put an incrementing number in column 10
.Cells(J, 10).Value = IRecords
End With
...


Is there a way to automate other rows in excel based on the number of entries
that are produced by the query. For example have a column named line
[quoted text clipped - 27 lines]

 

[John Nurick]

Adjust the query until it returns only the rows (and totals) that need
to go into the spreadsheet.

That doesn't change the values that appear in the spreadsheet. Also it looks
like that one of the Gross fields is appearing twice once being over by 1.5
and another being 1.5 below what I calculated looking at the values created
by the queries.

Did you try my suggestion of modifying your query so it does not group
on CHECK_DT?

The query that creates these results produces this output and I want to be
able to get the sum of a field based on the Account & Subaccount matching.

[quoted text clipped - 79 lines]

 

[John Nurick]

As I've already said, you need to remove CHECK_DT from the GROUP BY
clause and use your CHECK_DT criterion in a WHERE clause not a HAVING
clause. Here's an example that works in the Northwind sample database:

1) Groups on CustomerID and ShippedDate:

SELECT Orders.CustomerID,
Count([Order Details].OrderID) AS CountOfOrderID
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID, ShippedDate
HAVING ShippedDate Between #1/1/1997# And #6/30/1997#
ORDER BY CustomerID;


2) Groups on CustomerID only:

SELECT Orders.CustomerID,
Count([Order Details].OrderID) AS CountOfOrderID
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE ShippedDate Between #1/1/1997# And #6/30/1997#
GROUP BY Orders.CustomerID
ORDER BY CustomerID;

Note that the WHERE clause comes before GROUP BY and can include any
field, while the HAVING clause follows GROUP BY and can only include
fields included in GROUP BY.

Okay the results are being grouped by the CHECK_DT field and I can't remove
this from the query because that is part of the criteria being used to pull
the correct information from out of the database.

Adjust the query until it returns only the rows (and totals) that need
to go into the spreadsheet.

That doesn't change the values that appear in the spreadsheet. Also it looks
like that one of the Gross fields is appearing twice once being over by 1.5

[quoted text clipped - 12 lines]

 

[John Nurick]

With computers there's always a reason. In this case it might be the
cartesian join in your query, indicated by the comma in
FROM tblAllADPCoCodes,
tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings
In a query like this one would normally expect only inner joins and/or
left joins.

Is there any particular reason the results of the 'Sum
(tblAllPerPayPeriodEarnings.GROSS) As Gross' are being doubled.
sSQL = "SELECT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct,
tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.
AccountDescription , tblAllADPCoCodes.BranchNumber, Sum
(tblAllPerPayPeriodEarnings.GROSS) As GROSS FROM tblAllADPCoCodes,
tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept =
tblAllPerPayPeriodEarnings.GLDEPT WHERE PG = '" & Forms("frmJE").Controls
("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls
("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls
("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls
("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "#
GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct,
tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.
AccountDescription, tblAllPerPayPeriodEarnings.PG, tblAllPerPayPeriodEarnings.
[LOCATION#], tblAllADPCoCodes.BranchNumber ORDER BY tblGLAllCodes.GL_Acct;"

As I've already said, you need to remove CHECK_DT from the GROUP BY
clause and use your CHECK_DT criterion in a WHERE clause not a HAVING
clause. Here's an example that works in the Northwind sample database:

1) Groups on CustomerID and ShippedDate:

SELECT Orders.CustomerID,
Count([Order Details].OrderID) AS CountOfOrderID
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID, ShippedDate
HAVING ShippedDate Between #1/1/1997# And #6/30/1997#
ORDER BY CustomerID;

2) Groups on CustomerID only:

SELECT Orders.CustomerID,
Count([Order Details].OrderID) AS CountOfOrderID
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE ShippedDate Between #1/1/1997# And #6/30/1997#
GROUP BY Orders.CustomerID
ORDER BY CustomerID;

Note that the WHERE clause comes before GROUP BY and can include any
field, while the HAVING clause follows GROUP BY and can only include
fields included in GROUP BY.

Okay the results are being grouped by the CHECK_DT field and I can't remove
this from the query because that is part of the criteria being used to pull

[quoted text clipped - 10 lines]

 

[John Nurick]

When I have a problem like this I start by building the query in the
query designer (using the [Forms]![Form name]![Control Name] syntax to
refer to the parameters on the form).

I persevere until it gives me the results I want. Sometimes it's
necessary to switch to SQL view to tweak it.

Once it's working, I save it for future reference. Then I switch to
SQL view and reformat the SQL statement, deleting the superfluous
parentheses that Access always inserts and breaking it into lines to
make it easier to read.

Then I paste the SQL statement into my VBA code, add quotes and line
continuations, and replace the [Forms]![...] references with the VBA
equivalent.

How can I do that so I can fix the joins that I am trying to do.

Thanks!

With computers there's always a reason. In this case it might be the
cartesian join in your query, indicated by the comma in
FROM tblAllADPCoCodes,
tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings
In a query like this one would normally expect only inner joins and/or
left joins.

Is there any particular reason the results of the 'Sum
(tblAllPerPayPeriodEarnings.GROSS) As Gross' are being doubled.

[quoted text clipped - 48 lines]