SELECT Between two Dates Troubles....

[ndeans]

Been having awful trouble trying to work out why the following line of code isn't working. Any assistance greatly appreciated:

Set rs = db.OpenRecordset("SELECT * FROM tblCharges WHERE IsNull(InvCode) And CaseID = " & CaseID & " And ChargeDate BETWEEN #" & StartDate & "# AND #" & EndDate & "#")

Both the CaseID and InvCode WHERE criteria are working, both tested individually and in combination, however as soon as i add the ChargeDate Between criteria it all goes tits-up...

I have looked into every variation i can think of for setting the Format(Date) for the StartDate and EndDate variables with no success.

It's the last little piece of code i need to get working so i can make what i'm working on functional before moving onto the next step which is dependent upon this step.

Once again any assistance is greatly appreciated...
:banghead:

 

[MarkK]

however as soon as i add the ChargeDate Between criteria it all goes tits-up...

As a description of a problem, this offers no useful information from which to troubleshoot. I recommend that you describe actual things that you observe, like maybe there is an error, or maybe you get an unexpected result.
Hope this helps,

 

[ndeans]

Simply trying to set a recordset to run some update functions in the next step.

I can confirm that each of the following variations of the same code work and successfully create recordsets with the number of records i am expecting.

Set rs = db.OpenRecordset("SELECT * FROM tblCharges WHERE IsNull(InvCode))

Set rs = db.OpenRecordset("SELECT * FROM tblCharges WHERE IsNull(InvCode) And CaseID = " & CaseID)

However when i add the between dates variables to the WHERE statement as follows, i get a recordset of 0 records when i know this not to be correct:

Set rs = db.OpenRecordset("SELECT * FROM tblCharges WHERE IsNull(InvCode) And CaseID = " & CaseID & " And ChargeDate BETWEEN #" & StartDate & "# AND #" & EndDate & "#")

From this i can only deduce that there is something wrong with the Between Dates section of the code.

I have 2 dates (StartDate and EndDate) on a form and have declared the variables at the start of the code as follows:

Dim StartDate As Date
    Dim EndDate As Date
    
    CaseID = Me.CaseID.Value
    StartDate = CLng(Me.txtStartDate.Value)
    EndDate = CLng(Me.txtEndDate.Value)

I have also tried the following variations on the Format(Date) based on what i have seen in the forums with no success.

StartDate = Me.txtStartDate.Value
StartDate = CLng(Me.txtStartDate.Value)
StartDate = Format(Me.txtStartDate.Value, "dd/mm/yyyy")

Hope this gives a better insight

 

[JHB]

Try the below:

Set rs = db.OpenRecordset("SELECT * FROM tblCharges WHERE IsNull(InvCode) And CaseID = " & CaseID & " And ChargeDate BETWEEN #" & DateValue(Format(StartDate, "dd/mm/yyyy")) & "# AND #" & DateValue(Format(EndDate, "dd/mm/yyyy")) & "#")

An way to find out if the above is in a proper format, is to print it out using Debug.Print.
And take the output in the Immediate window and paste it into a new query.
Else show some sample data in Excel or MS-Access.

 

[ndeans]

WOW....

Tried your code suggestion with no go .... then on a whim changed the Format(Date, "dd/mm/yyyy") to Format(Date, "mm/dd/yyyy") and we have success....

I call bullshit btw on using USDates.... makes my head spin not knowing when to use which with the databases. It seems that the sql side of things might require the USDates to work properly.


Many Many thanks MarkK for your help....

 

[Galaxiom]

Yes. US Dates is the go.

BTW. In SQL it is more efficient to use "fieldname Is Null" than "IsNull(fieldname)".

 

[MarkK]

You're welcome. And if things go tits up again, don't forget to report the error number too.

 

[JHB]

Tried your code suggestion with no go .... then on a whim changed the Format(Date, "dd/mm/yyyy") to Format(Date, "mm/dd/yyyy") and we have success....

Sorry it was a typo - it should have been "mm/dd/yyyy"

 

[TimW]

I got this a whilst ago thanks to Allen Browne

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, "\#mm\/dd\/yyyy\#")
            Debug.Print "Date 1"
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
            Debug.Print "Date 2"
        End If
    Else
        Debug.Print "Not a date"
    End If
End Function

i would then use

strStartdate=SQLDate(startdate)

And then use strStartDate in the query