Another DLookup question

[Tony Williams]

On my form I have a control txtletter2 that I want to populate from a value
of a field in a seperate table tblcompliantnbr. The table has 2 fields
txtyear and txtletter with A for 2005, B for 2006 etc. I have another control
on my form that is a date called txtdaterecd. I want txtletter2 to be
populated with the value of txtletter appropriate for the year value of
txtdaterecd. I've tried using this Dlookup
=DLookUp("[txtletter]","[tblcomplaintnbr]","[txtyear]=Forms![frmcomplaint].Year([txtdatered])")

But I get #Error

Where am I going wrong?
Thanks
Tony

 

[Arvin Meyer [MVP]]

I'd try adding a new textbox named txtYear and setting it = to:

=Year([txtdatered])

Then:

DLookup("ID", "tblcomplaintnbr", "txtyear = '" &
Forms![frmcomplaint]![txtYear] & "'")

 

[Tony Williams]

Thanks Arvin. Year no problem but I get #Error on second step. I've changed
ID to name of letter field and this is my Dlookup
=DLookUp("txtletter","tblComplaintnbr","txtYear = '" &
Forms!frmcomplaint!txtYear & "'") Notice Access has removed the square
brackets from around the form control
Any ideas?
Thanks
Tony

I'd try adding a new textbox named txtYear and setting it = to:

=Year([txtdatered])

Then:

DLookup("ID", "tblcomplaintnbr", "txtyear = '" &
Forms![frmcomplaint]![txtYear] & "'")

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On my form I have a control txtletter2 that I want to populate from a
value
of a field in a seperate table tblcompliantnbr. The table has 2 fields
txtyear and txtletter with A for 2005, B for 2006 etc. I have another
control
on my form that is a date called txtdaterecd. I want txtletter2 to be
populated with the value of txtletter appropriate for the year value of
txtdaterecd. I've tried using this Dlookup
=DLookUp("[txtletter]","[tblcomplaintnbr]","[txtyear]=Forms![frmcomplaint].Year([txtdatered])")

But I get #Error

Where am I going wrong?
Thanks
Tony

 

[Tony Williams]

Arvin got it to work with this
=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms![frmcomplaint].[txtYear]")
Thanks for the pointer
Tony

I'd try adding a new textbox named txtYear and setting it = to:

=Year([txtdatered])

Then:

DLookup("ID", "tblcomplaintnbr", "txtyear = '" &
Forms![frmcomplaint]![txtYear] & "'")

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On my form I have a control txtletter2 that I want to populate from a
value
of a field in a seperate table tblcompliantnbr. The table has 2 fields
txtyear and txtletter with A for 2005, B for 2006 etc. I have another
control
on my form that is a date called txtdaterecd. I want txtletter2 to be
populated with the value of txtletter appropriate for the year value of
txtdaterecd. I've tried using this Dlookup
=DLookUp("[txtletter]","[tblcomplaintnbr]","[txtyear]=Forms![frmcomplaint].Year([txtdatered])")

But I get #Error

Where am I going wrong?
Thanks
Tony

 

[Arvin Meyer [MVP]]

I added txtYear twice. The new textbox should have a different name.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin got it to work with this
=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms![frmcomplaint].[txtYear]")
Thanks for the pointer
Tony

I'd try adding a new textbox named txtYear and setting it = to:

=Year([txtdatered])

Then:

DLookup("ID", "tblcomplaintnbr", "txtyear = '" &
Forms![frmcomplaint]![txtYear] & "'")

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On my form I have a control txtletter2 that I want to populate from a
value
of a field in a seperate table tblcompliantnbr. The table has 2 fields
txtyear and txtletter with A for 2005, B for 2006 etc. I have another
control
on my form that is a date called txtdaterecd. I want txtletter2 to be
populated with the value of txtletter appropriate for the year value of
txtdaterecd. I've tried using this Dlookup
=DLookUp("[txtletter]","[tblcomplaintnbr]","[txtyear]=Forms![frmcomplaint].Year([txtdatered])")

But I get #Error

Where am I going wrong?
Thanks
Tony

 

[Guest]

if you get #Error then you shoudl move to Access Data Projects and SQL
Server

Thanks Arvin. Year no problem but I get #Error on second step. I've
changed ID to name of letter field and this is my Dlookup
=DLookUp("txtletter","tblComplaintnbr","txtYear = '" &
Forms!frmcomplaint!txtYear & "'") Notice Access has removed the square
brackets from around the form control
Any ideas?
Thanks
Tony

I'd try adding a new textbox named txtYear and setting it = to:

=Year([txtdatered])

Then:

DLookup("ID", "tblcomplaintnbr", "txtyear = '" &
Forms![frmcomplaint]![txtYear] & "'")

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On my form I have a control txtletter2 that I want to populate from a
value
of a field in a seperate table tblcompliantnbr. The table has 2 fields
txtyear and txtletter with A for 2005, B for 2006 etc. I have another
control
on my form that is a date called txtdaterecd. I want txtletter2 to be
populated with the value of txtletter appropriate for the year value of
txtdaterecd. I've tried using this Dlookup
=DLookUp("[txtletter]","[tblcomplaintnbr]","[txtyear]=Forms![frmcomplaint].Year([txtdatered])")

But I get #Error

Where am I going wrong?
Thanks
Tony

 

[Guest]

#Error means that you've got too much data for Access database

Thanks Arvin. Year no problem but I get #Error on second step. I've
changed ID to name of letter field and this is my Dlookup
=DLookUp("txtletter","tblComplaintnbr","txtYear = '" &
Forms!frmcomplaint!txtYear & "'") Notice Access has removed the square
brackets from around the form control
Any ideas?
Thanks
Tony

I'd try adding a new textbox named txtYear and setting it = to:

=Year([txtdatered])

Then:

DLookup("ID", "tblcomplaintnbr", "txtyear = '" &
Forms![frmcomplaint]![txtYear] & "'")

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On my form I have a control txtletter2 that I want to populate from a
value
of a field in a seperate table tblcompliantnbr. The table has 2 fields
txtyear and txtletter with A for 2005, B for 2006 etc. I have another
control
on my form that is a date called txtdaterecd. I want txtletter2 to be
populated with the value of txtletter appropriate for the year value of
txtdaterecd. I've tried using this Dlookup
=DLookUp("[txtletter]","[tblcomplaintnbr]","[txtyear]=Forms![frmcomplaint].Year([txtdatered])")

But I get #Error

Where am I going wrong?
Thanks
Tony