Replace Field Name with Variable (SQL)

in my current procedure, I have string variable 'ConfirmRate',
in my sql section shown below, I need to replace the VATrate (select section) with ConfirmRate, I don't know how to do that.
your help is appreciated.

re: Replace Field Name with Variable (SQL)

I think you need to be more specific, I'm not sure what your problem is, my first reaction would be to try this:

But from looking at your code, you seem like you would know that, so if I am wrong please post more information.

-AJ

re: Replace Field Name with Variable (SQL)

Thank you AJ for your prompt response. You are working along the lines I am expecting. I tired the modification but it did not work.

In my fully working application, when an order is created, VATrate is applied to it according to the date period it was created.
if at a later date, the same customer repeats the order, I simpy duplicate it, including the VAT rate.
However, recently in the UK, there have been some VAT changes.
so now I wish to duplicate the order but apply the VAT rate applicable on the date period of duplication. current vat rate is 'ConfirmRate'

I hope I have explained myself better this time.

re: Replace Field Name with Variable (SQL)

AJ's response seems fine to me, assuming a numeric value is required.

Otherwise, please state which type of variable is required.

If this is not the problem perhaps you can post the relevant extract of the code that you've tried but have found to fail. I can only assume it's something else causing the problem.

Please include a clear explanation of any error.

re: Replace Field Name with Variable (SQL)

Unless I am reading this all wrong, which is very probable (LOL), you are trying to substitute an actual Value contained in the Variable ConfirmRate to a Field Descriptor in the Select Clause. If I am correct, then I imagine that this has to be a 2-Stage process, as in:

re: Replace Field Name with Variable (SQL)

Hello Adezii, you have understood correctly.

I have tried both options above, Record gets duplicated but I get the error '2001 - You cancelled previous operation' (VATRate never gets updated though).

here is the full Function. (It basically copies the main record and the sub-records) - Main record gets the new vat rate but the sub records don't.

Thanks in advance to all willing to help.

re: Replace Field Name with Variable (SQL)

I have added Code Tags and did a little reformatting. Will look at this closer tomorrow.

re: Replace Field Name with Variable (SQL)

A couple of items stick right out:

1. An Exit Sub should be placed between Lines 16 and 17, since there is no need to have the code fall through and process the Refresh Statements after Line #54 in Post #7.
2. Is the [Week] Field declared as an Integer, since the ThisWeek Variable is defined as such in Line #6 of Post #7.
3. Is the [ToDate] Field declared as a Date, since the LastDate Variable is defined as such in Line #7 of Post #7.
4. What exactly does the VATRateCheck() Sub-Routine do? You are passing this Routine 2 Dates and a Double. Are the Sub-Routine Parameters an exact match as far as Data Types?
5. ConfirmRate is declared in Line #9 but never initialized, yet it is passed (having no Value) to VATRateCheck() in Line #32 of Post #7, and then assigned to !VATRate in Line #33 where it again has no value.

re: Replace Field Name with Variable (SQL)

1. I could move the refresh statements above the end if Lines #53 post #7
2. Week is defined as Long Integer
3. TODate is Date/Time Data type
4. VATRATEcheck checks the VAT rate applicable between two dates -
5. I reading the CONFIRMRATE value back from the function VATRATECHECK

As you can see, I am duplicating a record with subrecords.
when a customers hires equipment between two dates, I manually enter all the items.
when the same customers returns at a later date and hires exactly the same equipement again, I simply duplicate.
Since the VAT rate changes (01/12/08) (and another one this year), when I duplicate I am carryiing over the old vat rate.
with my coding, above, main record take the new vat, subrecords do not.
I believe a change to line #43 post #7 is required.

I am wondering if I should add an SQL statment 'INSERT INTO' to the newly created record and change the vat value.
what is your suggestion?

re: Replace Field Name with Variable (SQL)

In the initial Post you indicate ConfirmRate is a String, then it is Declared Locally as a Double. It is then passed to a Public Function where this Parameter (placeholder) is a Variant. You 'Call' this Function which negates the entire purpose of a Return Value from a Function. Instead of the Function itself returning the Value of ConfirmRate by assignment (=), its value is sent back as an Argument. I imagine that this logic is only part of the problem, if not now then in the future. The only suggestion that I can make is that if you are willing to send me the Database to my Private E-Mail Address, I'll have a look at it when I get a chance. Other than that, I am basically dead in the water. Let me know either way.

P.S. - What Version of Access are you using?

re: Replace Field Name with Variable (SQL)

Tasawer,

It seems you missed my edit comment requesting you use the [ CODE ] tags when you post code.

Please make sure you read this, and use the tags in all such posts in future.

re: Replace Field Name with Variable (SQL)

to Adezzi, thanks for offering to look at my database. (post #10)
I have been to private messages but donot see a link for attachments. How do I send the file to yourself only please.

re: Replace Field Name with Variable (SQL)

I'll send you a Private Message (PM) with my E-Mail Address in it. Send it to that Address as an Attachment.

re: Replace Field Name with Variable (SQL)

The way the current Logic is structured, it appears as though you may need 2 independent events to accomplish what you are requesting, An Append to the Sub-Form (Extras) then an Update to same. See if this does the trick. Add Lines 7, 8, and 9 to this code segment in ReserveDup().

re: Replace Field Name with Variable (SQL)

Fantastic.. that works wonderfully. Thanks a million.
Now What about the double clicking to create new record.

re: Replace Field Name with Variable (SQL)

You are quite welcome. If you look back at Post #5, you'll see that we were on the right track - just lacked the specifics.
Did not happen for me.