Query in Access Comes up Blank

[euphoria5150]

Hi Guys,

I'm new to Access, but not so much on HTML/Java/Lingo/CSS, etc. But, I started a new job and they have a simple Access program, the function that doesn't work is the 'Lookup all jobs from this customer' It's a drop-down and click the 'view all customer' button, well... it comes up blank. I was wondering if the extra &..& have anything to do with it?! I've tried to tweak it a few times, no luck. All the other functions work; the database is acceptable other places. Below is the query - please help! - Thx, Russo

SELECT CustomerNo, ([FirstName]+" ") & [BusOrLastName] AS CustomerName, [tblContacts].[ConFirstName] & " " & [ConLastName] AS ContactName, tblJobs.CustomerPONo, tblJobs.JobDescription, tblJobs.OrderDate, tblJobs.DueDate, tblJobs.DoneDate, tblJobs.OfficeCopyNotes, tblJobs.ShopCopyNotes, tblCustomers.CustRowID
FROM (tblCustomers INNER JOIN tblJobs ON tblCustomers.CustRowID = tblJobs.CustRowID) INNER JOIN tblContacts ON (tblCustomers.CustRowID = tblContacts.CustRowID) AND (tblJobs.ContactIDRowNo = tblContacts.ContactIDRowNo) AND (tblJobs.ContactIDRowNo = tblContacts.ContactIDRowNo) AND (tblCustomers.CustRowID = tblContacts.CustRowID)
WHERE (((tblCustomers.CustRowID)=[Forms]![frmLookUpJobsByCustomer]![fldCustCode]))
ORDER BY CustomerNo;

[June7]

No, the field concatenation should not be an issue.

Exactly what is fldCustCode - textbox or combobox name? If it is a combobox, what is the RowSource?

[ranman256]

in immediate window, (ctrl-G), see if the combo gives the cortect value:
?[Forms]![frmLookUpJobsByCustomer]![fldCustCode]

[euphoria5150]

No, the field concatenation should not be an issue.

Exactly what is fldCustCode - textbox or combobox name? If it is a combobox, what is the RowSource?

----------------------

The fldCustCode is a list; The combobox is actually labled 'CustRowID_Label'

Row Source = SELECT tblCustomers.CustRowID, tblCustomers.CustomerNo, ([FirstName]+" ") & [BusOrLastName] AS Name, tblCustomers.AcctStatus FROM tblCustomers WHERE (((tblCustomers.AcctStatus)<>'delinquent' And (tblCustomers.AcctStatus)<>'inactive')) ORDER BY tblCustomers.CustomerNo;


Thanks,
Russ

[euphoria5150]

I couldn't seem to find the immediate window, like the older versions of Access. The value in the SQL view the set Criteria on the CustRowID is: [Forms]![frmLookUpJobsByCustomer]![fldCustCode]

Thanks,
Russ

[euphoria5150]

Some pics of what I'm looking at -

[orange]

Ctrl-G should get you the immediate window.

[June7]

So fldCustCode is name of combobox or is it CustRowID?

Is combobox bound? If you use a bound control to input filter criteria you will change data in record.

If you want to provide db for analysis, follow instructions at bottom of my post.

[euphoria5150]

CustRowID is the name of the combobox (drop-down)

It's unbound

Attached is the db and access file - the area that is broke is:

Jobs - Look up Jobs by Customer

https://qwf.sharepoint.com/:u:/s/Sol...pVOiw?e=04VNCt

[June7]

No, name of combobox is fldCustCode.

Might need to fix links in query. That circular linking could be confusing SQL engine.

I don't use dynamic parameterized query nor macros. I prefer VBA to build filter criteria. Review http://allenbrowne.com/ser-62.html

[euphoria5150]

I think it already is...

[June7]

Edited my post after downloading your db. You said CustRowID is name of combobox. It is not.

Records don't show because of query links. I changed to:

SELECT tblJobs.JobNo, ([FirstName]+" ") & [BusOrLastName] AS CustomerName, [tblContacts].[ConFirstName] & " " & [ConLastName] AS ContactName, tblJobs.CustomerPONo, tblJobs.JobDescription, tblJobs.OrderDate, tblJobs.DueDate, tblJobs.DoneDate, tblJobs.OfficeCopyNotes, tblJobs.ShopCopyNotes, tblCustomers.CustRowID
FROM tblContacts RIGHT JOIN (tblCustomers INNER JOIN tblJobs ON tblCustomers.CustRowID = tblJobs.CustRowID) ON tblContacts.CustRowID = tblJobs.ContactIDRowNo
WHERE (((tblCustomers.CustRowID)=[Forms]![frmLookUpJobsByCustomer]![fldCustCode]));

[euphoria5150]

Thank you so much June7! You're awesome!