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?
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 ConnectWise

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.