Inserting into a field called ' Currency' problem

[Meltdown]

Hi all,

I have a database and their is a field called 'Currency' in a table (a foreign key to a Currency table).

Access is throwing an error when I try to run an insert into this field, (probably because it's a reserved keyword).

Short of renaming the field and all the reports it's linked to, can anyone tell me how I might do an insert into the field.

Thanks

 

[Brianwarnock]

Do you think it might help a bit if you told us how you are currently doing the insert and what the error is?

Brian

 

[Meltdown]

Hi Brian,

It's a DoCmd.RunSQL insert. Putting the Currency field into my sql string below brings up an 'Invalid Sql' error

  sqlstr = "INSERT INTO SupportContracts" & _
        " (DetailsID2, StartDate, EndDate, Expiry, SupportContractValue, Comments, AsAgreedinContract, SupportContractCommissionPercentage, DetailsID2New)" & _
        " SELECT " & rs2("DetailsID2") & ", StartDate, EndDate, Expiry, SupportContractValue, Comments, AsAgreedinContract, SupportContractCommissionPercentage, DetailsID2New" & _
        " FROM SupportContracts WHERE SupportContractID = " & rs1("SupportContractID")

        DoCmd.RunSQL sqlstr

 

[DCrake]

If you put a Debug.Print strSql before the RunSQL line what does the code look like?

If you think it looks allright then copy the statement to buffer and create a new query and paste the resulting sql to the query and look at it in design mode to check it for errors

David

 

[Meltdown]

OK I got this sorted, I totally forgot about bracketing the names of reserved keywords...so instead of using Currency, I use [Currency] and now it accepts the insert.

Thanks for the replies guys.

Regards
Melt

 

[namliam]

Thank god for using Reserved words!!!

This is one reason why you should avoid them...

 

[Brianwarnock]

It would also have been useful if he had posted the code in error rather than something different.

Brian

 

[gemma-the-husky]

the trouble is sometimes its not obvious what IS a reserved word. I've tried to pass a variable called option, and I think option must be a reserved word because in

function myfunc(option as long) as boolean

the argument option is not liked by the compiler

 

[SOS]

the trouble is sometimes its not obvious what IS a reserved word. I've tried to pass a variable called option, and I think option must be a reserved word because in

function myfunc(option as long) as boolean

the argument option is not liked by the compiler

Check this out:
http://www.allenbrowne.com/AppIssueBadWord.html