Find first criteria

[Jane]

I am attempting to find a record in a DAO recordset with
the following criteria:

rstSappoint.FindFirst "[diveid] = " & Combo10 And "[ddate]

#" & Fdate & "#"

data type mis match is returned when the recordset date
field is compared to the variable Fdate. My local settings
are United Kingdom dates should I reformat the dates to
get a valid comparison. If so could anyone provide me with
the syntax.

Thanks in advance
Jane

 

[Douglas J. Steele]

Access SQL doesn't recognize Regional Settings. You either need the date to
be in mm/dd/yyyy format, or in an unambiguous format (such as yyyy-mm-dd or
dd mmm yyyy)

rstSappoint.FindFirst "[diveid] = " & Combo10 & " And [ddate] > " &
Format$(Fdate, "\#mm\/dd\/yyyy\#")

or

rstSappoint.FindFirst "[diveid] = " & Combo10 & " And [ddate] > " &
Format$(Fdate, "\#yyyy\-mm\-dd\#")

However, I don't think an incorrect date format is what's giving a date type
mismatch error. That message is more consistant with either diveid or ddate
being text fields, or, more likely in this case, the fact that your where
clause is syntactically incorrect: you need the AND conjunction to be inside
the string (and you need a space between the value of Combo10 and the
beginning of the continuation of the string). Note the difference between
what I have above and what you had.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am attempting to find a record in a DAO recordset with
the following criteria:

rstSappoint.FindFirst "[diveid] = " & Combo10 And "[ddate]

#" & Fdate & "#"

data type mis match is returned when the recordset date
field is compared to the variable Fdate. My local settings
are United Kingdom dates should I reformat the dates to
get a valid comparison. If so could anyone provide me with
the syntax.

Thanks in advance
Jane

 

[Roger Carlson]

I'd say rather your problem is with your criteria sting. It should be:

rstSappoint.FindFirst "[diveid] = " & Combo10 & "And [ddate] > #" & Fdate &
"#"

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I am attempting to find a record in a DAO recordset with
the following criteria:

rstSappoint.FindFirst "[diveid] = " & Combo10 And "[ddate]

#" & Fdate & "#"

data type mis match is returned when the recordset date
field is compared to the variable Fdate. My local settings
are United Kingdom dates should I reformat the dates to
get a valid comparison. If so could anyone provide me with
the syntax.

Thanks in advance
Jane

 

[Jane]

Many thanks works perfectly.
Jane

-----Original Message-----
Access SQL doesn't recognize Regional Settings. You either need the date to
be in mm/dd/yyyy format, or in an unambiguous format (such as yyyy-mm-dd or
dd mmm yyyy)

rstSappoint.FindFirst "[diveid] = " & Combo10 & " And [ddate] > " &
Format$(Fdate, "\#mm\/dd\/yyyy\#")

or

rstSappoint.FindFirst "[diveid] = " & Combo10 & " And [ddate] > " &
Format$(Fdate, "\#yyyy\-mm\-dd\#")

However, I don't think an incorrect date format is what's giving a date type
mismatch error. That message is more consistant with either diveid or ddate
being text fields, or, more likely in this case, the fact that your where
clause is syntactically incorrect: you need the AND conjunction to be inside
the string (and you need a space between the value of Combo10 and the
beginning of the continuation of the string). Note the difference between
what I have above and what you had.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am attempting to find a record in a DAO recordset with
the following criteria:

rstSappoint.FindFirst "[diveid] = " & Combo10 And "[ddate]

#" & Fdate & "#"

data type mis match is returned when the recordset date
field is compared to the variable Fdate. My local settings
are United Kingdom dates should I reformat the dates to
get a valid comparison. If so could anyone provide me with
the syntax.

Thanks in advance
Jane

.