Serious Problem with Dates

CraigLazar
CraigLazar used Ask the Experts™
on
Hi
I am having a weird problem with my dates
i am using vb6 and Access 2002
i am trying to pass a simple sql query to fill a data control (ado)
if i pass it this string
 AdodcPro.RecordSource = "SELECT tblControlRec.GRecID, tblControlRec.ProDate, tblControlRec.CapDate " _
                & " From tblControlRec " _
                & " WHERE (((tblControlRec.CapDate)=#" & Date & "#));"

It returns Nothing
However if i create the qry in access it works when i cut and paste into vb
SELECT tblControlRec.GRecID, tblControlRec.ProDate, tblControlRec.CapDate
FROM tblControlRec
WHERE (((tblControlRec.CapDate)=#12/9/2002#));

Also access is swopping my Day and Month around and it is driving me nuts
i typed into the query in access 09-12-2002, when i view the sql statement access builds, it swops the date around, how can i get round this
my CapDate fld is a date field set to short date, i have tried using the format property in the access table and it does not help

it shows my records in my grid which is connected to the data control

I am using a date picker for the user to select a date and then a comand button which refreshes my datacontrol and then the gris according to the date the user has selected
any ideas ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
In your mdb table, is the date field specified as a date field ? Or is it text ?
Your PC has a default short date format. See Control Panel, Regional Options, Date. You can change that to the format you want to use. That should stop the system's changing the format.
Your program can "force" the system's short date format. There is coding available for that. If your program is to be distributed however, that is not a good idea, as other users may not want their date format changed.
Finally, your VB program can specify your date variable's format as you prefer, eg:
 ThisDate = Format(date,"yyyy-mm-dd")
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
go with gron suggestion and you won't have problems
Commented:
Simply remember one thing with Access: It inserts dates in mm-dd-yyyy format and when you query a database the it searches the date in same mm-dd-yyyy format. Your system's control panel and other things dont work. So format every date to mm-dd-yyyy format and the queries will start working.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi thanx for the input
Regional setting are dd/MM/yyyy
i have tried the format command like this

AdodcPro.RecordSource = "SELECT tblControlRec.GRecID, tblControlRec.ProDate, tblControlRec.CapDate " _
               & " From tblControlRec " _
               & " WHERE (((tblControlRec.CapDate)=#" & format(Date,"DD/MM/YYYY") & "#));"

still does the same thing
i have even declared a date variable, used the format command then passed the variable into the sql statement and it does not work

my CapDate fld valus is a date value
i have also set the input mask to short date in the table field properties

Now what i have done is created a table called GoDate
i am now using this for the query and i put the date in the fld that the user selects
cn.Execute "UPDATE tblGODate set GoDate = '" & Date & "'; "
now if i use #" & date & "# then it swops my day and month around in the table and does not pick up the records for today - it seens to work if i pass it a s a string value


then it works, but this is a stupid way of doing it, but least it works

Author

Commented:
Hi Sethi
so must i in all my update and insert statements use the format comand to "mm/dd/yyyy"  ?
then the same for my queries ?

my data in the tables shows me 17-12-2002
so if i understand u, then if i do it this way, then i will not miss any records when i query
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
You better always use yyyy/mm/dd so that you it will always recognize the date correctly.

Commented:
I store my dates as string in "yyyymmdd" format.
Whenever I save the current date I use:

rs!field = Format(Date, "YYYYMMDD")

and never have a problem.

Whenever I retrieve a date, I format it like this:

myDate = Format(rs!field, "####-##-##)

and then I can format it to any other format required.

For example, some of my reports use these:

myReportDate = Format(myDate, "dd mmm yyyy")
myReportDate = Format(myDate, "dddd mmmm dd, yyyy")


All of this was done because using a date type field once upon a time was unreliable. If you saved it on one system, then it would save in "yyyymmdd" format, and another would save in "ddmmyyyy" format, leaving you always guessing which format it was using. The setting that controls the date format is in the Regional Settings control panel. If different systems have different settings, your default Date format will not be the same.

If you want reliable results, always format your date to whatever format you're using.

Commented:
By converting your dates to "mm-dd-yyyy" format while inserting and retrieving data from Microsoft Access, you will never have problems with dates.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial