find a date within a recordset in vba

[pb21]

I have a recordset created from a dynamic string which returns start dates for a course.

I have a combo on a form that allows the user to select a course start date from the available dates.

some dates have peak pricing so the idea was that i would scan through the recordset and see if the date in the combo box appeared in the recordset.

not sure how to implement this.

I have :
Dim db As DAO.Database
Set db = CurrentDb
DtStartDate = DateValue(Me.CBStartDate.Text)
Dim RstPeakDates As DAO.Recordset
Set RstPeakDates = db.OpenRecordset(Mysqlstring)
If Not RstPeakDates.BOF And Not RstPeakDates.EOF Then

'instead search
With RstPeakDates

.MoveFirst
.FindFirst (CStr(DtStartDate)) 'this bit fails invalid argument



End With
End If


kind regards in advance.

Peter

 

[tkpstock]

Dim db As DAO.Database
Set db = CurrentDb
DtStartDate = DateValue(Me.CBStartDate.[COLOR="Red"]Value[/COLOR])
Dim RstPeakDates As DAO.Recordset
Set RstPeakDates = db.OpenRecordset(Mysqlstring)
If Not RstPeakDates.BOF And Not RstPeakDates.EOF Then
  'instead search
  RstPeakDates.FindFirst "[myDateFieldName]=#" & DtStartDate & "#"
End If

The FindFirst method uses the WHERE clause of an SQL string (without the WHERE) as its argument.

if [myDateFieldName] is a String field, the date would have to be the exact same string, and you would replace the # with quotation marks.

HTH

 

[pb21]

how do i evaluate if this search is true

Thats great thank you would like to return a boolean value if the search is true
not quite sure how would:

if(rstxxx.findfirst)=true ...

or differently if search item (my case date) not found


regards
Peter

 

[tkpstock]

After the FindFirst, you can check to see if anything is found:

If RstPeakDates.NoMatch then...

HTH

 

[pb21]

If more than one row exists and the findfirst returns false, do i have to move through the recordset further?

in essence i want to see if my search value appears in any of the rows, of course i dont know which one.

i am using dao.

regards

heres what i have now

Dim RstPeakDates As DAO.Recordset
Set RstPeakDates = db.OpenRecordset(Mysqlstring)
If Not RstPeakDates.BOF And Not RstPeakDates.EOF Then
Dim n As Integer
'instead search
With RstPeakDates

.MoveFirst


Dim x As String
x = .Fields(8).Value
.FindFirst "[StartDate]=" & DtStartDate & ""
If (.NoMatch = True) Then
MsgBox ("No match Found") 'just for testing

Else
MsgBox ("match found") 'just for testing
End If

' not sure what to do here if match not found.





End With

I am not sure if the finfirst method moves through the recordset on its own.

regards in advance

Peter

 

[tkpstock]

The FindFirst method looks for a match throughout the entire recordset. If one is not found, NoMatch is set to true, and the current record becomes the first record of the recordset.

If a match is found, the current record becomes the record that is found. You would then use the FindNext method to move through all subsequent matches.

HTH

 

[pb21]

The .nomatch method does not evaluate correctly and i cannot see why as the framework of code is correct ithink.

here is the code so far:

Dim DtStartDate As Date
Dim strSel As String
Dim strFro As String
Dim strwhe As String
Dim strhav As String
Dim Mysqlstring As String

'*******************************************************************************
' lets get the dynamic string to give us the peak dates for our combination
strSel = "SELECT TblProduct.[Product Code], TblProduct.Description, TblProduct.CurrentProduct, TblPrice.CurrentYearPrice, TblPrice.NextYearPrice, TblPrice.Duration, TblProduct.ProductPK, TblProductDateLink.Peak, TblStartDates.StartDate, TblPrice.Peak"
strFro = " FROM (TblStartDates RIGHT JOIN TblProductDateLink ON TblStartDates.StartDateID = TblProductDateLink.StartDateID) RIGHT JOIN (TblProduct INNER JOIN TblPrice ON TblProduct.ProductPK = TblPrice.ProductPK) ON TblProductDateLink.ProductPK = TblPrice.ProductPK"
strwhe = " WHERE (((TblProduct.[Product Code]) Like " & """" & StrProduct & """" & ") AND ((TblProduct.CurrentProduct)=True)AND ((TblPrice.Peak)=True) AND ((TblPrice.Duration)Like " & """" & StrDuration & """" & ") AND ((TblProductDateLink.Peak)=True)) ORDER BY TblStartDates.StartDate;"
Mysqlstring = strSel & strFro & strwhe
' Debug.Print Mysqlstring

Dim RstPeakDates As DAO.Recordset
Set RstPeakDates = db.OpenRecordset(Mysqlstring)
Dim x As String 'for testing
If Not RstPeakDates.BOF And Not RstPeakDates.EOF Then


'instead search
With RstPeakDates

' .MoveFirst
x = .Fields(8).Value 'for testing lets see the record field date value
.FindFirst "[StartDate]=#" & DtStartDate & "#"
If (.NoMatch = True) Then
MsgBox ("No match Found") 'just for testing
' couldnt find a match so not a peak date
peakproduct = False
Else
MsgBox ("match found") 'just for testing
'found match
peakproduct = True
End If
End With

End If



*************************

x shows the correct search value I have a suspician that it is todo with data types between the dates.

The startdates table has the field as a date format. The value from the combo box is a string value (i guess) cb filled from sql string.

kind regards in advance.

Peter

 

[tkpstock]

Peter,

When posting code, please insert it into code blocks - to do this, select the code text, then hit the button "#". This makes the code easier to read and preserves your indentation.

I don't see where you're setting the value of DtStartDate... ?

 

[pb21]

Dim DtStartDate As Date

Me.CBStartDate.SetFocus
DtStartDate = DateValue(Me.CBStartDate.Value)


tried the selecting and then # but it didt seem to work so here is the bit that gets the date from the combo.


regards and thanks for the advice so far.

 

[tkpstock]

If you insert a

Msgbox DtStartDate

just before your "FindFirst" statement in your code, what exactly is shown?

I find it is useful to show the exact string representations of values by inserting a Debug.Print or a Msgbox into the code when I'm trying to figure stuff like that out.

More than likely, the DtStartDate isn't storing the value you are expecting, or the field that contains the data isn't storing the data the way you expect.

Is the Field you are search on a Date/Time value?

 

[tkpstock]

tried the selecting and then # but it didt seem to work

Are you replying in the Quick Reply box? If so, the # button isn't shown.

If you hit the "Post Reply" button, the advanced reply editor is shown with multiple formatting buttons, including a "#" button which surrounds the selected text with CODE tags. You can also FORMAT your text easily. You can also insert hyperlinks. You can also use those dang smilies

 

[pb21]

It returns 09/07/06 exactly as shown and yes the field is date time format short date.

 

[tkpstock]

It returns 09/07/06 exactly as shown and yes the field is date time format short date.

I ask this merely for formality, but are you sure 09/07/06 is a value in the returned recordset?

Take the generated sql string, paste it into the SQL Design View of a new query, run it, and make sure that date is in the results.

 

[pb21]

yes interestingly i did that. pasted code into new query and it runs and shows the dataset with the field in question. then i put data from combo in msgbox and also looked at field data.

its driving me mad as the code should work.

is it the 2 # around where part of findfirst?

 

[tkpstock]

is it the 2 # around where part of findfirst?

You use # to delimit dates in the same way you use ' quotes to delimit text strings and nothing to delimit numbers.

I don't suppose you can post a copy of your database or just the form / code?

 

[pb21]

not sure if this is enough or will work. you can see that I must select a year first ie current or next. i choose next as data only exists for next years product so far. then the combo will fill with the course codes for the year that you choose.

then choose the duration of the course and then a start date from the combo. each course has a number of start dates hence the cb.

then clicking calculate would get the date from combo box and creae a query based on inputs that gives a list of course dates that attract higher pricing. then if my selected date falls in the recordset I know to charge the higher price from the pricelist table.

thanks for your help so far.

regards

 

[pb21]

conversion problem

In an effort to find what is going wrong on the date search i created an expression based on the [startdate] expcstr([startdate])

searchstr=me.cbstartdate.text

with recordset ...

and then .FindFirst "[exp]='" & searchstr & "'"

based on strings it finds the date within the recordset (or not as the case may be)


is it possible that in my original code

' .FindFirst "[StartDate]=#" & DtStartDate & "#"

that my table date has some other item embedded in it (like time) that i cannot see? thus making the search impossible.

Ideas would be appreciated as i prefer searching on the date directly rather than conversion to string type.

regards