UtterAccess Discussion Forums > Form with button to make Query, Date not Formated Correctly

Full Version: Form with button to make Query, Date not Formated Correctly

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

neo_m

Jan 24 2006, 10:02 PM

Hi

I have been working on a cmd button on my form that once clicked will create a query using the data specified in some text fields on my form.
However once the date is transferd to my query it fails becuase instead of looking between
Between #1/08/2006 12:30:00 AM# And #25/01/2006 11:59:00 PM#
it acutall looks between
Between #8/1/2006 12:30:00 AM# And #25/01/2006 11:59:00 PM#

I am mainly having issues with this line

CODE

strSQL2 = "WHERE (((RTETABLE.RTEDateTime) Between #" & Format([txtStartDate], "dd/mm/yyyy hh:nn AM/PM") & "# And #" & Format([txtEndDate], "dd/mm/yyyy hh:nn AM/PM") & "#))"

I am trying to format the date in "dd/mm/yyyy hh:nn AM/PM"

when i vew my query in SQL view this is what is displayed, which is correct

CODE

WHERE (((RTETABLE.RTEDateTime) Between #8/1/2006 0:30:0# And #1/25/2006 23:59:0#))

but in normal design view this is what is displayed

CODE

Between #1/08/2006 12:30:00 AM# And #25/01/2006 11:59:00 PM#

Why is it switching the date about?

Full cmd Code:

CODE

Private Sub Command63_Click()
Dim varItem As Variant
    Dim strTemp As String
    Dim strSQL As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim strSQL4 As String
    Dim strSQL5 As String
    Dim db As DAO.Database
    Dim qry As QueryDef

    strSQL = "SELECT RTETABLE.SecondaryNumber, RTETABLE.SecondaryName, Sum(RTETABLE.CountItems) AS SumOfCountItems, Sum(RTETABLE.TransactionPrice) AS SumOfTransactionPrice FROM RTETABLE "
    strSQL2 = "WHERE (((RTETABLE.RTEDateTime) Between #" & Format([txtStartDate], "dd/mm/yyyy hh:nn AM/PM") & "# And #" & Format([txtEndDate], "dd/mm/yyyy hh:nn AM/PM") & "#))"
    strSQL3 = "GROUP BY RTETABLE.SecondaryNumber, RTETABLE.SecondaryName "
    strSQL4 = "HAVING (((RTETABLE.SecondaryNumber)<>'') AND ((Sum(RTETABLE.CountItems))>0)) "
    strSQL5 = "ORDER BY RTETABLE.SecondaryNumber"

    strSQL = strSQL & strSQL2 & strSQL3 & strSQL4 & strSQL5

'    On Error Resume Next
    Set db = CurrentDb
    With db
        .QueryDefs.Delete "qry1033Exp"
        Set qry = .CreateQueryDef("qry1033Exp", strSQL)
    End With

End Sub

Full SQL Code

CODE

SELECT RTETABLE.SecondaryNumber, RTETABLE.SecondaryName, Sum(RTETABLE.CountItems) AS SumOfCountItems, Sum(RTETABLE.TransactionPrice) AS SumOfTransactionPrice
FROM RTETABLE
WHERE (((RTETABLE.RTEDateTime) Between #8/1/2006 0:30:0# And #1/25/2006 23:59:0#))
GROUP BY RTETABLE.SecondaryNumber, RTETABLE.SecondaryName
HAVING (((RTETABLE.SecondaryNumber)<>'') AND ((Sum(RTETABLE.CountItems))>0))
ORDER BY RTETABLE.SecondaryNumber;

HiTechCoach

Jan 24 2006, 11:09 PM

If the Field "RTEDateTime" a data type of Date/Time?

If yes, then you do not want to Format the datas first.

Try:

CODE

strSQL2 = "WHERE (((RTETABLE.RTEDateTime) Between #" & [txtStartDate] & "# And #" & [txtEndDate] & "#))"

Note: All the dates must be in the format "mm/dd/yyyy" to be used with the between. Access will try to convert it to this format.

HiTechCoach

Jan 24 2006, 11:16 PM

You may be able to use:

CODE

strSQL2 = "WHERE (((RTETABLE.RTEDateTime) Between #" & Format([txtStartDate], "mm/dd/yyyy hh:nn AM/PM") & "# And #" & Format([txtEndDate], "mm/dd/yyyy hh:nn AM/PM") & "#))"

Larry Larsen

Jan 25 2006, 03:37 AM

Hi
A useful reference when using International Dates in Access.

neo_m

Jan 25 2006, 11:17 PM

QUOTE

Note: All the dates must be in the format "mm/dd/yyyy" to be used with the between. Access will try to convert it to this format.

Ohhh, I didnt know that. That will explain a lot.

what if the RTEDateTime is formated dd/mm/yyyy hh:nn:ss will i need to convert that date field to a mm/dd/yyy format?

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.