Saving DATE

[Sam]

I'm trying to save current DATE + 1 month to table, but it doesn't save it as
a date format but shows something like this "7.83754898468115E-04" after
saving. I'm using following code

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = " & DateAdd("m", 1, dd) & ";"

Where "dd" is defined as a DATE variable. What might be wrong? Could there
be any optional ways of completing this? Thanks!

 

[Rick Brandt]

I'm trying to save current DATE + 1 month to table, but it doesn't
save it as a date format but shows something like this
"7.83754898468115E-04" after saving. I'm using following code

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = " & DateAdd("m", 1, dd) & ";"

Where "dd" is defined as a DATE variable. What might be wrong? Could
there be any optional ways of completing this? Thanks!

What DataType is Field2? Dates in Access are numbers of type Double under the
covers. If you place one in a numeric field then a number is what you will see.

 

[David C. Holley]

The datatype for the field in which your storing the value should be set
to Date/Time.

 

[Sam]

Well, I changed the field2's value to date and now when using previous code
the field show following date "30/12/1899" and when I click to activate the
field it turns to "00:01:14".

This date that I want to save to table is next date when certain updates to
db is done. So the value should be something like "12/07/2005". But at the
moment when I read the field it gives as next updating date "00:01:14", which
of course messes the whole prosedure. Probably I'm missing something
essential?

 

[David C. Holley]

What values are going into the dd variable?

 

[Rick Brandt]

Well, I changed the field2's value to date and now when using
previous code the field show following date "30/12/1899" and when I
click to activate the field it turns to "00:01:14".

Sounds like dd also does not have a proper date value in it. The date you have
above corresponds to the number zero and the time portion suggests a small
fractional value.

 

[Sam]

The dd value has current date "date" saved to it. So basicly "dd" can be
replaced with "date" in previous code. This doesn't work either.

 

[Sam]

The "dd" contains "date" value. So basicly in previous code instead of "dd",
"date" can be used. This doesn't work either.

 

[David C. Holley]

We've lost the original code due to the number of replies so I can't
provide the exact syntax that you'll need for my suggestion.
Specifically, I would start to snoop around by first replacing the dd
variable and any others with actual dates enclosed in pound signs #. If
I recall correctly, you're using a SQL statement to update the value in
a field. Using an actual date will help to identify if the problem lies
with table or with the SQL statement. If the field value is updated to
literal date, then there's something going on with the code. Try that
and please post back including the original code.

 

[Sam]

Ok, here is a whole code I'm using at the moment and it doesn't work.

Sub UpdateDate()
Dim dd As Date

dd = date

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = " & DateAdd("m", 1, dd) & ";"

End Sub

 

[David C. Holley]

Did you try this variation to see if things in the table are set
correctly...

"SET Field2 = #12/31/2004#;"

If 12/31/2004 is not stored or displayed properly the issue lies with
the field properties for Field2. Otherwise the next step that I would
take would be to replace the original code and modify the parameters for
DateAdd() to DateAdd("m", 1, #12/31/2004#). I would also add the line

Debug.Print DateAdd("m", 1, #12/31/2004#) as this will confirm that date
that is being generated by the function.

Next step if you're having problems, would be to hit up a good Irish Pub
or better yet a Taverna. When you're back (and a bit sober), make the
following changes

dd = #12/31/2004#
Debug.Print DateAdd("m", 1, dd)
DateAdd("m", 1, dd)

Essentially what you're doing is debuging the code in a systematic
manner to identify where the problem may possibly lie.

David H

 

[Sam]

Now I have problem...I cannot find Irish pub or Taverna. Well getting drunk
has never been problem here in Finland. Problem is that getting drunk in
Finland may format the whole HDD...

About the code...I apologize, I tried all the options before going to that
pub.
Following advice worked

"SET Field2 = #12/31/2004#;"

Otherwise it really seems impossible to put date to table, or anyway I
cannot figure how. I'm not totally sure how to use those debugging things
work. I placed them to code, but there were no mistakes or any strange
behaviour.

Let m go and find the Irish Pub! On the way I might pay a visit to library
and borrow book conserning Visual Basic or something.

 

[David C. Holley]

If you're back from the pub, or better yet the Taverna. Are you getting
any error messages? What value is actually going into the field? Also

***PLEASE*** repost the code in question - I hate that it keeps getting
delted.. I have an idea that might help to figure out what's going on,
but I'll need to see the code.

 

[Sam]

Ok, I'm back...

Following code works and writes "12/31/2004" to table.

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = #12/31/2004#;"

This was the original code that should add one month to current month and
put it after that to table

dd = date

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = " & DateAdd("m", 1, dd) & ";"

 

[Rick Brandt]

Ok, I'm back...

Following code works and writes "12/31/2004" to table.

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = #12/31/2004#;"

This was the original code that should add one month to current month
and put it after that to table

dd = date

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = " & DateAdd("m", 1, dd) & ";"

Put the # symbols in there.

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = #" & DateAdd("m", 1, dd) & "#;"

 

[Douglas J. Steele]

Put the # symbols in there.

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = #" & DateAdd("m", 1, dd) & "#;"

Actually, just to handle the possibility that some of the users may not have
their short date format set to mm/dd/yyyy (even though Sam looks as though
he does), I'd recommend

DoCmd.RunSQL "UPDATE Administrative" & _
" SET Field2 = " &_
Format(DateAdd("m", 1, dd), "\#mm\/dd\/yyyy\#")

 

[David C. Holley]

Do you have any variable declarations?

The DateAdd() function is correct. The dd = date should work fine. Also,
could you please restate what's happening when you run the code as-is.

What value APPEARS in the table?

When's happy hour at the Taverna?

 

[Sam]

Thank you very much. Rick's advice solved the whole problem. Now it works as
I want it to work. Thank for everyone who helped me with this case.

Unfortunately David, I still didn't find any Irish pub.

 

[David C. Holley]

Of course its the simple things. But you still have a problem and a
major one at that no respecting developer should ever be without a handy
list of good Irish Pubs better yet great Tavernas.

 

[Brendan Reynolds]

Of course its the simple things. But you still have a problem and a major
one at that no respecting developer should ever be without a handy list of
good Irish Pubs better yet great Tavernas.

Hey!

Perhaps we can agree that the optimum solution would be a list of great
Irish pubs *and* great tavernas!