store date in table

[Balmora via AccessMonster.com]

I'm wondering how i could go about sending a date from a form to a system
table like tblsys to store the date. i tryed to use the folowing it workd
great for numbers and curency but wont work for dates, what can i do?

CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = " & Me.
MonthandYear, dbFailOnError"

pls help anyone, thanks in advance

 

[John W. Vinson]

I'm wondering how i could go about sending a date from a form to a system
table like tblsys to store the date. i tryed to use the folowing it workd
great for numbers and curency but wont work for dates, what can i do?

CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = " & Me.
MonthandYear, dbFailOnError"

pls help anyone, thanks in advance

Use # as a date delimiter, and be sure that the date is entered in either
American mm/dd/yyyy format or an unambiguous format such as yyyy-mm-dd:

CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" &
Format(CDate(Me.MonthandYear), "yyyy-mm-dd") & "#;", dbFailOnError"

Note that this will update *EVERY RECORD" in tblSys. That's ok if it's a one
record table but might not be what you intend!

 

[Balmora via AccessMonster.com]

Thanks

this is realy what i need but when i past the code i gets all red even if i
put it all on the same line any ideas y?

I'm wondering how i could go about sending a date from a form to a system
table like tblsys to store the date. i tryed to use the folowing it workd

[quoted text clipped - 4 lines]

pls help anyone, thanks in advance

Use # as a date delimiter, and be sure that the date is entered in either
American mm/dd/yyyy format or an unambiguous format such as yyyy-mm-dd:

CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" &
Format(CDate(Me.MonthandYear), "yyyy-mm-dd") & "#;", dbFailOnError"

Note that this will update *EVERY RECORD" in tblSys. That's ok if it's a one
record table but might not be what you intend!

 

[John W. Vinson]

Thanks

this is realy what i need but when i past the code i gets all red even if i
put it all on the same line any ideas y?

No. Please post the actual code (the whole sub or function). Is tblSys a table
that you created? What are its fields?

 

[Balmora via AccessMonster.com]

ok i found what was the problem, it was the " at the end had to take it off
CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" &
Format(CDate(Me.MonthandYear), "yyyy-mm-dd") & "#;", dbFailOnError

i just have one last question, what can i do to force a date i tryed this
CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" &
Format(CDate(Me.MonthandYear), "1900-01-01") & "#;", dbFailOnError
But it does not work

Becaus i want to use this too
If DLookup("monthandyear", "tblSys") > 1900 01 01 Then
Me.MonthandYear = DLookup("MonthandYear", "tblSys", "[Variable] = 'IDLast'")
Me.MonthandYear = DateAdd("m", 1, Me.MonthandYear)
End If

and im not shure the if then works too any ways thanks for all your help

 

[Balmora via AccessMonster.com]

I was thinking this over and i think i would be much bether if i can add a
month before i send it to tblSys and just to let you know tblSys is a one
record table so basicaly i would add a month when i use next month command
button and remove a month when i use previous month command button in the
click event and in the curent form event, i use

Me.MonthandYear = DLookup("MonthandYear", "tblSys", "[Variable] = 'IDLast'")
to display the new month. what do you think of this is it possible to add a
month using

CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" &
Format(CDate(Me.MonthandYear), "yyyy-mm-dd") & "#;", dbFailOnError

it would be great thanks hope to here from you soon!

ok i found what was the problem, it was the " at the end had to take it off
CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" &
Format(CDate(Me.MonthandYear), "yyyy-mm-dd") & "#;", dbFailOnError

i just have one last question, what can i do to force a date i tryed this
CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" &
Format(CDate(Me.MonthandYear), "1900-01-01") & "#;", dbFailOnError
But it does not work

Becaus i want to use this too
If DLookup("monthandyear", "tblSys") > 1900 01 01 Then
Me.MonthandYear = DLookup("MonthandYear", "tblSys", "[Variable] = 'IDLast'")
Me.MonthandYear = DateAdd("m", 1, Me.MonthandYear)
End If

and im not shure the if then works too any ways thanks for all your help
[quoted text clipped - 3 lines]

No. Please post the actual code (the whole sub or function). Is tblSys a table
that you created? What are its fields?

 

[Balmora via AccessMonster.com]

I found how to do it. this is what i did.

Private Sub Form_Current()
Me.MonthandYear = DLookup("MonthandYear", "tblSys", "[Variable] = 'IDLast'")
End Sub

Private Sub cmdNextRec_Click()
CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" & Format
(DateAdd("m", 1, Me.MonthandYear)) & "#;", dbFailOnError
End Sub

Private Sub cmdPreviousRec_Click()
CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" & Format
(DateAdd("m", -1, Me.MonthandYear)) & "#;", dbFailOnError
End Sub

Since the user goes thought the records only with the previous and the next
button and that the tblSys remembers on what record was the user last and
pops it up, the date is always good on the right record if you know a bether
way to do it, please say so.

I was thinking this over and i think i would be much bether if i can add a
month before i send it to tblSys and just to let you know tblSys is a one
record table so basicaly i would add a month when i use next month command
button and remove a month when i use previous month command button in the
click event and in the curent form event, i use

Me.MonthandYear = DLookup("MonthandYear", "tblSys", "[Variable] = 'IDLast'")
to display the new month. what do you think of this is it possible to add a
month using

CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" &
Format(CDate(Me.MonthandYear), "yyyy-mm-dd") & "#;", dbFailOnError

it would be great thanks hope to here from you soon!

ok i found what was the problem, it was the " at the end had to take it off
CurrentDb.Execute "Update tblSys Set tblSys.MonthandYear = #" &

[quoted text clipped - 18 lines]