Get Field ID not Field Name in Query

[andy_dyer]

Hi,

I have this query

SELECT tblInput.Referrer, Count(tblInput.[Centre Code]) AS [Number of Referrals]
FROM [tblMental Health] RIGHT JOIN tblInput ON [tblMental Health].[Mental Health] = tblInput.Referrer
WHERE (((tblInput.[Referral 1 Received Date]) Between [Forms]![frmReportDates]![txtStartDate] And [Forms]![frmReportDates]![txtEndDate]))
GROUP BY tblInput.Referrer;

This picks up my Referrer ID instead of my Referrer Name.

It works ok in my form frmInput, as I can hide my ID column and it still displays the Name however because it is the ID that's stored in my table tblInput that is what my query picks up...

I know I missed something or mucked up the relationship, can anyone help me unmuddle this??

 

[dcx693]

I'm guessing
tblInput.Referrer
is the combo or list box holding the ID, but displaying the name.

If you want the name to be picked up instead of the ID, you can refer to the column in the list or combo box holding the name data.

Every combo or listbox has a .Column property. The first column would be Referrer.Column(0), the second Referrer.Column(1), etc... Just refer to the column you want using that type of expression.

 

[andy_dyer]

Hi,

tblInput.Referrer is the field that when viewed either in table or query mode shows the ID of the selection from the combo box.

I've tried adding a column into my query saying;

Referrer2: [forms]![frmInput]![cboReferrer]![Column(2)]

Doesn't seem to work...

 

[dcx693]

You don't need brackets around the .Column part of the expression:
Referrer2: [forms]![frmInput]![cboReferrer]![Column(2)]
and you need to use the . before the Column property. Try this:
Referrer2: forms!frmInput!cboReferrer.Column(2)
Generally, you only need brackets around object names if they have spaces within them (which itself is a bad idea).

And is column number 3 the column where the name data is in the combo box?

 

[andy_dyer]

I have no idea!!!

My whole query in SQL is:

SELECT tblInput.Referrer, Count(tblInput.[Centre Code]) AS [Number of Referrals], forms!frmInput!cboReferrer.Column(2) AS Referrer2
FROM [tblMental Health] RIGHT JOIN tblInput ON [tblMental Health].[Mental Health] = tblInput.Referrer
WHERE (((tblInput.[Referral 1 Received Date]) Between [Forms]![frmReportDates]![txtStartDate] And [Forms]![frmReportDates]![txtEndDate]))
GROUP BY tblInput.Referrer, forms!frmInput!cboReferrer.Column(2);

But it still isn't working as it does like the "forms!frmInput!cboReferrer.Column(2);" part...

WHY ISN'T ANYTHING EASY OR STRAIGHT FORWARD??

Thanks for your help though!!

Much appreciated!!

 

[dcx693]

OK, I think I see a concept error here. Did you write that SQL code yourself (or make edits to SQL code that Access generated)? I hope so, otherwise Access has gone nuts.

You cannot add form fields usins a SQL "SELECT" statement. (That's why when you go to design a new query, you can only add tables or queries to it.) You can reference form fields as parameters for the query, like in your WHERE statement where you reference "[Forms]![frmReportDates]![txtEndDate]".

If you need to get the information that is shown in forms!frmInput!cboReferrer.Column(2) , you need to add the underlying field to the query and then reference the field in the WHERE statement of your query.

WHY ISN'T ANYTHING EASY OR STRAIGHT FORWARD??

Well, first Access is a very flexible program. Unfortunately, that also means that there are tons of options. You sound like you're at the beginning stage of learning how to make non-simple queries and the process is unfortunately frustrating at first.

 

[andy_dyer]

Ok by moving that around and getting this code:

SELECT [tblMental Health].Referrer AS Expr1, Count(tblInput.[Centre Code]) AS [Number of Referrals], tblInput.Referrer
FROM [tblMental Health] RIGHT JOIN tblInput ON [tblMental Health].[Mental Health] = tblInput.Referrer
WHERE (((tblInput.[Referral 1 Received Date]) Between [Forms]![frmReportDates]![txtStartDate] And [Forms]![frmReportDates]![txtEndDate]))
GROUP BY [tblMental Health].Referrer, tblInput.Referrer
HAVING (((tblInput.Referrer)=[Forms]![frmInput]!cboReferrer.Column(2)));

I now get a different error but it still doesn't like the "Forms]![frmInput]!cboReferrer.Column(2)" part of things!!

Yes, I have just about got my head around simple straight forward queries, and am now being asked to get more and more complex data out of the database!! I have a very long way to go but unfortunately I am the person who knows most about Access for about a mile in any direction!!

 

[dcx693]

OK, I know I asked you this already, but is column number 3 the column where the name data is in cboReferrer?

 

[Travis]

When you have the frmInput open goto the immediate window (Debug window for those of us who have been around awhile) and type the following line:


?[Forms]![frmInput]!cboReferrer.Column(2)

What does it return? A string of text or a numeric ID?

From what I am reading the field tblInput.Referrer is an numeric ID field. This means that you will need to compare it with a numeric ID value to have the HAVING statement work.

 

[andy_dyer]

Where did you get column 3 from?

tblMentalHealth has two columns

ID and Mental Health

 

[andy_dyer]

Hi Travis,

That returns "Null"

My combo box is:

Row Source : SELECT [tblMental Health].[ID], [tblMental Health].[Mental Health] FROM [tblMental Health] ORDER BY [tblMental Health].[Mental Health];

Column Count: 2

Column Widths: 0cm;2.542cm

Bound Column : 1

I can't work out when i have something in the combo box why it is coming up Null??

 

[dcx693]

Where did you get column 3 from?

(You can't see this, but I'm shaking my head. )

Re-read my second post where I wrote:

Every combo or listbox has a .Column property. The first column would be Referrer.Column(0), the second Referrer.Column(1), etc... Just refer to the column you want using that type of expression.

 

[dcx693]

FYI: Pressing Control+G is a great shortcut to get to the Immediate Window!

 

[andy_dyer]

Ah ha!!

So I don't want Column(2) I want Column(1)...

If I use Travis's idea and point at Column(1) then I get the text out!

But my query:

SELECT [tblMental Health].Referrer AS Expr1, Count(tblInput.[Centre Code]) AS [Number of Referrals], tblInput.Referrer
FROM [tblMental Health] RIGHT JOIN tblInput ON [tblMental Health].[Mental Health] = tblInput.Referrer
WHERE (((tblInput.[Referral 1 Received Date]) Between [Forms]![frmReportDates]![txtStartDate] And [Forms]![frmReportDates]![txtEndDate]))
GROUP BY [tblMental Health].Referrer, tblInput.Referrer
HAVING (((tblInput.Referrer)=[Forms]![frmInput]!cboReferrer.Column(1)));

Still doesn't work!!

 

[dcx693]

Same problem as before? Or different?

 

[andy_dyer]

Same problem still doesn't like the syntax of

[Forms]![frmInput]!cboReferrer.Column(1)

as the criteria...

 

[dcx693]

Smack! (Sound of my hand hitting my forehead)

In the form design view, make sure the Column Count property is 2 (or greater, depending on how many columns you need to reference).

 

[Travis]

If your bound column is 1 and you want to reference the first field in the columns property you need to use 0

[Forms]![frmInput]!cboReferrer.Column(0)

This is what is refered to as a Zero Based Property.

 

[andy_dyer]

I think we've just gone full circle...

Yes there are two columns both in the table and the combo box on the form.

The combo box is bound to the the first column (ID)

The name is in the second column (Mental Health)

At the moment whenever I select a location from this combo box it places the ID and not the Name in the table.

This isn't a problem as long as I can convert it back to a name when I output back out of the table.

I just don't know how to do this.

 

[Rich]

Your combo is working correctly, you only store the ID for the record from the lookup, to retrieve the value use a query or refer to the second column in your combo