TYPE MISMATCH IN EXPRESSION ERROR - NOT SURE HOW TO DEBUG...?!?

Experts,

When I try to open a query (aMiscOrderInfo), I get the "Type mismatch in expression!" error...

Here is the SQL:

SELECT tblMiscOrderInfo.MiscOrderID, tblMiscOrderInfo.ORDERDATE, tblMiscOrderInfo.ENDUSER, tblMiscOrderInfo.SHIPVIA, tblMiscOrderInfo.DROPSHIP, tblMiscOrderInfo.SHIPTO, tblMiscOrderInfo.COMPLETE, tblMiscOrderInfo.NOTES, tblLineItems.QTY, tblLineItems.[PART NO], tblLineItems.DESCRIPTION, tblLineItems.PRICE, tblLineItems.FREIGHT, tblLineItems.ORDERED, tblLineItems.BACKORDER, tblLineItems.HERE, tblLineItems.DELIVERED, tblLineItems.CONIFIG, tblLineItems.[SERIAL NO], tblLineItems.[MAKE SALES ORDER], tblLineItems.TRACKING, tblContacts.COMPANY, tblContacts.PHONE, tblContacts.[E-MAIL], tblContacts.PAYMENT, tblContacts.EXP, tblContacts.CODE, tblShipTo.[ADDRESS 01], tblShipTo.[ADDRESS 02], tblShipTo.[ADDRESS 03], tblShipTo.CITY, tblShipTo.STATE, tblShipTo.ZIP
FROM (tblShipTo INNER JOIN (tblMiscOrderInfo INNER JOIN tblLineItems ON tblMiscOrderInfo.MiscOrderID = tblLineItems.[ORDER NO]) ON tblShipTo.[SHIP TO] = tblMiscOrderInfo.SHIPTO) INNER JOIN tblContacts ON tblMiscOrderInfo.ENDUSER = tblContacts.[END USER];

I wish it were giving me a clue where to go, but it's not.

Help!

stevenjoe
stevenjoeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MageDribbleConnect With a Mentor Commented:
like LenaWood said.  Your error is in your WHERE clause.  One of these 3 is causing your error.

tblShipTo.[SHIP TO] = tblMiscOrderInfo.SHIPTO
tblMiscOrderInfo.MiscOrderID = tblLineItems.[ORDER NO]
tblMiscOrderInfo.ENDUSER = tblContacts.[END USER]
0
 
LenaWoodConnect With a Mentor Commented:
What I believe it means is that you are trying to link a text field to a number field.  When you are joining fields together, both fields need to be the same datatype...(both text, both numbers, both dates and etc).

Make sure all your fields you are joining on are of the same type.

Lena
0
 
MageDribbleCommented:
WHERE supposed to be FROM
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
stevenjoeAuthor Commented:
I think Mage got it.  Is there anyway to link an autonumber with a text field...?

stevenjoe
0
 
stevenjoeAuthor Commented:
Or is there an "autotext" field?
0
 
rockiroadsCommented:
for you info, not for points
stevenjoe, if u create a foreign key in another table, it has to be of the same data type
an autonumber is a numeric field, therefore if u want to store that value in another table u have to store it as number

You can create a unique text field if u want, autonumbers are optional, there is no strict rule that u should use it
though its usually better to have a numeric as a primary key

0
 
stevenjoeAuthor Commented:
I changed the data type accross the board for all of my orders from text to number, and then when attempting to run a form, I got a data mismatch on the below:

Private Sub OPEN_ORDERS_AfterUpdate()
    With Me.RecordsetClone
        .FindFirst "[ORDER NO] = '" & Me.OPEN_ORDERS & "'"
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
        If .NoMatch Then
            ' problem. you should probably inform the user
            ' and requery both the form and the listbox
        Else
            ' go to the record:
            Me.Bookmark = .Bookmark
        End If
    End With
End Sub

With the .FindFirst "[ORDER NO] = '" & Me.OPEN_ORDERS & "'" in yellow.

Help!!!

stevenjoe
0
 
LenaWoodCommented:
You need to remove the quotes from your code

.FindFirst "[ORDER NO] = '" & Me.OPEN_ORDERS & "'"

should be

.FindFirst "[ORDER NO] = " & Me.OPEN_ORDERS
0
 
MageDribbleCommented:
Lena should have gotten all the points.  I only showed what she said.
0
 
LenaWoodCommented:
Mage - please don't worry about it.  The end goal is that the author got things working.  Besides, you made what I said clearer :-)

Lena
0
All Courses

From novice to tech pro — start learning today.