Lookup Table info from a from

[Guest]

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.)
Thanks
Sue

 

[Nikos Yannacopoulos]

Sue,

Put the following expression in the Processing Month control's Default value
property in the form's design:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" & Date()
& "# And [End Date] >= #" & Date() & "#")
(watch out for wrapping, the whole expression is in one line)

In case your system date format is different to the standard US format
(month/day/year), then you will need to account for that in the expression
in order to get the correct result:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" &
Format(Date(),"mm/dd/yyyy") & "# And [End Date] >= #" &
Format(Date(),"mm/dd/yyyy") & "#")
(again, all in one line)

HTH,
Nikos

 

[Guest]

Thanks for your reply- I tried this and now I'm getting a #Error Message. The
code i wrote is below:

=DLookUp("[Processing Month]","Mnthly Closing Schedules","[Start Date] <=" &
Format(Date(),"mm/dd/yyyy") & "# And [End Date]>=#" &
Format(Date(),"mm/dd/yyyy") & "#")

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

Sue,

Put the following expression in the Processing Month control's Default value
property in the form's design:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" & Date()
& "# And [End Date] >= #" & Date() & "#")
(watch out for wrapping, the whole expression is in one line)

In case your system date format is different to the standard US format
(month/day/year), then you will need to account for that in the expression
in order to get the correct result:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" &
Format(Date(),"mm/dd/yyyy") & "# And [End Date] >= #" &
Format(Date(),"mm/dd/yyyy") & "#")
(again, all in one line)

HTH,
Nikos

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.)
Thanks
Sue

 

[Nikos Yannacopoulos]

Sue,

If this is a copy-and-paste from the expression on your form, then the
problem is simply that you missed out a hash (#)! Where it's currently:
.... "[Start Date] <=" & ...
it should be:
.... "[Start Date] <=#" & ...

HTH,
Nikos

Thanks for your reply- I tried this and now I'm getting a #Error Message. The
code i wrote is below:

=DLookUp("[Processing Month]","Mnthly Closing Schedules","[Start Date] <=" &
Format(Date(),"mm/dd/yyyy") & "# And [End Date]>=#" &
Format(Date(),"mm/dd/yyyy") & "#")

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

Sue,

Put the following expression in the Processing Month control's Default value
property in the form's design:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" & Date()
& "# And [End Date] >= #" & Date() & "#")
(watch out for wrapping, the whole expression is in one line)

In case your system date format is different to the standard US format
(month/day/year), then you will need to account for that in the expression
in order to get the correct result:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" &
Format(Date(),"mm/dd/yyyy") & "# And [End Date] >= #" &
Format(Date(),"mm/dd/yyyy") & "#")
(again, all in one line)

HTH,
Nikos

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 ta

ble.
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.)
Thanks
Sue

 

[Guest]

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?

Sue,

If this is a copy-and-paste from the expression on your form, then the
problem is simply that you missed out a hash (#)! Where it's currently:
.... "[Start Date] <=" & ...
it should be:
.... "[Start Date] <=#" & ...

HTH,
Nikos

Thanks for your reply- I tried this and now I'm getting a #Error Message. The
code i wrote is below:

=DLookUp("[Processing Month]","Mnthly Closing Schedules","[Start Date] <=" &
Format(Date(),"mm/dd/yyyy") & "# And [End Date]>=#" &
Format(Date(),"mm/dd/yyyy") & "#")

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

Sue,

Put the following expression in the Processing Month control's Default value
property in the form's design:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" & Date()
& "# And [End Date] >= #" & Date() & "#")
(watch out for wrapping, the whole expression is in one line)

In case your system date format is different to the standard US format
(month/day/year), then you will need to account for that in the expression
in order to get the correct result:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" &
Format(Date(),"mm/dd/yyyy") & "# And [End Date] >= #" &
Format(Date(),"mm/dd/yyyy") & "#")
(again, all in one line)

HTH,
Nikos

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 ta ble.
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.)
Thanks
Sue

 

[Nikos Yannacopoulos]

Sue,

This looks OK form a syntax point of view (it's all in one line, right?),
and the field types are OK too. I don't know what else to suggest. If you
want, you are welcome to zip your database and mail it so I can have a look.

Nikos

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?

Sue,

If this is a copy-and-paste from the expression on your form, then the
problem is simply that you missed out a hash (#)! Where it's currently:
.... "[Start Date] <=" & ...
it should be:
.... "[Start Date] <=#" & ...

HTH,
Nikos

Thanks for your reply- I tried this and now I'm getting a #Error

Message.
The

code i wrote is below:

=DLookUp("[Processing Month]","Mnthly Closing Schedules","[Start Date]

<="
&

Format(Date(),"mm/dd/yyyy") & "# And [End Date]>=#" &
Format(Date(),"mm/dd/yyyy") & "#")

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

:

Sue,

Put the following expression in the Processing Month control's

Default
value

property in the form's design:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#"

&
Date()

& "# And [End Date] >= #" & Date() & "#")
(watch out for wrapping, the whole expression is in one line)

In case your system date format is different to the standard US format
(month/day/year), then you will need to account for that in the expression
in order to get the correct result:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" &
Format(Date(),"mm/dd/yyyy") & "# And [End Date] >= #" &
Format(Date(),"mm/dd/yyyy") & "#")
(again, all in one line)

HTH,
Nikos

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 ta
ble.

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.)
Thanks
Sue

 

[Guest]

Sure How do i go about e-mailing to you? Your address?

Sue,

This looks OK form a syntax point of view (it's all in one line, right?),
and the field types are OK too. I don't know what else to suggest. If you
want, you are welcome to zip your database and mail it so I can have a look.

Nikos

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?

Sue,

If this is a copy-and-paste from the expression on your form, then the
problem is simply that you missed out a hash (#)! Where it's currently:
.... "[Start Date] <=" & ...
it should be:
.... "[Start Date] <=#" & ...

HTH,
Nikos

Thanks for your reply- I tried this and now I'm getting a #Error Message.
The
code i wrote is below:

=DLookUp("[Processing Month]","Mnthly Closing Schedules","[Start Date] <="
&
Format(Date(),"mm/dd/yyyy") & "# And [End Date]>=#" &
Format(Date(),"mm/dd/yyyy") & "#")

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

:

Sue,

Put the following expression in the Processing Month control's Default
value
property in the form's design:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" &
Date()
& "# And [End Date] >= #" & Date() & "#")
(watch out for wrapping, the whole expression is in one line)

In case your system date format is different to the standard US format
(month/day/year), then you will need to account for that in the
expression
in order to get the correct result:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date] <=#" &
Format(Date(),"mm/dd/yyyy") & "# And [End Date] >= #" &
Format(Date(),"mm/dd/yyyy") & "#")
(again, all in one line)

HTH,
Nikos

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 ta
ble.
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.)
Thanks
Sue

 

[Nikos Yannacopoulos]

Sue,

If your news client is Micrososft outlook, click Reply on my message and you
will get my address in the To field - just do the obvious. If not, then mail
to: nyannaco REMOVETHIS at in dot gr
Sorry about this, I onve posted the real thing and regretted it bitterly!

I forgot to tell you I have Access 2000, if you are on a later version
convert to 2000 before you send.

Nikos

Sure How do i go about e-mailing to you? Your address?

Sue,

This looks OK form a syntax point of view (it's all in one line, right?),
and the field types are OK too. I don't know what else to suggest. If you
want, you are welcome to zip your database and mail it so I can have a look.

Nikos

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?

:

Sue,

If this is a copy-and-paste from the expression on your form, then the
problem is simply that you missed out a hash (#)! Where it's currently:
.... "[Start Date] <=" & ...
it should be:
.... "[Start Date] <=#" & ...

HTH,
Nikos

Thanks for your reply- I tried this and now I'm getting a #Error Message.
The
code i wrote is below:

=DLookUp("[Processing Month]","Mnthly Closing Schedules","[Start

Date]
<="

&
Format(Date(),"mm/dd/yyyy") & "# And [End Date]>=#" &
Format(Date(),"mm/dd/yyyy") & "#")

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

:

Sue,

Put the following expression in the Processing Month control's Default
value
property in the form's design:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date]

<=#"
&

Date()
& "# And [End Date] >= #" & Date() & "#")
(watch out for wrapping, the whole expression is in one line)

In case your system date format is different to the standard US format
(month/day/year), then you will need to account for that in the
expression
in order to get the correct result:

=DLookUp("[Processing Month]","Monthly Schedule","[Start Date]

<=#"
&

Format(Date(),"mm/dd/yyyy") & "# And [End Date] >= #" &
Format(Date(),"mm/dd/yyyy") & "#")
(again, all in one line)

HTH,
Nikos

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 ta

ble.
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.)
Thanks
Sue