CODEWITHSUNDEEP
sqlvbams-access
Ronny Sigo
Ronny Sigo

Reputation: 1

My UPDATE query returning a weird long number in stead of a string in my access table? (vba)

I am a newbie to vba and sql language. My problem is probably caused by something small I overlooked or don't know about.I have an Access table called Mnd_Januari. This table contains, among others, two fields called ShrtDate and DagVMaand, both set to the Short Text data type. DagVMaand contains only string numbers indicating the day of the month (e.g. 25) What I want to do is construct a string including DagVMaand, monthnumber (as a variable), and year (also variable)(dd/mm/yyyy). So far so good, string (varLongDay) displays nicely on Debug.Print. Only the result of the UPDATE query is weird .. all updated records get the value: 0,01237011380 Here is the code:

Private Sub btnBewerken_Click() 'Construct proper datestring 
(dd/mm/yyyy)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [DagVdMaand], 
[ShrtDate]FROM Mnd_Januari ")
Dim maandnumero As String, tjaar As String, LDate As Date, 
varLongDay As String

maandnumero = "01"
tjaar = "2021"

If Not (rs.EOF And rs.BOF) Then
    varLongDay = ""
    rs.MoveFirst 'Unnecessary in this case, but a good habit
    Do Until rs.EOF = True
        If Len(rs![DagVdMaand]) = 1 Then
            varLongDay = "0" & rs![DagVdMaand]
        Else
            varLongDay = rs![DagVdMaand]
        End If
    
        varLongDay = varLongDay & "/" & maandnumero & "/" & 
        tjaar (this linebreak caused by this form.)
        Debug.Print varLongDay 'All goes well until here e.g. 01/01/2021 , 02/01/2021 and so on ..  
        CurrentDb.Execute "UPDATE Mnd_Januari SET [ShrtDate] = " 
        & varLongDay (this linebreak caused by this form.)
        'In this above query it goes wrong; I get 0,01237011380 
in every record
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

All help very much appeciated!

Upvotes: 0

Views: 75

Answers (1)

Gustav
Gustav

Reputation: 55806

Your code doesn't make sense. All you seem to do, is to reset the field for all records. A simple update query can do that:

Update 
    Mnd_Januari
Set 
    ShrtDate = Format(DateSerial(2021, 1, 1), "dd\/mm\/yyyy")

That said, never handle or store dates as text or numbers. No exceptions. Always use DateTime (SQL) and Date (VBA).

Also, read up on normalisation. Access is not a spreadsheet, and you would never have a table for each month.

Upvotes: 0

Related Questions