SQL syntax and functionality

Query...
PARAMETERS intID Short, intACHType Short, txtPlanNumber Text ( 255 ), curAmount Currency;
UPDATE tbl_ACH_Manual_Details SET tbl_ACH_Manual_Details.ACHType = [intACHType], tbl_ACH_Manual_Details.PlanNumber = [txtPlanNumber], tbl_ACH_Manual_Details.Amount = [curAmount], tbl_ACH_Manual_Details.[Date] = date()
WHERE (((tbl_ACH_Manual_Details.ID)=[intID]));

Before the above query runs the value for
tbl_ACH_Manual_Details.ACHType = 3
Which comes from a form cboType.Value

This table simply contains this data. ID is numeric and Type is Text.
tbl_lkpACHManual_Types...

ID    Type
1     Emergency
2     Domestic
3     International
4     N/A

Goal - We want the Type "International" to be retreived from tbl_lkpACHManual_Types table, which is the 3rd one. This 3 value comes from a form which has cboType.Value of 3

I believe the SET tbl_ACH_Manual_Details.ACHType = [intACHType], part of the query is what needs to be changed. How would I code this so that we get the value "International" in this case.

If cboType.Value is 1 then "Emergency" would be retreived
If cboType.Value is 2 then "Domestic" would be retreived
If cboType.Value is 3 then "International" would be retreived
If cboType.Value is 4 then "N/A" would be retreived

Thanks in advance
tesla764Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
You can use DLookup to retrieve the text value from the lookup table, based on the integer ID.
UPDATE tbl_ACH_Manual_Details SET tbl_ACH_Manual_Details.ACHType = dlookup("[Type]","[tbl_lkpACHManual_Types]",id=[intACHType]), tbl_ACH_Manual_Details.PlanNumber = [txtPlanNumber], tbl_ACH_Manual_Details.Amount = [curAmount], tbl_ACH_Manual_Details.[Date] = date()
WHERE (((tbl_ACH_Manual_Details.ID)=[intID]));

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tesla764Author Commented:
Off the bat this isn't working, but you at least pointed me in the right directio. I have something I can work with at least. I will figure this out.Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.