Do you know why when using Between and... dates from the 1st to the 9th do not show

[Tony Lucas]

When using the WHERECondition "Between And".
The dates range from the 1st to 9th do not filter in to the report but if I set the date from the 31st to 10th then they do.
Do you know why this happens?
If I hard code the query or use a parameter it filters fine.

I have tried changing the variables to strings, CDATE, DATEVALUE, format etc. am i missing something out ?
They all do the same

This is the code that runs

Sub TxtFinishDate_Exit()

  TxtDateStart = [Form_Frm Choose What To Print].TxtStartDate.Value
  TxtDateEnd = [Form_Frm Choose What To Print].TxtFinishDate.Value

  MYWhereCondition = "[Delivery_Date] Between #" & TxtDateStart & "# And #" & TxtDateEnd & "#"

'this is an alternate line of code
'MYWhereCondition = "[Delivery_Date] Between " & Format(TxtDateStart, "\#dd/m/yyyy hh:mm\#") & " And " & Format(TxtDateEnd, "\#dd/m/yyyy hh:mm#")

  ' this code is for checking string oputputs in the imediate window
  Debug.Print "From Sub TxtFinishDate_Exit :" & "[Delivery_Date] Between " & Format(TxtDateStart, "\#dd/m/yyyy hh:mm\#") & " And " & Format(TxtDateEnd, "\#dd/m/yyyy hh:mm\#")
  Debug.Print "From Sub TxtFinishDate_Exit :" & MYWhereCondition

  If Not (IsNull(TxtDateStart) Or IsNull(TxtDateEnd)) Then

    DoCmd.OpenReport "Rpt Access Export Without Matching Definitions by date range1", acViewPreview, WhereCondition:=MYWhereCondition

  Else
    MsgBox "You must specify both dates before opening the report"
  End If
End Sub

[dsatino]

The date field you're querying is probably not formatted as you believe.

For instance, it may also be time stamped so your end date of 9/9/2010 is actually 9/9/2010 00:00:00 which means that anything after the 0th millisecond of the ninth won't be picked up. This doesn't explain why the first of the month is excluded though so this probably isn't the format, but you get the idea: check the original format of the field.

[MMcCarthy]

Change this ...

Between " & Format(TxtDateStart, "\#dd/m/yyyy hh:mm\#") & " And

to ...

Between #" & Format(TxtDateStart, "dd/mm/yyyy hh:mm") & "# And

Fistly the correct month format is mm not m.
Secondly by putting the # indicators in the format it may be seeing it as a string instead of a date. Not sure though why it would only be doing it for the 1-9 dates.

Try these changes and see if it makes a difference.

[NeoPa]

Your date format is local and not SQL. SQL has it's own format for dates, which happens to match the local version for USA (See Literal DateTimes and Their Delimiters (#) for a full explanation).