Field in Query Expression Displaying Number on Report

[sofeeyah]

Using Access 2007

I have a field, "state" on a table "invoice".

Have a data entry form "invoices" which has a combo box field "states" that points to the state table giving the user a list to choose from.

I have a report that generates an invoice. This report is based on a query that pulls data from 2 tables, "contract" and "invoice".

In the query I have an expression that concatenates a series of fields to make a sort of address block. This expression works much like the address block feature in Word, if a field is blank then access adjusts and does not show blanks lines, groups city state zip etc.

However, I have one small problem. If I add the "state" field to the report via the wizard or in design mode manually, the actual state name specific to the current record, displays.

But the same "states" field that is in my query expression displays a number when in print preview mode in the report.

It appears to me that when adding a field to a report in Access 2007 access automatically sets the following to ensure that the state name appears and not the number being stored:

bound column 1
columns 2
column width 0, 1

but i am guessing this is not happening in the expression and i have no idea how to write these types of instructions in my query so that instead of the number the expression will display the actual state name.

below is my current expression:

Expr1: IIf(IsNull([fnom]),"",[fnom] & " " & [lnom] & Chr(13)+Chr(10)) & IIf(IsNull([dept]),"",[dept] & Chr(13)+Chr(10)) & IIf(IsNull([luorg]),"",[luorg] & Chr(13)+Chr(10)) & IIf(IsNull([add1]),"",[add1] & Chr(13)+Chr(10)) & IIf(IsNull([add2]),"",[add2] & Chr(13)+Chr(10)) & [city] & ", " & [lustate] & " " & [zip] & Chr(13)+Chr(10) & [ctry]

I would really appreciated any help on this.

 

[lameid]

You should outer join to the states table on the "states" field and pull the state name from the states table and use that field in your expression. I like the convenience of getting the combo box on a form when it is embedded in the table but the query aspect of what is really there or not usually is not intuitive. Even knowing that it always stores the bound column doesn't mean at a glance you know whether it is the display text or the value you are looking at. Lucky for me when I cut my teeth on Access, I did not even know about the comboboxes in tables option.

 

[sofeeyah]

ok outer join??? i have not totally grasped the outer join inner join concepts...

so let me see if i understand

1) outer join

state table ->>> invoice table
statename field ->>>>> state combo box field

2) query expression

do not use "states" field from "invoice" table but "statename" field from lookup state table

did i get it right?

 

[lameid]

You should have all the values in the invoice table and the matches from the state table. Your arrow seems to indicate the opposite.

Otherwise I think you have it. It is a little fuzzy, if you switch your query to SQL view and copy and paste the SQL statement here, I can be sure.

 

[sofeeyah]

I am getting mismatch type expression what does that mean?

below is the sql:


SELECT IIf(IsNull([fnom]),"",[fnom] & " " & [lnom] & Chr(13)+Chr(10)) & IIf(IsNull([dept]),"",[dept] & Chr(13)+Chr(10)) & IIf(IsNull([luorg]),"",[luorg] & Chr(13)+Chr(10)) & IIf(IsNull([add1]),"",[add1] & Chr(13)+Chr(10)) & IIf(IsNull([add2]),"",[add2] & Chr(13)+Chr(10)) & [city] & ", " & [lustates.State] & " " & [zip] & Chr(13)+Chr(10) & [ctry] AS Expr1, Invoice.luOrg, Invoice.luState, luStates.stabb, luStates.state, Invoice.luStatus, Invoice.[3not], Invoice.[2not], Invoice.invdt, CTA.cta AS CTA_cta, CTA.irb, Invoice.cta AS Invoice_cta, Invoice.fnom, Invoice.lnom, Invoice.dept, Invoice.add1, Invoice.add2, Invoice.city, Invoice.luState, Invoice.zip, Invoice.ctry, [city] & ", " & [lustate] & " " & [zip] AS CityZip, CTA.protocol, CTA.title, CTA.Sponsor, CTA.PI, Invoice.inv, Invoice.amt, Invoice.trm, Invoice.luType, Invoice.stmp4, Invoice.stmp2, Invoice.stmp3, Invoice.stmp1, Invoice.luAdd1, Invoice.cro
FROM luStates INNER JOIN (CTA INNER JOIN Invoice ON CTA.cta = Invoice.cta) ON luStates.ID = Invoice.luState
ORDER BY Invoice.invdt, CTA.irb;

 

[lameid]

The from clause...

FROM luStates INNER JOIN (CTA INNER JOIN Invoice ON CTA.cta = Invoice.cta) ON luStates.ID = Invoice.luState

Should look more like the below. The Type mismatch means that the data type of two values/fields being compared are not the same. I am guessing it is the red text below.

FROM (CTA INNER JOIN Invoice ON CTA.cta = Invoice.cta) Left Join luStates ON [Red]luStates.ID = Invoice.luState[/Red]

I'm guessing that ID is not the bound column (1) in your combo boxes rowsource in the Invoice table for lustate field. You want that field instead.

 

[sofeeyah]

ok i think i have a problem with my tables and relationships...am i on the right path...so do i create a new field StateID on my invoice table an setup a relationship that links:

invoice.stateid
lustates.stateid

then a combo box form field on my form:

invoice.states

then in my query have the following tables

contracts
invoices
state

and then join the

invoice.states (field)

with what field? on what table?

 

[lameid]

What is the datatyp of the luState in Invoice?

What is the rowsource (table or SQL) of the combobox for lustate?

What are the fields and datatypes of the Row source?