?
Solved

Data type mismatch in criteria expression

Posted on 2008-10-19
6
Medium Priority
?
1,062 Views
Last Modified: 2013-11-27
I have a query in which i am trying to select all the fields that are either blank or null. I the Criteria I am using the expression "" Or Is Null to try and accomplish this. I am however getting the "Data type mismatch in criteria expression" I have no idea why this is happening. The field I am running this criteria on is a date time field.
0
Comment
Question by:onaled777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22754585
remove "" , just use Null
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 22755291
Can you give us your query?
0
 

Author Comment

by:onaled777
ID: 22760040
Attached is my query. Thanks again for  your help
SELECT [Main Table].[Unique Number], [Main Table].Season, [Main Table].EBANumber, [Main Table].Customer, [Customer List].Ranking, [Main Table].StyleNumber, [Main Table].FabricNumber, [Main Table].Fabric, [Main Table].Color, [Main Table].Quantity, [Main Table].[DYE Lot], [Main Table].Body, [Main Table].Garment, MacysSKUNumber.MacysSku, [Main Table].[CXL Date], [Main Table].OriginalBaseline, [Main Table].[Revised Export Date], [Main Table].Factory, [Main Table].[Cut Recieved Date], [Main Table].[Cut Released Date], ManufacturingTracking.FabricDispatch, ManufacturingTracking.INCut, ManufacturingTracking.INSew, ManufacturingTracking.INPress, ManufacturingTracking.Audited, ManufacturingTracking.Shipped, ManufacturingTracking.UnitsCut, ManufacturingTracking.UNITSSHIPPED, IIf(IsNull([ManufacturingTracking]![INCut])," ",[ManufacturingTracking]![UnitsCut]-[Main Table]![Quantity]) AS [CUt Vs Ordered], IIf(IsNull([ManufacturingTracking]![Shipped])," ",[ManufacturingTracking]![UNITSSHIPPED]-[Main Table]![Quantity]) AS [Ship Vs Order], ManufacturingTracking.PlantComments, ManufacturingTracking.DailyAction, IIf(IsNull([Main Table]![OriginalBaseline]) Or IsNull([Main Table]![Revised Export Date]),"TBD",IIf([Main Table]![Revised Export Date]=[Main Table]![OriginalBaseline],"Same",IIf([Main Table]![Revised Export Date]<[Main Table]![OriginalBaseline],"Better",IIf([Main Table]![Revised Export Date]>[Main Table]![OriginalBaseline],"Worse"," ")))) AS [Worse or Better than Baseline], IIf([Worse or Better than Baseline]="Worse" Or "Better",[Main Table]![OriginalBaseline]-[Main Table]![Revised Export Date],"") AS [By How Much], [Main Table]![CXL Date]-54 AS [Pattern Deadline], [Main Table].[Pattern#], [Main Table].[PDM Date], [Main Table].[PTTN Date], [Main Table].[Approval Date], [Main Table].Yield, ([Main Table]![Yield]/36)*[Main Table]![Quantity]*1.03 AS [Yards Required], [Main Table].Cafta, [Main Table].[Briggs Direct], [Main Table].[Actual Shipped Date], IIf(IsNull([Main Table]![Actual Shipped Date])," ",[Main Table]![Actual Shipped Date]-[Main Table]![OriginalBaseline]) AS [Difference Vs Baseline], [Main Table].[Actual Shipped Quantity], IIf(IsNull([Main Table]![Actual Shipped Quantity])," ",[Main Table]![Actual Shipped Quantity]-[Main Table]![Quantity]) AS [Difference Vs Order], [Main Table].[DC Location], [Main Table].[Ship Method], [Main Table].[Container #], [Main Table].[Authorization Date], [Main Table].[EBA Mapping]
FROM (([Main Table] LEFT JOIN [Customer List] ON [Main Table].Customer = [Customer List].Customer) LEFT JOIN ManufacturingTracking ON [Main Table].[Unique Number] = ManufacturingTracking.UniqueNumber) LEFT JOIN MacysSKUNumber ON [Main Table].MacysUnique = MacysSKUNumber.MacysUnique
WHERE ((([Main Table].[Actual Shipped Date])="" Or ([Main Table].[Actual Shipped Date]) Is Null));

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 22760161
SELECT [Main Table].[Unique Number], [Main Table].Season, [Main Table].EBANumber, [Main Table].Customer, [Customer List].Ranking, [Main Table].StyleNumber, [Main Table].FabricNumber, [Main Table].Fabric, [Main Table].Color, [Main Table].Quantity, [Main Table].[DYE Lot], [Main Table].Body, [Main Table].Garment, MacysSKUNumber.MacysSku, [Main Table].[CXL Date], [Main Table].OriginalBaseline, [Main Table].[Revised Export Date], [Main Table].Factory, [Main Table].[Cut Recieved Date], [Main Table].[Cut Released Date], ManufacturingTracking.FabricDispatch, ManufacturingTracking.INCut, ManufacturingTracking.INSew, ManufacturingTracking.INPress, ManufacturingTracking.Audited, ManufacturingTracking.Shipped, ManufacturingTracking.UnitsCut, ManufacturingTracking.UNITSSHIPPED, IIf(IsNull([ManufacturingTracking]![INCut])," ",[ManufacturingTracking]![UnitsCut]-[Main Table]![Quantity]) AS [CUt Vs Ordered], IIf(IsNull([ManufacturingTracking]![Shipped])," ",[ManufacturingTracking]![UNITSSHIPPED]-[Main Table]![Quantity]) AS [Ship Vs Order], ManufacturingTracking.PlantComments, ManufacturingTracking.DailyAction, IIf(IsNull([Main Table]![OriginalBaseline]) Or IsNull([Main Table]![Revised Export Date]),"TBD",IIf([Main Table]![Revised Export Date]=[Main Table]![OriginalBaseline],"Same",IIf([Main Table]![Revised Export Date]<[Main Table]![OriginalBaseline],"Better",IIf([Main Table]![Revised Export Date]>[Main Table]![OriginalBaseline],"Worse"," ")))) AS [Worse or Better than Baseline], IIf([Worse or Better than Baseline]="Worse" Or "Better",[Main Table]![OriginalBaseline]-[Main Table]![Revised Export Date],"") AS [By How Much], [Main Table]![CXL Date]-54 AS [Pattern Deadline], [Main Table].[Pattern#], [Main Table].[PDM Date], [Main Table].[PTTN Date], [Main Table].[Approval Date], [Main Table].Yield, ([Main Table]![Yield]/36)*[Main Table]![Quantity]*1.03 AS [Yards Required], [Main Table].Cafta, [Main Table].[Briggs Direct], [Main Table].[Actual Shipped Date], IIf(IsNull([Main Table]![Actual Shipped Date])," ",[Main Table]![Actual Shipped Date]-[Main Table]![OriginalBaseline]) AS [Difference Vs Baseline], [Main Table].[Actual Shipped Quantity], IIf(IsNull([Main Table]![Actual Shipped Quantity])," ",[Main Table]![Actual Shipped Quantity]-[Main Table]![Quantity]) AS [Difference Vs Order], [Main Table].[DC Location], [Main Table].[Ship Method], [Main Table].[Container #], [Main Table].[Authorization Date], [Main Table].[EBA Mapping]
FROM (([Main Table] LEFT JOIN [Customer List] ON [Main Table].Customer = [Customer List].Customer) LEFT JOIN ManufacturingTracking ON [Main Table].[Unique Number] = ManufacturingTracking.UniqueNumber) LEFT JOIN MacysSKUNumber ON [Main Table].MacysUnique = MacysSKUNumber.MacysUnique
WHERE ([Main Table].[Actual Shipped Date]) Is Null;


0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses
Course of the Month8 days, 21 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question