Head Scratcher - Formatting Dates 2

[PantoKing]

Hello,

This is doing my head in!!

I have some code that runs to populate a table. Several processes are carried out and one of them needs to get information from a function. I pass two values to this function and one is a date. This date has been formatted into dd/mm/yy format. e.g.
dteCurrent = Format (dteCurrent,"dd/mm/yy&quot as it is needed in this way for several stages of the process.
It is passed to the function (called getenergy) like this:-
getenergy(strRef,dteCurrent)

getenergy has the following set up.

Public Function GetEnergy(strReference as string,dteDate as date)

during this function I need to reformat the date to americanised format for some sql ("mm/dd/yy&quot

so I use dteDate = format(dteDate,"mm/dd/yy&quot

however this seems to also format dteCurrent in the function that I called getenergy from. I've tried reformatting it at the end and it won't have it. It usually thinks mm is dd and vice versa!!
so 12th August 2003 becomes 8th December 2003 and the rest of my code crashes.

Has anyone any ideas to stop me throwing my pc out of the window and handing my notice in!!!

If you need further info (which you probably will do!) let me know.

Cheers,

Steve.

Make things as simple as possible — but no simpler.

 

[mp9]

I think the problem is in your statement:

dteDate = format(dteDate,"mm/dd/yy")

I think this actually changes the date value rather than just reformatting it! Let's say dteDate is 12/08/03 which, to us Brits, is 12th August. Your code, above, reformats this as 08/12/03 and then, crucially, sets dteDate to this value. However, dteDate hasn't changed format, so it then thinks it has a value of 8th December.

To get round this, don't use this code! Instead, when you want to use the dteDate value in your SQL, reformat it there and then, on the fly, e.g.

.
.
.
Dim strSQL As String
strSQL = "UPDATE tblUser SET Status = 1 WHERE ActiveDate > #" & Format(dteDate, "dd/mmm/yy") & "#;"
.
.
.

Note also that I've used a completely umabiguous date format in my reformatting of dd/mmm/yy as I've had other problems in the past with this issue that this approach helps to avoid.

Hope all this helps!

http://www22.brinkster.com/accessory/

 

[TonyJollans]

Hi SteveCarey,

First of all, the default way of passing parameters to a function is ByRef which means the function has a Reference (or a pointer) to the actual variable in the caller's scope. You can pass a copy if you like by declaring the function as, in your example:

Public Function GetEnergy(strReference As String,

ByVal

 dteDate As Date)

With this nothing that you do in the function will affect the variable in the mainline.

Secondly, and more importantly, why are you messing about with date formats like this? You are confusing what is held in a date variable with the representation of that value on output (or input) media. The Format function returns a string which is then converted into a date by the assignment of that string to a Date variable. In other words, using one of your examples again:

dteDate = format(dteDate,"mm/dd/yy")

has no effect at all

Now that I've got that out of the way, what are you actually doing? The only point at which conversion takes place is on assignment (to or from) a Date variable. Could your input date be being misinterpreted? I believe Access uses the settings from the Control Panel Regional Settings.

If this is not enough to guide you to the source of the error, could you post examples of what you assign to your date variable (and how and where) and where you see output which is 'incorrect' and how you assign to that from your date variable.

Enjoy,
Tony

 

[PantoKing]

Hi,

Thanks both for your responses, I'm sure that something here will help solve my problem!. I think the byVal is probably the best way, I'd forgotten about that one.

Tony the reason that I have to mess about with the date formats is due to a quirk in access. Usually, as you rightly say, access uses the regional settings from control panel and uses dd/mm/yy with no problems. However when passing dates to sql it causes errors if dates aren't americanized. e.g. 12/08/02 as in my code works fine as 12th August 2002 until you pass it to an sql string where it thinks it's 8th Decemeber.
It's my biggest gripe with access as it causes such headaches.

Anyway, thanks again both!

Cheers,

Steve.


Make things as simple as possible — but no simpler.

 

[TonyJollans]

Hi Steve,

I fully agree with you that dates in Access are VERY cross-making.

As far as your VBA code goes, the SQL string which you build is a point of output at which it converts a date into a string of some sort. Providing you format it as appropriate at that point (with something like mp9's posted code) you don't need to do anything else with the date variable.

Enjoy,
Tony