Syntax error

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hi,

Could someone help me to correct the following line. Thank you.

intYounger3Y = DCount("[Name]", "tbRegVacc", "[Birthdate]< DateAdd("y" -3,
"Me![InputBirth]")")
 
Jeff said:
Could someone help me to correct the following line. Thank you.

intYounger3Y = DCount("[Name]", "tbRegVacc", "[Birthdate]< DateAdd("y" -3,
"Me![InputBirth]")")
Click to expand...


I think you probably want something like:

intYounger3Y = DCount("[Name]", "tbRegVacc", _
"[Birthdate] < " & Foramt(DateAdd("yyyy", -3, _
Me![InputBirth]), "\#yyyy-m-d\#"))
 
Try:

intYounger3Y = DCount("[Name]", "tbRegVacc", "[Birthdate]<#" & DateAdd("y",
-3, Me![InputBirth]) & "#")
 
Neither of the above 2 will work beacuse of the quotes around the yyyy in the
dateadd function. Try this

Dim dtCheckDate As Date

dtCheckDate = DateAdd("yyyy", -3, Me!InputBirth)
Me.intYounger3Y = DCount("[Name]", "tbRegVacc", "[Birthdate]< #" &
dtCheckDate & "#")
 
And, in fact that likely won't work as expected if the user has his/her
Short Date format set to dd/mm/yyyy, since Access typically does not respect
Regional Settings in SQL statements (which is what a Domain Aggregate
function such as DCount boils down to)

Me.intYounger3Y = DCount("[Name]", "tbRegVacc", _
"[Birthdate]< " & Format(DateAdd("yyyy", -3, Me!InputBirth),
"\#yyyy\-mm\-dd\#")))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


RonaldoOneNil said:
Neither of the above 2 will work beacuse of the quotes around the yyyy in
the
dateadd function. Try this

Dim dtCheckDate As Date

dtCheckDate = DateAdd("yyyy", -3, Me!InputBirth)
Me.intYounger3Y = DCount("[Name]", "tbRegVacc", "[Birthdate]< #" &
dtCheckDate & "#")

Jeff said:
Hi,

Could someone help me to correct the following line. Thank you.

intYounger3Y = DCount("[Name]", "tbRegVacc", "[Birthdate]<
DateAdd("y" -3,
"Me![InputBirth]")")
Click to expand...
Click to expand...
 
RonaldoOneNil said:
Neither of the above 2 will work beacuse of the quotes around the yyyy in the
dateadd function. Try this

Dim dtCheckDate As Date

dtCheckDate = DateAdd("yyyy", -3, Me!InputBirth)
Me.intYounger3Y = DCount("[Name]", "tbRegVacc", "[Birthdate]< #" &
dtCheckDate & "#")
Click to expand...


Well, kismet only had a single y (day of the year), so
that's why it wouldn't work, but the quotes in both replies
are correct.

OTOH, as Doug said, using the Format function is also
necessary unless you can guarantee that every machine that
ever runs the application has its regional date settings
exactly the way you need them to get your code to work.
 
Back
Top