update query using parts of the date field

[mrrayj60]

I need to update bills with the checks number, both are called Invnum.
To match the checks to the bills I use the vendor, the amount,the gl account number and then the date. But some invoices have a date a few days before the check, so I want to use the date mmyyyy and would like to know how to change this code to match it that way. I have to convert it from mm/dd/yyyy. Thanks, Ray

UPDATE apchecks08 INNER JOIN apbills08 ON (apchecks08.VNUM = apbills08.VNUM) AND (apchecks08.[Vendor Name] = apbills08.[Vendor Name]) SET apbills08.INVNUM = apchecks08!INVNUM
WHERE (((apbills08.[Vendor Name])=[apchecks08]![Vendor Name]) AND ((apbills08.VNUM)=[apchecks08]![VNUM]) AND ((apbills08.TOTAMT)=[apchecks08]![TOTAMT]) AND ((apbills08.GLACC)=[apchecks08]![GLACC]) AND ((apbills08.DATE)=[apchecks08]![DATE]));

 

[ajetrumpet]

I need to update bills with the checks number, both are called Invnum.
To match the checks to the bills I use the vendor, the amount,the gl account number and then the date. But some invoices have a date a few days before the check, so I want to use the date mmyyyy and would like to know how to change this code to match it that way. I have to convert it from mm/dd/yyyy. Thanks, Ray

UPDATE apchecks08 INNER JOIN apbills08 ON (apchecks08.VNUM = apbills08.VNUM) AND (apchecks08.[Vendor Name] = apbills08.[Vendor Name]) SET apbills08.INVNUM = apchecks08!INVNUM
WHERE (((apbills08.[Vendor Name])=[apchecks08]![Vendor Name]) AND ((apbills08.VNUM)=[apchecks08]![VNUM]) AND ((apbills08.TOTAMT)=[apchecks08]![TOTAMT]) AND ((apbills08.GLACC)=[apchecks08]![GLACC]) AND ((apbills08.DATE)=[apchecks08]![DATE]));

hello again Ray, nice talkin to ya again.

use the format() function to get it done. look it up in help and check out the arguments.

 

[mrrayj60]

Thanks, found it, worked good. I appreciate the help.
Ray