Error in date when inserted to table

[brillig12]

Hi all,

I am using a database which displays data from a table on a grid. There is a button which runs a piece of vba to add a new row onto the grid, which is working normally. There is then another button which runs another piece of vba (please see below) which then inserts this data into the table. Currently all the data is entered correctly except that the date is changed as it is input, making it completely wrong. I am not the original programmer of the database and am a little lost in trying to fix this. Any suggestions are gratefully received. The vba code for inserting the data is:

Private Sub cmdOverTimeAdd_Click()
Dim i As Long
Dim iRow As Long
Dim colCount As Long
Dim strTemp As String
Dim apos As String
Dim eq As String
Dim dtSym As String
Dim ColName As String
Dim FldName As String
Dim strSQL As String
Dim Comma As String
Comma = ", "
dtSym = "#"
eq = " = "
apos = "'"
strSQL = "INSERT INTO OverTime ("
With msOverTime
    iRow = .Row
    colCount = (.Cols - 1)
    For i = 1 To colCount
       If Len(.TextMatrix(iRow, i)) > 0 Then
            ColName = Trim(.TextMatrix(0, i))
            If i = colCount Then
                strSQL = strSQL & ColName
            Else
                strSQL = strSQL & ColName & Comma
            End If
       End If
    Next i
    strTemp = Right(strSQL, 2)
    If InStr(strTemp, Comma) <> 0 Then strSQL = Left(strSQL, Len(strSQL) - 2)
    strSQL = strSQL & ") VALUES ("
    For i = 1 To colCount
       If Len(.TextMatrix(iRow, i)) > 0 Then
            FldName = Trim(.TextMatrix(iRow, i))
            ColName = Trim(.TextMatrix(0, i))
            strSQL = strSQL & IIf(InStr(ColName, "Date") <> 0, dtSym & FldName & dtSym & Comma, apos & FldName & apos & Comma)
       End If
    Next i
    strTemp = Right(strSQL, 2)
    If InStr(strTemp, Comma) <> 0 Then strSQL = Left(strSQL, Len(strSQL) - 2)
    strSQL = strSQL & ")"
Call UpdateAccessDB(strSQL)
strTemp = .TextMatrix(iRow, 1) 'get salarynumber
End With
Call PopulateOverTime(strTemp)
End Sub

 

[wiklendt]

Hi all,

I am using a database which displays data from a table on a grid.

do you mean a cross tab query? if not, what kind of 'grid'?

There is a button which runs a piece of vba to add a new row onto the grid, which is working normally.

if you mean table, better terminology is that a new 'record' was added to the 'table'

the date is changed as it is input, making it completely wrong.

how is it 'wrong'? does it change the month and day around? does it display as a number with a certain amount of decimals? or other?

also, the table into which the date data is being inserted - is the field type "date/time"? if so, you may be having issues due to the VBA declaring the date data as a "string" instead of "date"... but i myself am not expert at VBA, so i can't offer suggestions in terms of fixing it.

let's start with a description of how the data is "wrong", then we can make a better diagnosis.

 

[wiklendt]

also, had this code worked in the past? can the original programmer not look at it?

 

[CameronM]

As wiklendt has said, what date is being entered into the database when the code runs? It is not a date format issue, for example the date is being stored as mm/dd/yyyy instead of dd/mm/yyyy?

You could put a Breakpoint in your code to see what value is being sent for the FldName variable on the line

strSQL = strSQL & IIf(InStr(ColName, "Date") <> 0, dtSym & FldName & dtSym & Comma, apos & FldName & apos & Comma)

 

[brillig12]

My apologies. Upon invesitgating to describe further how the date was wrong I realised that the problem occurs only when the full date is not add. I am used to working with systems that auto correct the date from dd/mm/yy to dd/mm/yyyy, which is the required format for this database.

When presented with this the database swaps either dd with mm or dd with the second two yy.

The original programmer is completely unwilling to help so am trying to figure this stuff out.

Will do my best to more fully investigate the problem next time, as for now I can direct users to ensure that the full date is entered.

Thanks for all the help offered

 

[wiklendt]

make sure the field in the table that stores the date data is set to be of type "date/time" -this helps in data entry.