searching by date reference...

[ariel81]

Hi,

i have a START date "DD/MM/YYYY" <-- with 3 combo boxes "cboStartDD", "cboStartMM", "cboStartYYYY".

i have a END date "DD/MM/YYYY" <-- with 3 combo boxes "cboEndDD", "cboEndMM", "cboEndYYYY".

i have a Search command buttom.

upon a "search" button is click,

how can i search the dates in the table with "tblDate" (column) that matches the date ranges from the START date to the END date?

by using sql statement...

thank you.

 

[rainman89]

Use

BETWEEN [startdate] AND [enddate]

 

[ariel81]

thanks, here is another problem...lets say i have two records (dates) in the table that falls within the search 'start' and 'end' date.

the rs.RecordCount should have the value 2 because it founds two records within the search range.

however i tried to display the rs.RecordCount value in a textbox and it shows value 1 instead of 2.

i have attached my program...
-refer to the "Private Sub cmdSearch_Click()".
- i have made 3 records in the table.

 

[Rabbie]

Access often doesn't know the correct RecordCount initially. You should do

rs.moveLast
rs.MoveFirst

as soon as you open the recordset so it can calculate the correct RecordCount

Hope this helps

 

[ariel81]

it works...

however if there are no records found...it gives a debug msg in the code builder window...how do i avoid tat debug msg?

what is the reason to use rs.moveLast and rs.MoveFirst ?
if i don't use them, will all of my records in the table column be scanned thru from the 1st record to the last record?

thank you.

 

[Rabbie]

it works...

Happy to be of help

however if there are no records found...it gives a debug msg in the code builder window...how do i avoid tat debug msg?

Do a check on rs.RecordCount before doing the moves. IF it is zero then there are no records

what is the reason to use rs.moveLast and rs.MoveFirst ?
if i don't use them, will all of my records in the table column be scanned thru from the 1st record to the last record?

The point of the rs.MoveLast is to make sure that all the records in the recordset are included in the count so it needs to be there.

rs.MoveFirst is to get the pointer back to where it was at the beginning of the recordset.

Hope this helps