long date in shortdate type field mystery 2

[andzejek]

I have a table with one of the field [DATE] defined as "Date/Time" data type with format: "Short Date" and default value Now(). I'm adding records with sql Insert command:
"Insert into NWRKORDR([CUST_ID]) Values('" & str_CUST_ID & "');" - CUST_ID field is only required.
Why records added with insert command, have field [DATE] with long type of date in this field, when records added with form have short date?? - there is a form with record nav. bar where with asterix you can add new records.

Andrew

 

[pbrodsky]

Long date and short date are not field types! Datetime is the ONLY type of date field. Long date and short date are display formats and have nothing to do with what is in the field.
Also, it is probably not a good idea to use Date as a field name - that is a word which has meaning in Access

 

[andzejek]

Yes, I know, but someone did it before me and now I have a problem. Is there a way to force access to keep short date in this field?

Andrew

 

[pbrodsky]

Is there a way to force access to keep short date in this field?" is not a meaningful question - there is only one kind of date in a date/time field. If you are seeing it displayed differently than you want, you should explain where and how you are seeing it displayed and maybe someone can help.

 

[SgtJarrow]

You can try replace the Now() with Date() anywhere the Now() is used. Now() is an access method that returns date AND time...The Date() method only returns the Date. This should fix any records created going forward. You will have to update the records that already exists if you want to remove the times from them.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SkipVought]

FYI,

DateTime values are just NUMBERS: Date is the integral part of the number and Time is the fractional part.

Check out

http://support.microsoft.com/kb/210276/

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[andzejek]

Looks like replacing now() with date() does the trick but I'm not sure if this will not affect other things.

Thank you!

 

[JoeAtWork]

How is the time component a problem? If you don't want to see it, simply format it as a short date wherever it is displayed.

 

[MajP]

You are missing the point. The issue is formatting. Format the table, form, or query fields/control as you want to see the information displayed.

Todays date is the number 39351 in every table or variable in access regardless of any formatting. It is 39351 since there are 39351 days since 30 Dec 1899. The baseline date is 30 dec 1899 which is the value 0. It is now 10:32:17 PM which is a value of .9390856481. Because 10:32 is about 22.5 hours of a complete day which is about .939 of a day. So 9/26/2007 10:32:17 PM is the number
39351.9390856481. So the difference between now and date is
39351 vs 39351.9390856481. But it just does not matter because I can show these numbers any way I want in a field, table, query, or form.

Public Sub testDate()
Debug.Print CDate(1)
Debug.Print CDate(-1)
Debug.Print Date
Debug.Print CDbl(Date)
Debug.Print Now
Debug.Print CDbl(Now())
Debug.Print #9/26/2007# - #12/31/1899#
Debug.Print Format(CDate(39351.9381), ShortDate)
Debug.Print Format(CDate(39351.9381), "dd mmm yyyy")
Debug.Print Format(CDate(39351.9381), "hh:NN:SS AM/PM")
End Sub

12/31/1899
12/29/1899
9/26/2007
39351
9/26/2007 10:32:17 PM
39351.9390856481
39350
9/26/2007 10:30:52 PM
26 Sep 2007
10:30:52 PM

 

[andzejek]

Thank you for this explanation. With every post I know more about MsAccess, thanks to all of you.
Thank you!

 

[SkipVought]

If you would have read the link I posted,

http://support.microsoft.com/kb/210276/,

you would have gotten essentially the SAME INFORMATION.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]