DLookUp in a Query

[Guest]

I am getting a #Error message in the AMOD field of my query QUERY1:

AMOD: iif(IsNull([MSIZE]),"N/A",DLookUp("MOD","QUERY2","MODID= " & [MSIZE]))

MSIZE is a lngInt field located in 'Query1'. Sometimes it has a Null value.

MOD is the value I want to retrieve from QUERY2.
MOD is a field that has the expression MOD:[WIDTH] & " ft x " & [LENGTH] & "
ft"

However, if I substitute MOD with WIDTH in the DLookUp, it works. But I need
to have the text result instead of a number.

Any ideas why this doesn't work?

 

[Ken Snell \(MVP\)]

Post the full SQL statements of the QUERY2 and QUERY1.

 

[Guest]

For QUERY1 heres my SQL:
SELECT Module.ModID, [Width] & " ft x " & [Length] & " ft" AS Mod, _
Module.Width, Module.Length, [Width] * [Length] AS ModArea, FROM [Module];

For QUERY2 heres my SQL:
SELECT Space.DataRmID, IIf([GModArea]=0,"N/A",[AreaTot]/[GModArea]) AS _
CModNo, IIf(IsNull([MSize]),0,DLookUp("ModArea","QModule","ModID= " & _
[MSize])) AS GModArea, IIf(IsNull([MSize]),"N/A",DLookUp _
("Mod","QModule","ModID = " & [MSize])) AS AMod, Space.AreaTot _
FROM Space;

Post the full SQL statements of the QUERY2 and QUERY1.

--

Ken Snell
<MS ACCESS MVP>

I am getting a #Error message in the AMOD field of my query QUERY1:

AMOD: iif(IsNull([MSIZE]),"N/A",DLookUp("MOD","QUERY2","MODID= " &
[MSIZE]))

MSIZE is a lngInt field located in 'Query1'. Sometimes it has a Null
value.

MOD is the value I want to retrieve from QUERY2.
MOD is a field that has the expression MOD:[WIDTH] & " ft x " & [LENGTH] &
"
ft"

However, if I substitute MOD with WIDTH in the DLookUp, it works. But I
need
to have the text result instead of a number.

Any ideas why this doesn't work?

 

[Ken Snell \(MVP\)]

MOD is an mathematical operator and should not be used as the name of a
field or alias. Similarly, MODULE is a reserved word in Jet, and should not
be used as the name of a table.

Change MOD in QUERY1 and QUERY2 to ModString and try it. I also strongly
suggest that the name of the table be changed from MODULE to something else.


See these Knowledge Base articles for more information about reserved words
and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

For QUERY1 heres my SQL:
SELECT Module.ModID, [Width] & " ft x " & [Length] & " ft" AS Mod, _
Module.Width, Module.Length, [Width] * [Length] AS ModArea, FROM [Module];

For QUERY2 heres my SQL:
SELECT Space.DataRmID, IIf([GModArea]=0,"N/A",[AreaTot]/[GModArea]) AS _
CModNo, IIf(IsNull([MSize]),0,DLookUp("ModArea","QModule","ModID= " & _
[MSize])) AS GModArea, IIf(IsNull([MSize]),"N/A",DLookUp _
("Mod","QModule","ModID = " & [MSize])) AS AMod, Space.AreaTot _
FROM Space;

Post the full SQL statements of the QUERY2 and QUERY1.

--

Ken Snell
<MS ACCESS MVP>

I am getting a #Error message in the AMOD field of my query QUERY1:

AMOD: iif(IsNull([MSIZE]),"N/A",DLookUp("MOD","QUERY2","MODID= " &
[MSIZE]))

MSIZE is a lngInt field located in 'Query1'. Sometimes it has a Null
value.

MOD is the value I want to retrieve from QUERY2.
MOD is a field that has the expression MOD:[WIDTH] & " ft x " &
[LENGTH] &
"
ft"

However, if I substitute MOD with WIDTH in the DLookUp, it works. But I
need
to have the text result instead of a number.

Any ideas why this doesn't work?

 

[Guest]

Thank you pointing it out. I changed the name of the table and all references
in the database. The DLookUp code now works without any glitches.

Martin

MOD is an mathematical operator and should not be used as the name of a
field or alias. Similarly, MODULE is a reserved word in Jet, and should not
be used as the name of a table.

Change MOD in QUERY1 and QUERY2 to ModString and try it. I also strongly
suggest that the name of the table be changed from MODULE to something else.


See these Knowledge Base articles for more information about reserved words
and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

For QUERY1 heres my SQL:
SELECT Module.ModID, [Width] & " ft x " & [Length] & " ft" AS Mod, _
Module.Width, Module.Length, [Width] * [Length] AS ModArea, FROM [Module];

For QUERY2 heres my SQL:
SELECT Space.DataRmID, IIf([GModArea]=0,"N/A",[AreaTot]/[GModArea]) AS _
CModNo, IIf(IsNull([MSize]),0,DLookUp("ModArea","QModule","ModID= " & _
[MSize])) AS GModArea, IIf(IsNull([MSize]),"N/A",DLookUp _
("Mod","QModule","ModID = " & [MSize])) AS AMod, Space.AreaTot _
FROM Space;

Post the full SQL statements of the QUERY2 and QUERY1.

--

Ken Snell
<MS ACCESS MVP>


I am getting a #Error message in the AMOD field of my query QUERY1:

AMOD: iif(IsNull([MSIZE]),"N/A",DLookUp("MOD","QUERY2","MODID= " &
[MSIZE]))

MSIZE is a lngInt field located in 'Query1'. Sometimes it has a Null
value.

MOD is the value I want to retrieve from QUERY2.
MOD is a field that has the expression MOD:[WIDTH] & " ft x " &
[LENGTH] &
"
ft"

However, if I substitute MOD with WIDTH in the DLookUp, it works. But I
need
to have the text result instead of a number.

Any ideas why this doesn't work?