Sets to UK format
varJuniorDate = Format ( "DD/MM/YYYY")"

... on how to address the issue  I have encountered the following errors:


Not sure if I have installed the format correctly (see below) but comes up
with an 'Overflow' error (6) when run?

Private Sub cmbOK_Click()
Dim varSeasonAge As Double
Dim varJnrSeasonAge As Double
Dim varSeasonDate As Date
Dim varJuniorDate As Date
Dim varDateOfBirth As Date

varSeasonDate = Format(DD / MM / YYYY)
varJuniorDate = Format(DD / MM / YYYY)
varDateOfBirth = Format(DD / MM / YYYY)

varSeasonDate = "01/09/2006"
varJuniorDate = "01/01/2007"

Although I have specified a 'Format' template for all three possible Date
fields it is the DateOfBirth that is causing the problem. May be a red herring?


When I put the Format template in quotes ("") it came up with a 'Type
mismatch' error 13?

varSeasonDate = Format("DD/MM/YYYY")
varJuniorDate = Format("DD/MM/YYYY")
varDateOfBirth = Format("DD/MM/YYYY")

Again, being very new to VBA I may not be fully grasping where I should have
entered the suggested code.

Sets to UK format
varJuniorDate = Format ( "DD/MM/YYYY")

... on how to address the issue  I have encountered the following errors:

Not sure if I have installed the format correctly (see below) but comes up
with an 'Overflow' error (6) when run?

Private Sub cmbOK_Click()
Dim varSeasonAge As Double
Dim varJnrSeasonAge As Double
Dim varSeasonDate As Date
Dim varJuniorDate As Date
Dim varDateOfBirth As Date

varSeasonDate = Format(DD / MM / YYYY)
varJuniorDate = Format(DD / MM / YYYY)
varDateOfBirth = Format(DD / MM / YYYY)

varSeasonDate = "01/09/2006"
varJuniorDate = "01/01/2007"

Although I have specified a 'Format' template for all three possible Date
fields it is the DateOfBirth that is causing the problem. May be a red
herring?

When I put the Format template in quotes ("") it came up with a 'Type
mismatch' error 13?

varSeasonDate = Format("DD/MM/varSea
varJuniorDate = Format("DD/MM/YYYY")
varDateOfBirth = Format("DD/MM/YYYY")

Again, being very new to VBA I may not be fully grasping where I should have
entered the suggested code.

I think that there’s something wrong with your email as it seems to have
truncated lines in it.

The FORMAT function needs something to format.
The syntax is well documented in the help and is...

StringVariable = Format(expression[, format[, firstdayofweek[,
firstweekofyear]]])

Where the expresion is a gives a date  and the format is a string.

For example StringVariable=Format(Now(),"dd/mm/yy")

... Will return the current date as, for example, "01/05/07" for the 1st of
March.

You can also use string variables for the format part and format numbers and
strings as well as dates.

To "adjust" part of your code ....
> Private Sub cmbOK_Click(P
> Dim varSeasonAge As Date
> Dim varJnrSeasonAge As Date
> Dim varSeasonDate As String
> Dim varJuniorDate As String
> Dim varDateOfBirth As String
>
> Dim varResultDate as string
>
>
> varSeasonDate = "DD / MM / YYYY)"
> varJuniorDate = "DD / MM / YYYY"
> varDateOfBirth = “DD / MM / YYYY”
>
> varSeasonDate = #01/09/2006#
> varJuniorDate = #01/01/2007#
>
> varResultDate = Format(varSeasonDate ,varSeasonDate)
> varResultDate = Format(varJuniorDate ,varJuniorDate)

Note that the function returns a string and needs a string for the “pattern”
and also that dates  are entered  with hashes around them. I’ve not tested the
above but I’m sure it will work.

What are you trying to actually do again please?

I have not answered because the stuff you have posted does not make any sense
to me. I hoped someone else would understand it and help.

The line

varDateOfBirth = Format(DD / MM / YYYY)

Is quite meaningless AFAICS.

The Format function takes an expression and returns a string. You have
defined varDateOfBirth as a date. So you cannot hand it a string. If it was a
variant (which would be the normal reason for starting it with "var") it could
be given a string.

If x was a date, say 31 December 2001 and you used the format function you
could use it like this

Y=format(x,"yy/mm/dd") and y would then hold "01/12/31"

I doi not think this is what you need at all.

I think you need to know what form your input is in. I suspect it is strings
in the form "dd/mm/yyyyy". If so the best bet is to pull each date  apart and
reassemble them to get a julian (A number representing the date)

Make a function Like this:

Function ConvertDate(DateString As Variant) As Date

Dim DateBit, MonthPart, YearPart


DateBit = CInt(Left(DateString, 2))
MonthPart = CInt(mid(DateString, 4, 2))
YearPart = CInt(Right(DateString, 4))

ConvertDate = DateSerial(YearPart, MonthPart, DateBit)

End Function

Then in your main  code you would read the date as a string to a variable, say
strDate

Then you would have a line that said

varDateOfBirth = ConvertDate(strDate)

Now varDateOfBirth contains the correct date and you can use it.

Please let us know how you get on.

I have assumed that your regional settings on your computer are set to read
dates formatted the US way. If not, there will need to be a bit of tweaking
of the code.

You mentioned you were new to VBA. Perhaps you don’t yet understand how the
Format function works.



The Format function is used to take a value and convert  it to a specified
String representation. So if you have a date  variable:



Dim varDateOfBirth Ad Date



And if you set its value to 30 April 2007 like this (in the US):



varDateOfBirth = “4/30/2007”



And you want to convert it to a String so you can display it to the user,
and have this string in a certain format, you can use the Format function:



Dim strDateOfBirth as String

strDateOfBirth = Format(varDateOfBirth, “mm/dd/yyyy”)



In this specific case, this will make sure it prints out with two digits for
both the month and day, like “04/30/2007” instead of just “4/30/2007”.



By the way, it is more customary to use “var” as a variable name prefix if
the variable is of type Variant, like this:



Dim varDateOfBirth As Variant



It would be more common for a Date to use the prefix dt or dat like this:



Dim dtDateOfBirth As Date

Dim datDateOfBirth As Date



Additionally, double quotes are the standard delimiters for Strings, and
they work for Dates, but it is more common for Dates to use hash symbols, as
in:



dtDateOfBirth = #4/30/2007#

instead of

dtDateOfBirth = “4/30/2007”



You do not use the Format function to specify a formatting template, as you
said, that you can apply to a variable once and think it stays that way –
the Format function generates a String value of a specified format when you
call it – that one time.



Also, you mentioned in your Subject something about the UK. If you prefer
the day  to come before the month, you can use:



strDateOfBirth = Format(dtDateOfBirth, “dd/mm/yyyy”)



There are also predefined date formats you can make use of that would
automatically adjust based on your computer’s locale setting so the same
code could be used in the US or the UK without any code changes.

Other commitments prevent me from testing the various
ideas just yet. I thought it important to acknowledge the various responses
and that I will get back to you all when I am able to.