Date Format Issue

[psatkar]

Hello,

I have written VBA code where based on certain infrmation in the current record (in the form), I am manipulating / updating the data available in other corresponding record in the same table. In this table I have a field called Trans_Date which is Date/Time field and the format defined is Short Date. I have defined User Defined DataType where there is variable called varTransDetails.Date defined as Date type.

Based on certan condition, I am updating the value of this variable with the new data for the current record in the form. In this case, even varTransDetails.Date variable also gets assigned with the new value of field Trans_Details. Further to this assignment, I am updating other record in the same table with new values stored in these variables. While updating the Trans_Date field in the updated record, the value stored in Trans_Date field is "Time" portion of the variable whereas I have defined variable as Date type and also while assigning the value, I am converting with "format" function to "Short Date" format.

Hence, the date getting stored in updated record is erroeneous date (e.g. if original date is 16/04/2008 then the updated date is 30/12/1899). I am producing the code I have written for your easy reference.

    Private Type udtMyData
        ID As Long
        Type As String
        Date As Date
        Ref As String
        Acct As Long
        Currency As String
        varPayee As Long
        Amt_Spent As Currency
        Amt_Received As Currency
        Memo As String
        Category As Long
        Classification As String
        Reconcile As String
        Trf_Act As Long
        Trf_Trans_id As Long
    End Type
 
    Private varTransaction As udtMyData
 
        If Me.Trans_Type = 3 Then 'If original transfer transaction is Transfer Out
            With varTransaction
                .Type = 6 'Contra transaction type has to be Transfer In
                .Date = Format(Me!Trans_date, "Short Date")
                .Ref = Nz(Me!Trans_Ref)
                .Acct = Me!Trans_Transfer_to_Acct
                .Currency = Me!Trans_Currency
                .varPayee = Me!Payee
                .Amt_Spent = 0
                .Amt_Received = Me!Trans_Amt_Spent
                .Memo = Nz(Me!Trans_Memo)
                .Category = varTrfSrcCatId
                .Classification = Nz(Me!Trans_Classification, "")
                .Reconcile = Nz(Me!Trans_Reconcile_Flag, "")
                .Trf_Act = Me!Trans_Acct
                .Trf_Trans_id = Me!Trans_id
            End With
 
            CurrentDb.Execute "Update Transaction_Details " _
                                & "Set Trans_Type = " & varTransaction.Type & ", " _
                                & "Trans_date = " & Format(varTransaction.Date, "Short Date") & " , " _
                                & "Trans_ref = " & Chr(34) & Nz(varTransaction.Ref, "") & Chr(34) & " , " _
                                & "Trans_Acct = " & varTransaction.Acct & " , " _
                                & "Trans_Currency = " & Chr(34) & varTransaction.Currency & Chr(34) & " , " _
                                & "Payee = " & varTransaction.varPayee & " , " _
                                & "Trans_Amt_Spent = " & varTransaction.Amt_Spent & " , " _
                                & "Trans_Amt_Received = " & varTransaction.Amt_Received & " , " _
                                & "Trans_Category = " & varTransaction.Category & " , " _
                                & "Trans_Classification = " & Chr(34) & Nz(varTransaction.Classification, "") & Chr(34) & " , " _
                                & "Trans_Reconcile_Flag = " & Chr(34) & Nz(varTransaction.Reconcile, "") & Chr(34) & " , " _
                                & "Trans_Transfer_To_acct = " & varTransaction.Trf_Act & " , " _
                                & "Trans_Transfer_Trans_Id = " & varTransaction.Trf_Trans_id & " , " _
                                & "Trans_Memo = " & Chr(34) & Nz(varTransaction.Memo, "") & Chr(34) _
                                & " Where Trans_id = " & varTrfTransIdx

Any help in identifying the mistake which I am making is very much appreciated.

Thanks and regards,
--Prasad

 

[khawar]

try this

Private Type udtMyData
ID As Long
Type As String
Date As String
Ref As String
Acct As Long
Currency As String
varPayee As Long
Amt_Spent As Currency
Amt_Received As Currency
Memo As String
Category As Long
Classification As String
Reconcile As String
Trf_Act As Long
Trf_Trans_id As Long
End Type

 

[psatkar]

Hi,

I did try definging the variable as "String" type however result is just the same. There is no change.

Regards,
--Prasad

 

[psatkar]

Hi,

I have resolve this issue by adding chr(34) before and after the varTransaction.Date field in the update statement and now it is updating the table properly.

Thanks,
--Prasad

 

[chergh]

Theres a couple of issues.

First of all don't use a variable called 'Date'. Date is a reserved word and may cause problems for you. For a list of reserved words see:

http://www.access-programmers.co.uk/forums/showthread.php?t=129956

What you should have done is left your variable defined as a date as then added '#' instead of your chr(34).

I would advise going and changing the name of the 'date' variable now before it causes problems for you in the future.

 

[psatkar]

Thanks Chergh....Will centainly follow your recommendation....

 

[DavidBoyleHerts]

You should always wrap date literals in hash characters as you have discovered.

select blah from blah where date>#22/1/2009#

You can also use functions that retur a date type e.g.
select blah from blah where date>now()-7

 

[Atomic Shrimp]

Theres a couple of issues.

First of all don't use a variable called 'Date'...

...Or Type, or Currency, or Memo...

 

[ChrisO]

The use of those variable names is fine within a user defined data type.

If you are getting a display of 30/12/1899 then its value is 0.

Regards,
Chris.