Please help with Type Mismatch Error

  • Thread starter Thread starter swichman
  • Start date Start date
S

swichman

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
 
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??
 
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??
Click to expand...

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.
 
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")
 
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
Click to expand...

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

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.
 
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 said:
When I *eventually* (gasp) introduce myself on the mvp newsgroup, I'll
be asking everyone else what they use.
Click to expand...

Come on in, the water's fine! Most of us don't bite.
 
<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")


Tina,
Known to me as hth. Sorry I forgot my computer acronym book at work.
<Sigh> How do you think I feel?? I have been messing around with this
for the better part of a day. Your message states that you added an
ampersand and moved a double quote. I think you added a double quote.
My original formula was "MonthID = " & Me.ID You told me to try
"MonthID = " & Me.ID & ". Three doulbe quotes?? I am telling you
access gives me a syntax error. I am using this as a default value in
the form but because of the unexpected fact that the statement is not
working I am using VBA to troubleshoot. I hope you can help me with
this problem if you read this. I'll send you a free trip to Tahiti or
something.
TVM (thanks very much)
Sean
 
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.
Click to expand...
Cheers,
TC (MVP Access)
http://tc2.atspace.com
Click to expand...

TC
I've been using Dialog for many years now for newsgroups (as well as
e-mail).
http://www.40tude.com/dialog/
 
Your message states that you added an
ampersand and moved a double quote. I think you added a double quote.
My original formula was "MonthID = " & Me.ID You told me to try
"MonthID = " & Me.ID & ". Three doulbe quotes??
Click to expand...

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

above is what i originally posted. not a third double quote - unless you
take it out of context. however, i did find an error in the spacing. here's
the correction:

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

note the space between the double quote and the And, on the second line. my
previous comments about removing the line break character and placing the
entire expression on one line, if it's used directly in the form, still hold
true.

hth
 
Post the exact line of code that is getting the syntax error.

TC,
I just placed this in the on click event of a button to test the
formula. This gives me a Syntax error:
Me.Text97 = DLookup("[CraftAH]", "[HoursTracking]", "MID = " & Me.ID &
" And "CompanyID = 'SLM'")
This gives me a Type Mismatch Error:
Me.Text97 = DLookup("[CraftAH]", "[HoursTracking]", "MID = " & Me.ID
And "CompanyID = 'SLM'")
Thanks,
Sean
 
DLookup("CraftAH", "HoursTracking", "MonthID = " & Me.ID _
& "And CompanyID = 'SLM'")

above is what i originally posted. not a third double quote - unless
you
take it out of context. however, i did find an error in the spacing.
here's
the correction:


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


note the space between the double quote and the And, on the second
line. my
previous comments about removing the line break character and placing
the
entire expression on one line, if it's used directly in the form, still
hold
true.


hth


Tina
AKA hth
Thankyou so much for your help. I guess sometimes things need to be
spelled out several times for me to understand. Where do you want me
to send your free trip to Tahiti??
Thanks again.
Sean
 
This gives me a Syntax error:
Me.Text97 = DLookup("[CraftAH]", "[HoursTracking]", "MID = " & Me.ID &
" And "CompanyID = 'SLM'")
Click to expand...

I'm not surprised :-)

Do you mean to find the record where MID is the value of Me.ID, and
CompanyID is the fixed value 'SLM'? If so, this is what you need. Don't
retype it: cut & paste, to make sure it is right. I've coded it on
several lines, to make it look clearer.

Me.Text97 = DLookup( _
"[CraftAH]", _
"[HoursTracking]", _
"[MID] = " & Me.ID & " And [CompanyID] = 'SLM'")


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