Newbie: Questions about query to generate XL spreadsheet?

[Ed from AZ]

Thanks to help on this NG, I've got my database to the point where I
can enter data. Now I need to be able to get it out, too! I'm
working with only one data table in Access 2003.

From what I've read, I think I need to:
-- create a query based on the table
-- create a form based on that query
-- use a Command Button on the form to run the query based on my
inputs in the form
-- use TransferSpreadsheet to get the query output into an Excel file

Here's my questions so far:
-- I would like some of the fields on the form to be multiple-select
ListBoxes. How do you get multiple selections into the code?
-- I also need start date and end date entries, but I only have one
date field in my table. Would putting two controls sourced to the
same data field mess things up?
-- What if I want all the records in a field? Do I leave it blank?
Or is there something like an "ALL" criteria choice that Access
automatically recognizes?

Thank you all for your time and the sharing of your expertise.
Ed

 

[Douglas J. Steele]

Here's my questions so far:
-- I would like some of the fields on the form to be multiple-select
ListBoxes. How do you get multiple selections into the code?

That's a little more difficult. You can't just have the query refer to the
list box: multi select list boxes always return Null as a value, regardless
of how many rows are selected. You need to dynamically create a Where clause
based on what's selected. Unfortunately, that generally means you'll need to
dynamically change the SQL of your query.

To construct the Where clause is easy:

Dim strWhere As String
Dim varSelected As Variant

If Me.MyListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me.MyListBox.ItemsSelected
strWhere = strWhere & Me.MyListBox.ItemData(varSelected) & ", "
Next varSelected
strWhere = "MyFieldName IN (" & _
Left(strWhere, Len(strWhere) - 2) & ")"
End If

To dynamically change the SQL of a query, you need something like:

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

' Construct your SQL string, including additional Where clause parts (as
above)
strSQL = ....
Set qdfCurr = CurrentDb.QueryDefs("NameOfYour Query")
qdfCurr.SQL = strSQL

-- I also need start date and end date entries, but I only have one
date field in my table. Would putting two controls sourced to the
same data field mess things up?

Two date controls won't be an issue: your Criteria for the single date field
will be

BETWEEN Forms!NameOfForm!NameOfControl1 AND Forms!NameOfForm!NameOfControl2

If you want to be able to leave one (or both) of the controls empty on the
form, use

BETWEEN Nz(Forms!NameOfForm!NameOfControl1, #1/1/100#) AND
Nz(Forms!NameOfForm!NameOfControl2, #12/31/9999#)

-- What if I want all the records in a field? Do I leave it blank?
Or is there something like an "ALL" criteria choice that Access
automatically recognizes?

There's not an All criteria choice, but you can set Access to essentially
ignore specific controls when they're empty.

Rather than having

Forms!NameOfForm!NameOfControl

as the criteria, use

Forms!NameOfForm!NameOfControl OR (Forms!NameOfForm!NameOfControl IS NULL)

If you use list boxes or combo boxes, you may wish to put in a button to
allow you to unselect all currently selected entries.

For a combo box, the easiest way is simply to set it to Null:

Me!MyComboBox = Null

For list boxes, the following will work:

Dim varSelected As Variant

For Each varSelected In Me.MyListBox.ItemsSelected
Me.MyListBox.Selected(varSelected) = False
Next varSelected

 

[John W. Vinson]

Thanks to help on this NG, I've got my database to the point where I
can enter data. Now I need to be able to get it out, too! I'm
working with only one data table in Access 2003.

From what I've read, I think I need to:
-- create a query based on the table
-- create a form based on that query
-- use a Command Button on the form to run the query based on my
inputs in the form
-- use TransferSpreadsheet to get the query output into an Excel file

Here's my questions so far:
-- I would like some of the fields on the form to be multiple-select
ListBoxes. How do you get multiple selections into the code?
-- I also need start date and end date entries, but I only have one
date field in my table. Would putting two controls sourced to the
same data field mess things up?
-- What if I want all the records in a field? Do I leave it blank?
Or is there something like an "ALL" criteria choice that Access
automatically recognizes?

Thank you all for your time and the sharing of your expertise.
Ed

First off let's correct a misconception here. If you're using a Form or
controls on a Form to *select* records (for export, in this case) then that
form (or those controls) MUST be unbound, i.e. not based on the table.
Othewise you'll overwrite whatever is in the currently displayed record when
you try to enter criteria!

For example, to search a single date field for a range of dates, you could
have two (*unbound!*) textboxes on form frmCrit named, say, txtFrom and txtTo.
The query criterion would be

= CDate([Forms]![frmCrit]![txtFrom] AND < DateAdd("d", 1, [Forms]![frmCrit]![txtTo])

The CDate and DateAdd will cover for users entering various date formats and
for the table field containing a time portion respectively.

As Douglas says, the multiselect listbox requires a bit of code - see his
post.

Basically you will want to use the command button on your form to *actually
create* a Query by building the SQL string of the query in code (just because
of the multiselect, if you didn't have that required it would be simpler).
This code could be written to only insert a criterion in the query if there is
something in the (unbound, again!) form control so - from the user's viewpoint
- leaving the textbox blank returns all records. But it's up to you to write
the code to do this!

John W. Vinson [MVP]

 

[Ed from AZ]

Thank you, Doug and John! That was a lot of info, and I'm not sure I
understood it all, but here's what I came up with.

I was given some code which included a function to build a query
string. It worked well for just reading simple, single control values
and running the query to update the values shown in a form. I've
tried modifying it as per your help to fit what this instance needs.
Maybe I should scratch the function and start from the ground up? I
don't know. Here's the code I came up with:

Private Function BuildFilter() As Variant

Dim varFind As Variant
Dim varDate As Variant
Dim varSelected As Variant
Dim strList As String

varFind = Null
varFind = "WHERE "

'Get values from form
'Vehicle SN
If Me.lstVeh.ItemsSelected.Count > 0 Then
For Each varSelected In Me.lstVeh.ItemsSelected
strList = strList & Me.lstVeh.ItemData(varSelected) & ", "
Next varSelected
strList = "[Vehicle_SN] = IN (" & _
Left(strList, Len(strList) - 2) & ")"

varFind = varFind & strList & " AND "
End If

'Part Type
If Me.lstParts.ItemsSelected.Count > 0 Then
For Each varSelected In Me.lstParts.ItemsSelected
strList = strList & Me.lstParts.ItemData(varSelected) & ", "
Next varSelected
strList = "[Part_Code] = IN (" & _
Left(strList, Len(strList) - 2) & ")"

varFind = varFind & strList & " AND "
End If

'Position Code
strList = ""

If Me.txtMainPos1.Value <> "" Then _
strList = Me.txtMainPos1.Value

If Me.txtMainPos2.Value <> "" Then _
strList = ", " & Me.txtMainPos2.Value

If Me.txtMainPos3.Value <> "" Then _
strList = ", " & Me.txtMainPos3.Value

If strList <> "" Then _
varFind = varFind & "[Position] = " & Chr(34) & strList & Chr(34)
& " AND "

'Part SN
strList = ""

If Me.txtMainPart1.Value <> "" Then _
strList = Me.txtMainPart1.Value

If Me.txtMainPart2.Value <> "" Then _
strList = ", " & Me.txtMainPart2.Value

If Me.txtMainPart3.Value <> "" Then _
strList = ", " & Me.txtMainPart3.Value

If strList <> "" Then _
varFind = varFind & "[Part_Code] = " & Chr(34) & strList & Chr(34)
& " AND "

'Dates
If Me.txtDate1.Value <> "" Then
varDate = " BETWEEN Forms!frmQryData!txtDate1 AND "
Else
varDate = " BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND "
End If

If Me.txtDate2.Value <> "" Then
varDate = varDate & "Forms!frmQryData!txtDate2"
Else
varDate = varDate & "Nz(Forms!frmQryData!txtDate2, #1/1/100#)"
End If

varFind = varFind & varDate

BuildFilter = varFind

End Function

The BuildFilter was used in the previous instance as
Me.frmQryData.Form.RecordSource = "SELECT * FROM qryPartsInstl " &
BuildFilter
Me.frmQryData.Requery
This showed data in a form. As I said, it works fine in that instance
- don't know about this one.

Ed

 

[Ed from AZ]

Okay - working with what Doug and John gave me, I ran my function to
build the query string. It returns:
WHERE [Vehicle_SN] = IN (79U) AND [Part_Code] = IN (RDWL(STD)) AND
[InstDate] = BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#)

Unfortunately, I ge the following error:
Syntax error (missing operator) in query expression '[Vehicle_SN] = IN
(79U) AND [Part_Code] = IN (RDWL(STD)) AND [InstDate] = BETWEEN
Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND Nz(Forms!frmQryData!
txtDate2, #1/1/100#)'.

I do not know enough to know what operator I am missing. Can someone
give me a drop-kick in the right direction?

Ed

 

[Douglas J. Steele]

You don't use = and IN, just IN, nor do you use = and BETWEEN, just BETWEEN.

However, you're also missing the quotes around the values in the IN list.

WHERE [Vehicle_SN] IN ('79U') AND [Part_Code] IN ('RDWL(STD)') AND
[InstDate] BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#)

I find it's sometimes necessary to put parentheses around the BETWEEN
statement:

WHERE [Vehicle_SN] IN ('79U') AND [Part_Code] IN ('RDWL(STD)') AND
([InstDate] BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#))

 

[John W. Vinson]

WHERE [Vehicle_SN] IN ('79U') AND [Part_Code] IN ('RDWL(STD)') AND
[InstDate] BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#)

Just one additional tweak - your txtDate2 NZ function will ensure that only
the one day, January 1 100AD, will be retrieved if the second textbox is null.
I'd change it to

Nz(Forms!frmQryData!txtDate2, #12/31/9999#)



John W. Vinson [MVP]

 

[Ed from AZ]

Doug, I owe you many thanks!! The function is now working well, as
far as I know. But I can't really tell yet because my code hangs up
on the TransferSpreadsheet line, so I don't know what I'm getting from
my query.

Here's my code:

Dim xlApp As Object
Dim xlBook As Object
Dim strSaveMe As String
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
xlApp.Visible = True

strSaveMe = CurrentProject.Path & "\DataOutput_" & Format(Now,
"yyyymmdd") & ".xls"
xlBook.SaveAs strSaveMe

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set qdfCurr = CurrentDb.QueryDefs("qryPartsInstl")
strSQL = BuildFilter
qdfCurr.SQL = "SELECT * FROM qryPartsInstl " & strSQL

DoCmd.TransferSpreadsheet acExport, , qdfCurr.Name, strSaveMe

The last line gives me an error of
"Circular reference caused by 'qryPartsInstl' "
I could kind of see it if it was in the SELECT line, but I can't get a
handle on it in this line.

What am I looking at?

Ed

You don't use = and IN, just IN, nor do you use = and BETWEEN, just BETWEEN.

However, you're also missing the quotes around the values in the IN list.

WHERE [Vehicle_SN] IN ('79U') AND [Part_Code] IN ('RDWL(STD)') AND
[InstDate] BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#)

I find it's sometimes necessary to put parentheses around the BETWEEN
statement:

WHERE [Vehicle_SN] IN ('79U') AND [Part_Code] IN ('RDWL(STD)') AND
([InstDate] BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#))

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Okay - working with what Doug and John gave me, I ran my function to
build the query string.  It returns:
WHERE [Vehicle_SN] = IN (79U) AND [Part_Code] = IN (RDWL(STD)) AND
[InstDate] = BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#)

Unfortunately, I ge the following error:
Syntax error (missing operator) in query expression '[Vehicle_SN] = IN
(79U) AND [Part_Code] = IN (RDWL(STD)) AND [InstDate] = BETWEEN
Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND Nz(Forms!frmQryData!
txtDate2, #1/1/100#)'.

I do not know enough to know what operator I am missing.  Can someone
give me a drop-kick in the right direction?

Ed- Hide quoted text -

- Show quoted text -

 

[Douglas J. Steele]

What's the actual SQL stored in the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug, I owe you many thanks!! The function is now working well, as
far as I know. But I can't really tell yet because my code hangs up
on the TransferSpreadsheet line, so I don't know what I'm getting from
my query.

Here's my code:

Dim xlApp As Object
Dim xlBook As Object
Dim strSaveMe As String
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
xlApp.Visible = True

strSaveMe = CurrentProject.Path & "\DataOutput_" & Format(Now,
"yyyymmdd") & ".xls"
xlBook.SaveAs strSaveMe

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set qdfCurr = CurrentDb.QueryDefs("qryPartsInstl")
strSQL = BuildFilter
qdfCurr.SQL = "SELECT * FROM qryPartsInstl " & strSQL

DoCmd.TransferSpreadsheet acExport, , qdfCurr.Name, strSaveMe

The last line gives me an error of
"Circular reference caused by 'qryPartsInstl' "
I could kind of see it if it was in the SELECT line, but I can't get a
handle on it in this line.

What am I looking at?

Ed

You don't use = and IN, just IN, nor do you use = and BETWEEN, just
BETWEEN.

However, you're also missing the quotes around the values in the IN list.

WHERE [Vehicle_SN] IN ('79U') AND [Part_Code] IN ('RDWL(STD)') AND
[InstDate] BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#)

I find it's sometimes necessary to put parentheses around the BETWEEN
statement:

WHERE [Vehicle_SN] IN ('79U') AND [Part_Code] IN ('RDWL(STD)') AND
([InstDate] BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#))

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

message

Okay - working with what Doug and John gave me, I ran my function to
build the query string. It returns:
WHERE [Vehicle_SN] = IN (79U) AND [Part_Code] = IN (RDWL(STD)) AND
[InstDate] = BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #1/1/100#)

Unfortunately, I ge the following error:
Syntax error (missing operator) in query expression '[Vehicle_SN] = IN
(79U) AND [Part_Code] = IN (RDWL(STD)) AND [InstDate] = BETWEEN
Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND Nz(Forms!frmQryData!
txtDate2, #1/1/100#)'.

I do not know enough to know what operator I am missing. Can someone
give me a drop-kick in the right direction?

Ed- Hide quoted text -

- Show quoted text -

 

[Ed from AZ]

What's the actual SQL stored in the query?

qdfCurr.SQL = "SELECT * FROM qryPartsInstl " & strSQL

strSQL =
WHERE [Vehicle_SN] IN ("79U") AND [Part_Code] IN ("RDWL(STD)") AND
[Position] = "L1O" AND ([InstDate] BETWEEN Nz(Forms!frmQryData!
txtDate1, #1/1/100#) AND Nz(Forms!frmQryData!txtDate2, #1/1/100#))

Looking at that, I saw that "qryPartsInstl" isn't where my data is
stored - it's in "tabPartsInstl".

So I changed that. Now when it hits
DoCmd.TransferSpreadsheet acExport, , qdfCurr.Name, strSaveMe
I get the error
"Cannot modify table structure. Another user has the table open."

Huh??

Ed

 

[Douglas J. Steele]

I'd actually meant for you to open the QueryDef object and look at its SQL,
but that's okay.

Did you see John's comment to you about your Nz functions on the dates? Are
you sure you don't mean

([InstDate] BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #12/31/9999#))

Is the Excel workbook in use?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What's the actual SQL stored in the query?

qdfCurr.SQL = "SELECT * FROM qryPartsInstl " & strSQL

strSQL =
WHERE [Vehicle_SN] IN ("79U") AND [Part_Code] IN ("RDWL(STD)") AND
[Position] = "L1O" AND
Looking at that, I saw that "qryPartsInstl" isn't where my data is
stored - it's in "tabPartsInstl".

So I changed that. Now when it hits
DoCmd.TransferSpreadsheet acExport, , qdfCurr.Name, strSaveMe
I get the error
"Cannot modify table structure. Another user has the table open."

Huh??

Ed

 

[Ed from AZ]

Did you see John's comment to you about your Nz functions on the dates? Are

you sure you don't mean

([InstDate] BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #12/31/9999#))

No, for some reason his comment lagged in the postings. It makes
perfect sense, though. Thank you, John!! I have changed this.

Is the Excel workbook in use?

Well, the workbook is created and SavedAs just prior to running the
query, so I'm not writing anything else to it. The SaveAs should take
care of anything left untended, I think. It's still got the Object
handle, but nothing's actively trying to access the workbook until the
DoCmd.TransferSpreadsheet line.

I'd actually meant for you to open the QueryDef object and look at its SQL,
but that's okay.

Sorry. In the Locals window, the SQL is:
"SELECT * FROM tabPartsInstl WHERE ([InstDate] BETWEEN Nz(Forms!
frmQryData!txtDate1, #1/1/100#) AND Nz(Forms!frmQryData!txtDate2,
#12/31/9999#))"

Ed
(BTW - I'm on for about 1 more hour, then I'm ofline for 13 for 14
hours til I come back.)

I'd actually meant for you to open the QueryDef object and look at its SQL,
but that's okay.

Did you see John's comment to you about your Nz functions on the dates? Are
you sure you don't mean

([InstDate]  BETWEEN Nz(Forms!frmQryData!txtDate1, #1/1/100#) AND
Nz(Forms!frmQryData!txtDate2, #12/31/9999#))

Is the Excel workbook in use?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

What's the actual SQL stored in the query?

qdfCurr.SQL = "SELECT * FROM qryPartsInstl " & strSQL

strSQL =
WHERE [Vehicle_SN] IN ("79U") AND [Part_Code] IN ("RDWL(STD)") AND
[Position] = "L1O" AND
Looking at that, I saw that "qryPartsInstl" isn't where my data is
stored - it's in "tabPartsInstl".

So I changed that.  Now when it hits
   DoCmd.TransferSpreadsheet acExport, , qdfCurr.Name, strSaveMe
I get the error
   "Cannot modify table structure.  Another user has the table open."

Huh??

Ed

 

[Douglas J. Steele]

I don't believe the SaveAs is sufficient. That doesn't actually close the
workbook. You'll need to Close it (and perhaps even Quit Excel)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is the Excel workbook in use?

Well, the workbook is created and SavedAs just prior to running the
query, so I'm not writing anything else to it. The SaveAs should take
care of anything left untended, I think. It's still got the Object
handle, but nothing's actively trying to access the workbook until the
DoCmd.TransferSpreadsheet line.

 

[Ed from AZ]

That was it, Doug!! I didn't realize the workbook had to be closed to
be written to - I'm used to VBA in Word and Excel where the file has
to be open before you can write to it. I just moved the code to close
the workbook, quit the app, and "Nothing" the objects to _before_ the
DoCmd, and it worked great!

One more question, if I may:

My SQL says: "SELECT * FROM tabPartsInstl WHERE
The problem is that I need some parameters that are calculated fields
in the query, but are not in the table. Can I change the SQL to:
"SELECT * FROM qryPartsInstl WHERE
or am I likely to get that "Circular reference caused by
'qryPartsInstl' " error again?
Should I work that from the Excel side with a macro there? (It looks
like I may have to do something like that anyway to get the formatting
I want.) Or should I add those fields into the table as calculations
from the other table fields?

Thank you so much for all your help! You and John are great to stick
with me on this.

Ed

 

[Douglas J. Steele]

Sorry, I'd totally missed the fact that your code is

Set qdfCurr = CurrentDb.QueryDefs("qryPartsInstl")
strSQL = BuildFilter
qdfCurr.SQL = "SELECT * FROM qryPartsInstl " & strSQL

In other words, you're saying in the SQL of qryPartsInstl to select from
qryPartsInstl.

You have to take the existing SQL fro qryPartsInst and prepend that to
strSQL. Note that you cannot simply take the existing SQL for qryPartsInst
and add your Where clause to it, as the second time you do that, you'll end
up trying to add a Where clause to a SQL statement that already has a Where
clause.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My SQL says: "SELECT * FROM tabPartsInstl WHERE
The problem is that I need some parameters that are calculated fields
in the query, but are not in the table. Can I change the SQL to:
"SELECT * FROM qryPartsInstl WHERE
or am I likely to get that "Circular reference caused by
'qryPartsInstl' " error again?
Should I work that from the Excel side with a macro there? (It looks
like I may have to do something like that anyway to get the formatting
I want.) Or should I add those fields into the table as calculations
from the other table fields?

 

[medbiy]

Hello,

This is Michael from the Microsoft newsgroup - Subject: Create Excel
file with VBA in Access

Your welcome, that was my intent.

Should I work that from the Excel side with a macro there?  (It looks
like I may have to do something like that anyway to get the formatting
I want.)  Or should I add those fields into the table as calculations
from the other table fields?

In responce from your question above,

You can fully build an Excel worksheet in VBA. All Excel Methods and
Properties ca be used (for cell size, color, formula, etc.). You can
explore the posibilities in Excel to apply to your VBA code in Access.

Here is a little example:

MyXL.Worksheets(SheetName).range("D1") = 3
MyXL.Worksheets(SheetName).range("D2") = 4
MyXL.Worksheets(SheetName).range("D3") = 5
MyXL.Worksheets(SheetName).range("D4").Formula = "=SUM(D13)"

MyXL.Worksheets(SheetName).Cells(5, 5)= "Test"

Here are some Excel Cell Properties:
.Font.Name
.Borders(xlEdgeLeft).LineStyle


Michael

 

[Ed from AZ]

Doug, I appreciate the response. However,

In other words, you're saying in the SQL of qryPartsInstl to select from
qryPartsInstl.

You have to take the existing SQL fro qryPartsInst and prepend that to
strSQL. Note that you cannot simply take the existing SQL for qryPartsInst
and add your Where clause to it, as the second time you do that, you'll end
up trying to add a Where clause to a SQL statement that already has a Where
clause.

went right over my head!! 8>\

I think, though, that I will work this issue from the Excel side. I'm
much more familiar with that. Besides, the Access is now working (I
think - I'll do a separate post if strange messages start popping
up!), and I'd rather not bog myself down either with trying to do a
lot of Excel manipulation or with trying to tweak ne little line of
code to get something I could easily do another way.

Again, you are great to have stuck this through with me!! Many, many
thanks!!
Ed