American date

Now in the commonwealth we use the British date standard ,but now today after querying the TB I have discovered it is out of balance the report is not picking some transactions. After perusing through I have realized that we are posting our transactions in the UK date format DD/MM/YY instead of MM/DD/YY , how do I resolve this I do not want to use American format it is not known here in the commonwealth?????????


Regards

Chris
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
how do I resolve this I do not want to use American format it is not known here in the commonwealth?????????
try format your dates in YYYY-MM-DD or MM-DD-YYYY in your queries
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
try to be more specific here how do I do it? example I use a form to supply the some parameters
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in your query, you can try like:

select * from yourTable where yourDate = #2017-12-11#

Open in new window

or:
select * from yourTable where yourDate = #12-11-2017#

Open in new window


which means 11 Dec 2017
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Pawan KumarDatabase ExpertCommented:
Try this format..

YYYY/MM/DD
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
I have seen this code after searching on the net would you know how I can use it , all my reports are driven by form parameters , the users have to enter dates on forms to supply the underlying Query:

Function MakeUSDate(DateIn As Variant) As String

 ' Do nothing if the value is not a date.
 If Not IsDate(DateIn) Then Exit Function

 ' Convert the date to a U.S. Date format.
 MakeUSDate = "#" &; Month(DateIn) &; "/" &; Day(DateIn) &; "/" &; Year(DateIn) &; "#"
End Function

Where exactly can put on the parameter forms????????????
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
the users have to enter dates on forms to supply the underlying Query:
so the user entered the date in DD/MM/YYYY format?

can you upload a sample file here?
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
But you have already mentioned that so the user entered the date in DD/MM/YYYY format?

Right now I'm not with the production module.

If you have one database how have sorted out the dates variances or you simply using the American way?

Regards

Chris
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok... i try to make sure of your function: MakeUSDate in the sample attached below.

I'm using a Form with sub form for illustration.
29072043.accdb
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Like I said before I do not have the Ms access production module in front of me ,so I cannot download the sample . What if I try this on the query itself:

Format([Dates],"mm/dd/yy")

Format(forms!frmtbdaterange.txtstartdate,"mm/dd/yy")

Can this work ???????

Regards

Chris
0
Gustav BrockCIOCommented:
Date values carries no format, only a value.

A format is only used for display or when concatenating date values as string expressions , say as a parameter in an SQL command:

"Insert Into ... Values (#" & Format(YourDateValue, "yyyy\/mm\/dd") & "#);"

Open in new window

For more extensive and/or generic use, you can use my CSql function:

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String
    
    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"
    
    Dim Sql             As String
    Dim LongLong        As Integer
    
    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "
    
End Function

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Format(forms!frmtbdaterange.txtstartdate,"mm/dd/yy")

It may work, but notice that the slash is not a slash but a placeholder for the date separator - which can a slash or something else, even a space.
To force a slash, escape it as shown above.
Also, the mm-dd-yyyy sequence will fail in ADO, so you can just as well make it a habit to use the ISO sequence which will never  fail.

That said, always handle dates as Date, not text, not numbers, no exceptions.

/gustav
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Hi Gustav

I think you have a very good point here ,now all what I'm asking is assuming I go to my query design and in the criteria I enter this :

Format(forms!frmtbdaterange.txtstartdate,"yyyy/mm/dd")

kindly let us zero in with this code

cant it work???????????
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Okay so the way to go is to do it the ISO way:

Format(forms!frmtbdaterange.txtstartdate,"yyyy/mm/dd")


Regards

Chris
0
Gustav BrockCIOCommented:
In a query, that is not needed. But do specify the parameter  data type:

PARAMETERS Forms!frmtbdaterange.txtstartdate DateTime;
SELECT * FROM SomeTable
WHERE SomeDateField = Forms!frmtbdaterange!txtstartdate;

Open in new window


To build an SQL string in code you it:

SQL = "SELECT * FROM SomeTable WHERE SomeDateField = #" & Format(Forms!frmtbdaterange!txtstartdate.Value, "yyyy\/mm\/dd") & "#;"

Open in new window

/gustav
0
Fabrice LambertFabrice LambertCommented:
you can also use a parameterized query, and give it a valid date type variable. This way you won't have to struggle with date formated strings.
Consider the following code:
Public Sub test()
On Error GoTo Error
    Dim db As DAO.Database
    Set db = CurrentDb
    
        '// query with parameters
    Dim strSQL As String
    strSQL = "PARAMETERS criteria DateTime; " & _
             "SELECT Adm_Connexions.DateConnect " & _
             "FROM Adm_Connexions " & _
             "WHERE Adm_Connexions.DateConnect < [criteria];"
    
    Dim qd As DAO.QueryDef
    Set qd = db.CreateQueryDef("tempQD", strSQL)
        '// provide value to the parameter (date format is irrelevant)
    qd.Parameters("criteria") = Now
    
    Dim rs As DAO.Recordset
    Set rs = qd.OpenRecordset(dbOpenSnapshot)
    
        '// random code using the recordset
    
    
        '// cleanup
    rs.Close
    Set rs = Nothing
    qd.Close
    db.QueryDefs.Delete qd.Name
    Set qd = Nothing
    Set db = Nothing
Exit Sub
Error:
        '// error handler
    If Not (rs Is Nothing) Then
        rs.Close
        Set rs = Nothing
    End If
    If Not (qd Is Nothing) Then
        db.QueryDefs.Delete qd.Name
        qd.Close
        Set qd = Nothing
    End If
    If Not (db Is Nothing) Then
        Set db = Nothing
    End If
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Sub

Open in new window

Additional notes:
Make sur you delete the temporary querydef object, else the createQueryDef method will fail.
It also work with "Action queries", just call the queryDef's execute method (after providing parameters of course).
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
just call the queryDef's execute method (after providing parameters of course).

But where do I put the code?

How do I call at query level?

Regards

Chris
0
Fabrice LambertFabrice LambertCommented:
First, either create a queryDef as shown in my previous post, or reference an existing one.
Then, call the method.
Set qd = db.queryDefs("myQuery")
qd.Execute dbFailOnError

Open in new window

Note that the execute methode take an optional parameter, I recommend dbFailOnError value, this way an error will be raised (and you can catch it) in case of troubles.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.