sql select not yielding restriction data - Please Help!

[********]

In a query

SELECT * FROM RONCHECK_access
WHERE (((RONCHECK_access.DATE) Between [enter startdate] And [enter
enddate]));

this select statement works and retrieves data between a keyboard entered
date range.

If I try to do the same thing in a combo box, with start and end date data
coming from two calendar objects, on a form with this code for a mousedown
event

Private Sub db_betw_dates_obj_MouseDown(Button As Integer, Shift As Integer,
X As
Single, Y As Single)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM RONCHECK_access " _
& "WHERE (((RONCHECK_access.DATE) Between " _
& "#" & start_date_cal.Value & "# And #" & end_date_cal.Value & "#));"

Set rs = db.OpenRecordset(strSQL)
End Sub

the data still comes across but it is not restricted by the date range. I
know that DATE is a restricted word, but I don't think that is the problem
here. Using DATE in the query select produced the correct results. In debug
mode the start and end date values are correct. Am I using # or anything else
in this select statement incorrectly? There are no syntax or execution errors.

Thanks.

 

[Graham Mandeno]

You don't mention your location, but I'm guessing your computer does not use
US date formats. SQL takes no notice of the Windows regional settings, and
understands only dates formatted in the US way - mm/dd/yyyy.

Instead of "#" & start_date_cal.Value & "#", try this:
Format( start_date_cal.Value, "\#mm\/dd\/yyyy\#" )

Note that \/ is backslash-forwardslash, not the letter "V".

 

[lendapilot]

In a query

SELECT * FROM RONCHECK_access
WHERE (((RONCHECK_access.DATE) Between [enter startdate] And [enter
enddate]));

this select statement works and retrieves data between a keyboard entered
date range.

If I try to do the same thing in a combo box, with start and end date data
coming from two calendar objects, on a form with this code for a mousedown
event

Private Sub db_betw_dates_obj_MouseDown(Button As Integer, Shift As Integer,
X As
Single, Y As Single)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM RONCHECK_access " _
& "WHERE (((RONCHECK_access.DATE) Between " _
& "#" & start_date_cal.Value & "# And #" & end_date_cal.Value & "#));"

Set rs = db.OpenRecordset(strSQL)
End Sub

the data still comes across but it is not restricted by the date range. I
know that DATE is a restricted word, but I don't think that is the problem
here. Using DATE in the query select produced the correct results. In debug
mode the start and end date values are correct. Am I using # or anything else
in this select statement incorrectly? There are no syntax or execution errors.

Thanks.

Here's what the code looks like now:

Private Sub db_betw_dates_obj_MouseDown(Button As Integer, Shift As Integer,
X As Single, Y As Single)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM RONCHECK_access " _
& "WHERE (((RONCHECK_access.DATE) Between " _
& Format(start_date_cal.Value, "\#mm\/dd\/yyyy\#") & " And " &
Format(end_date_cal.Value, "\#mm\/dd\/yyyy\#") & "));"
Set rs = db.OpenRecordset(strSQL)
End Sub

It still does not restict the data. I see all the records in the table. Why
does the query select work and the vba code doesn't? I think you put me on
the right track but I'm still missing somethung. Thanks.

 

[Graham Mandeno]

I suggest you set a breakpoint in your code on the "Set rs =" line. When
the code stops there, examine the string strSQL in the debug window (just
type ?strSQL). Make sure that it does indeed contain valid dates between the
# signs.

If you still can't see any problem, then copy the string from the debug
window and paste it into the SQL view window of a new query. Then try to
run the query and observe the results.

 

[lendapilot]

I followed your suggestions, which were the most helpful I have received in
this discussion group. The select statement looked ok at the breakpoint. It
still doesn't pull in restricted data in the combo box, but it did work as a
query. Have I done something wrong in the combo box properties to overide the
mousedown code? Thanks again.

I suggest you set a breakpoint in your code on the "Set rs =" line. When
the code stops there, examine the string strSQL in the debug window (just
type ?strSQL). Make sure that it does indeed contain valid dates between the
# signs.

If you still can't see any problem, then copy the string from the debug
window and paste it into the SQL view window of a new query. Then try to
run the query and observe the results.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here's what the code looks like now:

Private Sub db_betw_dates_obj_MouseDown(Button As Integer, Shift As
Integer,
X As Single, Y As Single)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM RONCHECK_access " _
& "WHERE (((RONCHECK_access.DATE) Between " _
& Format(start_date_cal.Value, "\#mm\/dd\/yyyy\#") & " And " &
Format(end_date_cal.Value, "\#mm\/dd\/yyyy\#") & "));"
Set rs = db.OpenRecordset(strSQL)
End Sub

It still does not restict the data. I see all the records in the table.
Why
does the query select work and the vba code doesn't? I think you put me on
the right track but I'm still missing somethung. Thanks.

 

[Graham Mandeno]

I had no idea you wanted the result of the query to appear in a combo box.

For this, you don't need a Database or a Recordset variable. all you need
is to construct your SQL string and use it for the RowSource of your combo
box:

Me.cboSelectThing.RowSource = strSQL
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I followed your suggestions, which were the most helpful I have received in
this discussion group. The select statement looked ok at the breakpoint.
It
still doesn't pull in restricted data in the combo box, but it did work as
a
query. Have I done something wrong in the combo box properties to overide
the
mousedown code? Thanks again.

I suggest you set a breakpoint in your code on the "Set rs =" line. When
the code stops there, examine the string strSQL in the debug window (just
type ?strSQL). Make sure that it does indeed contain valid dates between
the
# signs.

If you still can't see any problem, then copy the string from the debug
window and paste it into the SQL view window of a new query. Then try to
run the query and observe the results.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here's what the code looks like now:

Private Sub db_betw_dates_obj_MouseDown(Button As Integer, Shift As
Integer,
X As Single, Y As Single)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM RONCHECK_access " _
& "WHERE (((RONCHECK_access.DATE) Between " _
& Format(start_date_cal.Value, "\#mm\/dd\/yyyy\#") & " And " &
Format(end_date_cal.Value, "\#mm\/dd\/yyyy\#") & "));"
Set rs = db.OpenRecordset(strSQL)
End Sub

It still does not restict the data. I see all the records in the table.
Why
does the query select work and the vba code doesn't? I think you put me
on
the right track but I'm still missing somethung. Thanks.

 

[lendapilot]

You had a lot of patience and perseverence and your advice totally solved the
problem. Your suggestions were always to the point, even when I left out a
few important details. In the process, I learned a lot about access & VBA. I
really appreciate your terrific help.

I had no idea you wanted the result of the query to appear in a combo box.

For this, you don't need a Database or a Recordset variable. all you need
is to construct your SQL string and use it for the RowSource of your combo
box:

Me.cboSelectThing.RowSource = strSQL
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I followed your suggestions, which were the most helpful I have received in
this discussion group. The select statement looked ok at the breakpoint.
It
still doesn't pull in restricted data in the combo box, but it did work as
a
query. Have I done something wrong in the combo box properties to overide
the
mousedown code? Thanks again.

I suggest you set a breakpoint in your code on the "Set rs =" line. When
the code stops there, examine the string strSQL in the debug window (just
type ?strSQL). Make sure that it does indeed contain valid dates between
the
# signs.

If you still can't see any problem, then copy the string from the debug
window and paste it into the SQL view window of a new query. Then try to
run the query and observe the results.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Here's what the code looks like now:

Private Sub db_betw_dates_obj_MouseDown(Button As Integer, Shift As
Integer,
X As Single, Y As Single)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM RONCHECK_access " _
& "WHERE (((RONCHECK_access.DATE) Between " _
& Format(start_date_cal.Value, "\#mm\/dd\/yyyy\#") & " And " &
Format(end_date_cal.Value, "\#mm\/dd\/yyyy\#") & "));"
Set rs = db.OpenRecordset(strSQL)
End Sub

It still does not restict the data. I see all the records in the table.
Why
does the query select work and the vba code doesn't? I think you put me
on
the right track but I'm still missing somethung. Thanks.

 

[Graham Mandeno]

You had a lot of patience and perseverence and your advice totally solved
the
problem. Your suggestions were always to the point, even when I left out a
few important details. In the process, I learned a lot about access & VBA.
I
really appreciate your terrific help.

Thanks! It's good of you to take the time to give some positive feedback.

I'm glad it's all working now.