FindFirst of a date

Hello,

I am in need of advice with the findfirst method again.
this time i need to position a recordset with a date.
my code:

'---sales prior year---'
tblRs.FindFirst "[RowLabel]= 'Sales Prior Year'"
tblRs.Edit

For iColumn = 0 To 11

AddMonth = DateAdd("m", iColumn, PriorStart)
rsSnp.FindFirst "[LogDate]= '#" & CVDate(AddMonth) & "#'"

Debug.Print "iColumn: " & iColumn & " AddMonth: " & AddMonth
Debug.Print "LogDate: " & rsSnp!LogDate & " Sales: " & rsSnp!
Sales

tblRs("Column" & (iColumn + 1)) = rsSnp!Sales

Next iColumn
tblRs.Update

heres what the immediate window shows:

iColumn: 0 AddMonth: 9/1/2007
LogDate: 2/1/1998 Sales: 895
iColumn: 1 AddMonth: 10/1/2007
LogDate: 2/1/1998 Sales: 895
iColumn: 2 AddMonth: 11/1/2007
LogDate: 2/1/1998 Sales: 895
iColumn: 3 AddMonth: 12/1/2007
LogDate: 2/1/1998 Sales: 895
iColumn: 4 AddMonth: 1/1/2008
LogDate: 2/1/1998 Sales: 895
iColumn: 5 AddMonth: 2/1/2008
LogDate: 2/1/1998 Sales: 895
iColumn: 6 AddMonth: 3/1/2008
LogDate: 2/1/1998 Sales: 895
iColumn: 7 AddMonth: 4/1/2008
LogDate: 2/1/1998 Sales: 895
iColumn: 8 AddMonth: 5/1/2008
LogDate: 2/1/1998 Sales: 895
iColumn: 9 AddMonth: 6/1/2008
LogDate: 2/1/1998 Sales: 895
iColumn: 10 AddMonth: 7/1/2008
LogDate: 2/1/1998 Sales: 895
iColumn: 11 AddMonth: 8/1/2008
LogDate: 2/1/1998 Sales: 895

if you'll notice, the AddMonth variable is showing the
correct date. however the LogDate is not showing the
same date as the AddMonth (like i want it to).

i tried several variations of this line, for example:

rsSnp.FindFirst "[LogDate]= '#" & CVDate(AddMonth) & "#'"

rsSnp.FindFirst "[LogDate]= '" & AddMonth & "'"

rsSnp.FindFirst "[LogDate]= '" & Str(AddMonth) & "'"

rsSnp.FindFirst "[LogDate]= '#" & AddMonth & "#'"

i also looked through google for examples but can't figure out
what i am doing wrong....

Thanks,
Bipin

bipin wrote:

> Hello,
>
> I am in need of advice with the findfirst method again.
> this time i need to position a recordset with a date.
> my code:
>
> '---sales prior year---'
> tblRs.FindFirst "[RowLabel]= 'Sales Prior Year'"
> tblRs.Edit
>
> For iColumn = 0 To 11
>
> AddMonth = DateAdd("m", iColumn, PriorStart)
> rsSnp.FindFirst "[LogDate]= '#" & CVDate(AddMonth) & "#'"
>
> Debug.Print "iColumn: " & iColumn & " AddMonth: " & AddMonth
> Debug.Print "LogDate: " & rsSnp!LogDate & " Sales: " & rsSnp!
> Sales
>
> tblRs("Column" & (iColumn + 1)) = rsSnp!Sales
>
> Next iColumn
> tblRs.Update
>
> heres what the immediate window shows:
>
> iColumn: 0 AddMonth: 9/1/2007
> LogDate: 2/1/1998 Sales: 895
> iColumn: 1 AddMonth: 10/1/2007
> LogDate: 2/1/1998 Sales: 895
> iColumn: 2 AddMonth: 11/1/2007
> LogDate: 2/1/1998 Sales: 895
> iColumn: 3 AddMonth: 12/1/2007
> LogDate: 2/1/1998 Sales: 895
> iColumn: 4 AddMonth: 1/1/2008
> LogDate: 2/1/1998 Sales: 895
> iColumn: 5 AddMonth: 2/1/2008
> LogDate: 2/1/1998 Sales: 895
> iColumn: 6 AddMonth: 3/1/2008
> LogDate: 2/1/1998 Sales: 895
> iColumn: 7 AddMonth: 4/1/2008
> LogDate: 2/1/1998 Sales: 895
> iColumn: 8 AddMonth: 5/1/2008
> LogDate: 2/1/1998 Sales: 895
> iColumn: 9 AddMonth: 6/1/2008
> LogDate: 2/1/1998 Sales: 895
> iColumn: 10 AddMonth: 7/1/2008
> LogDate: 2/1/1998 Sales: 895
> iColumn: 11 AddMonth: 8/1/2008
> LogDate: 2/1/1998 Sales: 895
>
> if you'll notice, the AddMonth variable is showing the
> correct date. however the LogDate is not showing the
> same date as the AddMonth (like i want it to).
>
> i tried several variations of this line, for example:
>
> rsSnp.FindFirst "[LogDate]= '#" & CVDate(AddMonth) & "#'"
>
> rsSnp.FindFirst "[LogDate]= '" & AddMonth & "'"
>
> rsSnp.FindFirst "[LogDate]= '" & Str(AddMonth) & "'"
>
> rsSnp.FindFirst "[LogDate]= '#" & AddMonth & "#'"
>
> i also looked through google for examples but can't figure out
> what i am doing wrong....
>
> Thanks,
> Bipin

Did you try
rsSnp.FindFirst "[LogDate]= #" & AddMonth & "#"
?

Why don't you see if the record is found?
rsSnp.FindFirst "[LogDate]= #" & AddMonth & "#"
If No rsSbp.NoMatch then
Debug.Print "iColumn: " & iColumn & " AddMonth: " & AddMonth
Debug.Print "LogDate: " & rsSnp!LogDate & " Sales: " & rsSnp!
Else
Debug.pring Addmonth & " not found."
Endif

>
> Did you try
> * * * * rsSnp.FindFirst "[LogDate]= #" & AddMonth & "#"
> ?
no i didn't try just using #'s and no single quotes.
but i have now and things are working just as i
wanted them to!!

YOU RULE!

a million thanks,
bipin