0

I have one table with the information of equipment and then another two linked tables 2 linked tables of Databases with images, one of them is full, hence the new one with new images that couldn't be put in the first. The connection between the images and the equipment is two fields, MARCA (brand) and MODELO(model). So I have to look up in which table is the image of the equipment. The null check is checking if looking at one I don't get an image, then go look in the other.I could do it fine with only one like this:

SELECT [Tabela Equipamentos ULSM Geral].*, [Fotografias e Manuais de Equipamentos2].FOTO
FROM [Tabela Equipamentos ULSM Geral] LEFT JOIN [Fotografias e Manuais de Equipamentos2] ON ([Tabela Equipamentos ULSM Geral].MODELO = [Fotografias e Manuais de Equipamentos2].MODELO) AND ([Tabela Equipamentos ULSM Geral].Marca_ = [Fotografias e Manuais de Equipamentos2].MARCA);

But with new one have to add it to so I'm using a case but it gives me a syntax error on it:

SELECT [Tabela Equipamentos ULSM Geral].*, CASE 
WHEN [Fotografias e Manuais de Equipamentos1].FOTO IS  NULL THEN [Fotografias e Manuais de Equipamentos2].FOTO 
ELSE [Fotografias e Manuais de Equipamentos1].FOTO
END AS FOTO
FROM [Fotografias e Manuais de Equipamentos2], [Fotografias e Manuais de Equipamentos1] INNER JOIN ([Tabela Equipamentos ULSM Geral] INNER JOIN [Fotografias e Manuais de Equipamentos2] AS [Fotografias e Manuais de Equipamentos2_1] ON ([Tabela Equipamentos ULSM Geral].MODELO = [Fotografias e Manuais de Equipamentos2_1].MODELO) AND ([Tabela Equipamentos ULSM Geral].Marca_ = [Fotografias e Manuais de Equipamentos2_1].MARCA)) ON ([Fotografias e Manuais de Equipamentos1].MODELO = [Tabela Equipamentos ULSM Geral].MODELO) AND ([Fotografias e Manuais de Equipamentos1].MARCA = [Tabela Equipamentos ULSM Geral].Marca_);

So what am I doing wrong in the CASE?

EDIT: Changed to the Iif, which became like this:

SELECT [Tabela Equipamentos ULSM Geral].*, 
IIf([Fotografias e Manuais de Equipamentos2].FOTO IS  NULL,[Fotografias e Manuais de Equipamentos1].FOTO, [Fotografias e Manuais de Equipamentos2].FOTO  ) 
FROM [Fotografias e Manuais de Equipamentos2], [Fotografias e Manuais de Equipamentos1] INNER JOIN ([Tabela Equipamentos ULSM Geral] INNER JOIN [Fotografias e Manuais de Equipamentos2] AS [Fotografias e Manuais de Equipamentos2_1] ON ([Tabela Equipamentos ULSM Geral].MODELO = [Fotografias e Manuais de Equipamentos2_1].MODELO) AND ([Tabela Equipamentos ULSM Geral].Marca_ = [Fotografias e Manuais de Equipamentos2_1].MARCA)) ON ([Fotografias e Manuais de Equipamentos1].MODELO = [Tabela Equipamentos ULSM Geral].MODELO) AND ([Fotografias e Manuais de Equipamentos1].MARCA = [Tabela Equipamentos ULSM Geral].Marca_);

If it helps, here's an image of the structure view

Structure view

Now there's no error but the query result is blank.

Micael Florêncio
  • 199
  • 3
  • 16
  • 3
    Access doesn't do `CASE` statements. You can use the inferior `Iif()` instead. – gvee Nov 19 '13 at 13:29
  • 1
    Or better would be to use `Nz` (the access function similar to SQL Server's `ISNULL` or the standard `COALESCE`) which will return its first argument, unless that is null, in which case it returns its second argument. – Damien_The_Unbeliever Nov 19 '13 at 13:39
  • You've changed which column you're checking for `NULL` against, in your edit (original was checking 1, `Iif` is checking 2) – Damien_The_Unbeliever Nov 19 '13 at 13:56
  • @Damien_The_Unbeliever Maybe it was my mistake explaining the situation well enough, I'll update it on the question too. Like I said I have 2 linked tables of Databases with images, one of them is full, hence the new one with new images that couldn't be put in the first. The connection between the images and the equipment is two fields, marca_ (brand) and modelo (model). So I have to look up in which table is the image of the equipment. The null check is checking if looking at one I don't get an image, then go look in the other. – Micael Florêncio Nov 19 '13 at 14:08
  • 1
    As I suggested in the comments to an [earlier answer](http://stackoverflow.com/a/19709053/2144390) you really should consider re-saving those [FOTO] images to reduce the bloat and see if you can consolidate the two files into one. Maintaining two separate files for (what should be) the same table in order to work around the 2GB limit is, and will continue to be, a nuisance. – Gord Thompson Nov 19 '13 at 14:53
  • @GordThompson So what you're saying is to use GIMP to open them in and exported them to PNG, right? I've been trying to work around that solution mostly because I'm working for a public institution on one of their computers exclusively with a lot of restrictions on it and a lot of bothersome bureaucracy to install anything. – Micael Florêncio Nov 20 '13 at 09:34
  • It's not necessary to use GIMP; I just used it because I had it handy. You can do a similar conversion in .NET itself using `System.Drawing.Image`. I just tried it on one of the test files you gave me a while ago: Before conversion the .mdb file (containing one OLE-wrapped JPEG) was 16.8 MB. After conversion, but before compacting, the .mdb file was 21.9 MB. After a Compact and Repair the .mdb file (now containing one raw PNG image) was 5.3 MB. – Gord Thompson Nov 20 '13 at 11:28
  • @GordThompson Already doing the conversion directly by code but having some new issues in it [here](http://stackoverflow.com/questions/20095591/external-exception-in-gdi-error-saving-specific-image) – Micael Florêncio Nov 20 '13 at 12:10

0 Answers0