Another DLookup question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
I'd try adding a new textbox named txtYear and setting it = to:

=Year([txtdatered])

Then:

DLookup("ID", "tblcomplaintnbr", "txtyear = '" &
Forms![frmcomplaint]![txtYear] & "'")
 
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
Arvin Meyer said:
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

Tony Williams said:
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
Click to expand...
Click to expand...
 
Arvin got it to work with this
=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms![frmcomplaint].[txtYear]")
Thanks for the pointer
Tony
Arvin Meyer said:
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

Tony Williams said:
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
Click to expand...
Click to expand...
 
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

Tony Williams said:
Arvin got it to work with this
=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms![frmcomplaint].[txtYear]")
Thanks for the pointer
Tony
Arvin Meyer said:
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

Tony Williams said:
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
Click to expand...
Click to expand...
Click to expand...
 
if you get #Error then you shoudl move to Access Data Projects and SQL
Server



Tony Williams said:
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
Arvin Meyer said:
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

Tony Williams said:
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
Click to expand...
Click to expand...
Click to expand...
 
#Error means that you've got too much data for Access database


Tony Williams said:
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
Arvin Meyer said:
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

Tony Williams said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top