cbo / rowsource date problem

[Guest]

why does my combo box show the date that is in the field (as it should) but
if in the field the date is 12/08/2005 (this is how it will display in the
cbo box) why do no records show but if i enter 08/12/2005 in the cbo box it
will... this also works vice versa...

Private Sub Form_Load()
' Populate the cbo with order dates where On Order is greater than 0
Me.cboOrderDate.RowSource = "SELECT OrderDate " & _
"FROM tblOrderCurr " & _
"WHERE OnOrder>0 AND OrderDate>#01/01/1900# " & _
"GROUP BY OrderDate " & _
"ORDER BY OrderDate;"

' Me.cboOrderDate.Value = Me.cboOrderDate.ItemData(0)

If Me.cboOrderDate.Value <> "" Then
Me.frmsubOrderCurr.Visible = True
Call cboOrderDate_AfterUpdate
Else
Me.frmsubOrderCurr.Form.RecordSource = ""
Me.frmsubOrderCurr.Visible = False
End If

End Sub

----------------------

Private Sub cboOrderDate_AfterUpdate()
On Error GoTo ErrorHandler

Me.frmsubOrderCurr.Form.RecordSource = "SELECT CartridgeName, " & _
"OnOrder, PricePerUnit, TotalCost FROM tblOrderCurr " & _
"WHERE OrderDate = " & "#" & Trim(Me.cboOrderDate.Value) & "#"

Me.frmsubOrderCurr.Form.Refresh

ErrorHandler_Exit:
Exit Sub

ErrorHandler:
MsgBox ("Error #: " & Err.Number & "; Description: " & Err.Description)
Resume ErrorHandler_Exit
End Sub

 

[Douglas J Steele]

What does 12/08/2005 mean to you: December 8th or August 12th? To Access,
whenever you encase the string in # delimiters, it means mm/dd/yyyy,
regardless of what your regional settings may have set the Short Date format
to.

You might find it useful to read Allen Browne's "International Dates in
Access" at http://www.allenbrowne.com/ser-36.html or what I have in my
September 2003 "Access Answers" column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Guest]

Sorry should have specified 08/12/2005 is the 8th December in UK format
but the 12/08/2005 is also that but in the american format..

All my regional settings are UK.

What does 12/08/2005 mean to you: December 8th or August 12th? To Access,
whenever you encase the string in # delimiters, it means mm/dd/yyyy,
regardless of what your regional settings may have set the Short Date format
to.

You might find it useful to read Allen Browne's "International Dates in
Access" at http://www.allenbrowne.com/ser-36.html or what I have in my
September 2003 "Access Answers" column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

why does my combo box show the date that is in the field (as it should) but
if in the field the date is 12/08/2005 (this is how it will display in the
cbo box) why do no records show but if i enter 08/12/2005 in the cbo box it
will... this also works vice versa...

Private Sub Form_Load()
' Populate the cbo with order dates where On Order is greater than 0
Me.cboOrderDate.RowSource = "SELECT OrderDate " & _
"FROM tblOrderCurr " & _
"WHERE OnOrder>0 AND OrderDate>#01/01/1900# " & _
"GROUP BY OrderDate " & _
"ORDER BY OrderDate;"

' Me.cboOrderDate.Value = Me.cboOrderDate.ItemData(0)

If Me.cboOrderDate.Value <> "" Then
Me.frmsubOrderCurr.Visible = True
Call cboOrderDate_AfterUpdate
Else
Me.frmsubOrderCurr.Form.RecordSource = ""
Me.frmsubOrderCurr.Visible = False
End If

End Sub

----------------------

Private Sub cboOrderDate_AfterUpdate()
On Error GoTo ErrorHandler

Me.frmsubOrderCurr.Form.RecordSource = "SELECT CartridgeName, " & _
"OnOrder, PricePerUnit, TotalCost FROM tblOrderCurr " & _
"WHERE OrderDate = " & "#" & Trim(Me.cboOrderDate.Value) & "#"

Me.frmsubOrderCurr.Form.Refresh

ErrorHandler_Exit:
Exit Sub

ErrorHandler:
MsgBox ("Error #: " & Err.Number & "; Description: " & Err.Description)
Resume ErrorHandler_Exit
End Sub
[/QUOTE]

 

[Guest]

Thanks for the help..