UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> All I Want Is A Good Looking Date....!, Access 2016    
 
   
chrismbaylis
post Mar 8 2018, 04:00 AM
Post#1



Posts: 91
Joined: 25-March 10
From: St Albans, UK


Morning folks - yes, I know this isn't a dating site - but thankfully this is an access question regarding dates!

Background:

I have a form that is used to capture forecast data from a team of consultants (frm_Forecast_Stage) - contains a subform in datasheet view which captures information regarding projects and forecast progess. See image 'Forecast_Staging_1'.

Attached File  Forecast_Staging_1.jpg ( 64.82K )Number of downloads: 11


Once all forecasts are captured, they need to be processed.

To do this, the three fields at the bottom of the form need to be populated:
Forecast Type is a combobox (cmbFType) that is used toi indicate if this is an initial Consultant Forecast, a Managers Amended Forecast or the Finance Final Forecast (Target)
Forecast Date is a text control (fcDate formatted as dd/mm/yyyy) which is used to enter the date of the forecast (default to now())
Forecast Period is a text control (fcPeriod) that returns a dblookup against the date, to determine which reporting Quarter the forecast applies to

Image 'Forecast Staging_2' shows these populated.

Attached File  Forecast_Staging_2.jpg ( 64.7K )Number of downloads: 7


The user (me at this point) then clicks the 'Process Forecast' button to run the following code:
CODE
Dim strSQL As String
    
    If Me.fcDate <> "" And Not IsNull(Me.fcDate) And Not IsNull(Me.cmbFType) And Not IsNull(Me.fcPeriod) Then
        'copy records to the Forecast Table
        strSQL = "INSERT INTO tbl_Forecast " & _
                 "( [Project Id], Forecast, Comments, [Forecast Type], [Forecast Date], [Forecast Target Quarter] ) " & _
                 "SELECT tbl_ForecastStage.[Project Id], " & _
                        "tbl_ForecastStage.Forecast, " & _
                        "tbl_ForecastStage.Comments, " & _
                        Me.cmbFType & " AS fcType, " & _
                        "format(#" & Me.fcDate & "#,'dd mmm yyyy')" & " AS fcDate, " & _
                        Me.fcPeriod & " AS fcQtr " & _
                 "FROM tbl_ForecastStage;"

        DoCmd.RunSQL strSQL
        
        'now copy the forecast to a backup
        DoCmd.CopyObject , "bkup__FC__" & Me.fcPeriod & Me.cmbFType.Column(1) & "_" & Me.fcDate, acTable, "tbl_ForecastStage"
        
        'finally delete everything from the Forecast stage table
        strSQL = "Delete * FROM tbl_ForecastStage;"
        DoCmd.RunSQL strSQL
    Else
        MsgBox "Before processing the Forecast, please ensure that:" & vbCrLf & _
                "1. All forecast data has been loaded to the stage table" & vbCrLf & _
                "2. A Forecast Type has been selected from the list" & vbCrLf & _
                "3. Today's date is entered in the Forecast Date field" & vbCrLf & _
                "4. The correct Forecast Period is indicated", vbInformation + vbOKOnly, "Process Forecast Error"
    End If


In short, I want to move some of the data from the Forecast Staging table, into the Forecast table, adding in the Forecast Type, The Date of the Forecast and the Forecast Period.

The issue I have is with the date read from the fcDate control.

Even though the control is formatted as dd/mm/yyyy (UK date format - same as system settings) and even though I am forcing it to format 'dd mmm yyyy' to ensure it is read correctly, when it is written to tbl_Forecast (the end table) the data is written as mm/dd/yyyy - even though the field format of the table is dd/mm/yyyy:

Attached File  Forecast_Final.jpg ( 94.87K )Number of downloads: 7


I detest the way that VBA/access handles dates and ignores system settings and field level format settings.

Any quick and easy fixes?

Thanks in advance for your help.

Chris

Go to the top of the page
 
pere_de_chipstic...
post Mar 8 2018, 04:24 AM
Post#2


UtterAccess Editor
Posts: 10,342
Joined: 8-November 07
From: South coast, England


Hi Chris

When amending/inserting dates using a query, hen they must always be in 'mm/dd/yyyy' format for Access to to process them.

I would normally use a variable to capture the date value e.g.
CODE

Dim dtfcDate as Date
dtfcDate = NZ(Me.fcDate,0)
If not dtfcDate = 0
And Not IsNull(Me.fcDate) And Not IsNull(Me.cmbFType) And Not IsNull(Me.fcPeriod) Then
'copy records to the Forecast Table
strSQL = "INSERT INTO tbl_Forecast " & _
"( [Project Id], Forecast, Comments, [Forecast Type], [Forecast Date], [Forecast Target Quarter] ) " & _
"SELECT tbl_ForecastStage.[Project Id], " & _
"tbl_ForecastStage.Forecast, " & _
"tbl_ForecastStage.Comments, " & _
Me.cmbFType & " AS fcType, " & _
"#" & format(dtfcDate ,"mm/dd/yyyy") & "# AS fcDate, " & _
Me.fcPeriod & " AS fcQtr " & _
"FROM tbl_ForecastStage;"
...

(Air code - - not tested!)

hth
Go to the top of the page
 
HairyBob
post Mar 8 2018, 04:39 AM
Post#3



Posts: 992
Joined: 26-March 08
From: London, UK


Hi Bernie,

That's interesting... I thought that 'yyyy/mm/dd' was guaranteed to work.

Hairy.
Go to the top of the page
 
pere_de_chipstic...
post Mar 8 2018, 04:48 AM
Post#4


UtterAccess Editor
Posts: 10,342
Joined: 8-November 07
From: South coast, England


Hi Bob

You are probably right! perhaps I overstated the "must always". The necessary part is that it is unambiguous - 11/12/2017 could be 11 December or 12 November. if you put the year first then there is no ambiguity in the year, and it is effectively assumed that the month is always second. I've always used mm/dd/yyyy as this is the default formatting that Access uses e.g. if you look at a date in the SQL query window.

Cheers!
Go to the top of the page
 
HairyBob
post Mar 8 2018, 05:02 AM
Post#5



Posts: 992
Joined: 26-March 08
From: London, UK


Hi Bernie,

I think Regional Settings comes into play if you use either 'mm/dd/yyyy' English (United States) or 'dd/mm/yyyy' English (United Kingdom) (for example).

If you have say 2 records with a date/time column, one has a value of '08/03/2018' and the other a value of '03/08/2018', then you write a query that filters on a date of #08/03/2018#, if Regional Settings is set to English (United Kingdom), you retrieve the '08/03/2018' row; however, if Regional Settings is set to English (United States) you retieve the '03/08/2018' row (or vice-versa, depending on how you entered the date and what the Regional Setting was at the time).

Hairy.
This post has been edited by HairyBob: Mar 8 2018, 05:06 AM
Go to the top of the page
 
pere_de_chipstic...
post Mar 8 2018, 05:21 AM
Post#6


UtterAccess Editor
Posts: 10,342
Joined: 8-November 07
From: South coast, England


Hi Bob

If you use the Query design view window and add a date criteria e.g. 3 August 2018 (03/08/2018) in the criteria grid it shows as '#03/08/2018#' (even if you type it in as "2018/08/03"). If you then look at the query in SQL view the same date criteria appears as (e.g.) WHERE (((tblMemberMusic.Purch_HireDate)=#8/3/2018#)) ie mm/dd/yyyy.

If you are creating date criteria in VBA then it must be in mm/dd/yyyy (it could be in yyyy/mm/dd, but this would be converted to mm/dd/yyyy when the query was processed in any case (well that's my understanding! smile.gif )

Go to the top of the page
 
HairyBob
post Mar 8 2018, 05:51 AM
Post#7



Posts: 992
Joined: 26-March 08
From: London, UK


Hi Bernie,

Point taken... however; if you now run the query, you will get back the row(s) where the date value is '3 August 2018' if Regional Settings is set to English (United Kingdom) and '8 March 2018' if Regional Settings is set to English (United States). If in SQL view, you make the date criteria #2018/03/08# (i.e. yyyy/mm/dd) you will always retrieve rows where the date is '8 March 2018', regardless of how Regional Settings is set.


EDIT:

I take that back blush.gif - I just tried entering the date criteria as #2018/03/08# in SQL view, then running the query for both Regional Settings - I got back different rows depending on the setting. It appears that even if you enter the date criteria in SQL view as #yyyy/mm/dd#, when you save the query, the criteria gets changed to #mm/dd/yyyy# format.

Just when you think you understand something ooo.gif

Hairy.
This post has been edited by HairyBob: Mar 8 2018, 06:06 AM
Go to the top of the page
 
chrismbaylis
post Mar 8 2018, 06:28 AM
Post#8



Posts: 91
Joined: 25-March 10
From: St Albans, UK


Thanks for the colourful debate, gents! Glad I'm not the only one who gets stymied by dates in Access/VBA.

Appreciate your help.

Chris
Go to the top of the page
 
HairyBob
post Mar 8 2018, 08:20 AM
Post#9



Posts: 992
Joined: 26-March 08
From: London, UK


Even the edit in my last post was wrong! blush.gif

I had 2 rows in my table whilst I was testing - one '3 August 2018' and the other '8 March 2018'; I was getting the same row back each time, just formatted differently depending on the Regional Setting. It's only when I deleted one of the rows did I realise this.

Anyway Chris, hope you've got it sorted out now!

Hairy.
Go to the top of the page
 
LPurvis
post Mar 8 2018, 10:10 AM
Post#10


UtterAccess Editor
Posts: 16,296
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Has nobody commended you on the question title yet?
I shall. Nicely done. :-)

While I'm here... :-p
I'll explain what was happening.
>> "format(#" & Me.fcDate & "#,'dd mmm yyyy')" & " AS fcDate, " & _

What you've done is build a string that will execute a function once evaluated in your SQL statement.
i.e. it'll result in a string like:
CODE
SELECT
...
format(#08/03/2018#,'dd mmm yyyy') AS fcDate,
...

Note that I mentioned there that Format returns a String. (i.e. text.) That string value is then coerced into the date field.

But you're working in SQL now and, as mentioned, it will make assumptions about date formats. (While there is ambiguity in the date, which the start of the month always has.)
But it was operating on a date which had been passed in using UK format (as above).
So rather than letting the format function be execute as part of the query, you're now working with a date literal in the SQL. i.e. it's formatted before it's parsed into the SQL String. That's key.

But yes, I do agree that you can't beat ISO format. (yyyy-mm-dd) That level of non-ambiguity can't be argued with (even from those who don't represent dates in a logical fashion :-p)

Cheers
Go to the top of the page
 
HairyBob
post Mar 8 2018, 11:51 AM
Post#11



Posts: 992
Joined: 26-March 08
From: London, UK


If I was putting together the SQL string, I'd do it like this (untested):

CODE
...
        strSQL = "INSERT INTO tbl_Forecast " & _
                 "( [Project Id], Forecast, Comments, [Forecast Type], [Forecast Date], [Forecast Target Quarter] ) " & _
                 "SELECT tbl_ForecastStage.[Project Id], " & _
                        "tbl_ForecastStage.Forecast, " & _
                        "tbl_ForecastStage.Comments, " & _
                        Me.cmbFType & " AS fcType, #" & _
                        Format(Me.fcDate, "yyyy/mm/dd") & "# AS fcDate, " & _
                        Me.fcPeriod & " AS fcQtr " & _
                 "FROM tbl_ForecastStage;"
...


BTW: I second Leigh on the question title!

Hairy.
This post has been edited by HairyBob: Mar 8 2018, 11:51 AM
Go to the top of the page
 
LPurvis
post Mar 9 2018, 04:11 AM
Post#12


UtterAccess Editor
Posts: 16,296
Joined: 27-June 06
From: England (North East / South Yorks)


It's always worth keeping a function handy.
e.g.
CODE
Function fSQLDate(varDate, Optional blnDelim As Boolean = True, Optional strDelim As String = "#")
On Error Resume Next

    If Not IsDate(varDate) Then
        fSQLDate = Null
    ElseIf Not blnDelim Then
        fSQLDate = Format(varDate, "yyyy\-mm\-dd")
    Else
        fSQLDate = Format(varDate, "\" & strDelim & "yyyy\-mm\-dd\" & strDelim)
    End If
    
End Function

To be present in your projects by default, and so called upon at any time.
CODE
strSQL = "INSERT INTO tbl_Forecast " & _
                 "( [Project Id], Forecast, Comments, [Forecast Type], [Forecast Date], [Forecast Target Quarter] ) " & _
                 "SELECT tbl_ForecastStage.[Project Id], " & _
                        "tbl_ForecastStage.Forecast, " & _
                        "tbl_ForecastStage.Comments, " & _
                        Me.cmbFType & " AS fcType, " & _
                        fSQLDate(Me.fcDate) & " AS fcDate, " & _
                        Me.fcPeriod & " AS fcQtr " & _
                 "FROM tbl_ForecastStage;"

Makes things simpler and more controlled.
Not that I'm advocating anything other than understanding the reason for what the function is doing. Understanding building string expressions that represent what you intend is vital. But once you do, a standard function is a good practice.

Cheers
Go to the top of the page
 
BruceM
post Mar 9 2018, 08:33 AM
Post#13


UtterAccess VIP
Posts: 7,757
Joined: 24-May 10
From: Downeast Maine


For some time I have been doing something such as Leigh describes with the conversion function, with consistent and accurate results. I too use yyyy/mm/dd since it is unambiguous, and can be understood by just about any software that works with dates. It never occurred to me to include the delimiter as an argument, but it makes sense if the function is to be used to assemble the SQL for a SQL Server passthrough query (for instance), where the single quote is the date delimiter rather than the octothorpe (#) used by Access. I'm not sure I understand the purpose of the blnDelim argument, though.
Go to the top of the page
 
LPurvis
post Mar 9 2018, 09:10 AM
Post#14


UtterAccess Editor
Posts: 16,296
Joined: 27-June 06
From: England (North East / South Yorks)


The blnDelim is for occasions where I just don't want it to delimit the returned date as if it was for a SQL string.
So I could switch it off if passing the return value as a parameter for example or onscreen display (e.g. describing criteria in text), as opposed to the more standard use of concatenating it into a SQL string and saving the effort of considering the date delimiter.
It does no harm to have that option, even if I hardly ever use it. The name of the function does describe the vast majority of the occasions that it's used though.
(When I copied and pasted that function, it was from an application I had open which was using SQL Server, so I changed the default delimiter from ' to # for this question ;-)

Cheers
Go to the top of the page
 
BruceM
post Mar 9 2018, 10:30 AM
Post#15


UtterAccess VIP
Posts: 7,757
Joined: 24-May 10
From: Downeast Maine


Oh, I see. I don't think I have used my version (Allen Browne's version, actually) other than in a criteria string, but as you say, no harm to it, and it could be useful.
Go to the top of the page
 
cheekybuddha
post Mar 9 2018, 12:23 PM
Post#16


UtterAccess VIP
Posts: 10,626
Joined: 6-December 03
From: Telegraph Hill


>> other than in a criteria string <<

Just as good for inserting values in SQL wink.gif

I also have a blIncTime parameter in my version which switches the format string to include hh:nn:ss.

The SQLString() equivalent is another handy one to always have around.

thumbup.gif

d
Go to the top of the page
 
BruceM
post Mar 9 2018, 12:39 PM
Post#17


UtterAccess VIP
Posts: 7,757
Joined: 24-May 10
From: Downeast Maine


QUOTE
Just as good for inserting values in SQL
I don't recall using it for that purpose, because most of the time with a SQL Insert I'm working with something like DateAdd or DateSerial that returns a date serial number, or else a value assigned to a date variable. But I can't recall for sure.

QUOTE
I also have a blIncTime parameter in my version which switches the format string to include hh:nn:ss.

Allen Browne's version includes that. I don't use it often, but it's useful sometimes.

BTW, to the OP, great title!
Go to the top of the page
 
LPurvis
post Mar 9 2018, 12:55 PM
Post#18


UtterAccess Editor
Posts: 16,296
Joined: 27-June 06
From: England (North East / South Yorks)


I wrote it years ago as and it came with a partner, fSQLDateTime. :-)
Never got around to amalgamating them, but no reason why not really. (Except for backwards compatibility with my old applications :-s)

Cheers
Go to the top of the page
 
chrismbaylis
post Mar 16 2018, 11:44 AM
Post#19



Posts: 91
Joined: 25-March 10
From: St Albans, UK


I'm fairly certain that, had the title been "SQL Date Formats", I'd have had not even half the responses. An engaging title is all the trap that's needed.

Thanks again for the great ideas and commentary - shows just how awesome the users of this site are.

Digital beers all round!

guiness.gif cheers.gif guiness.gif cheers.gif guiness.gif cheers.gif guiness.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2018 - 05:05 AM