[RESOLVED] Problem with DTPicker and dates

[VB rookie]

Is it possible that one machine would read a date differently than another? I am using DTPicker to pull in dates on one form. The date is then used to search a MS Access DB. The problem is, on my machine, I have no problem with the dates matching properly. BUT on the target machine, the dates come in with the year and day mixed up sometimes.

Is this a bug in my program or is there something else? Should i format the date as it comes from dtpicker?

[RobDog888]

Could be the regional date settings on the other system are different then yours. You should format the date when inserting and reading dates to/from a db to guarentee its in the expected date format.

[VB rookie]

startdate = Format(DateValue(dtpBOPTransactionsStart), "dd/mm/yy")
enddate = Format(DateValue(dtpBOPTransactionsEnd), "dd/mm/yy")

is something like this good enough?
the DB has the field for the dates to be set at short date (dd/mm/yyyy)
shouldnt the db maintain the same date format throughout?

[si_the_geek]

shouldnt the db maintain the same date format throughout?

Nope.. the database will always use its own format, and completely ignore your settings!

Those settings are purely for display, and do not have any effect on using the database from code.


Whenever possible you should use variables with a data type of Date, but if you are putting the values into a string at any point (especially an SQL statement) you need to use Format to put it into a specific format - either mm/dd/yyyy or yyyy/mm/dd (which is safer).

[leinad31]

I suggest mmm for month when storing strings

[VB rookie]

ok so in the code segments above I have startdate and enddate as Date variables. According to RobDogg, the format portion of my command will have no impact whatsoever.

The DB keeps the dates as Short Date (again - dd/mm/yyyy).

So my question is this: if there are errors converting the date on the other system how do i make this work??

I havent checked it myself, but he said he was getting dates like Mar 7, 2019 when trying to enter Mar 19, 2007

edit: no strings are being used here, except for error messages to the user when the startdate is beyond end date. Even then, I am using a line like:

MsgBox "Start Date " + Str(startdate) + " must be lower than Finishing Date. Try again " + Str(enddate)

but thats it.

[VB rookie]

i was poking around some other threads, and may have found the cause of my problem: startdate and enddate were actually Variant data types. I have changed them both to Date. Might that be the cause of my problems?

[Hack]

i was poking around some other threads, and may have found the cause of my problem: startdate and enddate were actually Variant data types. I have changed them both to Date. Might that be the cause of my problems?

You said that you haven't actually checked it, so unless you can recreate the problem or actually see what is going on, determining the cause of the issue will be problematic.

[si_the_geek]

ok so in the code segments above I have startdate and enddate as Date variables. According to RobDogg, the format portion of my command will have no impact whatsoever.

Almost correct.. using Format wont do any good, but it will noticeably increase the chances of errors - as the string has to be re-converted back to a date (which will be based on the PC settings, so 9/3/2007 could be either March 9th or Sept 3rd!).

The DB keeps the dates as Short Date (again - dd/mm/yyyy).

It doesn't store them as dd/mm/yyyy tho - that's just how it displays them for you in the Access interface (it actually stores them as numbers, eg: 39150.5733680556).

How you put the values into the database is what matters - if using SQL statements you need to use one of the formats I mentioned (preferably the second one) to ensure that there is no confusion

edit: no strings are being used here, except for error messages to the user when the startdate is beyond end date. Even then, I am using a line like:

MsgBox "Start Date " + Str(startdate) + " must be lower than Finishing Date. Try again " + Str(enddate)

I don't think I have ever seen a situation where Str is the best idea for converting data types, and here it is a terrible idea.. one way or another you should be using the Format function (either using "short date" etc, or a specific format like "dd/mm/yyyy").

i was poking around some other threads, and may have found the cause of my problem: startdate and enddate were actually Variant data types. I have changed them both to Date. Might that be the cause of my problems?

That wouldn't have helped - as you were using the format function they would have been stored as String rather than Date, which means that there was an extra conversion going on that wasn't needed (and could easily have been mis-interpreted).


One other thing I noticed is your use of the DateValue function - which is not a good idea as it converts the Date value you have into a variant/date.. which at best does nothing, and at worst will use the 'wrong' formatting assumptions and convert it wrongly.

Your code in post #3 should be like this:

startdate = dtpBOPTransactionsStart.Value

[VB rookie]

si:
so removing the dateValue command would help (potentially)?

Also, the str command is just for display purposes to let the user know their error. Not actually converted to a string and kept for later purposes

if how i put them into the DB matters, why do I not get the errors on my machine, yet get them on his machine?

edit: flipped out the str command and replaced with formatted strings of the date (MMM dd yyyy). also removed the datevalue command from a few areas. Seems to be fine for now...

[si_the_geek]

Good stuff, let us know if it goes wrong later!

if how i put them into the DB matters, why do I not get the errors on my machine, yet get them on his machine?

It's all about the date format settings on that computer - presumably yours are different to his.

Whenever you use a function to convert to/from a date (such as DateValue, Str), or convert without using a function at all (eg: StringVariable = DateVariable) VB makes assumptions of how dates should be formatted, and thus converted - based on the computers regional settings.


When converting a Date to a String you should use the Format function, as you can be sure the format will be correct - but this String cannot be converted back to a Date unless it is the same format as the computer settings (otherwise VB will read it the wrong way, and give you the wrong date - as in my 9/3/2007 example).

You could write your own function to convert a formatted String to a Date, but as you can use a control like the DTP there isn't much point - it does all the work for you (as well as checking the date is valid etc).

[VB rookie]

i have taken out all references to datevalue and Str conversions of dates for display purposes (lack of experience with VB) and replaced them with Date variables that are converted to strings with the format command. We shall see if this solves the problem...

thanks SI, Hack and RobDogg for the help.

[VB rookie]

update:

Just went to the client's machine and solved the problem. Dont know if it was me taking off the Str from the error msgbox commands, or if it was the removal of the datevalue commands, but it worked.... AFTER I changed the Regional date settings of his computer to match mine (English - Canada). His DTPicker was coming up with the date as displaying "March 2007" only. After the switch - it was all fine.

Curious, but true.

Thanks for all the help ladies/gents

[si_the_geek]

You shouldn't need to change the settings on the computer, and it is a very bad idea - as it is likely to confuse him, and may cause other programs to have problems.

I have no idea why the DTP was using that format, what format had you specified?

[VB rookie]

You shouldn't need to change the settings on the computer, and it is a very bad idea - as it is likely to confuse him, and may cause other programs to have problems.

I have no idea why the DTP was using that format, what format had you specified?

you can have dtp pick the format? i didnt know that... in hindsight, it must have been the datevalue thing that truly fixed the problem

will setting it to dptLongDate keep it at "March 12, 2007" no matter what the regional settings are?

[si_the_geek]

Short Date and Long Date are both regional settings, so they will be displayed in whatever is the usual way for that computer.

That isn't really important tho, as the Value property of the DTP returns the actual Date that was selected, not text which has been formatted.

As long as you are putting this into a variable which has a data type of Date, there will be no issues with it - it is only when you are using a String (or Variant) that issues will arise (as even tho you haven't specified it, a conversion like Str will happen - which may or may not be the right conversion for your needs!).

[VB rookie]

thanks a lot si. its all good now