DLookup #Name? Error

[Jani]

I'm trying to have a text field default to a mainframe date format which is
text YYYYMM. If the current month is 3/1/2008 than the default MF date would
be 200802, if the current month is 4/1/2008, than the default date would be
200803, and so on. My table is just two fields: one with the Mainframe date
[CASDate]and one with the current month date [Current]. The formula below is
giving me a #Name? error and I can't figure out why as the fields are named
correctly: CASDate is the mainframe date; dbo_uDateTable is the table that
has the two fields, Current is the current date (3/1/2008 and so on) and the
DateSerial... provides the current month/year. I would appreciate it if
someone would tell me what I've done wrong. Thanks so much! Jani

=DLookUp([CASDate],[dbo_uDateTable],[Current]=DateSerial(Year(Date()),Month(Date()),1))

 

[J_Goddard via AccessMonster.com]

Hi -

Why do you need a table to keep that data? The result you want is based on
the current date - Date() - but with the previous month (if I read your
question correctly).

Try this:

=format(DateSerial(Year(Date()),Month(Date())-1,1)),"yyyymm")

John

I'm trying to have a text field default to a mainframe date format which is
text YYYYMM. If the current month is 3/1/2008 than the default MF date would
be 200802, if the current month is 4/1/2008, than the default date would be
200803, and so on. My table is just two fields: one with the Mainframe date
[CASDate]and one with the current month date [Current]. The formula below is
giving me a #Name? error and I can't figure out why as the fields are named
correctly: CASDate is the mainframe date; dbo_uDateTable is the table that
has the two fields, Current is the current date (3/1/2008 and so on) and the
DateSerial... provides the current month/year. I would appreciate it if
someone would tell me what I've done wrong. Thanks so much! Jani

=DLookUp([CASDate],[dbo_uDateTable],[Current]=DateSerial(Year(Date()),Month(Date()),1))

 

[Jani]

I've inserted your suggestion and it doesn't like it... reverts back to my
code. I also tried inserting a new text box which didn't work - message is
displayed 'invalid syntax.' I thought I needed a table (and I should have
included this in my original posting) because CASDate is a text field. Any
other thoughts?

Hi -

Why do you need a table to keep that data? The result you want is based on
the current date - Date() - but with the previous month (if I read your
question correctly).

Try this:

=format(DateSerial(Year(Date()),Month(Date())-1,1)),"yyyymm")

John

I'm trying to have a text field default to a mainframe date format which is
text YYYYMM. If the current month is 3/1/2008 than the default MF date would
be 200802, if the current month is 4/1/2008, than the default date would be
200803, and so on. My table is just two fields: one with the Mainframe date
[CASDate]and one with the current month date [Current]. The formula below is
giving me a #Name? error and I can't figure out why as the fields are named
correctly: CASDate is the mainframe date; dbo_uDateTable is the table that
has the two fields, Current is the current date (3/1/2008 and so on) and the
DateSerial... provides the current month/year. I would appreciate it if
someone would tell me what I've done wrong. Thanks so much! Jani

=DLookUp([CASDate],[dbo_uDateTable],[Current]=DateSerial(Year(Date()),Month(Date()),1))

 

[J_Goddard via AccessMonster.com]

Sorry - too many closing brackets.

See if this works:

=format(DateSerial(Year(Date()),Month(Date())-1,1),"yyyymm")

John

I've inserted your suggestion and it doesn't like it... reverts back to my
code. I also tried inserting a new text box which didn't work - message is
displayed 'invalid syntax.' I thought I needed a table (and I should have
included this in my original posting) because CASDate is a text field. Any
other thoughts?

Hi -

[quoted text clipped - 20 lines]

=DLookUp([CASDate],[dbo_uDateTable],[Current]=DateSerial(Year(Date()),Month(Date()),1))

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Jani]

Thanks, John - worked perfectly! jms

Sorry - too many closing brackets.

See if this works:

=format(DateSerial(Year(Date()),Month(Date())-1,1),"yyyymm")

John

I've inserted your suggestion and it doesn't like it... reverts back to my
code. I also tried inserting a new text box which didn't work - message is
displayed 'invalid syntax.' I thought I needed a table (and I should have
included this in my original posting) because CASDate is a text field. Any
other thoughts?

Hi -

[quoted text clipped - 20 lines]

=DLookUp([CASDate],[dbo_uDateTable],[Current]=DateSerial(Year(Date()),Month(Date()),1))

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com