Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with Date Format

Posted on 2011-09-19
17
Medium Priority
?
401 Views
Last Modified: 2012-08-13
I have an Access 2007 ADP with a SQL 2008 database.  I am trying to insert either the current date or the date from one of my fields with an Update Query/Stored Procedure into a DateTime field with the desired result of mm/dd/yyyy format.  However no matter how I have tried to format the NOW() function or DATE function within VBA I get the result of 12:00:00 AM or some error message.  I have tried Format and Convert, but maybe not the right combinations.  Suggestions?  
0
Comment
Question by:JasBrad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 5

Expert Comment

by:25112
ID: 36561959
SELECT CONVERT(VARCHAR(10), GETDATE(), 101)
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 36561976
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36562018
Ultimately in the end, the field stores a double precision number.
0 is 30-Dec-899 12:00:00 AM
Right now is 40805.4862731481

There isn't point in massaging the crud out of what you are trying to put in
Try it for yourself

Function myDate()
MsgBox CDbl(Now())
End Function

If you want to put in just the date of something use
DateValue(WhateverHoldsYourDate)

Now, displaying your date as it comes out is a different problem
There you want a Format(YourFieldOrControl, "mm/dd/yyyy")
Be careful, though, because NOW() DOES store the time.
That can bite you when you build BETWEEN queries
Your chosen format is also bad.
Depending on Regional Settings 01/09/11 can be 9-Jan-11 or 1-Sep-11
In English the format dd-MMM-yyyy is hard to misinterpret
In any language or system ISO 8601 format is always good -- yyyy-mm-dd -- 2011-09-19

Play with these
Function myDate()
MsgBox CDbl(Now())
MsgBox Format(Now(), "mm/dd/yyyy")
MsgBox Format(Now(), "mm/dd/yyyy hh:Mm:Ss AMPM")
MsgBox Format(DateValue(Now), "mm/dd/yyyy hh:Mm:Ss AMPM")
MsgBox Format(Date(), "mm/dd/yyyy")
MsgBox Format(Date(), "mm/dd/yyyy hh:Mm:Ss AMPM")
End Function

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JasBrad
ID: 36562071
I am getting a strange error from my ADP.  Microsoft Visual Basic, Compile Error: Sub or Function not Defined. It is erroring out on "VarChar".
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36562152
You'll need to post some code or maybe a sample--although with an ADP, that's hard
Normally Access will look after most of this stuff if it is coming from a control.
If you are doing it through SQL and VBA the code I posted should help you out.

If it's a passthrough of some sort, SQL Server takes date literals surrounded by single quotes
'19-Sep-11' would be what you'd pass to SQL Server for today's date
If you had to build the string in VBA then chr(39) & Date & chr(39) would be good
ISO 8601 formats are always good too '2011-09-19'
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 36562156
here put the formate in palce of varchar..
0
 

Author Comment

by:JasBrad
ID: 36562226
Its just a simple bit of code actually.  The field of HOLADJUST is DateTime.

strSQL = " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST =" & Convert(VarChar(10), GetDate(), 101) & ","
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY = '" & Left(Replace([Forms]![HOLIDAY]![HOLIDAY], "'", "''"), 25) & "',"
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    DoCmd.RunSQL (strSQL)

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36562337
This should work

strSQL = " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST =#" & Date & "#,"
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY = '" & Left(Replace([Forms]![HOLIDAY]![HOLIDAY], "'", "''"), 25) & "',"
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    DoCmd.RunSQL (strSQL)

Since Access VBA seems to be doing the lifting, it should work out sending the date in a way SQL Server would like.
Note the hashes #  It needs those for a date

Let me know if it doesn't work
0
 

Author Comment

by:JasBrad
ID: 36562429
Maybe I do not have something turned on in the references?  I am getting Microsoft Office Access error of Incorrect syntax near '#'.  Seems odd.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36562452
So let's simplify first

strSQL = " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST =#19-Sep-11#,"
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY = '" & Left(Replace([Forms]![HOLIDAY]![HOLIDAY], "'", "''"), 25) & "',"
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    DoCmd.RunSQL (strSQL)

Does it like this?
0
 

Author Comment

by:JasBrad
ID: 36562516
Nope.  Same error message.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36562711
Ok, try

strSQL = " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST ='19-Sep-11',"
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY = '" & Left(Replace([Forms]![HOLIDAY]![HOLIDAY], "'", "''"), 25) & "',"
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    DoCmd.RunSQL (strSQL)
0
 

Author Comment

by:JasBrad
ID: 36562750
That works fine.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36562844
Ok now for dates.
Since this seems to be using SQL Server Syntax lets try

strSQL = " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST = GETDATE(),"
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY = '" & Left(Replace([Forms]![HOLIDAY]![HOLIDAY], "'", "''"), 25) & "',"
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    DoCmd.RunSQL (strSQL)

Note that GETDATE alos includes the time.
Is that what you want?
 
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 1000 total points
ID: 36562887
If that worked and you only wanted the date then

strSQL = " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST = "
    strSQL = strSQL & " CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)"
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY = '" & Left(Replace([Forms]![HOLIDAY]![HOLIDAY], "'", "''"), 25) & "',"
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    DoCmd.RunSQL (strSQL)

might be what you need
0
 

Author Comment

by:JasBrad
ID: 36562913
Much better.  I could do without the time but can widen the fields.  But can live with it there as all I need is a stamp.  Thank you.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36562969
The reason I ask about storing times is because BETWEEN criteria constructed with date values that are storing times can give unexpected results
We expect that Between 1-Sep-11 and 19-Sep-11 will return everything from 19-Sep-11.
When a time is stored it does not, because what actually occurs is
Between 1-Sep-11 12:00:00 AM and 19-Sep-11 12:00:00 AM.

If you set the time part of a datetime value to nothing before storing it, this isn't a bother since all the values will have the implicit time of 12:00:00 AM
Begin storing time with GETDATE() and you'll need to be careful to craft that between criteria as
Between 1-Sep-11 12:00:00 AM and 19-Sep-11 11:59:59 PM.


Glad it worked
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question