DATE Variable not inserting values properly

Question

I have a macro that opens a MS Project file and copies the content itself in an Access table. I make it through Excel because afterwards I need to make some queries and copy the results into some cells.

This code below creates or drops the table:

Dim dbX As DAO.Database

If Err.Number = 0 Then
    dbX.Execute ("DROP TABLE " & dbName & ";")
End If

dbX.Execute ("CREATE TABLE " & dbName & " ([ID] INTEGER, [TaskID] INTEGER, [Milestone] TEXT(3), [TaskName] TEXT(255), " & _
        "[pComplete] TEXT(10), [Start] DATE, [Finish] DATE, [BaselineStart] DATE, [BaselineFinish] DATE, " & _
        "[ActualStart] DATE, [ActualFinish] DATE);")
Err.Clear

This fills the table using the project fields:

dBquery = "INSERT INTO " & tName & "(ID, TaskID, Milestone, TaskName, pComplete, Start, Finish, BaselineStart, BaselineFinish, " & _
            "ActualStart, ActualFinish)" & _
            " VALUES (" & t.ID & ", " & t.UniqueID & ", '" & t.GetField(pjTaskMilestone) & "', '" & t.Name & "', '" & t.GetField(pjTaskPercentComplete) & _
            "', " & RetrieveDate(t.Start) & ", " & RetrieveDate(t.Finish) & ", " & RetrieveDate(t.BaselineStart) & ", " & _
            RetrieveDate(t.BaselineFinish) & ", " & RetrieveDate(t.ActualStart) & ", " & RetrieveDate(t.ActualFinish) & ");"

    dB.Execute dBquery
    RefreshDatabaseWindow

This is the function used to retrieve the date fields:

Function RetrieveDate(D As Variant) As Variant
    If D = "NA" Then
        RetrieveDate = "NULL"
    Else
        RetrieveDate = "#" & D & "#"
    End If
End Function

The problem that I have is that when the code finds an ambiguous date, uses the American format, so when I try to run queries, the results are not correct. For example, here I have a task with its dates and everything:

TASK

Whatever the date format I use it spins the date or it just doesn't insert the dates into the DB. For example, in this table, the dates are inserted in decimal format. The same task in the database is:

In this image above we can see fewer fields because I've just taken the ones that I need. So, if for example I make a query to retrieve the date in 'dd/mm/yyyy' format this same task, I get:

SELECT FORMAT(Start, "dd/mm/yyyy"), FORMAT(Finish, "dd/mm/yyyy")
FROM ow18_072014
WHERE TaskID = 202;

Query

I have tried to convert the format to yyyy/mm/dd but the dates are not pasted into the table. Another conversion I have tried is to change date format from Project but now Access changes some dates without any sense: a date equal to 20 Jun 2014 in MS Project becomes 15/11/2013 in MS Access.

I am sure that in case of dates where there is no possible interpretation like 24/11/98 (24th of december 1998) it's working as you want it to work and for other dates like 10/11/98 it chooses the default, which i think could be the american pattern mm/dd/aa which would then be 11th of october 1998. — schlonzo, Jul 30 '14 at 07:40
I thought the same too but the weirdest thing of this all is that not always works that way. It spins the date sometimes. — Eva FP, Jul 30 '14 at 07:45
@4dmonster it's not a code error because the macro just opens MS Project and inserts the values themselves into a table. I guess it's some kind of date format issue but though I have modified the date format many times to avoid this mistake, I'm completely unable to insert the dates properly. anyway, i can paste the code if you want to — Eva FP, Jul 30 '14 at 07:47
dates and numbers are treaded in Jet SQL as USA formatted. Usually it means you have to use smth like that: sql=sql & format(DateTimeValue, "mm\/dd\/yyyy") — 4dmonster, Jul 30 '14 at 07:52
You do not define what `t` is. Is it a recordset? is it a custom class? where does the data come from? Show the details please. — Renaud Bompuis, Aug 08 '14 at 23:41
`t` is a Task. Is the object I use to get the values of each MS Project File's task. Without it you can't retrieve this information nor work with the fields of this file itself. — Eva FP, Aug 09 '14 at 07:46

score 3 · Answer 1 · answered Jul 31 '14 at 00:51

3

You do not give us much information, and in the future, if you want people to actually have a chance of helping you, you have to put more effort in your question: give us some code you tried, some data example, etc.

Anyway, the issue is that by default, Access interprets literal dates as #mm/dd/yyyy#, except when it's unambiguous, like #25/12/2014#.

There are 2 ways to solve this issue: if you pass a litteral date to Access, use the #yyyy/mm/dd# ISO format instead because it's unambiguous and it will work in every locale.

Alternatively, convert your date to a decimal value and pass that to your Access date field instead, like CDec(myDate), will pass something like 41851.3465625 to Access and it will work.

answered Jul 31 '14 at 00:51

Renaud Bompuis

16,596
4
56
86

Well, but I actually I didn't think that the issue was related with Access configuration, so that's why I didn't attach any. I will edit the post to show you the problems that I'm facing. – Eva FP Aug 04 '14 at 10:02
Another question that I'm wondering is: if I store the dates as floats, will I be able to make queries using functions as `Month()` or `Now()` for example? – Eva FP Aug 04 '14 at 13:15
Maybe I wasn't clear, your only use the decimal value as way to use the date in your SQL queries without having to use date literals `#...#`. The field that stores the date in the table must still be defined as a `DATE`. It's just a trick to avoid having the issue of defining a date format as a string literal. – Renaud Bompuis Aug 05 '14 at 10:36
well but anyway, I have achieved to store the date values as you say and it keeps on storing them in American Format, but using its decimal value. I don't know if I have met myself enough clear... – Eva FP Aug 05 '14 at 11:29
I think you need to create concrete examples of the data in your question. There is nothing in the question or in your comments that could explain your results. – Renaud Bompuis Aug 05 '14 at 11:53
Ok, I'll try to edit the post again attaching examples. – Eva FP Aug 05 '14 at 12:05

score 0 · Answer 2 · answered Aug 04 '14 at 22:44

The GetField method of the Task object returns a string value. For date fields, it returns the value in the format selected by the user (or set by the DefaultDateFormat property of the application object).

The simplest solution is to use the explicit properties of the Task object instead of the GetField method.

Modify your SQL statement to something like this:

dBquery = "INSERT INTO " & t.Name & "(ID, TaskID, Milestone, TaskName, pComplete, Start, Finish, BaselineStart, BaselineFinish, " & _
            "ActualStart, ActualFinish)" & _
            " VALUES (" & t.ID & ", " & t.UniqueID & ", '" & t.Milestone & "', '" & t.Name & "', '" & t.PercentComplete & _
            "', " & RetrieveDate(t.Start) & ", " & RetrieveDate(t.Finish) & ", " & RetrieveDate(t.BaselineStart) & ", " & _
            RetrieveDate(t.BaselineFinish) & ", " & RetrieveDate(t.ActualStart) & ", " & RetrieveDate(t.ActualFinish) & ");"

It still keeps on not taking the dates properly... I have run the macro with your code and the dates are spinned too. I have no idea of how to fix it. — Eva FP, Aug 05 '14 at 07:26
Modify the RetrieveDate function to explicitly format the date; experiment with different formats. For example: `RetrieveDate = "#" & Format(D, "yyyy/mm/dd") & "#"`. — Rachel Hettinger, Aug 05 '14 at 16:30

DATE Variable not inserting values properly

2 Answers2