Possible to Use Function in FindFirst Recordset?

[adrift]

On my form I have a command to display records in a table based on whether the date the user enters equals that in the table.. BUT I don't care about time, just if it's the same day..

So assuming 'uptime' is the name of the date variable in the table and 'userday,month,year' is the users requested date, I've tried the following findfirst syntax with no luck .. keep getting syntax errors.. I wonder can functions be used in this way?

rst.Findfirst ((DatePart("d","[uptime]&quot=userday) &(DatePart("m","[uptime]&quot=usermonth)&(DatePart("yyyy","[uptime]&quot=useryear))

 

[UncleJack]

FindFirst merely moves the record pointer to the first record in the record set. Index the target date field in the table and use the SEEK method to locate the first matching record. See the SEEK method topic under Help for details.

Uncle Jack

 

[MichaelRed]

.FindFirst or .Seek will BOTH / Either require the function to match the data type(s) of the elements. You cannot "find" a string in a numeric field. I suspect that is the issue, although the FindFIRST nethod doesn;t exist in ADO type recordsets (replaced with a more generc FIND - which dioesn't support multiple fields).

Altogether, the Find functions are un-necessary, as it is always possible to simply code the (equivlent) SQL statement directly.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[adrift]

Notice that I have to break down the date variable using datepart so that I match only on m/d/yyyy and not the time portion. There seems to be an issue with using the 'datepart' function in my criteria for findfirst AND seek.

 

[MichaelRed]

UpTime = #11/20/2001#

UserDay = 20
UserMonth = 11
UserYear = 2001

? ((DatePart("d",uptime) = userday) ANd (DatePart("m",uptime) = usermonth) And (DatePart("yyyy",uptime)=useryear))
True
[code]

w/o reference to the rst & fields, the criteria expression can be made to work, as demonstrated above.

Another 'suggestion' is to seperate the criteria into a STRING expression, so you can peruse the expr in the debug window, as I have done above.

Also Notice that I changed the concatenation symbol ("&") with the logical operator (AND) to achieve the results.

 MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[adrift]

brilliant.. thanks michael..

 

[PaulF]

FindFirst uses Strings so you might need to do something like this


rst.Findfirst ("DatePart('d','[uptime]')= " & userday & " And DatePart('m','[uptime]')= " & usermonth & " And DatePart('yyyy','[uptime]')= " & useryear )

or a variation of this

PaulF

 

[adrift]

Paul, you're getting me one step closer although now I'm getting a data mismatch error..

Should the return of DatePart be an integer value? That's what I have set right now..

 

[adrift]

Ok, I've tried using all suggestions and still no luck..

 

[adrift]

criteria = "(DatePart('d','[uptime]')= " & Me.daytext & " And DatePart('m','[uptime]')= " & Me.monthtext & " And DatePart('yyyy','[uptime]')= " & Me.yeartext

This is what I have and now at

rst.FindFirst criteria

I get a runtime error 3077

Missing ),], or Item in expression

 

[adrift]

Ok, sorry to ramble on but after doing some research it appears the because uptime is a date/time type that I'm having some problems.

Data type mismatch in criteria expression.

 

[adrift]

OK, solved my problem.. for any person interested or having a similar problem..

I created a variable dateconcate to sort user input into #mm/dd/yyyy# format, then compared it to my date variable 'uptime'

dateconcate = Me.daytext & "/" & Me.monthtext & "/" & Me.yeartext

criteria = "[uptime] = #" & dateconcate & "#"

rst.findfirst criteria