Access VBA wont input date into date field

Question

I need some help with this code which i cant get over for many hours now.

Im making a db used for ducment handling and here i need a date for the newly uploaded document.

If MsgBox("Mai dátummal mehet?", vbYesNo, "Verzió") = vbYes Then
    db.Execute ("UPDATE Dokumentumok SET Verzió = Date() WHERE Jelölés='" & Me.Jelölés & "'")
Else
    version = InputBox("Add meg a dátumot éééé.hh.nn.", "Verzió", Date)
    version = Replace(version, ".", ". ")
    If IsDate(version) Then
        db.Execute ("UPDATE Dokumentumok SET Verzió ='" & CDate(version) & "' WHERE Jelölés='" & Me.Jelölés & "'")
    Else
        MsgBox "Rossz formátum, mentve mai dátummal"
        db.Execute ("UPDATE Dokumentumok SET Verzió = Date() WHERE Jelölés='" & Me.Jelölés & "'")
    End If
End If

First msgbox asks if it goes with todays date, if yes, then it updates the specific date which works just fine. If not, inputbox asks for the date. I live in Hungary, defualt date format is YYYY.MM.DD. here, in access by default it is YYYY. MM. DD. - there is space between them, dont know if it matters

So far i have tried to input with the following formats with lets say October 20, 2015:

2015.10.20.
1. 1. 20.
10.20.2015
1. 1. 2015
20.10.2015
1. 1. 2015
10/20/2015
20/10/2015

Even trying with a small code in a module with everything set to manual to update that date field in the table, through isdate() check Every type passed date check but still either code runs and date field remains empty or i get error Too few parameters, expected 1 if i try sate with "\" instead of "."

version = "2015.10.20."
version = Mid(version, 6, 6) & Left(version, 4)
version = Replace(version, ".", "/")
If IsDate(version) Then
db.Execute ("UPDATE Dokumentumok SET Verzió = #" & version & "# WHERE Jelölés=E2")
End If

This one gives error missing operator in query expression '2015. 10. 20.'

    version = "2015. 10. 20. "
    If IsDate(CDate(version)) Then
    db.Execute ("UPDATE Dokumentumok SET Verzió = " & CDate(version) & " WHERE Jelölés=E2")

End If

This one syntax error in date in query expression '#2015. 10. 20.'

db.Execute ("UPDATE Dokumentumok SET Verzió = #" & CDate(version) & "# WHERE Jelölés=E2")

I have tried many combinations of variable between '' or ##, values between '', ##, date variables, formatted string variables etc.

Guys, I cant seem to make it work on my own I need an help with it.

Thanks in advance, Kristof

have you got dim version as date? and cant you use format () to change the date? — LiamH
– LiamH, Commented Oct 22, 2015 at 14:58
If your standard is 'YYYY.MM.DD' than I don't think you want spaces in your date. However, I just tried this in the US and it doesn't care about the spaces. Try adding this line and see what happens: Debug.Print Format(Version, "mm.dd.yyyy") — Wayne G. Dunn
– Wayne G. Dunn, Commented Oct 22, 2015 at 16:08
could it be the last point in the string? A date normally reads as "2015.10.20" not "2015.10.20." In the provided code snippet you replace the last point to "/" which leads to "2015/10/20/" which isn't a date either... — user783388
– user783388, Commented Oct 22, 2015 at 16:59

Andre · Accepted Answer · 2015-10-22 17:20:36Z

0

The date format that all (I hope) international Access versions understand, is the ISO format YYYY-MM-DD.

And the date delimiter is #

So with the Format function:

"UPDATE Dokumentumok SET Verzió = #" & Format(version, "yyyy-mm-dd") & "#"

answered Oct 22, 2015 at 17:20

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kristof Molnar Over a year ago

Thanks Andre, this version of formating worked. Funny it still displays it as yyyy. mm. dd. in the database.

Andre Over a year ago

@KristofMolnar: That's normal - date display is by default always the localized format. And the internal storage isn't influenced by the UPDATE format, in the database it's always an Access date.

Collectives™ on Stack Overflow

Access VBA wont input date into date field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related