By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 2,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Dlookup #error message

P: n/a
Sue
I sent an e-mail below and got a responese but i still have the #error
message in the subject below: Can someone help me: questions and
replies are separted by -----

-------My Questions:
I have a form with a field that automatically popluates with todays
date
called "created date". I have another field on the form called
"Processing
Month1" that I want to update automatically base on a schedule in a
table. I
have an example below:
Form:
Created date- automatically filled
Processing Month1- Need help with this field's coding base on table
info
below.
Table: Name of it is Monthly Schedule
This Table has 3 fields:
Processing Month
Start date
End Date

I want the field in the form, Processing month1, to lookup the
processing
month in the table based on what date is in created date. Say the
created
date is 10-2-04 and the start date in the table= 10-1-04 and the end
date =
10-31-04 and the processing month is OCT. I want the answer to be OCT.
(Also
the Start and End dates are not always a perfect calender month.)

-Oh my table is called mnthly Closing schedules as seen above instead
of
what i had before. The dates are all in short date format except the
Processing month in the table which is in mmm-yy.
--------------------------
Thank you NIKOS for all of your help. I am still getting the #error
message.
I have did a copy paste
=DLookUp("[Processing Month]","Mnthly Closing Schedules","[Start Date]
<=#"
& Format(Date(),"mm/dd/yyyy") & "# And [End Date]>=#" &
Format(Date(),"mm/dd/yyyy") & "#")

Both my start and end date in the Table of Mnthly Closing Schedules in
in
short date. The Processing Month in table is in date form but
formatted as
mmm-yy. Is this the problem?
-Oh my table is called mnthly Closing schedules as seen above instead
of
what i had before. The dates are all in short date format except the
Processing month in the table which is in mmm-yy.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

I don't know if you have managed to find a solution yet, but have you tried using:
=DLookUp("[Processing Month]","Monthly Schedule","(([Start Date] <= " & Date() & ") And ([End Date] >= " & Date() & "))")

- I have found that Access is generally smart enough to work out what the date is regardless of how it is formatted, so long as it recognises it is a date.

The only other time I have found this #error is when one of the field or table names are spelt wrong.

Does the perfect calendar month relate to that date range?
I.e. If would the Processing month for Nov 17 be November? If so, why not just use: Format(date(),"mmmm") which will give you the relevant month.

If the months are out of synch by a couple of months (financial months?), then you could work in a dateadd function:
Format(DateAdd("m",3,Date()),"mmmm")
This will add three months to the current date, and return "February".

Then you won't even need the DLookup function?

Hope that helps.

Regards,

Fraser.

-----------
sh*******@aol.com (Sue) wrote:
I sent an e-mail below and got a responese but i still have the #error
message in the subject below: Can someone help me: questions and
replies are separted by -----

-------My Questions:
I have a form with a field that automatically popluates with todays
date
called "created date". I have another field on the form called
"Processing
Month1" that I want to update automatically base on a schedule in a
table. I
have an example below:
Form:
Created date- automatically filled
Processing Month1- Need help with this field's coding base on table
info
below.
Table: Name of it is Monthly Schedule
This Table has 3 fields:
Processing Month
Start date
End Date

I want the field in the form, Processing month1, to lookup the
processing
month in the table based on what date is in created date. Say the
created
date is 10-2-04 and the start date in the table= 10-1-04 and the end
date =
10-31-04 and the processing month is OCT. I want the answer to be OCT.
(Also
the Start and End dates are not always a perfect calender month.)

-Oh my table is called mnthly Closing schedules as seen above instead
of
what i had before. The dates are all in short date format except the
Processing month in the table which is in mmm-yy.
--------------------------
Thank you NIKOS for all of your help. I am still getting the #error
message.
I have did a copy paste
=DLookUp("[Processing Month]","Mnthly Closing Schedules","[Start Date]
<=#"
& Format(Date(),"mm/dd/yyyy") & "# And [End Date]>=#" &
Format(Date(),"mm/dd/yyyy") & "#")

Both my start and end date in the Table of Mnthly Closing Schedules in
in
short date. The Processing Month in table is in date form but
formatted as
mmm-yy. Is this the problem?
-Oh my table is called mnthly Closing schedules as seen above instead
of
what i had before. The dates are all in short date format except the
Processing month in the table which is in mmm-yy.


Nov 13 '05 #2

P: n/a
First, never use the DLookup functions ... they wind up being a
headache. But the easiest way to do what you're after is to change
the text box on your form to a combobox or a listbox, then set its
RowSource property. You'll build a query to find the data you're
looking for, and you can pull in other data if you want to display it,
so it makes more sense.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.