Dlookup() syntax

[TMC]

Hello:

I am having a problem with the syntax for dlookup.
I am trying to find the TARE (empty) weight of a vehicle in a table called
TBL_MSW_VEHICLE by searching on the ACCT_NUM and VEH_ID.


TBL_MSW_VEHICLE Fields:

ACCT_NUM - Number
VEH_ID - Text
TARE - Number

FORM:

CMBO_ACCOUNT - Account combo box in form
CMBO_VEHICLE - Vehicle combo box in form
FLD_TARE - Vehicle empty weight in form

CODE:

Fld_Tare = DLookup("[tare]", "[tbl_Msw_Vehicle]", "[acct_num] =" &
[Cmbo_Account] & " And [VEH_ID] = " & [cmbo_vehicle])

The code works in this format if I search only by Vehicle or by Account but
when I merge the code together with the AND statement I get the error:

Run-Time error '3464':
Data type mismatch in criteria expression

This whole & "" ' syntax totally confuses me.

Any help would be greatly appreciated. Also is there a location (Ie.
sight/doc) that gives a reasonable explanation on this syntax.

Thanks
TMC

 

[Dirk Goldgar]

Hello:

I am having a problem with the syntax for dlookup.
I am trying to find the TARE (empty) weight of a vehicle in a table called
TBL_MSW_VEHICLE by searching on the ACCT_NUM and VEH_ID.


TBL_MSW_VEHICLE Fields:

ACCT_NUM - Number
VEH_ID - Text
TARE - Number

FORM:

CMBO_ACCOUNT - Account combo box in form
CMBO_VEHICLE - Vehicle combo box in form
FLD_TARE - Vehicle empty weight in form

CODE:

Fld_Tare = DLookup("[tare]", "[tbl_Msw_Vehicle]", "[acct_num] =" &
[Cmbo_Account] & " And [VEH_ID] = " & [cmbo_vehicle])

The code works in this format if I search only by Vehicle or by Account
but
when I merge the code together with the AND statement I get the error:

From the look of it, I wouldn't expect it to work for any search for a
VEH_ID, since that's a text field. So maybe I don't understand something,
but based on what you've posted, try this:

Fld_Tare = DLookup("tare", "tbl_Msw_Vehicle", _
"acct_num =" & Me!Cmbo_Account & _
" And VEH_ID = '" & Me!cmbo_vehicle & "'")

I've embedded single-quotes around the vehicle ID, removed the square
brackets which aren't needed in this case (since there are no invalid
characters in any of the names), and prefixed the Me! qualifier on the
control names that I assume are on the form behind which this code is
running. The latter two changes are not required, but conform to my idea of
best practice.

This whole & "" ' syntax totally confuses me.

Any help would be greatly appreciated. Also is there a location (Ie.
sight/doc) that gives a reasonable explanation on this syntax.

I don't know. There may be a tutorial site that has such a thing. You may
want to poke around on Allen Browne's website, starting at his Tips page
(http://www.allenbrowne.com/tips.html).

 

[BruceM]

Dirk suggested Allen Browne's site, which is a fine resource. To be a bit
more specific about the use of quotes, try:
http://allenbrowne.com/casu-17.html
As I recall, Allen tends to stay away from single quotes (apostrophe on the
keyboard), preferring instead a pair of double quotes(""), but that choice
depends in part on whether there may be apostrophes or quotes in the text.
In my experience apostrophes are more common, especially if the text
includes names, so I too tend toward the use of a pair of double quotes.

 

[TMC]

Thanks for your quick reply Dirk:

That works great. I don't know why I struggle with this syntax. I'll check
out Allen's site for any info.

Again, I appreciate your expertise.

TNC