Replies

Many thanks – works excellently (as usual)
John

Many thanks – works excellently (as usual)
John

Thanks Wendell – Hans was, as ever, most helpful. My train of thought changed as I was working through it and I need a fairly painless solution. I think I’ll leave the synthesizing for a date just after hell freezes over!

Thanks Wendell – Hans was, as ever, most helpful. My train of thought changed as I was working through it and I need a fairly painless solution. I think I’ll leave the synthesizing for a date just after hell freezes over!

Thanks for that – are there any other areas where the uk/us date is a problem? Is this “feature” corrected in Access 2002/2003 – it would seem logical to make it conform to the international date settings on the computer. Or maybe there IS a practical reason for this??

The full line of the promo query is now

Promo: DLookUp(“[PromoPrice]”,”ProductPromo”,”[PromoStart] = #” & Format([ReportDate],”mm/dd/yyyy”) & “# And [ProductID]= ‘” & [ProductID] & “‘”)

Now returns the correct value for any date of promo

I’ve got a little bit further to go with this query, but I think this has broken the back of it.

Many thanks

John

Thanks for that – are there any other areas where the uk/us date is a problem? Is this “feature” corrected in Access 2002/2003 – it would seem logical to make it conform to the international date settings on the computer. Or maybe there IS a practical reason for this??

The full line of the promo query is now

Promo: DLookUp(“[PromoPrice]”,”ProductPromo”,”[PromoStart] = #” & Format([ReportDate],”mm/dd/yyyy”) & “# And [ProductID]= ‘” & [ProductID] & “‘”)

Now returns the correct value for any date of promo

I’ve got a little bit further to go with this query, but I think this has broken the back of it.

Many thanks

John

Products aren’t on promotion all the time, so yes, I do need to synthesize data.
I’ve started to look at the problem another way – using dlookup to find if a promo record exists for the date and using it if it does.

I’m using:
Promo: DLookUp(“[PromoPrice]”,”ProductPromo”,”[PromoStart] = #” & [ReportDate] & “# And [ProductID]= ‘” & [ProductID] & “‘”) as one column of my query
and
pricetouse: IIf(IsNull([promo]),[productsellpricecase],[promo]) as the other

I guess i could merge these together, but haven’t yet.

My problem now is that the date between the # marks doesn’t work correctly.
I’m using Uk date formats DD/MM/YYYY, but the dates between #’s seem to only work in US date format.

I have a valid promotion for UK format 2/4/4 to 4/4/4 2nd April to 4th April, but it only matches if I use 4/2/4 (4th Feb in my language, but 2nd April in yours)

Should I use some other means of date selection?

John

Products aren’t on promotion all the time, so yes, I do need to synthesize data.
I’ve started to look at the problem another way – using dlookup to find if a promo record exists for the date and using it if it does.

I’m using:
Promo: DLookUp(“[PromoPrice]”,”ProductPromo”,”[PromoStart] = #” & [ReportDate] & “# And [ProductID]= ‘” & [ProductID] & “‘”) as one column of my query
and
pricetouse: IIf(IsNull([promo]),[productsellpricecase],[promo]) as the other

I guess i could merge these together, but haven’t yet.

My problem now is that the date between the # marks doesn’t work correctly.
I’m using Uk date formats DD/MM/YYYY, but the dates between #’s seem to only work in US date format.

I have a valid promotion for UK format 2/4/4 to 4/4/4 2nd April to 4th April, but it only matches if I use 4/2/4 (4th Feb in my language, but 2nd April in yours)

Should I use some other means of date selection?

John

I had tried the between [startdate] and [enddate] method, but the problem is more than just a simple selection process

I want to have both those that are on promotion and those that aren’t.
If product A has a normal price of

I had tried the between [startdate] and [enddate] method, but the problem is more than just a simple selection process

I want to have both those that are on promotion and those that aren’t.
If product A has a normal price of

Thanks for the advice regarding the Word testing.
One slight problem I’m still having with this routine is with regard to which folder has the focus (for want of a better word).
The start of the routine creates a client folder in C:Data eg. c:DataClientOne and a subfolder in this for the contract eg. C:DataClientOneContractThree. The files I’m creating are then stored in this structure.
During testing I was creating and deleting files, but found that I wasn’t able to delete a subfolder which I had just written to via the Access/Word routines. I could delete the Word file which I had just created, but not the folder in which it was contained.
I probably won’t need to delete anything in this manner, but was interested to know what is holding the folder to make it undeleteable
The lock is released when I close the Access database
John

Thanks for the advice regarding the Word testing.
One slight problem I’m still having with this routine is with regard to which folder has the focus (for want of a better word).
The start of the routine creates a client folder in C:Data eg. c:DataClientOne and a subfolder in this for the contract eg. C:DataClientOneContractThree. The files I’m creating are then stored in this structure.
During testing I was creating and deleting files, but found that I wasn’t able to delete a subfolder which I had just written to via the Access/Word routines. I could delete the Word file which I had just created, but not the folder in which it was contained.
I probably won’t need to delete anything in this manner, but was interested to know what is holding the folder to make it undeleteable
The lock is released when I close the Access database
John

Thanks very much for all your help with that Hans – works great. I’ll refine it further now that I have a few of the commands and have the gist of what is required to carry out each task.
Many thanks
John

Thanks very much for all your help with that Hans – works great. I’ll refine it further now that I have a few of the commands and have the gist of what is required to carry out each task.
Many thanks
John

Is there a method of forcing Word to maximise at the time of opening the document? It seems to be a bit hit and miss as to how it starts.

Also, how would I open a specific file, rather than creating a new one – I’m going to change the routine so that if the file exists it opens it, otherwise it creates a new file with the specified filename.