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
> UK and US date format in SQL statement in VBA    
 
   
zedleb
post May 1 2009, 06:50 AM
Post#1



Posts: 54
Joined: 18-July 08
From: Cumbria, UK


Hi all

I've spent the morning having a good read around the problem with the American default date when using the # symbol in VBA. I've tried a few unsuccessful resolutions to my own problem but am just so stuck. Can anyone see where I'm going wrong?

The code below takes a date from an unbound text field (Forms!frmReceivedInput!txDeliveryDate) which is set to a default value of Date() and formatted to display a medium date.

This date is then input into a table (tblReceivedNote) via an SQL statement. It then appears in the table in the American format. So I need to ensure that the date written to the table is in the UK format but can't work out where to alter the format.

Any suggestions most gratefully received.

CODE
Public Sub myIncompleteReceivedItem(OrderDetailID As Long)
im strRef As String
Dim dDate As Date
Dim SQL1 As String
Dim SQL2 As String
Dim MyLastId As Long
Dim PartialDelivery As String
Dim OriginalItem As String
On Error GoTo myIncompleteReceivedItem_Err
'capture the delivery note reference and date
        DoCmd.OpenForm "frmReceivedInput", acNormal, , , , acDialog
        If CurrentProject.AllForms("frmReceivedInput").IsLoaded Then
            strRef = Forms!frmReceivedInput!txDeliveryRef
            dDate = Forms!frmReceivedInput!txDeliveryDate
            DoCmd.Close acForm, "frmReceivedInput"
        Else 'user has hit Cancel or x
            myDisplayInfoMessage ("Action cancelled")
            Exit Sub
        End If
'create new record in tblReceivedNote
    
        SQL1 = "INSERT INTO [tblReceivedNote]([Received_Note_Reference], [Date_Received]) " & _
                "VALUES ('" & strRef & "', #" & dDate & "#); "
    
    
        With CurrentProject.Connection
            .Execute SQL1
            MyLastId = .Execute("SELECT @@IDENTITY")(0)         'captures the last created ID
        End With
        
'create new record in tblReceived with the newly created Received_Note_ID (MyLastId)
        PartialDelivery = True   'to populate Partial_Delivery
        OriginalItem = False     'to populate Original_Order_Item
        SQL2 = "INSERT INTO [tblReceived] ([Order_Detail_ID], [Order_ID], [Received_Note_ID], " & _
                                [Partial_Delivery], [Original_Order_Item])  " & _
                "SELECT [tblOrderDetails].[Order_Detail_ID], [tblOrderDetails].[Order_ID], '" & _
                                MyLastId & "', " & PartialDelivery & ", " & OriginalItem & " " & _
                "FROM [tblOrderDetails] " & _
                "WHERE ([tblOrderDetails].[Order_Detail_ID])= " & OrderDetailID & " ; "
        With CurrentProject.Connection
            .Execute SQL2
        End With
myIncompleteReceivedItem_Exit:
    Exit Sub
myIncompleteReceivedItem_Err:
    MsgBox Error$
    Resume myIncompleteReceivedItem_Exit
End Sub



[Edit: Code edited to reduce width]
Edited by: LPurvis on Fri May 1 10:52:22 EDT 2009.
Go to the top of the page
 
LPurvis
post May 1 2009, 06:59 AM
Post#2


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


Hi
If you maintain a standard function you can make this easier.
e.g.
CODE
Function fSQLDate(varDate)
    
    If Not IsNull(varDate) Then
        fSQLDate = Format(CStr(varDate), "\#yyyy\-mm\-dd\#")
    End If
    
End Function

Then you end up with
CODE
...
        SQL1 = "INSERT INTO [tblReceivedNote]([Received_Note_Reference], [Date_Received]) " & _
                "VALUES ('" & strRef & "', " & fSQLDate(dDate) & "); "

Note I've used the international (ISO) format - as it's my preference and I genuinely believe it's the better choice.
Cheers.
Go to the top of the page
 
datAdrenaline
post May 1 2009, 09:34 AM
Post#3


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


FYI ...
Please take note that the FORMAT your date/time field is displayed, has nothing to do with how it is stored. Date/Time datatypes are stored as a numeric value that is equivalent to the Access datatype of Number/Double. The number is in a unit of Days, and valued at the number of days offset from the "base date" of 12/30/1899 12:00 AM. So ... when Access stores 0.0 in a date/time field the date of 1899-12-30 12:00 AM is stored. If that field has a Format property set at the table level or is bound to a control that has a Format property, Access will display the numeric value (aka: DateSerial number) in the format specified by the property. If the field does not encounter any formatting properties on its path to being displayed, Access will use the regional settings of the computer to determin the format to show that date.
Also, I noticed this ...
>> The code below takes a date from an unbound text field (Forms!frmReceivedInput!txDeliveryDate) which is set to a default value of Date() and formatted to display a medium date. <<
Which is fine, but do take note that it is very easy to get "fooled" by unbound controls. What I mean by that is this, and unbound control returns a variant, which is then interpreted as a STRING, unless given direction to do otherwise (ie: a format property that gives a hint to Access as to what data type the variant should be coerced to). For example, when you set the dDate variable, you may note get what you thought you would since Access automatically coerces the Variant/String into a Date variable. However, since you have the unbound control set to a medium date format that will probably not be an issue, but its good to be aware of issues that may arise when using unbound controls ...
HAs much as I don't like input masks, I will often use them for unbound controls that represent a date value.
Go to the top of the page
 
LPurvis
post May 1 2009, 09:58 AM
Post#4


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


Edited the original code to make the thread more readable (at least on a wide screen ;-).
rent raises a good point which I, admittedly, didn't bother doing (even though I remember reading briefly that you'd mentioned format of stored data, I was low on time ;-) as formatting the date into text (which is what the function does) removed the consideration.
But it's certainly worth knowing so you don't fall into the trap of struggling with irrelevant formatting of stored data.
Cheers.
Go to the top of the page
 
zedleb
post May 4 2009, 11:00 AM
Post#5



Posts: 54
Joined: 18-July 08
From: Cumbria, UK


Hi Leigh and Brent
Thank you so much for your help. Leigh as always your solution worked like a dream! sad.gif
Hope you're not reading this today though as it's Bank Holiday but then maybe the weather over your side of the country is as miserable as ours in Cumbria!
All the best
Z<><e
Go to the top of the page
 
LPurvis
post May 4 2009, 11:40 AM
Post#6


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


No probs. Hopefully the background info was informative too.
las I am reading it today, the weather is as miserable over here and I'm much too busy to leave the PC and have a "bank holiday" type day. :-s
I'll give myself a day off instead... in a few days... maybe... wary.gif
Glad you're sorted!
Cheers.
Go to the top of the page
 
ChrisO
post May 5 2009, 06:33 AM
Post#7


Remembered
Posts: 3,905
Joined: 24-February 03
From: Brisbane, Australia


>>So ... when Access stores 0.0 in a date/time field the date of 1899-12-30 12:00 AM is stored.<<
o it is not, it is stored as 0.0. It only assumes a date format when required not when stored.
Regional settings are used to format it ‘on the way out’ not going in.
Regards,
Chris.
Go to the top of the page
 
datAdrenaline
post May 7 2009, 09:25 AM
Post#8


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


Hello Chris ...
don't believe I ever stated that a format was stored ... I merely coorelated how a raw value of 0.0 being stored in a date/time typed field is the date of 1899-12-30 12:00 AM to Access ... maybe it would be better, or more clear, to say storing a 0.0 represents the date of 1899-12-30 12:00 AM.
Otruly respect the knowledge you have of dates and their effects on an Access application, as evidenced by my seeking advice from you, but I have to ask, do you honestly think, based on our discussions, that I don't understand a date/time typed field and how formats are a visual representation of the double that represents the date? ... Or ... is it just that you thought the verbage I used was unclear?
Go to the top of the page
 
ChrisO
post May 7 2009, 05:04 PM
Post#9


Remembered
Posts: 3,905
Joined: 24-February 03
From: Brisbane, Australia


G’day Brent.
just think you were being unclear.
HAs you know, this thing of dates does cause a lot of confusion so when I see someone, particularly of your caliber, state >>So ... when Access stores 0.0 in a date/time field the date of 1899-12-30 12:00 AM is stored.<< it can only add to that confusion.
It would seem logical to someone reading this that if Access stored 1899-12-30 12:00 AM it would have to store that formatting. But we know that is not correct.
Regards,
Chris.
Go to the top of the page
 
datAdrenaline
post May 8 2009, 08:41 AM
Post#10


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


Hello Chris,
Thank you for the feedback. It is my goal to be clear for the folks we are trying to help here at UA and elsewhere, and I truly appreciate the followup and feedback.
Go to the top of the page
 
wis123
post May 9 2009, 09:55 PM
Post#11



Posts: 21
Joined: 30-April 09



"since you have the unbound control set to a medium date format "
On undocumentated? feature of the Format ppties in both tbls and frms allows you to set the date format as dd/mmm/yyyy.
Go to the top of the page
 
Rainlover
post May 9 2009, 10:49 PM
Post#12


Utterly Banned
Posts: 6,006
Joined: 5-October 05
From: Brisbane Qld Australia


wis123
You can set the Format any way you like but MS Access stores a Number (Double) not a date.
Thr Format Property dictates what it looks like when it is viewed.
Go to the top of the page
 
datAdrenaline
post May 10 2009, 08:43 PM
Post#13


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


Hello Wis123 ...
I am unsure of your question/statement ... Rain correctly and succinctly describes how date/time fields are stored ... The comment of mine you quoted was in the context of how an unbound control's .Value property is interpreted by the seeker/caller of that information.
Go to the top of the page
 
nmurray
post Aug 19 2014, 01:56 PM
Post#14



Posts: 439
Joined: 28-February 11
From: UK


I know this is a very old thread, but seeing as it has been bumped to Links at some point I thought I'd add my 2 cents. I've seen lots of different solutions around the web, but IMHO the best one is already provided by Microsoft and is a VBA function.
To pass a date to SQL from VBA, use BuildCriteria()
Example:
CODE
Function test()
    Dim SQL As String
    SQL = "SELECT tblEmployees.* FROM tblEmployees WHERE "
    SQL = SQL & BuildCriteria("StartDate", dbDate, ">01/03/2013")
    Debug.Print SQL
End Function

Immediate Window
CODE
SELECT tblEmployees.* FROM tblEmployees WHERE StartDate>#3/1/2013#

This returns a date converted from the local (UK) format and returns the correct (US) format, adding the operators and hashes. I find that BuildCriteria is especially useful when using DAO recordset .FindFirst, as it makes the code look very clean.
http://msdn.microsoft.com/en-us/library/of...office.15).aspx
Go to the top of the page
 
theDBguy
post Aug 19 2014, 02:06 PM
Post#15


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


Hi Neil,
Thanks for the update but seeing that, as you said, this is an old thread, and the link you provided specifies Access 2013, could you please confirm as to which versions BuildCriteria() will definitely work? Thanks again.
Go to the top of the page
 
nmurray
post Aug 21 2014, 09:17 AM
Post#16



Posts: 439
Joined: 28-February 11
From: UK


Hi DBGuy,
Its been there all along.
Attached File  buildcriteria.PNG ( 35.96K )Number of downloads: 5
Go to the top of the page
 
dmhzx
post Aug 21 2014, 10:20 AM
Post#17



Posts: 7,033
Joined: 22-December 10
From: England


That is amazingly useful. It's a new one on me.
I've been converting to Long Date up to now. - Which is fine till someone has a different long date format.
And from the MS help screen, I'd never have interpreted their text Until I'd read this a third time.

I can see me using this quite a bit for date comparison from now on.
I even works with variables strings
This
CODE
buildcriteria("Test", dbdate, "Between " & startd & " and 06/08/2014")

where I set startd with
CODE
StartD = "06/07/2014"

and it gave
CODE
Test Between #7/6/2014# And #8/6/2014#

(Between 6th Jul and 6th August)
So thank you nmurray hat_tip.gif thumbup.gif thanks.gif
Go to the top of the page
 
theDBguy
post Aug 21 2014, 10:25 AM
Post#18


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


Hi Neil,
Thanks for the confirmation! I am also going to add this technique in my bag of tricks.
Cheers!
Go to the top of the page
 
nmurray
post Aug 25 2014, 02:33 AM
Post#19



Posts: 439
Joined: 28-February 11
From: UK


You're welcome! thumbup.gif
Here's the function in action with DAO.FindFirst I think you'll agree it's quite neat.
CODE
Private Function LocateRecord(dteSearch As Date)
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst BuildCriteria("dteBrief", dbDate, dteSearch)
    If Not rs.NoMatch Then
        Me.Bookmark = rs.Bookmark
    End If
End Function

It also handles the problem people sometimes have with where to put quote marks, double quotes etc. with the parameter dbText
CODE
            rst.MoveFirst
            rst.FindFirst BuildCriteria("nStore", dbInteger, sStore) & " AND " _
            & BuildCriteria("nCatNumber", dbLong, nCatNumber) & " AND " _
            & BuildCriteria("sSize", dbText, sSize)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 09:27 PM