Replace: Data type mismatch in Criteria Expression

[kraigt84]

By no means am I an expert is Access Programming, but I am building a query to inspect and field and look for "null" data, then replace it with a field that has data in it.



I can get the query to find null data without issue, just cant seem to get the replace function to work.

I checked both fields and both data types are "text"

So far I have built the function:

Replace([Branch update]![Sector],Is Null,[Branch update]![Employee JobFamName])

Here is the SQL view of my query:

SELECT [Branch update].[Region Name], [Branch update].[Desktop Serial No], [Branch update].[Desktop Assignment], [Branch update].[Hardware Brand Name], [Branch update].[Hardware Model Name(3rd Level)], [Branch update].[Desktop Model Name], [Branch update].[Hardware Model Name(4th Level)], [Branch update].[Date Counter], [Branch update].[Desktop Last Scan Date], [Branch update].Userlogin, [Branch update].[Employe Status], [Branch update].[Employee Fullname], [Branch update].[Location Country Name], [Branch update].City, [Branch update].State, [Branch update].Address1, [Branch update].[Desktop IP address], [Branch update].[Desktop Disk Size(MB)], [Branch update].[Desktop Memory size (MB)], [Branch update].[Desktop Operating System], [Branch update].ModelcitidtEOS, [Branch update].ModelcitidtProductLaunch, [Branch update].[GTAM ID], [Branch update].[Name (Sub-model ofSub-model of)], [Branch update].[CATE Win 7 Supported], [Branch update].[Employee JobFamName], [Branch update].Sector
FROM [Branch update]
WHERE ((([Branch update].Sector)=Replace([Branch update]![Sector],([Branch update].Sector) Is Null,[Branch update]![Employee JobFamName])));

Any suggestion would be much appreciated!

[JoeM]

Take a look at the NZ function.
http://www.techonthenet.com/access/f...dvanced/nz.php
http://office.microsoft.com/en-us/ac...001228890.aspx

[kraigt84]

Okay tried that but now I am getting the records that don't have a "null" value

Nz([Branch update]![Sector],[Branch update]![Employee JobFamName])

[JoeM]

If you just want to return records where the Sector field is Null, then your criteria should look like:

WHERE [Branch update]![Sector] is Null

If you want to replace the value being returned using the NZ function, you would do that in that SELECT section of the SQL code.

If this is not what you want to do, then perhaps you could post a small sample and your expected output. That might clarify exactly what you are trying to do.

[kraigt84]

Please see the example below what I want to do is any where there is a blank "null" I want to move the information to the left of the yellow highlight in to the Sector column.

[JoeM]

I think you just want a simple IIF statement. Place it in your SELECT portion, NOT in the WHERE clause.

Sector:IIF(IsNull([Branch update]![Sector]),[Branch update]![Employee JobFamName],([Branch update]![Sector])