Binary Operation in INCLUDEPICTURE Field Chris 11/2/2008 12:24:00 AM Hello, I'm trying to initiate a Mail Merge in Word 2007 from an Access 2007 database. In this database, there is an Image column which is actually just an integer. I'm using it as a Flag to indicate which of the 8 images are actually stored for a particular record. The path creation is rather simple, Static_Path\<<ID>>-<<Flag_Bit>>.jpg. So, in access, I would normally do something like If Me!Image And 4 Then //This image slot is being used End if This has worked well for Access. It's efficient and matches the project's objectives exactly. Unfortunately, I can't seem to get it to work in the Mail Merge process. I've tried the following with no success: {INCLUDEPICTURE "{IF {MergeField Image} And 4 "C:\\pics\\{MergeField ID}-4.jpg" "C:\\pics\\blank.jpg"}" } The error i receive is: "A field calculation error occured in record 1." And the field now reads { INCLUDEPICTURE "Error! Unknown op code for conditional." } To make sure it wasn't just me misunderstanding, I changed And to =, and the picture merged fine. This leads me to believe the And is the problem. So, is there some special trick to getting bitwise operations working with mail merge? Thanks for the help, Chris

Re: Binary Operation in INCLUDEPICTURE Field Peter Jamieson <pjj[ at ]KillMAPSpjjnet.demon.co.uk> 11/2/2008 10:07:37 AM 1. Generally speaking, Word needs to use { = } fields to do calculations etc. 2. Word's { = } field does not have bitwise operators AFAIK 3. You could try something like { INCLUDEPICTURE "{ IF { = mod(round({ MergeField Image }/4,0),2) } = 1 "C:\\pics\\{MergeField ID}-4.jpg" "C:\\pics\\blank.jpg"}" } (sorry if my arithmetic is wrong, but you get the idea). 4. If you are in a position to create queries in your Access database, it might be simpler to do the "and 4" operation in a query, either to return (e.g.) 0 or 1, or to return the complete pathname as an additional field, then use that query as the data source for your merge. -- Peter Jamieson http://tips.pjmsn.me.uk Chris wrote: [Quoted Text] > Hello, > > I'm trying to initiate a Mail Merge in Word 2007 from an Access 2007 > database. In this database, there is an Image column which is actually just > an integer. I'm using it as a Flag to indicate which of the 8 images are > actually stored for a particular record. The path creation is rather simple, > Static_Path\<<ID>>-<<Flag_Bit>>.jpg. > So, in access, I would normally do something like > If Me!Image And 4 Then > //This image slot is being used > End if > > This has worked well for Access. It's efficient and matches the project's > objectives exactly. > Unfortunately, I can't seem to get it to work in the Mail Merge process. > > I've tried the following with no success: > > {INCLUDEPICTURE "{IF {MergeField Image} And 4 "C:\\pics\\{MergeField > ID}-4.jpg" "C:\\pics\\blank.jpg"}" } > > The error i receive is: > "A field calculation error occured in record 1." > > And the field now reads > { INCLUDEPICTURE "Error! Unknown op code for conditional." } > > To make sure it wasn't just me misunderstanding, I changed And to =, and the > picture merged fine. This leads me to believe the And is the problem. > > So, is there some special trick to getting bitwise operations working with > mail merge? > > Thanks for the help, > Chris

Re: Binary Operation in INCLUDEPICTURE Field Chris 11/2/2008 5:05:01 PM 4. Sounds like the cleanest option, so I tried that, and hit another wall. I created the query, and used calculated fields to create the columns Image1-8 using a VBA function. I ran into problems using the ODBC, but saw other messages on this list about using the DDE option instead. I tried to do that, but received the following error message: "Unrecognized database format 'C:\Users\Administrator\Documents\My Data sources\+Connect to New Data source.odc'. I also tried to go from access 2007, and "Merge it with Microsoft Word." I received the following error: Could not find file 'C:\Dad.mdb'. (The path of the database is C:\Dad\MergedSales.accdb). I'll try suggestion 3, but It'd be nice to get this way working. Thanks a lot for your help so far, Chris "Peter Jamieson" wrote: [Quoted Text] > 1. Generally speaking, Word needs to use { = } fields to do calculations > etc. > > 2. Word's { = } field does not have bitwise operators AFAIK > > 3. You could try something like > > { INCLUDEPICTURE "{ IF { = mod(round({ MergeField Image }/4,0),2) } = 1 > "C:\\pics\\{MergeField ID}-4.jpg" "C:\\pics\\blank.jpg"}" } > > (sorry if my arithmetic is wrong, but you get the idea). > > 4. If you are in a position to create queries in your Access database, > it might be simpler to do the "and 4" operation in a query, either to > return (e.g.) 0 or 1, or to return the complete pathname as an > additional field, then use that query as the data source for your merge. > > > -- > Peter Jamieson > > http://tips.pjmsn.me.uk > > Chris wrote: > > Hello, > > > > I'm trying to initiate a Mail Merge in Word 2007 from an Access 2007 > > database. In this database, there is an Image column which is actually just > > an integer. I'm using it as a Flag to indicate which of the 8 images are > > actually stored for a particular record. The path creation is rather simple, > > Static_Path\<<ID>>-<<Flag_Bit>>.jpg. > > So, in access, I would normally do something like > > If Me!Image And 4 Then > > //This image slot is being used > > End if > > > > This has worked well for Access. It's efficient and matches the project's > > objectives exactly. > > Unfortunately, I can't seem to get it to work in the Mail Merge process. > > > > I've tried the following with no success: > > > > {INCLUDEPICTURE "{IF {MergeField Image} And 4 "C:\\pics\\{MergeField > > ID}-4.jpg" "C:\\pics\\blank.jpg"}" } > > > > The error i receive is: > > "A field calculation error occured in record 1." > > > > And the field now reads > > { INCLUDEPICTURE "Error! Unknown op code for conditional." } > > > > To make sure it wasn't just me misunderstanding, I changed And to =, and the > > picture merged fine. This leads me to believe the And is the problem. > > > > So, is there some special trick to getting bitwise operations working with > > mail merge? > > > > Thanks for the help, > > Chris >

Re: Binary Operation in INCLUDEPICTURE Field Chris 11/2/2008 6:07:01 PM In reference to 3, with one modification this formula worked. For anyone who is interested, The working formula is { INCLUDEPICTURE "{ IF { = mod(Int({ MergeField Image }/4,0),2) } = 1 "C:\\pics\\{MergeField ID}-4.jpg" "C:\\pics\\blank.jpg"}" } (Replaced round with Int) This was because if I had say 7 as the Image number (flags 1,2 and 3 up). 7 / 4 = 3.75 rounded to 4. 4 mod 2 = 0, so the function fails. By truncating the remainder off, the formula works fine. I'd still like to get the other way working though, since this requires a lot of changes for different paths, etc. "Peter Jamieson" wrote: [Quoted Text] > 3. You could try something like > > { INCLUDEPICTURE "{ IF { = mod(round({ MergeField Image }/4,0),2) } = 1 > "C:\\pics\\{MergeField ID}-4.jpg" "C:\\pics\\blank.jpg"}" } >

Re: Binary Operation in INCLUDEPICTURE Field "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 11/2/2008 10:59:49 PM OK, I really meant "use predefined VBA functions etc. in your query". If you use your own VBA function then you have no option but to use DDE to get the data. Because that creates other problems it is probably best avoided. Unfortunately, you cannot use VBA's "and" operator as a bitwise operator within a Jet SQL query - it is always treated as the logical operator. So you have to do something like SELECT (myfield\4) mod 2 AS [mynewfield], * FROM mytable Then you should be able to connect via OLE DB (the default - you may still be able to use ODBC but there are likely to be problems with the file extension .accdb ) (Just in case you are having problems getting VBA functions to work in SQL queries, you will probably need to ensure that your database is in a trusted location to get VBA functions, even when you are working within the database itself). Peter Jamieson "Chris" <Chris[ at ]discussions.microsoft.com> wrote in message news:B381F701-BEE8-4F5D-A11A-5FF085C3590D[ at ]microsoft.com... [Quoted Text] > 4. Sounds like the cleanest option, so I tried that, and hit another wall. > I created the query, and used calculated fields to create the columns > Image1-8 using a VBA function. I ran into problems using the ODBC, but saw > other messages on this list about using the DDE option instead. I tried to > do > that, but received the following error message: > > "Unrecognized database format 'C:\Users\Administrator\Documents\My Data > sources\+Connect to New Data source.odc'. > > I also tried to go from access 2007, and "Merge it with Microsoft Word." > I received the following error: > Could not find file 'C:\Dad.mdb'. (The path of the database is > C:\Dad\MergedSales.accdb). > > I'll try suggestion 3, but It'd be nice to get this way working. > > Thanks a lot for your help so far, > Chris > > > "Peter Jamieson" wrote: > >> 1. Generally speaking, Word needs to use { = } fields to do calculations >> etc. >> >> 2. Word's { = } field does not have bitwise operators AFAIK >> >> 3. You could try something like >> >> { INCLUDEPICTURE "{ IF { = mod(round({ MergeField Image }/4,0),2) } = 1 >> "C:\\pics\\{MergeField ID}-4.jpg" "C:\\pics\\blank.jpg"}" } >> >> (sorry if my arithmetic is wrong, but you get the idea). >> >> 4. If you are in a position to create queries in your Access database, >> it might be simpler to do the "and 4" operation in a query, either to >> return (e.g.) 0 or 1, or to return the complete pathname as an >> additional field, then use that query as the data source for your merge. >> >> >> -- >> Peter Jamieson >> >> http://tips.pjmsn.me.uk >> >> Chris wrote: >> > Hello, >> > >> > I'm trying to initiate a Mail Merge in Word 2007 from an Access 2007 >> > database. In this database, there is an Image column which is actually >> > just >> > an integer. I'm using it as a Flag to indicate which of the 8 images >> > are >> > actually stored for a particular record. The path creation is rather >> > simple, >> > Static_Path\<<ID>>-<<Flag_Bit>>.jpg. >> > So, in access, I would normally do something like >> > If Me!Image And 4 Then >> > //This image slot is being used >> > End if >> > >> > This has worked well for Access. It's efficient and matches the >> > project's >> > objectives exactly. >> > Unfortunately, I can't seem to get it to work in the Mail Merge >> > process. >> > >> > I've tried the following with no success: >> > >> > {INCLUDEPICTURE "{IF {MergeField Image} And 4 "C:\\pics\\{MergeField >> > ID}-4.jpg" "C:\\pics\\blank.jpg"}" } >> > >> > The error i receive is: >> > "A field calculation error occured in record 1." >> > >> > And the field now reads >> > { INCLUDEPICTURE "Error! Unknown op code for conditional." } >> > >> > To make sure it wasn't just me misunderstanding, I changed And to =, >> > and the >> > picture merged fine. This leads me to believe the And is the problem. >> > >> > So, is there some special trick to getting bitwise operations working >> > with >> > mail merge? >> > >> > Thanks for the help, >> > Chris >>

Re: Binary Operation in INCLUDEPICTURE Field Chris 11/3/2008 4:14:00 PM Ah, unfortunate. Unless I'm missing something, creating calculated fields via the SQL statement provides no benefit over the way I'm already using that works (Method 3). It's just doing the calculation and possibly creating the full image URL in the select statement rather than when it processes the INCLUDEPICTURE tag. This still relies on a static path in the Word document (which is okay, but undesirable if avoidable). Yeah, it'd be cleaner if I could get DDE working but it indeed appears to be quite temperamental. I dont' receive VBA macro warnings anymore when I open the DB and use it normally (I believe I disabled them), so I don't think that would cause the DDE problem. I guess I could just solve the static path issue by making another table with the normal path as a row. If you've got no other advice, Thanks again for all the help Peter. "Peter Jamieson" wrote: [Quoted Text] > OK, I really meant "use predefined VBA functions etc. in your query". If you > use your own VBA function then you have no option but to use DDE to get the > data. Because that creates other problems it is probably best avoided. > > Unfortunately, you cannot use VBA's "and" operator as a bitwise operator > within a Jet SQL query - it is always treated as the logical operator. So > you have to do something like > > SELECT (myfield\4) mod 2 AS [mynewfield], * > FROM mytable > > Then you should be able to connect via OLE DB (the default - you may still > be able to use ODBC but there are likely to be problems with the file > extension .accdb ) > > (Just in case you are having problems getting VBA functions to work in SQL > queries, you will probably need to ensure that your database is in a trusted > location to get VBA functions, even when you are working within the database > itself). > > Peter Jamieson >

Re: Binary Operation in INCLUDEPICTURE Field Peter Jamieson <pjj[ at ]KillMAPSpjjnet.demon.co.uk> 11/4/2008 7:49:07 AM > Unless I'm missing something, I don't think so. I guess you must have been doing something in your VBA that would add some value. Peter Jamieson http://tips.pjmsn.me.uk Chris wrote: [Quoted Text] > Ah, unfortunate. > Unless I'm missing something, creating calculated fields via the SQL > statement provides no benefit over the way I'm already using that works > (Method 3). It's just doing the calculation and possibly creating the full > image URL in the select statement rather than when it processes the > INCLUDEPICTURE tag. This still relies on a static path in the Word document > (which is okay, but undesirable if avoidable). > > Yeah, it'd be cleaner if I could get DDE working but it indeed appears to be > quite temperamental. I dont' receive VBA macro warnings anymore when I open > the DB and use it normally (I believe I disabled them), so I don't think that > would cause the DDE problem. > > I guess I could just solve the static path issue by making another table > with the normal path as a row. > > If you've got no other advice, > Thanks again for all the help Peter. > > "Peter Jamieson" wrote: > >> OK, I really meant "use predefined VBA functions etc. in your query". If you >> use your own VBA function then you have no option but to use DDE to get the >> data. Because that creates other problems it is probably best avoided. >> >> Unfortunately, you cannot use VBA's "and" operator as a bitwise operator >> within a Jet SQL query - it is always treated as the logical operator. So >> you have to do something like >> >> SELECT (myfield\4) mod 2 AS [mynewfield], * >> FROM mytable >> >> Then you should be able to connect via OLE DB (the default - you may still >> be able to use ODBC but there are likely to be problems with the file >> extension .accdb ) >> >> (Just in case you are having problems getting VBA functions to work in SQL >> queries, you will probably need to ensure that your database is in a trusted >> location to get VBA functions, even when you are working within the database >> itself). >> >> Peter Jamieson >> >