Insert Into and automatic record entry

[Jeff]

Hi All,

I would like automate some records in a table.

I have a form with a tabcontrol. There are 4 tabs and each one has a
subform control.

Tab 1 for input of student info
Tab 2 for input of emergency info
Tab 3 for Class info
Tab 4 for payments input

Class info and Payments are linked by Studentref, Year and Semester.

When the user inputs info in the first 3 subform controls, I'd like to
automate payment input in subform control on tab 4.

Class info has a field as Number for the number of monthly payments the
student will pay for the course, and a field as Date where it is the
date of the first payment.

I have the rest of the info put into the new record row automatically
(Default values because they don't change) so that the user just puts in
the date of each payment and the number of the payment (are different
for each payment).

What I'd like:

When the user inputs payment date and number of payments in subform
control on tab 3 and presses button update, in subformcontrol payments,
the exact number of records has already been input with date increasing
by month and payment number incrementing by 1.

I've tried using an apend query using INSERT INTO, but it isn't working;
syntax perhaps!

Any help really appreciated,

Jeff

 

[Steve Schapel]

Jeff,

I've tried using an apend query using INSERT INTO, but it isn't working;
syntax perhaps!

Yes, it could be. If you could post the code you have tried, along with
some more information about what "isn't working" means, maybe someone
can spot where the problem lies.

 

[Jeff]

Thanks Steve

I finally got the sql code working after an awful lot of Googling,
Yahooing, trial and error and a few heavy-duty swear words!

Thanks for the advice, though, really appreciate it.

Jeff

 

[Jeff]

Steve, Help me!

I thought I had it licked... but this code is increasing the days by
one, not the month.

Code for the button:

Dim i As Integer
Dim PagNo As Integer
Dim Ref As Integer
Dim SQL As String
Dim Count As Integer
Dim NewDate As String
Dim Dateplus As String


' Set Variables
PagNo = 0
Count = 0
i = Forms![add Aluno 2].Form![fmAlunosCurso input]!InglesImprest
Ref = Forms![add Aluno 2]![fmAlunosCurso input].Form!EspanholRef
Dateplus = Forms![add Aluno 2].Form![fmAlunosCurso input]!DataVenc


' Open Loop
For PagNo = 1 To i
'NewDate = Dateadd("m", Count, Dateplus)
NewDate = DateSerial(year(Dateplus), (month(Dateplus) + Count),
day(Dateplus))

MsgBox PagNo & " " & Ref & " " & Count & " " & NewDate

SQL = "INSERT INTO tbAlunosPag ( AlunoRef, [CursoRef Ing], [CursoRef
Esp], DataVenc, Quanto, AnodoCurso, SemestredoCurso, Prestação )" & _
"SELECT Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!AlunoRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!InglesRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!EspanholRef, " & "#" & NewDate & "#" & " ,
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!Quanto, Formulários![add Aluno
2]![tbAlunosPag subformulárioinaddAluno].Form!AnodoCurso,
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!SemestredoCurso ," & PagNo & ";"

DoCmd.RunSQL SQL
Count = Count + 1
Next

I have a msgbox that shows the increasing month, but the saved record in
the table has the days increased.

all the date fields are set as Normal.

Date in form = 5-jan-06
Date in table after insert into = 5-mai-06
6-mai-06

The msgbox shows: 5/01/2006
5/02/2006

If I use 15-jan-2006 then everything works fine!

Help me, it's driving me nuts!

Thanks

jeff

 

[Steve Schapel]

Jeff,

When you are using dates in code, VBA will always interpret in American
format if it can. So when your MsgBox shows 5/02/2006 the code that
uses this value will interpret as 2 May 06.

Also I note that you are declaring the NewDate and Dateplus variables as
String instead of Date... I am not sure of the consequences of this,
I've never done it

I think the easiest way to handle it is to force the use of the
numerical value of the date, like this...

Dim NewDate As Date
Dim DatePlus As Date
Dateplus = CLng(Forms![add Aluno 2].Form![fmAlunosCurso input]!DataVenc)
or, if I understand correctly, the code is being called from a button on
the fmAlunosCurso input subform, so more simply...
Dateplus = CLng(Me.DataVenc)
NewDate = CLng(DateAdd("m", i, Dateplus))

Well, there are some other ways I would change the Append Query as well.
Am I right in supposing that the data in most of the fields that are
being appended, with the exception of the date and the PagNo, are the
same for all records? And that this is data which is already saved as
the current record in the form and subforms? So can you based your
append query on a query that uses the tables and selects for the current
record on the form? If so, good. If not, and you need to refer for the
value of all the fields to controls on the form and subforms, it would
be better to use a VALUES() clause in the SQL, rather than a SELECT
clause with no FROM... But anyway, let's tackle things one at a time!

--
Steve Schapel, Microsoft Access MVP

Steve, Help me!

I thought I had it licked... but this code is increasing the days by
one, not the month.

Code for the button:

Dim i As Integer
Dim PagNo As Integer
Dim Ref As Integer
Dim SQL As String
Dim Count As Integer
Dim NewDate As String
Dim Dateplus As String


' Set Variables
PagNo = 0
Count = 0
i = Forms![add Aluno 2].Form![fmAlunosCurso input]!InglesImprest
Ref = Forms![add Aluno 2]![fmAlunosCurso input].Form!EspanholRef
Dateplus = Forms![add Aluno 2].Form![fmAlunosCurso input]!DataVenc


' Open Loop
For PagNo = 1 To i
'NewDate = Dateadd("m", Count, Dateplus)
NewDate = DateSerial(year(Dateplus), (month(Dateplus) + Count),
day(Dateplus))

MsgBox PagNo & " " & Ref & " " & Count & " " & NewDate

SQL = "INSERT INTO tbAlunosPag ( AlunoRef, [CursoRef Ing], [CursoRef
Esp], DataVenc, Quanto, AnodoCurso, SemestredoCurso, Prestação )" & _
"SELECT Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!AlunoRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!InglesRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!EspanholRef, " & "#" & NewDate & "#" & " ,
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!Quanto, Formulários![add Aluno
2]![tbAlunosPag subformulárioinaddAluno].Form!AnodoCurso,
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!SemestredoCurso ," & PagNo & ";"

DoCmd.RunSQL SQL
Count = Count + 1
Next

I have a msgbox that shows the increasing month, but the saved record in
the table has the days increased.

all the date fields are set as Normal.

Date in form = 5-jan-06
Date in table after insert into = 5-mai-06
6-mai-06

The msgbox shows: 5/01/2006
5/02/2006

If I use 15-jan-2006 then everything works fine!

Help me, it's driving me nuts!

Thanks

jeff

 

[Jeff]

Steve

I changed the sequence of the date format


Dateplus = CLng(DateSerial(year(Forms![add Aluno 2].Form![fmAlunosCurso
input]!DataVenc), day(Forms![add Aluno 2].Form![fmAlunosCurso
input]!DataVenc), month(Forms![add Aluno 2].Form![fmAlunosCurso
input]!DataVenc) + Count))

Instead of year month day

I put

year day month

Now, with the variable as Date, it works OK. I think I tried this
combination before, but this time, with variable as date it worked fine.

Thanks very much for the help,


Now, with each insert, there appears a warning message. Any way just to
have the warning message on the first insert and not the others?

Thanks very much for your advice, expertise and patience. This is my
first db creation!

Jeff

 

[Douglas J Steele]

PMFJI, but DateSerial MUST be in year, month, day order: it is not dependent
on regional settings.

 

[Steve Schapel]

Jeff,

Doug's comment is correct, about the syntax of the DateSerial function.
But I can't see why you are using DateSerial anyway. It is not
necessary, and it was not what I suggested. The DataVenc field/control
is a date, right?

Anyway, to answer your question about the warning message, this is what
happens when you use DoCmd.RunSQL. You can change to this...
CurrentDb.Execute SQL
.... or do it like this...
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

 

[Jeff]

Thanks, guys.

Steve, I changed the Select to Values as you suggested.

I used the DateAdd as you suggested, but it continued adding one to the
day instead of the month.

I don't know the difference beteen DateAdd and SerialDate, only that I
changed the order of the days,months and years and it did the trick. The
dateserial is working to accomplish what I wanted.

The strange thing about the DateAdd is that I put the same variable on a
MsgBox and the date appeared to increase by month, but in the table it
shows an increase by one. I can only guess that it's something to do
with the sql because in the table after the insert, the date shows
increase by day too.

As I said, I'm new to Access and trying my best to understand all this
code, learning as I go along. The serialdate is working, and I'm going
to copy the db to the computer at school tomorrow. We'll see then, if
there is anything amiss!

Thanks for all your help, it is really appreciated,

Jeff

 

[Steve Schapel]

Jeff,

I am glad to hear that all is working well for you. And congratulations
for jumping into an aspect of programming that is not entirely simple,
and coming through it relatively unscathed. There are some parts of
what you have done that I can't quite reconcile, but hey, you can't
argue with correct results! Best wishes.

 

[Jeff]

Hi Steve

I got home from work and decided to try your advice again. I used
dateadd and bingo, it works! I did the same thing yesterday and it
didn't. Can't figure that one out!

Anyway, I have implemented the suggestions you gave me and all's fine.

Thanks very much for all your help and patience. It really is appreciated.

Jeff

 

[Jeff]

Steve, Help Me!

I have a code to add a record but increasing the date by one month.

Problem:

date in formfield = 01/jan/2006
Result: 01/jan/2006 02/jan/2006 03/jan/2006

date in formfield = 27/jan/2006
Result: 27/jan/2006 27/feb/2006 27/mar/2006

The field in the table is set as datatype date with no formating
The field in the form is format dd mmmm yyyy
Field in subform is format dd mmmm yyyy

I've tried setting fields in table and forms to dd mmm yyyy and then
tried mmm dd yyyy and then tried no format. Same problem!

The global settings are for Brazil and I have this problem.
When the global settings are for Engish (United States) the whole thing
works perfectly.

Question: how do I get it to work using the Portuguese (Brazil) global
settings?

The Code:


Dim i As Integer
Dim PagNo As Integer
Dim SQL As String
Dim Count As Integer
Dim NewDate As Date
Dim DatePlus As Date



' Set Variables
PagNo = 1
Count = 0
i = Me.InglesImprest



For PagNo = 1 To i 'Begin the loop

If PagNo > 1 Then 'Turn off warning after first execution
DoCmd.SetWarnings False
Else
DoCmd.SetWarnings True
End If

' set the date variables

DatePlus = CLng(Dateadd("m", Count, Me.DataVenc))

'Create the sql statement to insert the records
SQL = "INSERT INTO tbAlunosPag ( AlunoRef, [CursoRef Ing],
[CursoRef Esp], DataVenc, Quanto, AnodoCurso, SemestredoCurso, Prestação
)" & _
"Values (Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!AlunoRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!InglesRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!EspanholRef, " & "#" & DatePlus & "#" & ",
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!Quanto, Formulários![add Aluno
2]![tbAlunosPag subformulárioinaddAluno].Form!AnodoCurso,
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!SemestredoCurso ," & PagNo & ");"

'Run the SQL statement
DoCmd.RunSQL SQL
DoCmd.SetWarnings True 'Restore warnings
Count = Count + 1

Next

At least I've found out what the problem is - I think I have, anyway.
Every time I think I have, I find out it doesn't work! Frustrating.

Jeff

 

[Steve Schapel]

Jeff,

Here is something that might help you understand this stuff a bit
better... A date is a date. The format is totally irrelevant. It only
affects the way the data is displayed, i.e. it is cosmetic only, and has
no bearing at all on the values you will work with in queries, code, etc.

The problem I immediately see with the code is the #s which should not
be there. Also, there should be a space before the word "Values"...
looks like your code runs ok without it, which surprises me. And also,
I am not sure why you did not follow the suggested structure I gave you
before, regarding the DataVenc=>DatePlus=>NewDate.

Also, can I ask again... is this code being run from an event on the
[add Aluno 2] form, or the [fmAlunosCurso input] subform, or somewhere
else? You will have less trouble with your code if you simplify it by
avoiding the logn references to Forms!blabla.

So, I still can't be totally clear, but I would do it more like this...

Dim i As Integer
Dim PagNo As Integer
Dim SQL As String
Dim Count As Integer
Dim NewDate As Date
Dim DatePlus As Date

' Set Variables
PagNo = 1
Count = 0
i = Me.InglesImprest
DatePlus = CLng(Me.DataVenc)

For PagNo = 1 To i 'Begin the loop

' set the date variables
NewDate = CLng(Dateadd("m", Count, DatePlus))

'Create the sql statement to insert the records
SQL = "INSERT INTO tbAlunosPag ( AlunoRef, [CursoRef Ing],
[CursoRef Esp], DataVenc, Quanto, AnodoCurso, SemestredoCurso, Prestação
)" & _
" VALUES ( Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!AlunoRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!InglesRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!EspanholRef, " & NewDate & ",
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!Quanto, Formulários![add Aluno
2]![tbAlunosPag subformulárioinaddAluno].Form!AnodoCurso,
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!SemestredoCurso ," & PagNo & " );"

'Run the SQL statement
CurrentDb.Execute SQL, dbFailOnError
Count = Count + 1

Next PagNo

--
Steve Schapel, Microsoft Access MVP

Steve, Help Me!

I have a code to add a record but increasing the date by one month.

Problem:

date in formfield = 01/jan/2006
Result: 01/jan/2006 02/jan/2006 03/jan/2006

date in formfield = 27/jan/2006
Result: 27/jan/2006 27/feb/2006 27/mar/2006

The field in the table is set as datatype date with no formating
The field in the form is format dd mmmm yyyy
Field in subform is format dd mmmm yyyy

I've tried setting fields in table and forms to dd mmm yyyy and then
tried mmm dd yyyy and then tried no format. Same problem!

The global settings are for Brazil and I have this problem.
When the global settings are for Engish (United States) the whole thing
works perfectly.

Question: how do I get it to work using the Portuguese (Brazil) global
settings?

The Code:


Dim i As Integer
Dim PagNo As Integer
Dim SQL As String
Dim Count As Integer
Dim NewDate As Date
Dim DatePlus As Date



' Set Variables
PagNo = 1
Count = 0
i = Me.InglesImprest



For PagNo = 1 To i 'Begin the loop

If PagNo > 1 Then 'Turn off warning after first execution
DoCmd.SetWarnings False
Else
DoCmd.SetWarnings True
End If

' set the date variables

DatePlus = CLng(Dateadd("m", Count, Me.DataVenc))

'Create the sql statement to insert the records
SQL = "INSERT INTO tbAlunosPag ( AlunoRef, [CursoRef Ing], [CursoRef
Esp], DataVenc, Quanto, AnodoCurso, SemestredoCurso, Prestação )" & _
"Values (Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!AlunoRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!InglesRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!EspanholRef, " & "#" & DatePlus & "#" & ",
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!Quanto, Formulários![add Aluno
2]![tbAlunosPag subformulárioinaddAluno].Form!AnodoCurso,
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!SemestredoCurso ," & PagNo & ");"

'Run the SQL statement
DoCmd.RunSQL SQL
DoCmd.SetWarnings True 'Restore warnings
Count = Count + 1

Next

At least I've found out what the problem is - I think I have, anyway.
Every time I think I have, I find out it doesn't work! Frustrating.

Jeff

 

[Douglas J Steele]

Steve: Shouldn't he be delimiting the date with # characters and ensuring
it's not in dd/mm/yyyy format?

I would assume that the reference to NewDate in the SQL:

2]![fmAlunosCurso input].Form!EspanholRef, " & NewDate & ",

would result in a date as a string, not as a number.

I'd use

2]![fmAlunosCurso input].Form!EspanholRef, " & Format(NewDate,
"\#mm\/dd\/yyyy\#") & ",


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Jeff,

Here is something that might help you understand this stuff a bit
better... A date is a date. The format is totally irrelevant. It only
affects the way the data is displayed, i.e. it is cosmetic only, and has
no bearing at all on the values you will work with in queries, code, etc.

The problem I immediately see with the code is the #s which should not
be there. Also, there should be a space before the word "Values"...
looks like your code runs ok without it, which surprises me. And also,
I am not sure why you did not follow the suggested structure I gave you
before, regarding the DataVenc=>DatePlus=>NewDate.

Also, can I ask again... is this code being run from an event on the
[add Aluno 2] form, or the [fmAlunosCurso input] subform, or somewhere
else? You will have less trouble with your code if you simplify it by
avoiding the logn references to Forms!blabla.

So, I still can't be totally clear, but I would do it more like this...

Dim i As Integer
Dim PagNo As Integer
Dim SQL As String
Dim Count As Integer
Dim NewDate As Date
Dim DatePlus As Date

' Set Variables
PagNo = 1
Count = 0
i = Me.InglesImprest
DatePlus = CLng(Me.DataVenc)

For PagNo = 1 To i 'Begin the loop

' set the date variables
NewDate = CLng(Dateadd("m", Count, DatePlus))

'Create the sql statement to insert the records
SQL = "INSERT INTO tbAlunosPag ( AlunoRef, [CursoRef Ing],
[CursoRef Esp], DataVenc, Quanto, AnodoCurso, SemestredoCurso, Prestação
)" & _
" VALUES ( Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!AlunoRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!InglesRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!EspanholRef, " & NewDate & ",
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!Quanto, Formulários![add Aluno
2]![tbAlunosPag subformulárioinaddAluno].Form!AnodoCurso,
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!SemestredoCurso ," & PagNo & " );"

'Run the SQL statement
CurrentDb.Execute SQL, dbFailOnError
Count = Count + 1

Next PagNo

--
Steve Schapel, Microsoft Access MVP

Steve, Help Me!

I have a code to add a record but increasing the date by one month.

Problem:

date in formfield = 01/jan/2006
Result: 01/jan/2006 02/jan/2006 03/jan/2006

date in formfield = 27/jan/2006
Result: 27/jan/2006 27/feb/2006 27/mar/2006

The field in the table is set as datatype date with no formating
The field in the form is format dd mmmm yyyy
Field in subform is format dd mmmm yyyy

I've tried setting fields in table and forms to dd mmm yyyy and then
tried mmm dd yyyy and then tried no format. Same problem!

The global settings are for Brazil and I have this problem.
When the global settings are for Engish (United States) the whole thing
works perfectly.

Question: how do I get it to work using the Portuguese (Brazil) global
settings?

The Code:


Dim i As Integer
Dim PagNo As Integer
Dim SQL As String
Dim Count As Integer
Dim NewDate As Date
Dim DatePlus As Date



' Set Variables
PagNo = 1
Count = 0
i = Me.InglesImprest



For PagNo = 1 To i 'Begin the loop

If PagNo > 1 Then 'Turn off warning after first execution
DoCmd.SetWarnings False
Else
DoCmd.SetWarnings True
End If

' set the date variables

DatePlus = CLng(Dateadd("m", Count, Me.DataVenc))

'Create the sql statement to insert the records
SQL = "INSERT INTO tbAlunosPag ( AlunoRef, [CursoRef Ing], [CursoRef
Esp], DataVenc, Quanto, AnodoCurso, SemestredoCurso, Prestação )" & _
"Values (Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!AlunoRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!InglesRef, Formulários![add Aluno
2]![fmAlunosCurso input].Form!EspanholRef, " & "#" & DatePlus & "#" & ",
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!Quanto, Formulários![add Aluno
2]![tbAlunosPag subformulárioinaddAluno].Form!AnodoCurso,
Formulários![add Aluno 2]![tbAlunosPag
subformulárioinaddAluno].Form!SemestredoCurso ," & PagNo & ");"

'Run the SQL statement
DoCmd.RunSQL SQL
DoCmd.SetWarnings True 'Restore warnings
Count = Count + 1

Next

At least I've found out what the problem is - I think I have, anyway.
Every time I think I have, I find out it doesn't work! Frustrating.

Jeff

 

[Jeff]

Steve: Shouldn't he be delimiting the date with # characters and ensuring
it's not in dd/mm/yyyy format?

I would assume that the reference to NewDate in the SQL:

2]![fmAlunosCurso input].Form!EspanholRef, " & NewDate & ",

would result in a date as a string, not as a number.

I'd use

2]![fmAlunosCurso input].Form!EspanholRef, " & Format(NewDate,
"\#mm\/dd\/yyyy\#") & ",

Thanks To both of you.

I simplified the reference to the date as Steve suggested.

The code above for the date worked perfectly. Now the date is correctly
adding, and I've tried so many combinations of dates. It's working
perfectly in regional language - Portuguese (Brazil).

I don't know what the code above does, could you explain it to me?

Thanks for all the help

Jeff

 

[Steve Schapel]

Thanks, Doug. No, NewDate is a number. That's why I *always* use
CLng() to handle dates in code, because it will *always* be right,
regardless of formats and regional settings. If you're using an Append
Query to insert 38652 into a date field, then you must get today's date
whether you're in Brazil or Timbuktu.

Common example: A couple of unbound textboxes for entry of a date range
for query criteria. My SQL in code will always be like this...
"WHERE MyDateField Between " & CLng(Nz(Me.Start, Me.End)) & " And " &
CLng(Me.End)
So, the entries in the unbound textboxes would (for me) normally be in
dd-mm-yy format, and similarly the date in the field. The above
approach always gets it right. Silly, probably, but I somehow don't
trust the Format() approach to always get it right... and even if it
did, I find the syntax more difficult to use.

Of course, I was also just trying to make it simpler for Jeff, who is
probably now more confused than ever

 

[Steve Schapel]

I don't know what the code above does, could you explain it to me?

You mean the shenanigans with the dates? A date is treated by Access as
a long integer. It is the number of days since 30 Dec 1899. When you
enter a date into a Date/Time data type field in Access, this is the
value that is actually stored. For today, the value is 38652. This
assumes no Time information is included. So, if you have a date field
called DataVenc, then the CLng function will return the numerical
equivalent of that date. If the date in the DataVenc field is
27-Oct-05, then CLng(DataVenc) will be 38652. If the date in the
DataVenc field is 10/27/05, then CLng(DataVenc) will still be 38652.
DateAdd("m",1,CLng(DataVenc)) will return 27-Nov-05.
CLng(DateAdd("m",1,CLng(DataVenc))) will return 38683, i.e. 31 days
later

Hope that is not too esoteric! I am in New Zealand, so also not using
American date format, and as I said in my reply to Doug, I favour the
use of the CLng finction in this way to provide the value I want to work
with. The alternative is to use the type of syntax which Doug
demonstated, to explicitly force the dates to American format.

 

[Jeff]

Thanks Steve

You have been very patient with this poor novice. Thanks awfuly for the
expertise and the help. It's great to have people who we can turn to for
help. Every now and then, I get to help someone too, with a simple
question, of course. I know that the help I have been given is being
forwarded to those I can help. Your help is being multiplied manyfold.

Thanks very much,

Until the next question...

Jeff C