Datepart function

  • Thread starter Thread starter Ethel
  • Start date Start date
E

Ethel

When I use the DatePart function on a table in one database to extract the
month I get message saying "There was an error compiling this function. the
Visual Basic module contains a syntax error. Check the code, and then
recomile it."

However, when I take a protion of the table to a new database with no
changes to test it and use the exact code it works. What I am using is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time format.
 
I tried that with the same result.

Douglas J. Steele said:
Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ethel said:
When I use the DatePart function on a table in one database to extract the
month I get message saying "There was an error compiling this function.
the
Visual Basic module contains a syntax error. Check the code, and then
recomile it."

However, when I take a protion of the table to a new database with no
changes to test it and use the exact code it works. What I am using is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time format.
Click to expand...
Click to expand...
 
Do you have another function named DatePart in your database?

Are your References okay? (while in the VB Editor, select Tools |
References from the menu bar. Make sure none of the selected references--the
ones with check marks in front of them at the top of the list--have MISSING:
in front of them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ethel said:
I tried that with the same result.

Douglas J. Steele said:
Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ethel said:
When I use the DatePart function on a table in one database to extract
the
month I get message saying "There was an error compiling this function.
the
Visual Basic module contains a syntax error. Check the code, and then
recomile it."

However, when I take a protion of the table to a new database with no
changes to test it and use the exact code it works. What I am using
is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time format.
Click to expand...
Click to expand...
Click to expand...
 
There is no other function named DatePart.

I also checked the references and they are OK. In fact if I open a new
database and use an extract from this larger database the DatePart works. It
just does not work on this database.

Douglas J. Steele said:
Do you have another function named DatePart in your database?

Are your References okay? (while in the VB Editor, select Tools |
References from the menu bar. Make sure none of the selected references--the
ones with check marks in front of them at the top of the list--have MISSING:
in front of them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ethel said:
I tried that with the same result.

Douglas J. Steele said:
Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use the DatePart function on a table in one database to extract
the
month I get message saying "There was an error compiling this function.
the
Visual Basic module contains a syntax error. Check the code, and then
recomile it."

However, when I take a protion of the table to a new database with no
changes to test it and use the exact code it works. What I am using
is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time format.
Click to expand...
Click to expand...
Click to expand...
 
The fact that it works elsewhere but not in this database points even more
to a problem with the references.

Try adding another reference at random, backing out of the References
dialog, then going back in and deleting the reference you just added.

If that still doesnt' work, write down all of the references you currently
have selected. Unselect as many as you can (Access won't let you unselect
them all), then back out of the dialog. Go back in and reselect all of the
ones you succeeded in deleting. (Note: They'll be in alphabetic order, not
at the top anymore!)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ethel said:
There is no other function named DatePart.

I also checked the references and they are OK. In fact if I open a new
database and use an extract from this larger database the DatePart works.
It
just does not work on this database.

Douglas J. Steele said:
Do you have another function named DatePart in your database?

Are your References okay? (while in the VB Editor, select Tools |
References from the menu bar. Make sure none of the selected
references--the
ones with check marks in front of them at the top of the list--have
MISSING:
in front of them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ethel said:
I tried that with the same result.

:

Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use the DatePart function on a table in one database to
extract
the
month I get message saying "There was an error compiling this
function.
the
Visual Basic module contains a syntax error. Check the code, and
then
recomile it."

However, when I take a protion of the table to a new database with
no
changes to test it and use the exact code it works. What I am using
is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time
format.
Click to expand...
Click to expand...
Click to expand...
 
This is one stubborn problem. I agree it is probably a library, but it still
does not work. E.

Douglas J. Steele said:
The fact that it works elsewhere but not in this database points even more
to a problem with the references.

Try adding another reference at random, backing out of the References
dialog, then going back in and deleting the reference you just added.

If that still doesnt' work, write down all of the references you currently
have selected. Unselect as many as you can (Access won't let you unselect
them all), then back out of the dialog. Go back in and reselect all of the
ones you succeeded in deleting. (Note: They'll be in alphabetic order, not
at the top anymore!)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ethel said:
There is no other function named DatePart.

I also checked the references and they are OK. In fact if I open a new
database and use an extract from this larger database the DatePart works.
It
just does not work on this database.

Douglas J. Steele said:
Do you have another function named DatePart in your database?

Are your References okay? (while in the VB Editor, select Tools |
References from the menu bar. Make sure none of the selected
references--the
ones with check marks in front of them at the top of the list--have
MISSING:
in front of them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried that with the same result.

:

Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use the DatePart function on a table in one database to
extract
the
month I get message saying "There was an error compiling this
function.
the
Visual Basic module contains a syntax error. Check the code, and
then
recomile it."

However, when I take a protion of the table to a new database with
no
changes to test it and use the exact code it works. What I am using
is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time
format.
Click to expand...
Click to expand...
Click to expand...
 
Rereading the error message ("There was an error compiling this function.
TheVisual Basic module contains a syntax error. Check the code, and then
recompile it."), I take back what I said about References. It sounds more
like you do have another function DatePart somewhere in your database.

Have you tried compiling the database to see whether an error is revealed?

Another option would be to create a new database, and import everything from
the old database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ethel said:
This is one stubborn problem. I agree it is probably a library, but it
still
does not work. E.

Douglas J. Steele said:
The fact that it works elsewhere but not in this database points even
more
to a problem with the references.

Try adding another reference at random, backing out of the References
dialog, then going back in and deleting the reference you just added.

If that still doesnt' work, write down all of the references you
currently
have selected. Unselect as many as you can (Access won't let you unselect
them all), then back out of the dialog. Go back in and reselect all of
the
ones you succeeded in deleting. (Note: They'll be in alphabetic order,
not
at the top anymore!)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ethel said:
There is no other function named DatePart.

I also checked the references and they are OK. In fact if I open a new
database and use an extract from this larger database the DatePart
works.
It
just does not work on this database.

:

Do you have another function named DatePart in your database?

Are your References okay? (while in the VB Editor, select Tools |
References from the menu bar. Make sure none of the selected
references--the
ones with check marks in front of them at the top of the list--have
MISSING:
in front of them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried that with the same result.

:

Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use the DatePart function on a table in one database to
extract
the
month I get message saying "There was an error compiling this
function.
the
Visual Basic module contains a syntax error. Check the code, and
then
recomile it."

However, when I take a protion of the table to a new database
with
no
changes to test it and use the exact code it works. What I am
using
is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time
format.
Click to expand...
Click to expand...
Click to expand...
 
I have not tried compiling the database. How would I do that?

Douglas J. Steele said:
Rereading the error message ("There was an error compiling this function.
TheVisual Basic module contains a syntax error. Check the code, and then
recompile it."), I take back what I said about References. It sounds more
like you do have another function DatePart somewhere in your database.

Have you tried compiling the database to see whether an error is revealed?

Another option would be to create a new database, and import everything from
the old database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ethel said:
This is one stubborn problem. I agree it is probably a library, but it
still
does not work. E.

Douglas J. Steele said:
The fact that it works elsewhere but not in this database points even
more
to a problem with the references.

Try adding another reference at random, backing out of the References
dialog, then going back in and deleting the reference you just added.

If that still doesnt' work, write down all of the references you
currently
have selected. Unselect as many as you can (Access won't let you unselect
them all), then back out of the dialog. Go back in and reselect all of
the
ones you succeeded in deleting. (Note: They'll be in alphabetic order,
not
at the top anymore!)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


There is no other function named DatePart.

I also checked the references and they are OK. In fact if I open a new
database and use an extract from this larger database the DatePart
works.
It
just does not work on this database.

:

Do you have another function named DatePart in your database?

Are your References okay? (while in the VB Editor, select Tools |
References from the menu bar. Make sure none of the selected
references--the
ones with check marks in front of them at the top of the list--have
MISSING:
in front of them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried that with the same result.

:

Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use the DatePart function on a table in one database to
extract
the
month I get message saying "There was an error compiling this
function.
the
Visual Basic module contains a syntax error. Check the code, and
then
recomile it."

However, when I take a protion of the table to a new database
with
no
changes to test it and use the exact code it works. What I am
using
is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time
format.
Click to expand...
Click to expand...
Click to expand...
 
Doug, I figured it out and found a module that had a bad reference. I
deleted the module and it works. I was not using that module.

Why would a bad reference in an unused module cause a problem on this
function when I have other functions on other queries where this caused no
problem?

Thanks for the help.
E.

Douglas J. Steele said:
Rereading the error message ("There was an error compiling this function.
TheVisual Basic module contains a syntax error. Check the code, and then
recompile it."), I take back what I said about References. It sounds more
like you do have another function DatePart somewhere in your database.

Have you tried compiling the database to see whether an error is revealed?

Another option would be to create a new database, and import everything from
the old database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ethel said:
This is one stubborn problem. I agree it is probably a library, but it
still
does not work. E.

Douglas J. Steele said:
The fact that it works elsewhere but not in this database points even
more
to a problem with the references.

Try adding another reference at random, backing out of the References
dialog, then going back in and deleting the reference you just added.

If that still doesnt' work, write down all of the references you
currently
have selected. Unselect as many as you can (Access won't let you unselect
them all), then back out of the dialog. Go back in and reselect all of
the
ones you succeeded in deleting. (Note: They'll be in alphabetic order,
not
at the top anymore!)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


There is no other function named DatePart.

I also checked the references and they are OK. In fact if I open a new
database and use an extract from this larger database the DatePart
works.
It
just does not work on this database.

:

Do you have another function named DatePart in your database?

Are your References okay? (while in the VB Editor, select Tools |
References from the menu bar. Make sure none of the selected
references--the
ones with check marks in front of them at the top of the list--have
MISSING:
in front of them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried that with the same result.

:

Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use the DatePart function on a table in one database to
extract
the
month I get message saying "There was an error compiling this
function.
the
Visual Basic module contains a syntax error. Check the code, and
then
recomile it."

However, when I take a protion of the table to a new database
with
no
changes to test it and use the exact code it works. What I am
using
is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time
format.
Click to expand...
Click to expand...
Click to expand...
 
Whenever you call a function, Access has to find where it is in the database
(if it's a user-defined function), or in the libraries pointed to by the
References collection. Access actually looks in the VBA library last, so if
anything stops it from getting there, it's going to have a problem.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ethel said:
Doug, I figured it out and found a module that had a bad reference. I
deleted the module and it works. I was not using that module.

Why would a bad reference in an unused module cause a problem on this
function when I have other functions on other queries where this caused no
problem?

Thanks for the help.
E.

Douglas J. Steele said:
Rereading the error message ("There was an error compiling this function.
TheVisual Basic module contains a syntax error. Check the code, and then
recompile it."), I take back what I said about References. It sounds more
like you do have another function DatePart somewhere in your database.

Have you tried compiling the database to see whether an error is
revealed?

Another option would be to create a new database, and import everything
from
the old database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ethel said:
This is one stubborn problem. I agree it is probably a library, but it
still
does not work. E.

:

The fact that it works elsewhere but not in this database points even
more
to a problem with the references.

Try adding another reference at random, backing out of the References
dialog, then going back in and deleting the reference you just added.

If that still doesnt' work, write down all of the references you
currently
have selected. Unselect as many as you can (Access won't let you
unselect
them all), then back out of the dialog. Go back in and reselect all of
the
ones you succeeded in deleting. (Note: They'll be in alphabetic order,
not
at the top anymore!)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


There is no other function named DatePart.

I also checked the references and they are OK. In fact if I open a
new
database and use an extract from this larger database the DatePart
works.
It
just does not work on this database.

:

Do you have another function named DatePart in your database?

Are your References okay? (while in the VB Editor, select Tools |
References from the menu bar. Make sure none of the selected
references--the
ones with check marks in front of them at the top of the list--have
MISSING:
in front of them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried that with the same result.

:

Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use the DatePart function on a table in one database to
extract
the
month I get message saying "There was an error compiling this
function.
the
Visual Basic module contains a syntax error. Check the code,
and
then
recomile it."

However, when I take a protion of the table to a new database
with
no
changes to test it and use the exact code it works. What I am
using
is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time
format.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for all the help. It was great working with you. E.

Douglas J. Steele said:
Whenever you call a function, Access has to find where it is in the database
(if it's a user-defined function), or in the libraries pointed to by the
References collection. Access actually looks in the VBA library last, so if
anything stops it from getting there, it's going to have a problem.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ethel said:
Doug, I figured it out and found a module that had a bad reference. I
deleted the module and it works. I was not using that module.

Why would a bad reference in an unused module cause a problem on this
function when I have other functions on other queries where this caused no
problem?

Thanks for the help.
E.

Douglas J. Steele said:
Rereading the error message ("There was an error compiling this function.
TheVisual Basic module contains a syntax error. Check the code, and then
recompile it."), I take back what I said about References. It sounds more
like you do have another function DatePart somewhere in your database.

Have you tried compiling the database to see whether an error is
revealed?

Another option would be to create a new database, and import everything
from
the old database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


This is one stubborn problem. I agree it is probably a library, but it
still
does not work. E.

:

The fact that it works elsewhere but not in this database points even
more
to a problem with the references.

Try adding another reference at random, backing out of the References
dialog, then going back in and deleting the reference you just added.

If that still doesnt' work, write down all of the references you
currently
have selected. Unselect as many as you can (Access won't let you
unselect
them all), then back out of the dialog. Go back in and reselect all of
the
ones you succeeded in deleting. (Note: They'll be in alphabetic order,
not
at the top anymore!)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


There is no other function named DatePart.

I also checked the references and they are OK. In fact if I open a
new
database and use an extract from this larger database the DatePart
works.
It
just does not work on this database.

:

Do you have another function named DatePart in your database?

Are your References okay? (while in the VB Editor, select Tools |
References from the menu bar. Make sure none of the selected
references--the
ones with check marks in front of them at the top of the list--have
MISSING:
in front of them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried that with the same result.

:

Try using "m" instead of "mm"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use the DatePart function on a table in one database to
extract
the
month I get message saying "There was an error compiling this
function.
the
Visual Basic module contains a syntax error. Check the code,
and
then
recomile it."

However, when I take a protion of the table to a new database
with
no
changes to test it and use the exact code it works. What I am
using
is:

month: DatePart ("mm",[2008 New Format leads]![Week Of])

I checked the format of the date field and it is in Date/Time
format.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top