UK Fixed Date in VBA (1 Viewer)

tucker61

Registered User.
Local time
Yesterday, 17:40
Joined
Jan 13, 2008
Messages
321
Throughout my database when running reports i regular ask for date parameters, and convert these using the code provided by Allen Browne

Code:
Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd\#")
        Else
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd hh\:nn\:ss\#")
        End If
    End If
End Function

I have one peice of code that i want to input a default date if the operator forgets to input the date.

Code:
  Startdate = SQLDate(InputBox("Please Enter the Start Date"))
                If Len(Startdate) < 1 Then
                Startdate = SQLDate(#1/2/2019#)
                End If

But unfortunately this does not work correctly for me.
I have tried "01/02/2019" and #01/02/2019# but in both instancies the code fails to bring back the correct information.

Any help appreciated.
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:40
Joined
Sep 21, 2011
Messages
14,048
Works for me?

Code:
? newsqldate(#01/02/2019#)
#2019/01/02#
? newsqldate("01/02/2019")
#2019/02/01 00:00:00#
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:40
Joined
Oct 29, 2018
Messages
21,358
Hi. What is the correct and incorrect results look like? Is the default date in January or February?
 

tucker61

Registered User.
Local time
Yesterday, 17:40
Joined
Jan 13, 2008
Messages
321
Definately does not like it on mine, is it becuase the dates are defined as strings ?

Here is all the code in the function.

Code:
Dim Startdate As String
Dim Enddate As String

 Startdate = SQLDate(InputBox("Please Enter the Start Date"))
                If Len(Startdate) < 1 Then
                Startdate = SQLDate(#1/2/2019#)
                'Exit Function
                End If
                
 Enddate = SQLDate(InputBox("Please Enter the End Date"))
                 If Len(Enddate) < 1 Then
                 Enddate = date
                'Exit Function
            End If
            
WarningsOff

ExportQuery "EMG NWL Data", "SELECT DISTINCT tblDeliveryData.Delivery_Date, tblDeliveryData.Delivery_Reference, tblDeliveryData.Supplier_Code, tblsuppliers.Sup_Desc, Qry_EMG1.Line_No, Left([tbldeliverydata]![Delivery_Reference],2) AS DC_NO, IIf([DC_NO]=""07"",""Little"",IIf([DC_NO]=""12"",""Big"",""Error"")) AS Site, IIf([tbldeliverydata].[delivery_date]>[tblemgSuppliers].[date_Added],1,0) AS New_Labels_Ordered, Qry_EMG1.Pass_Fail, Qry_EMG1.Reason_Desc, Qry_EMG1.Comments, Qry_EMG1.Contract_No, TblEmgSuppliers.Date_Added " & vbCrLf & _
"FROM ((tblDeliveryData LEFT JOIN tblsuppliers ON tblDeliveryData.Supplier_Code = tblsuppliers.Sup_Code) LEFT JOIN Qry_EMG1 ON (tblDeliveryData.Delivery_Reference = Qry_EMG1.Delivery_Reference) AND (tblDeliveryData.Supplier_Code = Qry_EMG1.Sup_Code)) LEFT JOIN TblEmgSuppliers ON tblDeliveryData.Supplier_Code = TblEmgSuppliers.Sup_Code " & vbCrLf & _
"WHERE (((tblDeliveryData.Delivery_Date) >= " & Startdate & " AND (tblDeliveryData.Delivery_Date) <= " & Enddate & " ) AND ((tblDeliveryData.Delivery_Reference) Is Not Null) AND ((Left([tbldeliverydata]![Delivery_Reference],2))=""12"" Or (Left([tbldeliverydata]![Delivery_Reference],2))=""07"") AND ((tblDeliveryData.Record_Type)=""1""));"

WarningsOff
    
End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:40
Joined
Sep 21, 2011
Messages
14,048
I am taking it as you have said UK date that we are talking about dd/mm/yyyy

As you can see from my post, if you supply a string it outputs as a UK date
If you supply a date with # and it cannot be determined US or UK, it outputs as US.
If you supply #13/02/2019# it will supply UK date.

Code:
? newsqldate("13/02/2019")
#2019/02/13 00:00:00#
? newsqldate(#13/02/2019#)
#2019/02/13#
 

Minty

AWF VIP
Local time
Today, 00:40
Joined
Jul 26, 2013
Messages
10,355
You are wrapping your input box function with the SqlDate function in the first place, so I'm not sure how this would work.

Try it the other way around, or better still put the input on a form and you can easily supply any default value.

Code:
strInputBox = InputBox("Please Enter the Start Date")

StartDate = SqlDate("2/1/2019")

If IsDate(strInputBox) Then Startdate = SqlDate(strInputBox)
 

tucker61

Registered User.
Local time
Yesterday, 17:40
Joined
Jan 13, 2008
Messages
321
Tried it, still not working. i might go down the route of inputting the date via the form, or just exiting the function if the date is empty.

The date system is the UK, the query should bring back records that have been input since the 1st of February (this works if i type the date in the input box), but the query returns a blank if i do not type the date.

I Will think about it again.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 20:40
Joined
Jan 23, 2006
Messages
15,364
Try enclosing with CDate(SqlDate(strInputBox)) to convert string to Date

Why Dim these as strings?
Code:
Dim Startdate As String
Dim Enddate As String
 

Minty

AWF VIP
Local time
Today, 00:40
Joined
Jul 26, 2013
Messages
10,355
There is something odd going on then as the immediate window produces
Code:
?isdate("2019-02-01")
True
?Isdate("1/2/2019")
True
?isdate("")
False

Did you try it without wrapping the original inputbox in the SqlDate() function.
 

tucker61

Registered User.
Local time
Yesterday, 17:40
Joined
Jan 13, 2008
Messages
321
that gives me a type mismatch error .

Code:
 Startdate = CDate(SQLDate(InputBox("Please Enter the Start Date")))
 

Minty

AWF VIP
Local time
Today, 00:40
Joined
Jul 26, 2013
Messages
10,355
Startdate should be a string so it can be concatenated correctly in the SQL statement.

It should evaluate to #2019/02/01#

Debug.Print StartDate ; End Date and see what you get.

Code:
?sqldate(date())
#2019/02/11#
?cdate(#2019/02/11#)
11/02/2019
 

tucker61

Registered User.
Local time
Yesterday, 17:40
Joined
Jan 13, 2008
Messages
321
with this code

Code:
Dim Startdate As String
Dim Enddate As String



 Startdate = SQLDate(InputBox("Please Enter the Start Date"))

                If Len(Startdate) < 1 Then
            
                Startdate = SQLDate(#1/2/2019#)
                'Exit Function
                End If
                
 Enddate = SQLDate(InputBox("Please Enter the End Date"))
                 If Len(Enddate) < 1 Then
                 Enddate = date
                'Exit Function
            End If
            WarningsOff
Debug.Print Startdate; Enddate

i get the output of

#01/02/2019#11/02/2019
 

tucker61

Registered User.
Local time
Yesterday, 17:40
Joined
Jan 13, 2008
Messages
321
and this code
Code:
strInputBox = InputBox("Please Enter the Start Date")
Startdate = SQLDate("2/1/2019")
If IsDate(strInputBox) Then Startdate = SQLDate(strInputBox)

                
 Enddate = SQLDate(InputBox("Please Enter the End Date"))
                 If Len(Enddate) < 1 Then
                 Enddate = date
                'Exit Function
            End If
            WarningsOff
Debug.Print Startdate; Enddate

#01/02/2019 00:00:00#11/02/2019
 

Minty

AWF VIP
Local time
Today, 00:40
Joined
Jul 26, 2013
Messages
10,355
That isn't what you need.
You haven't wrapped your default end date in the SqlDate() function.

EndDate= SqlDate(Date())

I still think it's more readable / much easier to follow using a form to gather the values. Input boxes are clunky, and difficult to evaluate. You can set default values to the current month automatically, and not allow null entries and Bob is your uncle.
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:40
Joined
Sep 21, 2011
Messages
14,048
I get for today entering 11/02/2019
Code:
? newsqldate(inputbox("Enter date")) 
#2018/02/11 00:00:00#
 

tucker61

Registered User.
Local time
Yesterday, 17:40
Joined
Jan 13, 2008
Messages
321
That isn't what you need.
You haven't wrapped your default end date in the SqlDate() function.

EndDate= SqlDate(Date())

I still think it's more readable / much easier to follow using a form to gather the values. Input boxes are clunky, and difficult to evaluate. You can set default values to the current month automatically, and not allow null entries and Bob is your uncle.

it looked like this was the problem all along. Seems to work now i have added the SqlDate(Date()) to the enddate

I will still consider the other points that have been discussed.
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:40
Joined
Sep 21, 2011
Messages
14,048
The reason I am using newsqldate is a sqldate I have is as follows

Code:
Public Const conHandleErrors          As Boolean = True

'   From Allen Browne http://members.iinet.net.au/~allenbrowne/ser-36.html
'   Please see above acknowledgements.
'   This is required to force dates used by SQL into US format, irrespective of Regional settings.
Public Function SQLDate(ByVal vntThisDateTime As Variant) As String
    
    If (conHandleErrors) Then On Error GoTo ErrorHandler
    
    If IsDate(vntThisDateTime) Then
        SQLDate = "#" & Format(vntThisDateTime, "mm\/dd\/yyyy hh:nn:ss") & "#"
    End If
    
ExitProcedure:
    Exit Function

ErrorHandler:
    SQLDate = ""
    DisplayError "Function SQLDate", conModuleName
    Resume ExitProcedure
    
End Function
 

Minty

AWF VIP
Local time
Today, 00:40
Joined
Jul 26, 2013
Messages
10,355
@Gasman - as a slight variation on the theme, as an alternative if you use the yyyy/mm/dd format, as SQL Server recognises it without further adjustment, and imho it's easier to read without the ambiguity.
Code:
Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd\#")
        Else
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd hh\:nn\:ss\#")
        End If
    End If
End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:40
Joined
Sep 21, 2011
Messages
14,048
Thanks Minty,

TBH I use the code below mostly. It was just when I pasted the o/p's code, Access complained about the same name. That code above would be something I copied at the time with the idea of using it sometime in the future. I don't think I'll ever get exposed to SQL Server, but worth taking note

Code:
Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
 

Users who are viewing this thread

Top Bottom