How to create combo box where 2 columns and use data from each?

[jmuirman1]

I HAVE A COMBO BOX. I ENTERED VALUES:

1ST COLUMN	2ND COLUMN
Amethyst	$100
CareGuard	$500

in my form I am able to choose either Amethyst of CareGuard from the dropdown list and it shows the corresponding value.

However, I want to create a query and report that shows the total charged for each item. In my query, I only see Amethyst, but not $100.

Any ideas?



Thanks,

John

[alansidman]

What does your sql statement for your query look like? Need to understand your query to advise.

[jmuirman1]

Not sure what you need. Where do i find the sql statement?

[alansidman]

With your query open in design view, in the upper left corner, there is a drop down. Click on it and select SQL. Copy your statement and paste it into this forum.

[jmuirman1]

SELECT [2015qNewCases].[Claim Number], [2015qNewCases].Claimantfirst, [2015qNewCases].Claimantlast, [2015qNewCases].[Date Closed], [2015qNewCases].tContacts_2.CFirstName, [2015qNewCases].tContacts_2.CLastName, [2015qNewCases].tContacts_2.CClient, [2015qNewCases].[Ametros or Amethyst Assigned?], [2015qNewCases].[Ametros Amount]
FROM 2015qNewCases
WHERE ((([2015qNewCases].[Ametros Amount])>0));

[alansidman]

Ok. Now from the original post, what is the name of the fields that you call column1 and column2 and what is the table name. Trying to determine where they fit into your SQL statement. We have two separate pieces of information but cannot see how they relate.

[jmuirman1]

Column 1: [2015qNewCases].[Ametros or Amethyst Assigned?]
Column 2 [2015qNewCases].[Ametros Amount]

the table is tnewcases, the query is 2015qNewCases

Let me know if you need anything else.

Thanks,

John

[alansidman]

I have recreated your query and table and can find nothing wrong with it. Perhaps your data (Amount) is not formatted as a number for Care Guard. Check to make sure it is actually a number and not input as text that looks like a number. Additionally, not sure how your combo box plays into this scenario. Can you share your database in an abridged manner so we can analyze exactly how the situation plays out because as it currently stands, I can see no issue with your query.