Please help with Type Mismatch Error

I am using a split database
I have the following lookup statement
DLookup("CraftAH", "HoursTracking", "MonthID = " & Me.ID And "CompanyID
= 'SLM'")
The statement hangs up on "MonthID = " & Me.ID
The properties of MonthID (in Table HoursTracking) and ID (in another
Table) are both long integer. ID in the other table is however an
autonumber field. Am I missing something here??? If anybody could
suggest something it would be much appreciated.

looks like the problem is a misplaced double quote and a missing ampersand.
try

DLookup("CraftAH", "HoursTracking", "MonthID = " & Me.ID _
& "And CompanyID = 'SLM'")

hth


wrote in message
ups.com...
I am using a split database
I have the following lookup statement
DLookup("CraftAH", "HoursTracking", "MonthID = " & Me.ID And "CompanyID
= 'SLM'")
The statement hangs up on "MonthID = " & Me.ID
The properties of MonthID (in Table HoursTracking) and ID (in another
Table) are both long integer. ID in the other table is however an
autonumber field. Am I missing something here??? If anybody could
suggest something it would be much appreciated.

Thanks hth,
I tried that and I got syntax error. With number fields isn't the
correct reference "MonthID = "& Me.ID
Whereas if both were Text fields it would be "MonthID = '"&Me.ID&"'".
Any other suggestions??

On 31 Mar 2006 16:29:12 -0800, wrote:

Thanks hth,
I tried that and I got syntax error. With number fields isn't the
correct reference "MonthID = "& Me.ID
Whereas if both were Text fields it would be "MonthID = '"&Me.ID&"'".
Any other suggestions??

1) It's to your benefit (as well as for others) to include the
relevant part of any previous post you are referring to.
This message, by itself, does not give any important information. Only
in regards to your previous message is it relevant.

I'll post here your original message here so others can read it:

I am using a split database
I have the following lookup statement
DLookup("CraftAH", "HoursTracking", "MonthID = " & Me.ID And
"CompanyID = 'SLM'")
The statement hangs up on "MonthID = " & Me.ID
The properties of MonthID (in Table HoursTracking) and ID (in another
Table) are both long integer. ID in the other table is however an
autonumber field. Am I missing something here??? If anybody could
suggest something it would be much appreciated.


What do you mean by "and ID (in another Table) are both long integer"

Both criteria fields must be in the same table.

Even if they were in the same table your error is not in the "MonthID
= "& Me.ID part but what happens immediately after that.

If you place it all on one line, you can use:
DLookup("CraftAH", "HoursTracking", "MonthID = " & Me.ID & " And
CompanyID = 'SLM'")

** BUT ** MonthID and CompanyID must both be fields in the
"HoursTracking" table.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thanks Fred.
I am sorry about not posting my original message but I was under the
impression that if you clicked on the topic you could read back and
review all of the messages relating to that particular topic as I am
fairly new to this whole thing. Let me clarify about my original post
which is as follows:
I am using a split database
I have the following lookup statement
DLookup("CraftAH", "HoursTracking", "MonthID = " & Me.ID And
"CompanyID = 'SLM'")
The statement hangs up on "MonthID = " & Me.ID
The properties of MonthID (in Table HoursTracking) and ID (in another
Table) are both long integer. ID in the other table is however an
autonumber field. Am I missing something here??? If anybody could
suggest something it would be much appreciated.

MonthID and CompanyID are both in the "HoursTracking" Table. ID is a
field in a parent form which is linked to the child form via MonthID.
ID is a field in another table called "HoursMonthly". ID is an
autonumber long integer. The Dlookup gives me a Type Mismatch on
comparing MonthID and ID if I use "MonthID = " & Me.ID and it gives me
a Syntax error if I use
"MonthID = " & Me.ID & ". The function evaluates Me.ID correctly (VBA
assigns the correct value) but fails to use it as a condition in the
Dlookup statement (ie it gives me the Type Mismatch error when
comparing it back to MonthID). I hope this is clear and any help would
be appreciated.
Thanks

sigh yes, the correct syntax for a number data type is
"MonthID = "& Me.ID

but as i said before, the problem appears to be "a misplaced double quote
and a missing ampersand." if you'll look carefully at the expression i
posted, you'll see that i did not change the syntax for the MonthID. rather,
i added an ampersand (&) and moved a double quote. compare my posted
expression and your original posted expression and i'm sure you'll see the
differences.

as far as the "syntax error" goes, i made the assumption that you're using
this expression in a VBA procedure. if instead you're using it directly in a
form, report, or query, then the line-break character ( _ ) is not
recognized. remove it, and put the entire expression all on one line.

hth
(and btw, "hth" stands for "hope this helps")


wrote in message
oups.com...
Thanks hth,
I tried that and I got syntax error. With number fields isn't the
correct reference "MonthID = "& Me.ID
Whereas if both were Text fields it would be "MonthID = '"&Me.ID&"'".
Any other suggestions??

wrote:
Thanks Fred.
I am sorry about not posting my original message but I was under the
impression that if you clicked on the topic you could read back and
review all of the messages relating to that particular topic

It's called a "thread". All of the messages that are posted in reply to
a new topic, or posted in reply to those replies, are collectively
called a thread.

Whenever someone posts a reply to a thread, the post (reply) is
transmitted around the world, between so-called "news servers". The
problem is, that all replies do not get copied to all news servers! And
even when they do, they do not necessarily get copied in the same order
they were sent.

So it's not unusual to see some replies, but not be able to see the
original question! Or to see a reply come first, then the question come
later (though that is unusual).

Hence the fun of trying to communicate effectively through "usenet" -
the name of this medium.

HTH,
TC (MVP Access)
http://tc2.atspace.com

"TC" wrote in message
oups.com...

wrote:
Thanks Fred.
I am sorry about not posting my original message but I was under the
impression that if you clicked on the topic you could read back and
review all of the messages relating to that particular topic

It's called a "thread". All of the messages that are posted in reply to
a new topic, or posted in reply to those replies, are collectively
called a thread.

Whenever someone posts a reply to a thread, the post (reply) is
transmitted around the world, between so-called "news servers". The
problem is, that all replies do not get copied to all news servers! And
even when they do, they do not necessarily get copied in the same order
they were sent.

So it's not unusual to see some replies, but not be able to see the
original question! Or to see a reply come first, then the question come
later (though that is unusual).

Hence the fun of trying to communicate effectively through "usenet" -
the name of this medium.

Another point is that not all newsreaders work the same way. Some let you
retrieve the entire thread that the current article is part of, some don't.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

OT, but, I'm struggling to find a good newsreader. I like the way that
google groups works, but they impose a posting limit, & they won't
increase it for anyone. (I know; I've asked them; they've told me!)
When I *eventually* (gasp) introduce myself on the mvp newsgroup, I'll
be asking everyone else what they use.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

"TC" wrote in message
ups.com
When I *eventually* (gasp) introduce myself on the mvp newsgroup, I'll
be asking everyone else what they use.

Come on in, the water's fine! Most of us don't bite.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)