Find first criteria

  • Thread starter Thread starter Jane
  • Start date Start date
J

Jane

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

rstSappoint.FindFirst "[diveid] = " & Combo10 And "[ddate]
#" & Fdate & "#"
Click to expand...

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
 
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)


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

rstSappoint.FindFirst "[diveid] = " & Combo10 And "[ddate]
#" & Fdate & "#"
Click to expand...

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
Click to expand...
 
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

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

rstSappoint.FindFirst "[diveid] = " & Combo10 And "[ddate]
#" & Fdate & "#"
Click to expand...

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
Click to expand...
 
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 & "#"
Click to expand...

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
Click to expand...


.
Click to expand...
 
Back
Top