This Expression is Typed Incorrectly

[MattBaldry]

Hi all,

I am getting an error message on a query when I try to filter on a ListBox. Criteria is as below.

[Forms]![frmBarcodeProductionManagement]![lstBarcodeWorksOrderOperationList]

Bound Column is 1 and the field is from an SQL table, WorksOrderOperationID and the field I am trying to filter is the same name and data type, but a different table.

It was working fine, but I added a new listbox to my form, and it stopped working. Anyone had a similar issue?

~Matt

 

[Gasman]

Might want to show the WHOLE expression?

 

[MattBaldry]

Might want to show the WHOLE expression?

My issue here is, that it was working. But now it is just not. I am just trying to filter a based on an ID number. There is nothing complex about it, despite what access is telling me.

Below is the full SQL of the query.

SELECT dbo_WorksOrderOperationBooking.WorksOrderOperationID, dbo_WorksOrderOperationBooking.WorksOrderOperationBookingID, dbo_WorksOrderOperationBooking.EnteredBy, dbo_WorksOrderOperationBooking.StartTime, dbo_WorksOrderOperationBooking.FinishTime, dbo_WorksOrderOperationBooking.QtyCompleted
FROM dbo_WorksOrderOperationBooking
WHERE (((dbo_WorksOrderOperationBooking.WorksOrderOperationID)=[Forms]![frmBarcodeProductionManagement]![lstBarcodeWorksOrderOperationList]))
ORDER BY dbo_WorksOrderOperationBooking.StartTime;

There are other filters on this form using the same format, but they work. It is doing my head, as it was working fine. I changed nothing with this query at all, but it is suddenly not working.

~Matt

 

[MattBaldry]

Update: If I change it to Like [Forms]![frmBarcodeProductionManagement]![txtBarcodeWorksOrderOperationFilter], it works.....

~Matt

 

[Gasman]

I did not think Like worked unless an * was used?

 

[MattBaldry]

I did not think Like worked unless an * was used?

It never has for me before. But somehow this is the only way I can get this criteria to work.

~Matt

 

[Gasman]

Are you sure it is this query?
Copy all that and try it in the query sql window and run from there.

 

[MattBaldry]

SELECT dbo_WorksOrderOperationBooking.EnteredBy AS Employee, dbo_WorksOrderOperationBooking.StartTime AS Start, dbo_WorksOrderOperationBooking.FinishTime AS [End], dbo_WorksOrderOperationBooking.QtyCompleted AS Quantity
FROM dbo_WorksOrderOperationBooking
GROUP BY dbo_WorksOrderOperationBooking.EnteredBy, dbo_WorksOrderOperationBooking.StartTime, dbo_WorksOrderOperationBooking.FinishTime, dbo_WorksOrderOperationBooking.QtyCompleted, dbo_WorksOrderOperationBooking.WorksOrderOperationID
HAVING (((dbo_WorksOrderOperationBooking.WorksOrderOperationID) Like [Forms]![frmBarcodeProductionManagement]![lstBarcodeWorksOrderOperationList]))
ORDER BY dbo_WorksOrderOperationBooking.StartTime;

It has changed the Where to Having. I have never seen this before....but it still works.

~Matt

 

[CJ_London]

I did not think Like worked unless an * was used?

Like will work without a * and is almost equivalent to =, but works differently

Like matches on a char by char basis and is for strings only, whilst = matches on the whole value (string or number). So Like is potentially a little bit slower

Don't believe it is relevant in Access sql or vba but you can get different results in t-sql - for example the "ä" character is equivalent to "ae", so "ä" LIKE "ae" returns false, "ä" = "ae" returns true

 

[MattBaldry]

Like will work without a * and is almost equivalent to =, but works differently

Like matches on a char by char basis and is for strings only, whilst = matches on the whole value (string or number). So Like is potentially a little bit slower

Don't believe it is relevant in Access sql or vba but you can get different results in t-sql - for example the "ä" character is equivalent to "ae", so "ä" LIKE "ae" returns false, "ä" = "ae" returns true

Can you shed any light on why this will not work without the Like in front? The SQL fields are both the same data type and this is the only query that does not work without the Like. But it was working this morning. I changed nothing, but it just stopped working.....

~Matt

 

[CJ_London]

I changed nothing, but it just stopped working.....

If this was working before with = then something will have changed. Short of corruption I can't suggest any reason other than your comment in the first post ' but I added a new listbox to my form, and it stopped working'

'ID' implies a number, listbox values are displayed as text, so without knowing the datatypes, whether this is the new listbox, or another.....

 

[jdraw]

Matt,
Post some code or a copy of your database with only enough data to show the issue. Also, provide instructions to run a query or code to highlight the issue. The more focus you can provide, the more likely you are to get focused responses.

 

[Pat Hartman]

The fact that LIKE works but = does not, points to a data type conflict.

The two relational operators are not equivalent though. LIKE will probably cause a full table scan whereas = will use any available indexes and so the = will be significantly more efficient the larger your table becomes.

 

[CJ_London]

LIKE will probably cause a full table scan

sorry that is not correct, assuming there is an index and an initial * is not used, Like will make use of indexes.

Per my post #9 - Like will do a char by char evaluation rather than the whole value used with =, so might be a bit slower since it will make 'x' number of comparisons rather than one.

 

[GPGeorge]

My guess as to one possible reason for the problem is the presence of leading or trailing spaces in the field being evaluated. Don't ask me why I might think that is a possibility; it wasn't my fault. Ctl-C did it on purpose to annoy me.

 

[MattBaldry]

Hi all,

The 3 tables I am using are from an external source, CIM50. I cannot change the field types at all. Please see below

dbo.WorksOrderOperation

Column Name	Data Type	Allow Nulls
WorksOrderOperationID	bigint	False

dbo.WorksOrderOperationBooking

Column Name	Data Type	Allow Nulls
WorksOrderOperationID	bigint	True

dbo.WorksOrderOperationResource

Column Name	Data Type	Allow Nulls
WorksOrderOperationID	bigint	True

I can link the Resource table ID to the Operation table ID without any issues. It is the Booking table ID to the Operation table ID. Just will not work.

Despite the Allow Nulls in 2 of the tables, there are actually no blank fields in the data at all. And there are no leading or trailing spaces on any of the data.

All 3 tables are populated with data at the same point from the software in this order Operation > OperationResource > OperationBooking

Just has me stumped. When I logged on first thing this morning, I removed the Like from the criteria and it worked first time. Then failed after closing and re-opening the form. Add the Like, and it works again.

~Matt

 

[Isaac]

What if it's really something else generating the error? How certain are you that you even have the correct query & expression that it's having trouble with?
Access, like many programs, is notorious for giving poor error messages. Like "If without end" when really what you forgot is "Next, or vice versa.